While playing with BlackLitterman stuff, I realized that we'd need to calculate a covariance matrix for a bunch of stocks (or other assets), so I ... >So you made up a spreadsheet, eh?
Click on picture to download the spreadsheet You type in the Yahoo symbols for your assets (praying that Yahoo has three years worth of monthly returns), click a button and ... >So where's the covariance matrix?
Since the covariances for monthly returns are pretty small, they're multiplied by 1000. Note, too, that the diagonal elements of the matrix (coloured a darker blue) are the variances of the individual assets. Remember: the volatility (or standard deviation) is the square root of the variance. The first asset (in the above example, it's ^GSPC, the S&P500) has a variance of 0.000633 so the volatility is (0.000633)^{1/2} = 0.0252 which'd make the annualized volatility 0.0252*SQRT(12) = 0.087 or 8.7%. >Huh?
>Are we finished? Yes ... uh, well, not quite ...
That's just a few dozen prices, right? I've had requests for three years worth of daily data, so there's another spreadsheet which'll do that. Click on stockcorrelations2.xls to download. >What else is different?
