We use a matrix of variances and covariances of different securities all the time in finance. There're many Matlab routines for building them, but sometimes you just want to do a simple one in Excel.
Timothy Mayes has posted a brilliant VBA add-in for making VCV matrices. It can make them weighted by probability or unweighted, and its uses live arrays in Excel so that the VCV matrix updates when you update the data.
You can find the add-in at http://www.tvmcalcs.com/blog/
I've got a slightly less elegant approach, which works by giving each column of returns for your VCV matrix a name and then using the Indirect() function in Excel to build out a VCV matrix. By building a matrix with rows and columns labeled the same as the names of returns, the covariance between any two columns becomes =covar(indirect(colName1),indirect(colnName2)). Every field in the VCV matrix can have the same formula; the diagonal is just the covariance of one column with itself, which is just another way of describing the variance.
It's easier to look at an example than explain it. Paste this field into a browser and download the spreadsheet. There's a version that pulls live data from Yahoo finance using the SMH functions from Randy Harmelink. If you don't have those installed in your Excel, you can get them from http://finance.groups.yahoo.com/group/smf_addin/ . If that doesn't work, just look at the third sheet, which does not have live data.
Paste this link into your browser:
https://docs.google.com/leaf?id=0BzmdxHsBfTsVNzNkM2RjYjQtOWFjZi00YjE0LThmMzMtMTY3MGE2YTA3ZTA1
If you can't be bothered to install the VCV Matrix add-in, this makes for an elegant solution.
No comments:
Post a Comment