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/A
s. 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
Exercise instructions
In
I14
, useCORREL()
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
