## Computing The Correlation Matrix – By Simon Benninga

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

 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

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:

• Hit Insert | 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.