Worksheet – Case #8 – Calculating a rolling correlation

Finding Data

For this example, you will use two time series: the S&P GSCI Gold index and the US dollar index. As a commodity index, the gold index can be found in the Region World:

Go to:

Country & Region > World > Commodities & Energy > Commodity Indices > S&P GSCI > Precious Metals

Select Gold Index.

The US dollar index can be found in:

Country & Region > United States > Interest & Exchange Rates > FX Indices > ICE

You can also use its short code dxy directly as a new expression in Series List.

Finally, for reasons that will be explained below, you should duplicate the Gold index series by right-clicking on it and selecting “Duplicate”.

Analysis

Rate of change

As a first step, you have to express all series as annual returns:

  • Select “Time chart” in the tree
  • Click on “Insert” and choose Rate of change
  • For “Method”, choose Change over period %
  • For “Length”, select 1 Year

Smoothing

To make the lines easier to analyze you can add a moving average, for instance of 6 months

  • Select “Time chart” in the tree
  • Click on “Insert” and choose Smoothing
  • Add a Moving average for all three series
  • Change to “Month” in the drop-down menu and change the length to 6

Rolling correlation

You can calculate rolling correlations with different analyses. But in this case, we suggest using the Covariation analysis:

  • Select “Time chart” in the tree
  • Click on “Insert” and choose Covariation
  • Under “Covariation type” select “Correlation”, for one of the US Gold Index series
  • Under “Explanatory” select the U.S. dollar index
  • “Length” should be set to 5 years

Charting

Adding a separate pane

In this example the rolling correlation should be displayed in a separate chart or “pane”, below the main chart. To add panes:

  • Go to Graph Layout
  • Click on “Add pane”
  • Click on the new pane and click on “Area”
  • Drag the correlation time series (I.e. with the text “cov. Correlation” in brackets) to the new pane

You can automatically set the proportion of the panes.

  • For our case, click on the 3rd option from the right (2:1)

This is the reason why the Gold index had to be added twice. Doing so, you can display the rolling correlation in the lower pane while keeping the original series together with the US Dollar index in the pane above.

Using alternate scale

Since you are in the Graph Layout menu:

  • In the upper pane, set the “Gold Index” series as alternate scale by ticking the box “alt. Scale" for that series

Inverting the scale

You can reverse the y-axis to change the perspective of the chart

  • Click on the y-axis for the Gold Index series (in the upper pane)
  • In “Presentation properties” tick the box labeled “Reverse” in the “Scale” window

Adding a title to the lower pane

Each pane can have subtitles.

First, remove the redundant “%” sign on the lower pane:

  • click on it and press delete.

Now, click on the background of the lower pane

  • In “Presentation properties” locate “Subtitle” in the “Elements group”, click on Edit…
  • Having added your desired text, click on it and adjust the settings in “Presentation properties” to center the text

Other changes:

  • Title: S&P GSCI Gold Index vs. US Dollar Index
  • Subtitle: Annual returns %, 6 months moving average
  • The analysis text has been removed
  • The location of the legend is set to “Above”
  • Added text Inverted Scale above left y-axis
  • Horizontal lines set to 0 in both panes
  • Lower pane: the y-axis has been synchronized to “Values”, so that it appears in both sides
  • Upper pane: the Y-axis scale for the Gold Index has been manually adjusted: from –50 to 50, with a step of 10