Generating the Variance-Covariance Matrix
ฝัง
- เผยแพร่เมื่อ 5 ก.ย. 2024
- This is a follow-up video to a video posted previously by Dr. Colby Wright explaining how to execute mean-variance portfolio optimization in Excel. This video demonstrates how to generate the variance-covariance matrix, which is necessary in order to calculate the portfolio standard deviation. Dr. Wright also demonstrates how to transform the var-cov matrix into the correlation matrix.
Working on my MBA and you just saved me on my Finance project!!! You are great at teaching....keep up the great work!!! Thank you!!!
Colby, you are a god among doctors. Thank you for this video!
Thanks Dr. Wright for the conceptual clarity! Using correlation to figure out the diversification in our portfolio, i think is the key concept in the entire portfolio optimisation exercise. Which I realised and understood through your explanation & example at end of the video. Really appreciate Sir!!
this is by far the clearest instruction I found on the Var-Cov matrix. Thanks!!
Dr. Wright,
Your videos are very helpful and i'm learning so much from them because you explain the concepts really well and make it easy to follow along.Thank you so much.
One of the greatest video I ever watched. Thanks so much
Thanks Dr. Wright - Great pace and clarity!
YES!!! After 5 hours of work I nailed it! Thanks a lot! Very helpful and should upload some more of these! Again Thanks a lot
YOU ARE A STAR! BEST VIDEO on this TOPIC.
Very well presented. It was easier to understand the concepts seeing the video.
Thank you very much.
Hi Colby, I found this video hugely useful for my portfolio theory and management project. Thanks heaps mate!! It made my project super easy.
Ok Tx Dr Wright. At the moment I am not really into portfolios (although I already saw a video how to combine stocks of positive correlation with stocks of negative correlation to protect your investments 💰💰💰💰💱💲💳🏦🏧) but at the moment I am into correlations concerning patients suffering from the corona virus admitted to the hospital against people that died due to corona in order to dig out anything that could show that a number of people are left to die outside the hospital. Which here in the Netherlands appears to be happening to the age group of 80+, while some of them are already in elderly care homes, but still.... So I am studying that.
This is really helpful.Thanks Dr
Hi. I just wanted to thank you for this video - a part of my math exam in my quantitative finance master is an actual portf.optim. in EXCEL and this video really helped me...so - thanks :).
Dear Colby Wright, as columns standard deviations were not known a-priori and were estimated from data, the covariance matrix is defined as [1/(n-1)]*... and not as [1/n]*... .
Thank you very much for this video Mr. Wright, it has been very helpful!
thank you so much, Mr Wright , that was very useful
That video was great. I went to High school (TimpView) by BYU. Thanks for the upload.
Thank u so much, Dr. Wright, It's amazing and really useful
Smirchi, if the diagonal values in your correlation matrix are .9X, subtract 1 from N. I figured out the problem I had (may be the same as yours), I did the =count function for the N returns, and it gave me 1 more than it should have, despite having the exact rows selected. You can double check your N by subtracting the min from the max row numbers of the excess return data.
And thanks for the reply, wrigh.
Thank You very much Dr. Wright. It was very helpful. I am going to follow these methods into my project. Even though it asked only co variance matrix but i think i will add correlation matrix too since it elaborates the study. :)
You should use the geometric mean instead of the average function, when the asset return highly varies, there is a difference between the both of them that may lead to losses.
Aside from that it´s a great and very usefull video.
Isidoro Manrique Fernández you use arithmetic if you are predicting the future using the historical data.
Great video , one thing though we all know that buying beaten up stocks usually have better returns , not always I know,stocks that are doing well are usually over priced.
seriously, who and why would anyone dislike this video.
Thank you Colby, excellent tutorial!
Great video! Thank you for posting!
Thank you very much Sir.... Eternally grateful to you....!
sir, you saved my life. Thanks a lot!
State 1 73 -71.8
State 2 -71.8 74.76
State 3 #N/A #N/A
State 4 #N/A #N/A
Im getting that when I apply the CMD-Shift-enter.
Thanks! Very helpful and very good! Just a quick question. Is there a reason why you divide by n (which is sample size) not n-1 in your variance-covariance computations? If you treat the 5 year data as a sample, wouldn't it be dividing by n-1? Thanks again!
Excellent video
Really helpful! Keep up the awesomeness!
Very well explained ... Thank You
great teaching!
Thanks a lot Dr. colby for the good video its is very usfull. If you do not mind kindly guid us to a reference or joural with includes those formulas. Thanks in advance
thank you Colby. Could you please upload videos on efficient frontier calculation? It would be helpful
That was very helpful. Thanks !
This is so good
Hi Colby, thank you for your tutorial video. but I am confusing about your excess return, according to my class information, the excess return should be return - risk free rate? would you like to give more details on that? Many thanks!
Dr. thank you that you are helping us i appreciate you very much. please explain that how to make an efficient frontier of N assets and also by putting the constraint of short selling on two or more assets...?
Thank you so much for your work!
Super helpful! Thank you!
just use the LN function on the prices, it gives you the return (i.e =LN price1/price2)
Hello Dr Colby, your video has been immensely helpful to me, I really appreciate it. Just to verify however, has I've understood it's N-1, why have you used 60 has N as opposed to 59?
Many thanks.
This video was very usefull for me, im very gratefull. Im concerned about the returns. Why did you put them with natural logarithm.?
Thank you.
I'm having the same issues. 61 price periods so 60 return periods (n=60). I suspect it is because we used the stdev function which uses the "n-1" method to account for sample pop. I'm not 100% sure though. When i use n=59 to divide XTX, 1s in the correlation matrix diag appear. Massive kudos to Mr Wright in making this video! Helped heaps. Does anyone know why this is so?
I tried this on my work but the formula does not copy down vertically. take your example: R4 = J4 - J64. but my next line is R5 = J5 - J65 (suppose to be J64).
Could you show me how to correct the mistake. THank you very much Dr.
do you mean, it doesn't populate to the rest of the vector or matrix automatically? if this is your question please try pressing Ctrl+shft+enter instead of pressing enter button for the execution of the formula. we need to do this because of matrix operations
I noticed you have a Monte Carlo Simulation add-in on your Excel. Do you like the functionality of that particular add-in... and if so, can you refer me to the site you got it from? Also noticed that (as of the video date) you don't have the FRED add-in from the St. Louis Federal Reserve... go to their site and the add-in is free... it's a powerful tool that lets you instantly download all fed data and global data too. Thanks! Your videos are an excellent resource!
In my texts Excess Returns is defined as the return over the risk free rate. In this example you are using excess returns as the return over the market rate. Will thsi result in a different outcome in terms of optimizing the portfolio?
The excess returns defined in this example are simply the differences from the mean to calculate the covariane (or variance of a single asset). The correct definition of excess return would indeed be the asset's return minus the risk-free rate.
@@Moonborne2689 So is this excess returns is wrong? thanks!
very very well done!
What function has been used for the VAR and STD DEV? Population or sample based?
It is population based.
Found: www.macroption.com/population-sample-variance-standard-deviation/
By googling: population or sample standard deviation finance
Example 1: Population Variance and Standard Deviation
Question: What is the standard deviation of last year’s returns of the 12 funds I have invested in?
There is no estimating or forecasting in this task. I am only interested in the 12 funds I have invested in and I don’t care about the thousands of other funds which exist in the world. My population is only these 12 funds. I have all the data available, as it is very easy to find these 12 funds’ performance data.
I take the performance of each of the 12 funds in the last year, calculate the mean, then the deviations from the mean, square the deviations, sum the squared deviations up, divide by 12 (the number of funds), and get the variance. Then the square root of variance is the standard deviation. In this case, because I have the data for the whole population available, I call them population variance and population standard deviation.
Example 2: Sample Variance and Standard Deviation
Question: What is the standard deviation of last year’s returns of equity funds in the world?
Compared to calculating standard deviation of concretely specified 12 funds, I now want to know the standard deviation of returns of all equity funds in the world. My population is now much larger than in the previous example. There are thousands of equity funds in the world. Some of them probably aren’t on the Bloomberg, don’t have a website, and don’t publish their performance. In short, I have no chance that I could get the data for all the funds. And even if I could, it would take a long time and cost a lot of money to get all the data.
Contrary to the previous example, I now don’t have all the data available and I will have to estimate the population’s standard deviation from a sample.
I have the same question! and thanks for the videos.
Thank you very very much sir.
Hi Dr. Colby, I have really enjoyed your videos which are greatly assisting me with some study assignments. Unfortunately the picture quality on this module is poor, blurry. Any chance of tidying it up? Regards David
I am following all the steps described above but in my xtransposex matrix I get a number like 5.15968E-05. Where is the problem? Is it possible that some of my log returns returns are zero and thus my matrix is compromised? Also,in my variance-covariance matrix allmost all numbers are similar to the one above. If there is a simple logical explanation to this issue i would very much appreciate it if you enlighten me please?
Thank you in advance
Very good! You show the correlation of each asset with another asset, but how can I measure the correlation of all the assets together against the S&P500 for example? Can I put a weight of each asset of my portfolio and find a global correlation? regards
Thank you! It was very useull!
Hi Dr. Wright, Thanks for the video. Could you say how to generate the inverse of the matrix only, excluding the duplicates?
AWESOME VIDEO! thanks you!
What is the difference in terms of result of doing the Analysis Function and the matrix Algebra? Why Analysis Function are not recommended?
this was super helpful!!!
to calculate the correlation, how did you get the standard deviation? is the st. dev of the return or the adjuested close price?
Is the model intended for an original data set with Prices to be on the X-Axis, and Assets along the Y-Axis? The reason I ask is I notice you use a tX*X as opposed to the instructed XtX.
Hi again
I sorted my previous problem out but now I have another one. My correlations do not add up to one and I suspect that the scalar is wrong. Can please explain what is it exactly? Cause I am using the number of log returns as n. THANMKS
If you use monthly prices and returns, does it mean that the results are monthly standard deviation and monthly variance?
Hi Colby, why is arithmetic average used instead of geometric average in calculating the average return? Thanks!
For the excess returns that you have calculated. Why have you taken away the average and not the US T-bill?
Dear professor Colby Wright,
I have one question, is it the same if I take DATA ANALYSIS in excel and take CORRELATION function or COVARIANCE function to get the matrices?
very good
N should be the # of periods of returns. That would be the number of months of price data you have, minus one (since you cannot calculate returns for the first month of price data. Perhaps, you were counting months of price data as your N, which translates into N-1 observations of returns
any video of how to calculate the principle components of a variance covariance matrix ?
did you use Close or Adjust Close data for the equities?
For some reason I need to use N-1 to get a proper correlation Matrix. Does that really matter? (And no, not counting the N of prices, it's the N of the returns)
Why your variance for SPY in var-cov matrix differs from variance for SPY under "returns" table?
hello could you tell me how to find the variances of returns if we know the covariance matrix and mean matrix in excel please?
I'm trying to create an optimal portfolio mix for Indian EQ. However I'm getting data on daily or weekly basis for last 10 years. Would it be necessary to roll upto monthly level or is continuing with daily or weekly returns a good idea?
Also, I must thank you for intriguing my inner fire to really see an end to create my own portfolio weights rather than relying on broker research. I always had the ideas to create an optimal mix, I was just lacking a sharp tool. Thanks a lot for sharpening that vital element.
Dr. wright in previous message i asked about that how to make an efficient frontier of n assets and you suggests me about it but i try my best and failed. please help me i m now stuck off at this stage and my master thesis work has been stopped till now due to this problem please please tell me that how to construct an efficient frontier of N risky assets. i m waiting your kind reply
Why to use LN? Why not simply =price1/price2 - 1? Are the results much different? Could anyone explane it to me? Thanks!
can u suggest the way to generate a precision matrix ( inverse of a matrix)
Good stuff!
When dividing by n, that assumes population size. Is your dataset here the whole population or a sample of data (n-1) from the population?
I think it is 60 because 5 years x 12 months for N observations
Thank you. But is this the population size or sample size as you have to account for that. Otherwise, it should be 59 Observations, not 60.
Thank you Sir
Great Video! why do we use 'excess returns' instead of returns though?
+Ed Mel Look at the equation in the slide being shown at 6.40 in the video, the variance-covariance matrix is attempting to solve for the covariance of each asset which involves subtracting the mean return of the asset from each observation of it. This will ultimately show how each asset "covaries" with the others being considered, and represents how (and by how much) the asset moves relative to the others in the matrix.
In my mind, the answer is always some function that I've not yet heard of.
hey colby, i was wondering how you calculated the 'Returns' table. The only data I have is the prices of each asset for the last 5 years. Is it possible to calculate the 'Returns' table by using the solver function, i.e. by setting the diagonal of the correlation matrix equal to 1?
Great video! Thx
Return means Change of Price between two period, in % value.
Example: Close Price on 20th January vs Close Price on 19th January. Calculate the difference of both. Then, divide it with Previous Close Price (19th jan) to get the % value.
Hi,
You have indicated that you do not like to use the data analysis tool in Excel to generate the var/covar matrix - may I know why?
It is far easier and quicker than the matrix solution you demonstrate. I use Data Analysis for producing the var/covar matrix and was not aware of any limitations or problems - Can you help me understand your position?
Thanks.
for me the data analysis tool doesn't take into account changes in the stock data, thats why for my model i use this method to allow for those changes.
Daymond Goulder-Horobin
Oh, Okay. That makes sense. So, what you are saying is that your method is dynamic where the use of the analysis tool requires a recalculation every time a return changes. I understand - thank you for the clarification.
Best regards, Robert.
Hi Dr. Write! I want to transfer expected return (ER) and standard deviation (SD) of assets from daily to monthly form. should I multiply SD by 30 as well as ER?
~20 trading days in a month
Keep in mind the standard deviation should be multiplied by the square root of the number of days.
thanks Sacrius & horlacsd
I don't Know why i don't get the ones on the correlation matrix pls help me
oh god. thank you thank you so much :)))
thanks a lot.
How can you calculate Standard Deviation and Total Return for this portfolio?
+Stealth_Bomber_B2 you can calculate SD by typing "=stdev.p()" this formula,
any way we can get the excel sheet please ?
Please make more videos.
PLEASE I NEED HELP. HOW to you lock in the row copy and paste down? That would help me a alot thank you!
+James Lam Type ($) in the middle of the col-row cell
e.g. you want to hold cell H15
you type H$15
Thank you my friend
It's simply (Price B - Price A) / Price A
Can we use =covar in excel to do the var/covar matrix?
Chen Bai Yes. But if you can't do it by hand, you don't know nothin.
Don't use the covar function. That's for newbs. Use coveriance.p and covariance.s.
Can someone explain how to get the data?I've tried with Yahoo Finance but the valuations I get are different. Any hint?
I've checked the historical prices on Yahoo Finance and indeed, the values differ from the video, not sure what changed.
If you have access to Datastream or Bloomberg I'd recommend that, allows for way more datatypes and history.
I have one question why LN and not Log for feturns/
LN is a log base e (~2.718....), the LN is useful because it is the "base of compounding" it scales growth perfectly. They are time-consistent (you can for example just add ln returns for T=1,2,3,4,5 and it will equal the 5-period ln return). Log assumes base 10, which is okay for graphing and programming, but it is not standard for financial assets. We prefer to use bases that transition to very small (instintaneous) timescale.
Just using the standard convention for continuously compounded returns. Where returns are continuously compounded, the correct way to calculate returns is ln(Pt/Pt-1).