The correlation is one of the most common and useful statistical analysis tools that you can use in financial modeling and analysis. A correlation is a number that describes the degree of relationship between two variables.
Excel’s CORREL function enables you to compute correlations by returning the correlation coefficient of 2 cell ranges. You can use the correlation coefficient to determine the relationship between two properties, which may provide basis for pair trade arbitrage opportunities in stock market investing. For example, you can examine the relationship between the stock price movements or returns of 2 separate listings of the same stock, or the stock prices / returns of 2 separate companies in the same industry.
Although Excel’s CORREL function is able to compute correlations for individual data ranges, there is no easy way to compute a matrix of correlations, i.e. when more than 2 variables need to be analyzed together at the same time. This is often the case when working with financial data, when, for instance, we need to analyze the returns of a basket of stocks to determine their dependence (or independence), and more importantly relative attractiveness and relative performance to date.
In this article I will explain how to use a correlation matrix and also provide an Excel macro in VBA that I developed. This Excel macro, named CORRMATRIX, computes the correlation matrix directly and inserts easily into any standard Excel spreadsheet.
Correlation Matrix: A Statistical Definition
Many methods of multivariate statistical analysis rely on a correlation matrix as the initial data.
A correlation matrix of n random variables X1, …, Xn is the n × n matrix whose i,j entry is corr(Xi, Xj). The correlation is defined as the covariance between Xi and Xj divided by the product of their standard deviations:
The correlation matrix is symmetric as the correlation between Xi and Xj is the same as the correlation between Xj and Xi.
How The CORRMATRIX Excel Macro Works
In the spreadsheet diagram below I use an example from my book, Financial Modeling, 3rd Edition(Chapter 10) to show how CORRMATRIX works. The cells B16:G21 show the correlation matrix of the stock return data in cells B2:G12; this matrix is computed with CORRMATRIX, the new function we will add to our spreadsheet.
Correlation Matrix Example - Stock Return Data
Correlation Matrix Example - Results
How to Add CORRMATRIX to your Spreadsheet
Before you can use CORRMATRIX in your own Excel spreadsheet, you will have to insert it using the VBA editor. If you are not familiar with VBA, you can download a spreadsheet with CORRMATRIX already installed (free of charge) from Finance 3.0′s VBA Users Group. Manual installation instructions using the VBA Editor follows:
- Open the spreadsheet in which you want the formula to work.
- Push [Alt]+F11. This will open the VBA editor. The screen will look something like this:
(Depending on the degree of customization and version of your Excel application, your screen may look different. Don’t worry about this.)
- Hit Insert | Module:
- Now insert the following text into the Module window:
- Close the VBA window (no need to save). The formula is now part of the spreadsheet and will be saved along with it.
‘Adaptation of variance-covariance function in FM3
Function CorrMatrix(rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim numCols As Integer
numCols = rng.Columns.Count
numRows = rng.Rows.Count
Dim matrix() As Double
ReDim matrix(numCols – 1, numCols – 1)
For i = 1 To numCols
For j = 1 To numCols
matrix(i – 1, j – 1) = _
CorrMatrix = matrix
Using CORRMATRIX In Your Spreadsheet
To compute the correlation matrix in your spreadsheet:
- Mark the whole area into which the correlation matrix is to be inserted:
- Write the function, indicating the return data for the stocks in columns:
- Enter the function as an array function – see Financial Modeling, 3rd Edition(Chapter 34) by pressing [Ctrl] [Shift] [Enter].
About the Author: Simon Benninga
This article was contributed by Simon Benninga, a Visiting Professor of Finance at the Wharton School of Business, University of Pennsylvania. He is the author of Financial Modelingand Principles of Finance with Excel. This short article gives additional material to his book, Financial Modeling, 3rd Edition.
Simon Benninga can be reached through his website. Feel free to use this material, but be sure to cite its source. He would like to thank David Mercurio, Director of Financial Programs at the Northwestern University’s School of Continuing Studies for motivating him to write this article. Additional comments and suggestions should be addressed to Simon at Benninga @ wharton.upenn.edu.