Covariance Matrix in Excel Tutorial

This video covers how to construct the covariance matrix in Excel. I cover two ways to do it: (1) the covariance function in Excel, and (2) matrix multiplication in excel using Excel.

The Covariance matrix itself is useful in myriad fields, especially in portfolio optimization, where it can be used when undertaining mean variance optimization and minimum variance optimization.

Time stamps:
* 0:00 Introduction
* 0:47 The Covariance matrix
* 3:02 Calculation using the Covariance function in Excel
* 7:29 Calculation using Matrix algebra in Excel

#Covariance #CovarianceMatrix #Excel

Courses:

****************************
*MY GEAR
****************************

[financemark]

If you have any tips for construcing a covariance matrix, drop those in the comments below

[kurtislidstrom]

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.

[mouradmadouni]

Thank you very much! it's very helpful.

[kevinzhu]

YOOO, I WAS FOR A F*CKING WAY TO FILL THE COVARIANCE MATRIX
UNDERATEDDDD

[winstonwithay]

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

[naufalammar]

In my understanding, covariance matrix is Xtranspose * X. Why did you divide that by n? Thanks for the answer!

[PUMALIFE]

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?

[khyentseyozer]

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?

[fariazarin]

In which spreadsheet, you’ve done this matrix? I'm not getting data analysis option in office 2013, 2016 & 2019.

[arnaldo]

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?

[yusataslim]

How did you get that 252?
Update : Oh i got it

[spyhunter]

You got 252 rows, but you said 250 in the begining. :) That's ok.

[drek]

you couldve just copied and psted the transposed work in the first method. you did extra work for nothing