Computing The Correlation Matrix – By Simon Benninga

Simon Benninga's Correlation Matrix VBA Macro

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:

Formula for Correlation

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

  A B C D E F G
1 Date GE MSFT JNJ K BA IBM
2 3-Jan-94 56.44% -1.50% 6.01% -9.79% 58.73% 21.51%
3 3-Jan-95 18.23% 33.21% 41.56% 7.46% -0.24% 6.04%
4 2-Jan-96 56.93% 44.28% 57.71% 37.76% 65.55% 27.33%
5 2-Jan-97 42.87% 79.12% 22.94% -5.09% 54.34% 41.08%
6 2-Jan-98 47.11% 38.04% 17.62% 32.04% 37.11% 2.63%
7 4-Jan-99 34.55% 85.25% 26.62% -10.74% 22.00% -2.11%
8 3-Jan-00 28.15% 11.20% 3.41% -48.93% 43.53% 23.76%
9 2-Jan-01 4.61% -47.19% 10.69% 11.67% 28.29% 21.76%
10 2-Jan-02 -19.74% 4.27% -7.00% 19.90% -15.09% 4.55%
11 2-Jan-03 -44.78% -29.47% -5.67% 10.88% -23.23% 15.54%
12 2-Jan-04 35.90% 18.01% -1.27% 15.49% 39.82% 31.80%

Correlation Matrix Example - Results

  A B C D E F G
15   GE MSFT JNJ K BA IBM
16 GE 1 0.5791 0.5383 -0.056 0.905 0.2819
17 MSFT 0.5791 1 0.56 -0.0532 0.3502 -0.0406
18 JNJ 0.5383 0.56 1 0.2838 0.3925 0.0002
19 K -0.056 -0.0532 0.2838 1 -0.1234 -0.1427
20 BA 0.905 0.3502 0.3925 -0.1234 1 0.5821
21 IBM 0.2819 -0.0406 0.0002 -0.1427 0.5821 1

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:
  • Open the Microsoft VBA Editor

(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:
  • Insert Microsoft VBA Module

  • Now insert the following text into the Module window:
  • ‘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) = _
    Application.WorksheetFunction.Correl(rng.Columns(i), rng.Columns(j))
    Next j
    Next i
    CorrMatrix = matrix
    End Function

  • Close the VBA window (no need to save). The formula is now part of the spreadsheet and will be saved along with it.

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:
  • Using The Correlation Matrix VBA Macro / Array Function

  • Write the function, indicating the return data for the stocks in columns:
  • Using the Correlation Matrix VBA Macro / Array Function

  • 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 ExcelPrinciples of Finance with Excel: Includes CD by Simon Benninga. 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.


Visit Finance 3.0 - don't miss out on this high quality financial learning opportunity

Comment on this article