While playing with Black-Litterman 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%.
>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 stock-correlations2.xls to download.
>What else is different?