Just wanted to come here and say thank you for providing such an easy and straightforward way for transposing the matrix. Efficiency in Excel is not celebrated enough.
instead of creating a third matrix with the if statement around 7:00 you could have copied special the second into the first using copy special -> skip blanks, couldnt you?
Can you just copy the first covariance matrix then transpose it. The copy the transpose and paste it into the original covariance matrix but select paste special and skip blanks?
Great video, just a question about Step three, shouldn't we divide the XTX (step 2) values by D-1 which would be 251 (252-1)? Can you also not just use =Covar(array1,array2) to get the covariance?
Great video I have an assignment where I need to find the optimal portfolio of 20 stocks using two years of historic data, then test the portfolio in a performance testing period for the next year. ie. Find optimal portfolio using data from 2016-17 and test the portfolio in 2018. I am getting really high weights in the portfolio where short selling is allowed for some of the scenarios after using solver. Would you be able to help me out? Thanks
If you cannot see the data analysis tab, you will likely need to go to options, then to add ins, and then select the data analysis add in. This should install it in excel.
@@financemark The formula shouldn"t change, right! The order of the matrices matter. BTW, did you mean T rows for N columns! Could you please check your email for me? How do you construct the covmatrix from a fit results on a data supposing that you got 9 parameters in total? Cheers.
It makes a difference because if you have a T*N matrix, you will need to transpose it to N*T. Otherwise, your resultant matrix (XX') would be. T*T matrix, which would be incorrect
If you have any tips for construcing a covariance matrix, drop those in the comments below
Just wanted to come here and say thank you for providing such an easy and straightforward way for transposing the matrix. Efficiency in Excel is not celebrated enough.
Thanks! Glad it was helpful
Thank you very much! it's very helpful.
YOOO, I WAS LOOKINGGGGG FOR A F*CKING WAY TO FILL THE COVARIANCE MATRIX THANKKKKKKKKK
YOUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU UNDERATEDDDD
instead of creating a third matrix with the if statement around 7:00 you could have copied special the second into the first using copy special -> skip blanks, couldnt you?
Can you just copy the first covariance matrix then transpose it. The copy the transpose and paste it into the original covariance matrix but select paste special and skip blanks?
Great video, just a question about Step three, shouldn't we divide the XTX (step 2) values by D-1 which would be 251 (252-1)?
Can you also not just use =Covar(array1,array2) to get the covariance?
In my understanding, covariance matrix is Xtranspose * X. Why did you divide that by n? Thanks for the answer!
it's analogous to dividing by n when caculating variance
Great video
I have an assignment where I need to find the optimal portfolio of 20 stocks using two years of historic data, then test the portfolio in a performance testing period for the next year. ie. Find optimal portfolio using data from 2016-17 and test the portfolio in 2018. I am getting really high weights in the portfolio where short selling is allowed for some of the scenarios after using solver. Would you be able to help me out?
Thanks
In which spreadsheet , you’ve done this matrix? I'm not getting data analysis option in office 2013, 2016 & 2019.
If you cannot see the data analysis tab, you will likely need to go to options, then to add ins, and then select the data analysis add in. This should install it in excel.
How did you get that 252?
Update : Oh i got it
how did you get that?
@@cindyyeo6419 252 is amount of stock data in this video
@@yusataslim3666 How? 52 X 5 = 260 then how 252?
You use X^T.X formula, but the correct one is otherr way around. en.wikipedia.org/wiki/Covariance_matrix. I couldn't get it .
It depends on how your data . If you have N assets, you want a N*N covariance matrix. Here, my X matrix is T*N (ie To rows for N assets), hence X'X
@@financemark The formula shouldn"t change, right! The order of the matrices matter. BTW, did you mean T rows for N columns! Could you please check your email for me? How do you construct the covmatrix from a fit results on a data supposing that you got 9 parameters in total? Cheers.
It makes a difference because if you have a T*N matrix, you will need to transpose it to N*T. Otherwise, your resultant matrix (XX') would be. T*T matrix, which would be incorrect
You got 252 rows, but you said 250 in the begining. :) That's ok.
you couldve just copied and psted the transposed work in the first method. you did extra work for nothing