Learn how I calculated the rolling correlation of two key players in my retirement fund, and get a calculation spreadsheet.

In common with most investors, I want a diversified portfolio. If one of my investments drops like a brick, I don’t want everything else to crash as well.

That’s why I keep tabs on the correlation between investment pairs.

This article describes how I calculated the rolling correlation coefficient (over a customizable time period) of two major funds in my RRSP:

- a S&P 500 tracker
- and an ETF that tracks crude oil prices

I used Excel and VBA for the calculations and data manipulation. Download the complete spreadsheet for correlation analysis at bottom of this article.

It also contains with five years of historical prices for WTI and the S&P 500. You can use the spreadsheet to analyze the relationship of your own two investments by simply copying the historical data into the appropriate tabs.

Read on for more information.

## Step 1: Getting the Historical Data

My go-to source for historical market data is Yahoo Finance. With this spreadsheet, I downloaded daily data for the S&P 500 (ticker: ^GSPC) over the last five years (January 2010 to January 2015)

However, Yahoo doesn’t give historical crude oil prices. For that, I used a spreadsheet that queries the Quandl API to download historical market prices for WTI (West Texas Intermediate) crude, again for the last five years.

The spreadsheet contains both data sets, each in a separate tab (“Asset1” and “Asset2”)

If you want to copy your own data in, ensure that

- cell A1 contains the name of the stock as you want it presented in the charts,
- the time series data is copied below the data and value headers,
- and you do not change the name of the tabs (Asset1 and Asset2)

## Step 2: Aligning the Data

There are a few dates for which only the price of WTI or S&P 500 is available (but not both).

But if you want to calculate the correlation coefficient, both data sets must contain a price for every date under consideration. So I wrote some VBA that

- calculates which data set has the shortest time history, and copies it into a new sheet – in this case it was WTI crude.
- for each date-value pair of WTI crude, locates the price of S&P 500 with the same date.
- place that price next to the corresponding price of oil
- run down the partially-merged list, and delete rows for which only prices for WTI or S&P 500 is available.

There are more sophisticated methods of aligning the data, but this is quick to implement.

Here’s part of the VBA (the complete code is in the spreadsheet).

'Find the asset with the shortest time history minRow = Sheets("Asset1").UsedRange.Rows.Count Set minTickerWS = Sheets("Asset1") If Sheets("Asset2").UsedRange.Rows.Count < minRow Then minRow = Sheets("Asset2").UsedRange.Rows.Count Set minTickerWS = Sheets("Asset2") End If Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = "Collated Prices" i = 1 minTickerWS.Range("A2", "B" & minRow).Copy Destination:=Sheets("Collated Prices").Cells(1, i) Sheets("Collated Prices").Cells(1, i + 1) = minTickerWS.Range("A1") i = i + 2 For Each ws In Worksheets If ws.Name <> "Collated Prices" And ws.Name <> minTickerWS.Name And ws.Name <> "Results" Then Sheets("Collated Prices").Cells(1, i) = ws.Range("A1") Sheets("Collated Prices").Range(Sheets("Collated Prices").Cells(2, i), Sheets("Collated Prices").Cells(minRow - 1, i)).Formula = _ "=vlookup(A2," & ws.Name & "!A$2:G$" & minRow & ",2,0)" i = i + 1 End If Next nRows = Sheets("Collated Prices").Range("A" & Sheets("Collated Prices").Rows.Count).End(xlUp).Row For i = 2 To nRows If IsEmpty(Sheets("Collated Prices").Range("B" & i)) Or IsEmpty(Sheets("Collated Prices").Range("C" & i)) Then Sheets("Collated Prices").Rows(i).EntireRow.Delete End If Next |

Click the “Collate Prices” button on the “Results” tab to run the filtering code.

## Step 3: Calculating the Correlation Coefficient

I wanted to calculate the rolling correlation coefficient over a time period of n days.

- Starting from the n
^{th}day, the code calculates the correlation coefficient for day n and the previous n-1 days - every day, the window is moves forward one day.

There are several ways to write the formula, but I chose to use the Formula R1C1 method to programmatically place formulas into the spreadsheet. Here’s a code snippet.

timeWindow = Sheets("Collated Prices").Range("timeWindow") Sheets("Collated Prices").Range("D" & 1 + timeWindow & ":D" & nRows - 1).FormulaR1C1 = _ "=CORREL(R[-" & timeWindow & "]C[-2]:RC[-2], R[-" & timeWindow & "]C[-1]:RC[-1])" |

Change the time window and run the complete code by clicking the “Calculate Rolling Correlation Coefficient” button in the “Results”.

## The Results!

This chart gives the rolling 30-day correlation coefficient for the S&P 500 and WTI crude between 21st January 2010 and 12th January 2015.

A value of 1 means both are synchronized with each other, a value of -1 means that if one falls, the other rises, while zero means no relationship exists.

Over the last five years, the rolling correlation coefficient dips and rises fairly regularly. Increasing the time window of the rolling correlation coefficient smooths out frequency. Try experimenting with the spreadsheet and see what difference increasing or decreasing the time window makes.

The correlation coefficient over the full five years is 0.285. That’s a moderately positive correlation.

This chart gives the price of a barrel of WTI crude oil and the S&P 500 between 21st January 2010 and 12th January 2015.

Both investments track upwards for the first four years (with crude oil having a rockier ride).

As you can see, something drastic happens during the latter half of 2014. WTI crude rapidly falls in price as a result of Saudi Arabia flooding the market with crude.

**Download Excel Spreadsheet to Calculate Rolling Correlation Coefficient of Two Assets**

assuming your data series are in Column A and B, and start in row 2, why didn’t you just do something like the following, and drag it down:

=CORREL(OFFSET(A2,0,0,30),OFFSET(B2,0,0,30))

I wanted to automate all calculations in VBA. I did not want to click and drag in the spreadsheet environment at all

The periodicity of the correlation is interesting. Is is approximately the same as the period chosen for the moving average. This holds if you use a 30,60,90 or 120 items in the moving average data . I think this relates to the long term correlation of .25 which pretty much says these two data sets are not correlated. Ie the day to day change in the differences is pretty much random. In which case you would expect the correlation of the small moving average data sets to vary at random from a nice close correlation to no correlation to a negative correlation. As each successive data set only changes by two values – one added and one removed – the correlation cannot chance too dramatically from one day to the next, but the wild oscillations of the 30 day moving average do show the “correlation” changing rapidly again hinting at the distinct lack of correlation between these data sets.

Nice thought though.

Even averaging over a year there are 4 distinct peaks one for each year we have rolling data for. Mind you from 2010 to 2013 you saw a nice enough correlation in the region of 90% for a lot of the time.

I guess there is a mistake within the macro, ”Correlation coefficient over entire history” is now counted out of date and first atribute. Not out of both atributes

instead of A2″A – B2: B schould be B2:B – C2:C

as per below, this code can be replaced with the current one (6th line in Module1):

Sheets(“Results”).Range(“corrCoeff”).Formula = “=CORREL(‘Collated Prices’!B2:B” & nRows & “,’Collated Prices’!C2:C” & nRows & “)”

However, thank’s so much for such a grat tool MAN!!!!!!! 🙂 🙂 AWESOME