Variance/Covariance Matrix Add-in for Excel 2007

Wednesday, January 7, 2009

Update: Please also see the variance/covariance matrix add-in for Excel 2003.

Update 2: New features added on February 7, 2009. Please see end of post.

Update 3 (October 24, 2014): New features are coming very soon in version 2.0. It is complete, except for testing. Watch this blog for details and download. The new version will require Excel 2010 or later. I should have it posted within a few days. Because of the coming update, I have renamed the previous versions and updated the links in the posts.

Excel doesn’t always do everything that you might want, or it doesn’t do it in a way that is sufficient for your needs. That’s why we have VBA and add-ins. I finally decided to do something about one problem that has been bothering me for years. The variance/covariance matrix is very important in finance. In particular, it is needed to calculate the standard deviation (volatility) of a portfolio, and is used to calculate the beta of an asset. It also has numerous uses in other fields. For this reason, Excel provides the Covar() function and also the Covariance tool in the Analysis ToolPak add-in. While Excel’s built-in tools are perfectly functional, they don’t always work in exactly the way that I would like. In particular, the Covariance tool doesn’t create a “live” variance/covariance matrix, so if you change the data you have to recreate the matrix. It is this point that prompted me to write my own add-in that solves what I perceive to be the shortcomings of Excel’s offerings.

There is another problem with the Covar() function and the Covariance tool: They don’t calculate a weighted variance/covariance matrix. This is important to me because we often use probability distributions of potential asset returns to evaluate optimal portfolios (or generate an efficient frontier). So, in these circumstances we need to weight the potential returns by their probability of occurrence. Excel’s tools can’t handle this situation, unless you use array formulas. My new add-in will create a weighted variance/covariance matrix if you supply the weights. If you don’t give it any weights, then it assumes equal weighting and works much like Excel’s built-in tools.

Calculating Covariance

Covariance is similar to the variance, which measures average squared deviations from the mean for a single variable. Covariance, as the name suggests, measures how two variables co-vary, and it is calculated very much like the variance. In fact, the (single variable) variance is really just the covariance of that variable with itself. The equation for covariance is usually presented as:

or, we can show it in matrix form as:

where D’ is the transpose of the difference matrix, D is the difference matrix, and N is the number of variables. The difference matrix is simply a matrix of each observation less the mean. Using Excel’s matrix functions, we can calculate the covariance function in one swift move with:

 

=MMult(Transpose(Data – Mean), (Data – Mean))/N

 

where Data is your range of data, and Mean is an array of means for each variable. That is pretty straightforward if you have pre-calculated the means. Your formula might end up looking something like this (array entered):

=MMult(Transpose(B2:D11 – B12:D12), (B2:D11 – B12:D12))/10

assuming that your data (3 variables, 10 observations each) are in B2:D11, and the means are calculated in B12:D12.

Now, if we need to calculate a probability-weighted covariance matrix, we would slightly modify that to:

=MMult(Transpose(A2:A11*(B2:D11 – B12:D12)), (B2:D11 – B12:D12))

assuming that the probabilities are located in A2:A11. Note that we aren’t dividing by 10, because that assumes equal probability for each outcome (1/10). Instead, we are allowing for the probabilities to differ. Also, note that your means need to be probability-weighted averages (expected values), instead of just an equal-weighted average.

That is the background. When you run this add-in, you will notice that the formula looks much more complex. The reason is that I wanted the covariance matrix to be completely self-contained, so I calculated the means within the formula. Also, for the equally weighted (you might think of it as unweighted) covariance matrix, I actually generate an array of weights within the formula. So, the formula created by the add-in will be ugly and difficult to follow. Just think of the above equations if you are trying to understand it.

The Variance/Covariance Add-in

The cov-matrix.xlam add-in works with Excel 2007 and mimics Excel’s Covariance tool, except for some additional features. Here is a screen shot of the dialog box:

Just like Excel’s tool, it allows you to specify an input range with the data grouped by columns or rows and, optionally, allows you to have data labels in the first row or column. It also allows you to specify the upper-left corner of the output range. Those are the similarities.

My add-in is different in that it allows you to specify a range that contains weights (probabilities) to create a weighted variance/covariance matrix. Note that the weights must sum to 1, or else you will get an error message. Also, the weights must have the same orientation as the data in the input range. This is because the add-in uses the MMult() and Transpose() functions to create the matrix, and if the orientations don’t match the formula would return a #VALUE error. If you don’t specify weights, then this add-in will create either a population (same as Excel) or sample variance/covariance matrix.

Also, unlike Excel’s Covariance tool, this add-in allows you to specify whether to show the entire matrix, or just the upper or lower portion. Since a covariance matrix is symmetric around the diagonal, you only need to see half of it and that is all that Excel provides. My add-in always fills in the entire matrix, but it can hide part of it by setting the font color equal to the background color. This is valuable because having the entire matrix makes other calculations (e.g., portfolio standard deviation) much easier.

Finally, this add-in allows you to have a title above the matrix and it also has a Reset All button to reset everything to the defaults. The output will look something like the picture below:

But wait, there’s more! The add-in creates a live variance/covariance matrix, so you don’t need to recreate it if you edit your data. It will update automatically. Excel’s Covariance tool only enters formulas along the diagonal (for the variances), but not for the covariances. I always found that particularly strange as they could just as easily insert the Covar() function into those off-diagonal cells.

You can download the variance/covariance add-in for Excel 2007 from this link. I have also uploaded a workbook that contains some data for demonstrating or testing the add-in.

The add-in is provided without charge for any usage, even commercial. To install the add-in click the Office button and then the Excel Options button. At the bottom of the options dialog box, set Manage to “Excel Add-ins” and then click the Go button. Finally, browse to the location where you saved the add-in and load it. When installed and loaded, you will find the following button on the Data tab:

Click that button to run the add-in.

The source code can be viewed from within the VBA editor. Basically, the code shows the dialog box, validates the input, and the constructs a string that will be entered as an array formula on the worksheet. This creates a formula in your worksheet that is independent of the add-in.  There are no user-defined functions in the output, so once you have created the variance/covariance matrix you no longer need the add-in unless you add additional data points or variables.

Please let me know if you find any bugs, inconsistencies, or just things that irritate you. Of course, I’d like to know if you find it useful as well.

Known Limitations: Because this add-in creates an array formula that uses the matrix functions, it is limited to 73 variables at most.

Update: On January 19, 2009 the add-in was updated. It will now calculate either a population or sample variance/covariance matrix. I also fixed a small validation bug that does not affect the results.

Update 2: On February 7, 2009 the add-in was updated to allow for the output to be on a different sheet than the input range. Previously, that would work but the formula would result in an error. The variance/covariance matrix add-in for Excel 2003 has been updated as well.

Update 3: On June 13, 2009 I uploaded a new version of the add-in. I implemented the fix that was mentioned in this comment.

If you have previously downloaded the add-in, you may wish to download it again.

Posted by .(JavaScript must be enabled to view this email address) on 01/07 at 05:01 PM

Permalink Tell-a-Friend

Comments

Hi, The add-in is great! Exactly what I’ve been looking for.
There is one problem I am encountering: When I specify data from one sheet for input range and then specify a different sheet for the output range, instead of covariance/variance values I am getting a #VALUE error.
In order to keep it tidy I really need to be able to keep the returns data on a different sheet.
Please help me.
Thanks a lot in advance.
PS. When I produce the matrix in the same sheet and then copy-paste the result to my desired sheet (just tried to overcome the initial problem)I get the same problem- #VALUE.


Posted by Bobosher Sharipov on 01/20 at 02:02 PM | #

Bobosher, thanks for pointing that out. I’ll have to look over the code and see if I can fix that. The problem is that the sheet name is being dropped from the ranges when the formula is pasted in.

I’ll fix that as soon as I can and post a new version here, and e-mail it to you.

As a workaround, you can do a single Find and Replace. Select one of the formulas and copy the data range address (e.g., $B$2:$E$6). Now, select the covariance matrix and then do the Find and Replace:

Find What: $B$2:$E$6
Replace With: Sheet1!$B$2:$E$6

and then click the Replace All button.

Be careful with the sheet name if it has a space. In that case, you will need do surround it with single quote marks. So, say that the sheet name is My Returns then you would do this:

Find What: $B$2:$E$6
Replace With: ‘My Returns’!$B$2:$E$6

This workaround isn’t ideal, but it works and only takes a few seconds.


Posted by Tim on 01/20 at 05:41 PM | #

The add-in has been updated to rectify the problem that Bobosher pointed out. It will now allow you to put the variance/covariance matrix on a sheet that is different than the one that contains the input data.

If you previously downloaded the add-in, I suggest that you download it again. Just save it on top of the previous version and you shouldn’t have to do anything else.


Posted by Tim on 02/07 at 04:59 PM | #

How can one calculate variance/covariance in excel vba?

Many thanks


Posted by Tina on 02/09 at 06:19 PM | #

Tina,

The easiest way is to use Excel’s built-in functions. I assume that you need this for some other code, so you could use this line of code:

Application.WorksheetFunction.Covar(Range(“Sheet1!A5:A7”), Range(“Sheet1!B5:B7”))

Obviously, you would change the ranges to those appropriate for your needs. Also, you can use the VarP (or Var) function in place of Covar if you just want the variance. If you do that then you only supply one range.

Note that Excel’s Covar function calculates a population covariance, if you need the sample covariance then multiply by N and divide by N-1.


Posted by Tim on 02/09 at 11:03 PM | #

Wow this add-in is sweet thanks!


Posted by Mark on 03/27 at 12:05 PM | #

That’s what I like to hear! grin Thanks for the feedback, Mark.


Posted by Tim on 03/27 at 12:18 PM | #

Hi!
I just wanted to know what it means that the add-in is limited to 73 variables. (My first language is danish and unfortunately not english!) That does not mean that I can’t have more than 73 observations does it?

    best regards!


Posted by Helene on 04/06 at 09:32 AM | #

Helene, you can have as many observations as you want. For example, you could have up to 73 stocks, each of which has, say, 500 (or more) returns.

Here is a Danish translation from Google translate (I don’t know if it will make sense):

Helene, du kan have lige så mange indlæg, som du ønsker. For eksempel kan du have op til 73 bestande, der hver især har, siger, 500 (eller flere) afkast.


Posted by Tim on 04/06 at 10:16 AM | #

Thank you so much!
This is genious! It is so much easier than the ordinary way to construct a covariance matrix! Thanks a lot for your help, I truly appreciate it smile


Posted by Helene on 04/06 at 11:17 AM | #

Hey!

Great tool! I used it for some time now but had problems with Microsoft Office Live which I uninstalled. After that, I cannot use the addin anymore because some library (in the VBA tool under references) is apparently not enabled. Which one do I have to activate in order to have the tool working again?
Thanks!


Posted by Jan on 06/13 at 12:52 AM | #

Jan,

Thanks, I’m glad that you like it and I’m sorry for the problem. I don’t know exactly what is causing it, but I think that you can find the fix in a comment to the post on the other version.

Basically, you will need to edit the code just a bit. Let me know if you don’t know how to do that.


Posted by Tim on 06/13 at 02:45 AM | #

Anybody having the missing library problem will want to know that Jan was able to fix the problem by re-installing Office Live. Apparently, un-installing that add-in disabled or removed something important, though I still don’t know what it might have been.

The add-in works fine with Office Live installed, or if you never installed it in the first place. So, if you have Office Live installed, please don’t remove it. A recent update to Office Live may (who knows?) have rectified the problem.


Posted by Tim on 06/26 at 11:41 AM | #

Commenting is not available in this channel entry.