Get startedGet started for free

Compute the rolling-window correlation

A rolling-window correlation is a useful tool to investigate how the relationship between ABC and its benchmark moved over time.

In this exercise, your task is to compute moving correlation using a one-year window (12 months). After that, display the values through a line chart.

Rolling correlation is easy to perform with Google Sheets. Just expand the correlation formula with relative values of the input cells, corresponding to the sub-windows of interest.

Note that cells I2:I13 are filled with #N/As. To do so, we used the functionNA(), which requires no argument and returns a #N/A. That's because when dealing with rolling windows, starting the computation in line with the last cell of the first period makes the process easier and more understandable.

This exercise is part of the course

Financial Analytics in Google Sheets

View Course

Exercise instructions

  • In I14, use CORREL() to compute the correlation coefficient based on the first 12 returns.

  • Complete the table by using the autofill feature.

  • Display the series using a line chart

  • On the horizontal axis, place the dates starting from December 2013.

Hands-on interactive exercise

Turn theory into action with one of our interactive exercises

Start Exercise