This was so helpful! I kept making the dumbest mistake. I forgot to hit control+shift while hitting the return key. For a solid five minutes I thought I was going to lose my mind.
Brian Many thanks for your video! would like to ask how would you deal in case not all of your assets have same data available. Let's suppose that there are hour bonds and for three bonds you have data for the last 250days however for the forth bond you have data just for the last 100 days (as is a new bond)? Much appreciated your reply.
If we have monthly returns and we are to compute an annualised covariance matrix in excel, how do we do that? I know how to generate the covariance matrix using the 'Data Analysis' function. However, how do I convert it to yearly figures as the returns are all monthly??
Hi Brian, very useful video. Thank you. I have a further question...given that you have a time series of stock prices and weights, could one calculate an index value for each period which is the sum product of the weights and stock returns in each period and then calculate the standard deviation of that index series? Would that be the same value as when we calculate the standard deviation of the portfolio via matrix multiplication of the variance-covariance matrix and weights?
Do you have any idea HOW MUCH YOU HAVE HELPED ME?! Thank You So Much. Really! Splendid. I did it after practice! And one thing, CTRL+SHFT+return is actually CTRL+SHFT+Enter as well. Both are the same. They are used to create flower brackets
Hello there, thanks for the upload. I have a couple of questions. 1. why there is a slight deviation when I use this formula to solve for the returns. stock close/stock open-1instead of ln. last q I have is regard to the matrice. why you don't subtract -1 at the end. thx
Hi Brian, Thanks for the video. I have the book, but I am unable to find where it shows how to calculate the matrix. Can you kindly tell me where it is?
not really. The excess returns are in this case the returns that deviate from the mean/average return. Hence he uses the actual returns and subtracts the average return to derive the excess returns. Does that make sense?
hey there, so something weird happens, I know this for sure because sometimes the cov of company A/company A does not equal the variance of company A ... for some cells it works and for some it doesn't do you have any idea why ?
Hi Ryan, See if you can get hold of Simon Benninga's text book - Financial Modelling. He provides the best overview of this material I have seen with lots of examples and spreadsheets.
+Adilah Hab You could interpolate the change over the missing days. Sometimes also you have to accept that your data is faulty and your results can only be interpreted in a limited way.
5 years later and this is so help for my coursework. God bless you sir
Glad it helped!
You saved my life, sir! Wish you health and success!
You're a life saver, sir. Thank you so much!!
Highly simplified and impactful.Thank you
Using ln(This Yr Price/Previous Yr Price) as Annual Rate of Return is something fresh and wonderful.
Why use ln(4.15/2.36) instead of (4.15/2.36)-1 for returns?
56.44% vs 75.84%
Same question here
Log returns rather than discrete returns. You can use either but continuous returns are handy when the periodicity is not annual.
Indeed the basics really important. Just nice. Thank you so much.
This was so helpful! I kept making the dumbest mistake. I forgot to hit control+shift while hitting the return key. For a solid five minutes I thought I was going to lose my mind.
so funny i read your comment and i kept on my mind " Ok. keep an eye on this guys mistake". Reality: Same mistake for 10 solid minutes.
Brian Many thanks for your video! would like to ask how would you deal in case not all of your assets have same data available. Let's suppose that there are hour bonds and for three bonds you have data for the last 250days however for the forth bond you have data just for the last 100 days (as is a new bond)? Much appreciated your reply.
So helpful. Thank you so much!
If we have monthly returns and we are to compute an annualised covariance matrix in excel, how do we do that? I know how to generate the covariance matrix using the 'Data Analysis' function. However, how do I convert it to yearly figures as the returns are all monthly??
Hi Brian, very useful video. Thank you. I have a further question...given that you have a time series of stock prices and weights, could one calculate an index value for each period which is the sum product of the weights and stock returns in each period and then calculate the standard deviation of that index series? Would that be the same value as when we calculate the standard deviation of the portfolio via matrix multiplication of the variance-covariance matrix and weights?
Do you have any idea HOW MUCH YOU HAVE HELPED ME?! Thank You So Much. Really! Splendid. I did it after practice! And one thing, CTRL+SHFT+return is actually CTRL+SHFT+Enter as well. Both are the same. They are used to create flower brackets
Please guide me, You take an average of return, why not you take Geometric mean of return.
Why are you calculation the excess return by return minus average return. I think instead of average return the risk free rate has to substracted?
Yes you are right, or security minus the Index return of the same period
Great video, very helpful. Thank you!
Thank you for this helpful video. But could you show how to calculate VAR for a simple stock index by variance covariance simulation?
Hello there, thanks for the upload. I have a couple of questions. 1. why there is a slight deviation when I use this formula to solve for the returns. stock close/stock open-1instead of ln. last q I have is regard to the matrice. why you don't subtract -1 at the end. thx
Thank you so much, Sir!
How would we calcualte sample co-variance matrix on excel?
this video very very very helpful, thank you sir
Hi Brian, Thanks for the video. I have the book, but I am unable to find where it shows how to calculate the matrix. Can you kindly tell me where it is?
Omg，thank you every much！very very thank you ！🎉🎉🎉
Ffhvrq1sv
Does anyone find his method of calculating excess return confusing?
not really. The excess returns are in this case the returns that deviate from the mean/average return. Hence he uses the actual returns and subtracts the average return to derive the excess returns. Does that make sense?
I did also find it confusing, because excess return is security minus the Index return of the same period. Or Security minus risk-free rate
How do you know which one is the stock price in yahoo finance because there are high low ?
thank you very much
could you link the excel sheet please
All used sample data to calculate the standard deviation, variance and covariance and so using n-1.
Hi, how can I do it with bond prices?
hey there, so something weird happens, I know this for sure because sometimes the cov of company A/company A does not equal the variance of company A ... for some cells it works and for some it doesn't do you have any idea why ?
I'm running into the same trouble ~~ did you figure out what went wrong?
LIFE SAVER !!!
Could you share this document as it is difficult to read?
Hi Ryan, See if you can get hold of Simon Benninga's text book - Financial Modelling. He provides the best overview of this material I have seen with lots of examples and spreadsheets.
Thanks!
Hi, what happens if there are missing data (prices) on some days?
+Adilah Hab You could interpolate the change over the missing days. Sometimes also you have to accept that your data is faulty and your results can only be interpreted in a limited way.
Brian Byrne Oh alright, thanks for your help!
much obliged broski
Thank you so much.
Always welcome
why did you divide it by 10?
n - 1 = 10
Thanks! I missed that.
is this always the case? sometime I see /n only
thanks for your video!
You saved me! Thank you!!! xoxo
Thanks!
standard deviation shouldn't be in percentages
The Variance is not a percentage. Standard Deviation is.