Five years ago (wow, it has been a long time) I wrote an add-in to create a variance/covariance matrix in Excel. The purpose of that add-in was to do something that Excel should do, but doesn't: create a "live" variance/covariance matrix.
For some reason, Excel only places formulas along the diagonal. So, I wrote an add-in that used the matrix algebra functions to create a covariance matrix that would change if you changed the data. At that time, I used the matrix functions to do this because I wanted to be able to create a probability-weighted covariance matrix, and...Continue Reading
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...Continue Reading
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...Continue Reading
I’m knee-deep into work on the 5th edition of Financial Analysis with Microsoft Excel, but I feel the need to make this post while I’m thinking about this stuff. I have finally figured out how to create high quality screenshots of various Excel dialog boxes and other components. This is something that I’ve been trying to get to work for a long time, but I had to rely on the artists at my publisher to spruce up screenshots in the past. I’m no artist, so I didn’t figure this out on my own. I got the instructions from the graphicPUSH blog, and I did some experimenting of my...Continue Reading