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