Worksheet – Case #2 – Using Slice to look at the seasonal pattern of a series & using Change Region to replicate a file for another country.

Finding Data

In this case, you will have to use the S&P 500 index. 

Using the data tree, you can locate the data here: 

Country & Region > United States > Equity Market > Equity Indices > S&P > 500 > Composite > Price return 

Add: 

  • Index (highlighted with an exclamation mark) 

You can also directly type its short code as Expression in Series List: spx 

Data preparation

You first need to calculate the monthly returns (in %) of the series, meaning that you are looking for one value per month. However, the original series is in a daily frequency. Therefore, you will have to make some adjustments in Series List: 

  • Select “Series list” in the tree 
  • Go to “Frequency” and choose Monthly 
  • Go to the “Conversion Settings” tab and choose Last for “To lower frequency conversion method” 

The series will become a monthly one, using the last value in each month. 

Analysis

Rate of change 

To calculate the monthly return, you will have to use the Rate of Change analysis: 

  • Select “Time chart” in the tree 
  • Click on “Insert” and choose Rate of change 
  • Change “Method” to be Change over period % 
  • Set the “Length” to 1 month 

Slice 

Next step is to create one series for each year, the S&P series stating in 1928.  

  • Select “Rate of change” in the tree 
  • Click on “Add” and choose Slice

In the Slice analysis, just make sure that “Periods” is set to Year. 

Cross section

You now have one category series for each year, starting in 1928, with values going from January to December. What you would like is now to calculate the average for each month. Since you are working with a large set of series, you should use Cross Section: 

  • Select “Slice” in the tree 
  • Click on “Add” and choose Cross section 
  • In “Calculations, click on “Add” and choose Mean 

In the chart, we are comparing the average with 2018 values. Following these steps will output the average versus the 2018 year. Please note that average values on our screenshot will differ from the one you are making.

Click on the series' name to see all year-series. Exclude year 2018 from the calculation by unticking “include” for the last series in the list.

  • Untick the setting “include only observation where there are values for all series” 

For instance, the series for 1928 only starts in February. If you do not untick the setting the Application will not calculate a value for January.  

 

Charting

Add a Category chart after Cross Section. 

Graph Layout

To display the series as columns: 

  • Go to Graph Layout 
  • Change “Line to “Column” 

Adjusting the columns’ margin

To change the width of the columns and the space between them, 

  • Click on one of them 
  • In “Presentation properties” change “Group margin” to 30% 
  • Change “Margin” to None 

Other changes

  • The legends have been edited, and placed in the chart.  
  • Title: United States: S&P 500 average monthly performance since 1928 
  • Axis synchronization: Value  

Changing region

You might want to look at the same chart for another equity market. Instead of recreating the analyses from scratch, you can first duplicate the file: 

  • Click on "File" at the top left corner 
  • Select "Duplicate" 

You now have an exact copy of the document. The next step is to change the region of the series: 

  • Click on "File" again 
  • Select "Change region..." 
  • From the drop-down menu, select the new region you are interested in, for instance United Kingdom to look at the FTSE. 
  • Click on "Apply" 

This will change the original series used in the document while keeping the exact structure and analyses applied. You might however do a few manual changes in this specific case: 

  • In Cross Section: making sure the current year is not included in the calculations 
  • Changing the title of the chart