Variance/Covariance Matrix Add-in for Excel 2003 and Earlier

Tuesday, January 20, 2009

I recently created an add-in to create a variance/covariance matrix for Excel 2007. If you follow that link, you will be able to read about the add-in and how to use it. The purpose of this post is to announce that I have created the same add-in for prior versions of Excel.

Note that both versions of the add-in are now updated so that they will calculate either a population or sample variance/covariance matrix. They also now allow for the covariance matrix to be output to a worksheet that is different from the input range.

This version of the add-in works in Excel 2003. It should work fine in other recent versions, though I haven’t tested it in anything prior to 2003. It only differs from the Excel 2007 version in that it adds a Var/Cov Matrix menu item to the Data menu.

You can download the variance/covariance matrix add-in for Excel 2003, and also some test data. (Note: Internet Explorer users should right-click the link to the add-in and choose Save Target As. Then, change the file extension from .xls to .xla before saving.) As with the other version, the add-in can be used without charge for both individual and commercial purposes.

To install the add-in, go to Tools –> Add-Ins and then click the Browse button. Locate the cov-matrix.xla file and then click the OK button. If it installs successfully, you should now have the "Var/Cov Matrix” item on your Data menu.

The source code can be viewed in the VB Editor. 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.

Posted by .(JavaScript must be enabled to view this email address) on 01/20 at 12:38 AM

Permalink Tell-a-Friend

Comments

Hi
The Excel 2003 version seems to be an xls instead of an xla

Best Regards
Henning Hansen


Posted by Henning Hansen on 01/29 at 04:16 AM | #

Henning,

I’ve been struggling with this for a few days since I noticed the problem. If you are using Internet Explorer as your browser, it will want to save the add-in with a .xls file extension. This is a Microsoft problem.

As I understand it, IE “sniffs” the first several bytes of the file and uses what it finds to determine the file type. It completely ignores the MIME-type setting from the server and there is nothing that I can do to fix this behavior. So, despite the information from the server, IE thinks that it is an xls file.

The workaround is to use another browser (Firefox works properly), or to right-click the link and choose to “Save Target As.” Then when the Save As dialog box opens, change the file extension to .xla in the File name box.

The other thing that you can do is to simply change the file extension after you have downloaded it.


Posted by Tim on 01/29 at 11:43 AM | #

Thank you very much.

I chaged the extension to xla, and it is now working perfectly.

Best regards

Henning


Posted by Henning Hansen on 01/30 at 05:10 AM | #

Hi!

Very nice add-in, I was just looking for a way to dynamically create a variance/covariance-matrix.

I have one problem though, when I want to run the add-in it gives an error ‘can’t find project or library’ (it happens with Format*i, “#”).

What references should I load to avoid this error?

TIA

Cheers,
Willem!


Posted by Willem! on 02/27 at 02:10 AM | #

Well… commenting on my previous remark, I can think I can work around it. Just alter some code. No problem.

Really SUPERB add-in, I’m very VERY happy :D :D


Posted by Willem! on 02/27 at 04:10 AM | #

Willem,

I’m glad to know that you like the add-in. I haven’t seen this error, but I Googled it and found some instances where others have encountered it in their code. I couldn’t find a solution.

I don’t think it involves any references to external libraries because I don’t have any other than the defaults.

I would suggest changing this line:

LabelArray(i) = “Var ” + Format(i, “#”)

to this:

LabelArray(i) = “Var” + Str(i)

and see if that fixes it. The Format and Str functions basically do the same thing, but Format offers more flexibility (which isn’t needed here).

Update: More searching has made me think that it must involve a reference in some way. At least the error message and virtually all posts seem to point to missing or incorrect references. To check for this, go to Tools—> References and see if there are any problems or references to any Office 12 (2007) libraries. There shouldn’t be, but that might cause the problem. I’ve also uploaded a new version with the OLE Automation reference removed because it isn’t needed. That probably won’t fix the error though.

One other thing that you might try is to change the above line of code so that it explicitly references VBA:

LabelArray(i) = “Var ” + VBA.Format(i, “#”)

That might fix it.


Posted by Tim on 02/27 at 11:33 AM | #

Hi Tim,

Thanx, explicitly referring to VBA did trick. The same thing needed to be done with ‘Chr(13)’ (VBA.Chr(13))


By the way: did you notice that the maximum size of the Input Range is restricted? When I use an input range of 43R/73C it works just fine, but with a range of 43R/74C it fails (it returns a #VALUE!)

I’ve broken down the formula into little pieces, and the part of the formula which fails is:

=MMULT(TRANSPOSE(InputRange);(InputRange))

I’ve tested a bit around and the problem lies with the number of columns. I can use as many rows I want (tested up to 4690 rows), but I cannot use more than 73 columns.

I am constructing a giant (co)var matrix of the returns of all the assets my employer (a pensiun fund) invests in (165 in total), so I came across this issue.

Cheers,
Willem!


Posted by Willem! on 03/02 at 05:46 AM | #

Willem,

Thanks for pointing out this limitation. I wasn’t aware of the exact limit, but I knew there was one.

It turns out that the MMult function will return #VALUE! if the result exceeds 5,460 cells. Since we are dealing with a square matrix, taking the square root of that gives 73.89. So, the biggest number of variables is 73, exactly as you found. The number of observations (returns for the securities) should only be limited by available memory.

This limit isn’t mentioned (that I could find) in the help file. I found it on this Microsoft Support page.

I don’t see any easy workaround for this limit. To use this add-in you would need to create several covariance matrices and then recombine them. Much better, I think, to do it completely in VBA. For that I think that you are going to need your own matrix manipulation code. I’m pretty sure that you can find it on the Internet somewhere.

An alternative would be to simply construct the matrix using the Covar function. When I get a chance, I may update the add-in to allow that as an option. It would definitely get around the size limitation.


Posted by Tim on 03/02 at 11:10 AM | #

Hi Tim,

I’ve just mailed you an alternative to create a dynamic (co)variance matrix.

Cheers,
Willem


Posted by Willem! on 03/04 at 03:15 AM | #

Hi

this is definately usedful i would like to know how to used the matrix and calculate the value at risk in absolute terms


Posted by Desmond on 04/08 at 04:26 AM | #

Desmond,

VaR is too complicated to explain in a comment, so I’ll give you a link to look at:

Calculate VaR in Mathematica

That PDF shows how to calculate VaR using several methods in Mathematica. You should be able to translate that into Excel. For example, to arrive at their first result under “A Really Simple Example” you would use the following formula:

=100-NORMINV(0.01,110,30)

That will give you the 1% VaR of 59.79044.


Posted by Tim on 04/08 at 12:17 PM | #

Is the array limited to 73 variables in Excel 2007?  If so, I wonder if Microsoft plans a “fix”?


Posted by Billy Gee on 06/11 at 12:11 PM | #

Billy, yes the limit applies to Excel 2007. I don’t know why the limit exists, but I suspect that larger matrices would cause performance and memory issues. Maybe Microsoft will lift the limit in the 64-bit version of Excel 14, but I doubt it. As long as their is a 32-bit version of Excel, I suspect that they will want to maintain perfect compatibility.

If you need larger matrices, then I think you either need to write your own VBA code or use a more appropriate tool (such as Matlab).


Posted by Tim on 06/11 at 12:26 PM | #

Thank you for developing and sharing this, and for being so easy to find on google smile


Posted by Dylan on 07/05 at 05:57 AM | #

Commenting is not available in this channel entry.