| Stock Covariances |
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?
Yes, and it looks like this:
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?
I was getting to that!
When the data has all downloaded, you'll see the matrix. It'll look like this:
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?
To annualize a monthly standard deviation, we (normally) multiply by the square root of 12 ... since there are twelve months in a year.
(See: square root of time.)
>So?
So that's all I wanted to say.
>Excellent!
Just one more thing. Although the various terms in the matrix give the covariance between two assets, one can detemine the
Pearson Correlation from that info. We just need to divide the covariance by
the product of the two standard deviations and ...
>So why doesn't the spreadsheet do that?
Uh ... it does.
>Are we finished?
Yes ... uh, well, not quite ...
| Daily Stuff |
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?
The pretty pictures: 
