Examining Rolling Correlation Within Excel

Click Here to Download Workbook: Rolling Correlation

Rolling correlation can be used to examine how relationships between two assets change over time. This is nothing more than a moving average correlation. This is typically used with two assets, in this case perhaps two indices, two commodities, and etc. It is commonly known that markets across the globe have grown increasingly correlated with the U.S. market. In theory, we could analyze the relationship between two markets on a moving average basis through a given user input. This could be 20 to 30-day input for a moving average or rolling correlation. This can illustrate a change in the relationship between the two assets over a given time frame.

The reason I made this workbook was not specifically geared towards equity securities representing major companies within the United States. Rather, currencies, major market indices, commodities, and cryptocurrencies. This model could theoretically be applied to many different assets with historical price data. What we are doing here is examining correlation on specific levels of granularity. Let’s start by going over some of the workbook code.

Let’s run some correlations! Each of the comparisons listed below will use a 5-year time frame if applicable as well as a combination of a 50-day and 200-day moving average correlation series. Theoretically, you could overlay as many averages as you want in this workbook. Everything is dynamic. The green line on the chart represents the correlation across the entire data range. In this case, that range is 5 years. The prices of both assets are standardized and graphed also.

Let’s start with the SPY ETF and USO ETF to represent the markets correlation to oil over time:

Let’s also examine the SPY and gold:

Here is the SPY compared to the MSCI EAFE World Index ETF:

Oil and Gold:

Finally, let’s examine bond market in comparison with the SPY.

About the author


Hi, I'm Frank. I have a passion for coding and extend it primarily within the realm of Finance.

View all posts

Leave a Reply

Your email address will not be published. Required fields are marked *