Category: E-learning Step by Step
Video – Case #5 – Calculating a 12-month rolling sum
Video – Case #3 – Calculating and presenting year-on-year % change
Worksheet – Case 6 – Create a category chart using Scalar
Finding Data
To find sectoral returns of the S&P 500, you can use the Country & Region database. Navigate to
Country & Region > United States > Equity Market > Equity Indices > S&P > 500 > Industry Classification category.
Adjust the slider to show only level 1 of, that way you only have to look through the main sectors of the S&P 500. Select all series and add them to a document.
Analysis
Scalar
Scalar produces category series, so the output can’t be displayed using a time chart. Instead, in the analysis tree you need to highlight “series list” and click on the add button to select the scalar analysis.
Once you’ve done that, look for the “Calculations” setting. Select “Year to Date” as the calculation. Do not set a point in time.
The settings for Output series should be set to “one series per calculation”.
Under Value labels you can specify the name of the categories created for the series. These labels will appear on the X-axis of the category chart.
Sorting
Since the example chart displays the sectors in ascending order, you will have to add the Sorting analysis after Scalar to specify the direction of the series.
Lastly click on add and select “Category chart” to visualize the output.
Charting
Graph Layout
By default, the category series is displayed as a line. In this case you need to change this to Column in Graph Layout.
Horizontal line at 0
To add the horizontal line, go to the Chart annotations tab. Select “horizontal line” and place it in the chart. The exact position is not important, as you can set it manually in the Presentation Properties tab. There, set the Position to 0 and tick the ‘Foreground’ setting to make it appears above the columns.
Column Width
You’ll notice than in the example chart the column width is narrower than what you currently have. To access these settings, click once on any of the columns displayed and look up at the Presentation Properties tab. You will see two options for the margin settings: Margin and Group Margin. Margin adjusts all columns that are within the same category, while Group margin sets the margin between each category. Since there is one column per category, you will use the Group Margin setting and set it to 40%.
Case #6
Create a category chart using Scalar
Objectives
- Using the Scalar analysis to create a category series
- Creating a category chart, and sorting the results
- Making visual adjustments to a category chart
Solution
If you get stuck trying to replicate the example, use the video or worksheet to see what to do next.
Video (Note that since making this video we have further developed some elements in app and our structure of the data)
To the see exact construction of the document, you can download the file here.
Learning resources for this case
Different views – entry-points and how to work with them
Case #5
Calculating a 12-month rolling sum
Objectives
- Calculating a rolling sum using the Aggregate analysis
- Editing the chart title & removing the legend
- Saving the document and creating folders
- Setting the chart dimensions & exporting to PowerPoint
Solution
If you get stuck trying to replicate the example, use the video or worksheet to see what to do next.
Video (Note that since making this video we have further developed some elements in app and our structure of the data)
To the see exact construction of the document, you can download the file here.
Learning resources for this case
Different views – entry-points and how to work with them
Worksheet – Case 5 – Calculating a 12-month rolling sum
Finding Data
To find the German Current Account Balance, you can use the Country & Region database. Navigate to Country & Region > Germany > Balance of Payments > Current Account > Total
Add the headline series for Balance - marked with an exclamation mark – to the document.
Analysis
Aggregate
To calculate a rolling sum, you can use the Aggregate analysis. Insert the analysis from the time chart.
Since you have to calculate a 12-month rolling sum, you should first set the period to annual, and then tick the box for “Rolling”.
Charting
Graph Layout
By default, the series is displayed as a line. To change it to an area, go to Graph Layout and change the graph style from “Line” to “Area”.
Title & Subtitle
To edit the title, the easiest option is to double click on it.
For subtitles, click within the chart area, and look up at the Presentation Properties tab in order to find the “Subtitle: Edit” setting. Click on “Edit” and write your subtitle in the new window.
Removing the legend
To remove the legend, you should select it first and then press delete on your keyboard.
Worksheet – Case 3 – Calculating and presenting year-on-year % change
Finding Data
US Industrial Production
To find US Industrial Production, the best approach is to start looking in Country & Region. Navigate to:
Country & Region > Production > Industrial Summary > Output > Market Groups
Click on Total Industrial Production Index – this is the headline series, indicated by the red exclamation mark.
US ISM PMI
For the ISM Manufacturing PMI, you can do one of the following:
- Go to: Surveys & Leading Indicators > Business Surveys > ISM > Report on Business > Manufcturing
- Use the search bar at the top of the series browser and type “US ISM PMI”
Analysis
Rate of Change
Now you need to calculate the year-on-year % change on the Industrial Production series.
- In the analysis tree click on “time chart” and select the rate of change analysis from the list.
- Adjust the calculations that will be applied to Industrial Production. The method should be set to “change over period %”, with a length of 1 year.
You don’t need to make any changes to the ISM PMI, so make sure the method is set to “none”.
Charting
Alternate Scale
To align ISM PMI to a second axis, click on the Y axis to activate the relevant settings in the presentation properties tab. Check the setting for “Use alternate scale”.
Graph Layout
To change the chart style, you will need to use graph layout. Right click on the chart and select it from the context menu. On the right side of the window that opens, click on industrial production, and then click on Area from the options above.
If the area graph is displayed above the line, you also need to reorder the series in Graph Layout, by dragging it into the right place in the list. The series are added to the chart in the same order as the list, so the first series will be displayed beneath the second, and so on.
Synchronizing the scale of the axes
0 and 50 are two reference values for these series. To align these value on the axes you have two options:
- Click on one vertical axis, and manually edit the range and step length in the Presentation Properties tab
- Click on one vertical axis, and use the Synchronize setting in the Presentation Properties tab.
In this case, since the series have different scales, you want to select “Grid” to synchronize the axes.
Case #3
Calculating and presenting year-on-year % change
Objectives
- Apply a Rate of Change analysis
- Display one series on an alternate axis
- Combine line and area graphs in one chart
- Synchronize the scale of the vertical axes
Solution
If you need more detailed help, have a look at the learning resources below, and download the Macrobond document for more details.
If you need more detailed help, have a look at the learning resources below.
Video (Note that since making this video we have further developed some elements in app and our structure of the data)
To the see exact construction of the document, you can download the file here.
Learning resources for this case
Different views – entry-points and how to work with them
Working with the Analysis tree
Worksheet – Case 1 – Comparing GDP across countries using Rebase
Finding Data
Since we’re looking at comparing an indicator across countries, we should start by exploring the Concept & Category database. For this exercise, we’ll use GDP from National Sources.
GDP series can be found under National Accounts. We will use constant prices. Now we just need to select the countries we are interested in: France, Germany, Italy, Portugal and Spain. To add multiple series at once, hold on CTRL while selecting series and then press “Add selected Series”.
Analysis
Rebase
Now that we’ve added our series to the document, let’s move on to the Analysis tree. By default a time chart which shows the raw series is already in place below the series list. Let’s insert a Rebase analysis on this chart to compare their evolution.
In Rebase, the next step is to define the settings for our analysis. We’ll use the Absolute Method and set the base date to the 1st of January 2008.To apply these settings to the rest of the series, we will use the fill-down arrow. To see the result click on time chart.
Charting
Basic charting adjustments
We have our analysis in place, but the chart could still be improved a bit to suit our purposes. To adjust the time range, click on the X-axis and look up to the command bar, at the presentation properties tab. Under Display range, we will set the start date to 2008.
To edit text, double click on a text element, such as the chart title and adjust the text in the editor that pops up.
To edit the legend text for a specific series, you’ll need to double click on its graph in the chart. Clicking on the legend itself allows you to edit the legend as a whole. To tidy it up a bit, we can remove the analysis text by clicking on the legend area and unticking the setting “show analysis text” in presentation properties.
Horizontal line
Finally, we can highlight the reference value, which in this case is 100, by adding an horizontal line. Click on the Chart annotations tab at the top, and select horizontal line. You can place it manually or set the position by suing the setting in the presentation properties tab.