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.
Wednesday, September 15, 2010
Monday, September 6, 2010
Excel in Finance Lab
In this lab, we're going to provide a very brief overview of using Excel in the MFRM program.
First, we're going to look at Excel and how to organize a workbook. The emphasis here will be on the finance industry convention of using the first worksheet for assumptions and to explain the structure of the worksheet.
Second, we're going to discuss cell references, from the simple a1 format, to locked rows and columns (a$1:A$27), and named ranges.
Third, we're going to talk about getting data from outside sources, like Bloomberg or financial web sites, into Excel.
Finally, the "lab" part: you are going to build a financial calculator in Excel. If somebody finishes early enough, they'll have a chance to show their financial calculator to the class and earn a gold star.
First, we're going to look at Excel and how to organize a workbook. The emphasis here will be on the finance industry convention of using the first worksheet for assumptions and to explain the structure of the worksheet.
Second, we're going to discuss cell references, from the simple a1 format, to locked rows and columns (a$1:A$27), and named ranges.
Third, we're going to talk about getting data from outside sources, like Bloomberg or financial web sites, into Excel.
Finally, the "lab" part: you are going to build a financial calculator in Excel. If somebody finishes early enough, they'll have a chance to show their financial calculator to the class and earn a gold star.
Sunday, September 5, 2010
Download this file for Finance Excel Lab
In order to complete today's lab, download this spreadsheet. This spreadsheet has an introduction, a discussion of cell references and range names, information on how Excel uses dates, and today's lab task, which is to be build a calculator that calculates the future value, given
Your calculator should find the future value based on different compounding. The compounding can be
You will use the concept of continuous compounding in every aspect of finance.
Paste this link into your browser:
https://docs.google.com/leaf?id=0BzmdxHsBfTsVNWMzYzk2YTctYjczYy00ZmVkLThiY2QtZDNmMzdhYWI3ODA3&hl=en
- a present value
- a starting date
- an ending date
- an interest rate
Your calculator should find the future value based on different compounding. The compounding can be
- annual
- monthly
- hourly... etc.
- and CONTINUOUSLY
You will use the concept of continuous compounding in every aspect of finance.
Paste this link into your browser:
https://docs.google.com/leaf?id=0BzmdxHsBfTsVNWMzYzk2YTctYjczYy00ZmVkLThiY2QtZDNmMzdhYWI3ODA3&hl=en
Saturday, September 4, 2010
Download file on Getting Data into Excel
Download this file which has instructions and examples about getting financial data into Excel.
Past this link into your browser to download the file:
https://docs.google.com/leaf?id=0BzmdxHsBfTsVMTM4ZjJmOTMtOGE1NC00MTFjLTg0MTAtZjZlZTMwOTEzM2Vl&hl=en
Past this link into your browser to download the file:
https://docs.google.com/leaf?id=0BzmdxHsBfTsVMTM4ZjJmOTMtOGE1NC00MTFjLTg0MTAtZjZlZTMwOTEzM2Vl&hl=en
The easiest way to get Bloomberg data into Excel
There are many tools for getting Bloomberg data into Excel. The easiest way is using Windows copy-and-paste. I've got a short, low-quality video of doing exactly that.
If you make a graph of a price in Bloomberg, Use the Copy, Copy data to Clipboard commands, you can then paste the data into Excel.
This solution is not as elegant as using the API provided by Bloomberg, but it's very flexible and fast!
The video demonstration of this technique is at:
http://glennpowers.fileave.com/EasyDataFromBloomberg.wmv
If you make a graph of a price in Bloomberg, Use the Copy, Copy data to Clipboard commands, you can then paste the data into Excel.
This solution is not as elegant as using the API provided by Bloomberg, but it's very flexible and fast!
The video demonstration of this technique is at:
http://glennpowers.fileave.com/EasyDataFromBloomberg.wmv
Subscribe to:
Posts (Atom)