Finding Data

For this example, you will use the S&P 500 Price Return and the S&P Volatility index.

For the S&P 500 Price Return, go to:

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

Locate and add “Index”. You can also use the short code “spx” in Series list.

Now, for the VIX, go to:

Country & Region > United States > Equity Market > Equity Indices > Volatility Indices > CBOE

Locate and add “S&P 500 Volatility Index (VIX), Close”. You can also use the short code “vix” in Series List.

Data Preparation

Limiting the data sample

In this example, you will only work on a data sample of 15 years. To do so, you can use the Data Range feature in Series List:

• In Series list, locate the “data range” feature on the top right of the screen
• for “Range start”, write “–15y”

You will now only have 15 years of observations on a rolling basis.

Analysis

Rate of change

First, you will have to express the two series as week-on-week percentage change:

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

Regression

To create a fitted line in a Scatter Chart, you need to generate a regression model between the two variables:

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

Next step is to set the parameters in the Regression analysis:

• check the tick-box “Output the dependent series”
• check the tick-box “Output the explanatory series”
• Check “Include” for both series
• Pick the volatility index as dependent series

Fitted line in a Scatter chart

From the Regression analysis, add a scatter chart as Presentation.

To start with, you can make the chart cleaner by displaying the line as dots only:

• Click on the line graph
• Change “Graph style” to Custom
• Set “Line” to None
• Set “Marker style” to 4px

You can now add the regression line. To do so:

• Go to “Graph layout”
• On the 2^nd Line graph, create a pair of series with the predicted volatility index and the S&P price return.
• Make sure the order of the series is the same as the 1^st line graph: S&P price return first, and then the VIX index.

Charting

You can change the axis titles to the series names:

• Click on the Y-axis
• Change Axis title to “Custom” and click on “Edit...”
• Hoover the mouse over the “dynamic properties” tab
• Click on the red cross beside “Graph text” and click OK

By clicking on the axis title you can change where you want the text to be located in the “appearance” window under the presentation properties tab.

Other changes:

Chart title:  VIX and S&P 500: Regression model with fitted line

Subtitle:  One week % change

Legend position: Above, centered

Main scale: Left

Legend text changed

Axis title is set to “outside the edge” and text alignment is set to “center”

Creating a category scatter chart

Objectives

• Using Change Region to quickly add a set of series for multiple countries
• Using the Scalar analysis and grouping series into categories
• Creating and working with category scatter charts

Solution

If you get stuck, use the worksheet to see what to do next.

If you need more detailed help, have a look at the learning resources below.

The Macrobond document is also available for download here.

Worksheet

Learning resources for this case

Scalar analysis

Category scatter chart

Graph layout

Adding Annotations to a Chart

Change Region

Finding Data

In this case, you need to add 2 series – Deficit and Debt as % of GDP from Eurostat - for 11 Euro Area countries.

To make it easier and efficient, you can start by these two series for one country, let’s say Austria. While you can go to the Eurostat database, a more user-friendly way would be to go to the “Concept & Category” database as the tree is simplified

Go to:

Concept & Category > Government & Public Finance > Eurostat > General Government

Locate and add Austria in the subsections “Debt/GDP” and “Net Lending/Borrowing (Percent of GDP)”.

Using Change Region in Series List

Now, you can use the Change Region feature in Series List to duplicate these two series for multiple countries:

• go to Series list
• Highlight both series for Austria
• Right-click on them and select “Change region and duplicate”
• In the new window, select a new country and click on “add”
• Once you’ve added all the country you need, click on close.

Analysis

Scalar

To create this scatter category chart, you will need to use the scalar analysis:

• From Series List, click on “Add” and then select Scalar
• In Scalar, change “One series per calculations” to “Partition into” and keep 2 series
  • This setting will create two category series, one for Debt/GDP, the other for Deficit/GDP
• Under Calculations, add “Last common”

You can change the name of the Category series by modifying the text in the “Output Series” box. The first text block is for the series 1. Verify the number it refers to in the “input series” box > column Group.

Finally, add a Category scatter chart from the Analysis tree.

Charting

Changing series lines to dots

By default, all series are displayed as lines connecting the values. You can change this by clicking on a line and go into the Presentation properties tab. Then:

• Switch to “Custom” in Graph style
• Change Line from “2px” to “None”
• Change Marker type from “None” to “8px”
• Modify color and style of marker to your preference

Choosing which series is displayed on which axis

You can decide which series should be in the X and Y axes.

• Go to Graph Layout
• The pair of series listed in the Pane is in a specific order:
  • The first series is put on the X-axis
  • The second series will be located on the Y-axis
• You can drag and drop series to change the order

Horizontal and vertical lines

To add lines at specific values, go to the Chart annotations tab. Select “horizontal line” and place it in the chart.  Set the position to -3 if you have the Deficit / GDP on the Y-axis.

Now do the same for the vertical line but change the Position to 60 (assuming the Debt / GDP is on the X-axis)

Other Changes

• Title: Euro Area: Maastricht Convergence Criteria
• Subtitle: Maastricht Criteria: Debt/GDP = 60% & Deficit/GDP = 3%
• "Main scale” has been set to “left”
• Axis titles for Y- and X-axis have been set to “Graph text” from the Dynamic properties tab

Calculating a rolling correlation

Objective

Calculating a 5 years rolling correlation

Adding a pane to the chart

Inverting the y-axis

Solution

If you get stuck, use the worksheet to see what to do next.

If you need more detailed help, have a look at the learning resources below.

The Macrobond document is also available for download here.

Worksheet

Learning resources for this case

Rate of change analysis

Smoothing analysis

Covariation analysis

Graph layout

Adding an alternate scale to charts

Adding Annotations to a Chart 

Working with panes

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 3^rd 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

Disaggregating a series – an example with Chinese series

Objective

Filling in missing values for a specific month

Using formulas to disaggregate a series

Solution

If you get stuck, use the worksheet to see what to do next.

If you need more detailed help, have a look at the learning resources below.

The Macrobond document is also available for download here.

Worksheet

Learning resources for this case

Formula language

Built-in formula functions

Formula

Using the Series list

Finding Data

For this case, you will be using a Retail Trade indicator for China. So, best is to start in the Country & Region category.

Go to:

Country & Region > China > Trade > Domestic Trade > Retail Trade > Totals. 

Locate and add:

• China, Domestic Trade, Retail Trade, Consumer Goods, Total, Current Prices, Aggregate, CNY

Data Preparation

Replacing missing observations

First, let’s add a time table:

• Right-Click on Series List
• Select “Add” and choose Time Table

On the time table, you can notice that January is missing for every calendar year. We can enable that month to appear by going through a few steps:

• First, go to "Series list" in the tree
• Click on the “Observations” drop-down menu
• Select All points

This setting will force the application to show all points in time (i.e. dates) for the frequency chosen, here monthly.

So, returning to the time table, you will notice that January has been added as an observation, containing the value from its preceding month, which is the default setting.

For this example, however, January should take the value from February each year.

• Return to "Series list" in the tree
• Click on the "Conversion settings" tab
• In the drop-down menu "Missing value method", select Next value

Analysis

Adding a formula analysis

To disaggregate the time series, you will have to access the formula language function. While we could have written it as a new expression in Series List, using the formula analysis will be easier:

• Select “Time chart” in the tree
• Click on “Insert” and choose Formula
• Click on the small button labeled fx

Working with the formula language

The data is aggregated on a yearly basis, from January to December.

To disaggregate it, you need to subtract the current value with its previous value, except in January and February.

There is therefore a condition needed this calculation: being after February. In Macrobond, you will need to use the following formula: if(condition, value 1, value2)

The condition is to be between March and December. In other words, after February. In Macrobond, you can write such condition with: month()>2.

Value 1 is what you want to do when the condition is true. In our case, we want to disaggregate the data. Calculating the difference between a value and a previous value of a series can be achieved with the formula: momentum(series, length), where length is the observation window between the value and the previous value. Here, you want to subtract the value of the previous month, so the length is 1.

Value 1 will therefore be: momentum(fx:s1, 1)

Value2 is what you want to do when the condition is false. In this case, what we wanted is to distribute the February value between January and February.

Value 2 will therefore be: fx:s1 / 2

So you now have the entire expression, which should be:

If(month()>2, momentum(fx:s1, 1), fx:s1/2)

Finally:

• Uncheck the tick-box "Include input series as output"
• Click on "Done" at the bottom right corner

If you click on "Time chart" in the tree you will notice the line fluctuating less between different observations than before.

Other changes

• Title: Disaggregating a monthly series
• The analysis text has been removed from the legend

Counting occurrences of events with the formula language

Objectives

Using built-in formula functions to create a series

Combining formulas together

Displaying condition series in a graph

Solution

If you get stuck, use the worksheet to see what to do next.

If you need more detailed help, have a look at the learning resources below.

The Macrobond document is also available for download here.

Worksheet

Learning resources for this case

Graph layout

Formula language

Built-in formula functions

Formula

Finding Data

To find the Volatility Index for the S&P, you can use the Country & Region entry:

Go to:

Country & Region > United States > Equity Market > Volatility Indices > CBOE.

Locate and select:

• S&P 500 Volatility Index (VIX), Close

Add the series to a new document.

You can also use the series short code in the Series List expression, by typing vix

Analysis

Formula

The aim of the document is to calculate how many times the VIX has been lower than 10 in history.

The first step is to know when the VIX has been lower than 10. You will have to formulas to achieve this:

• Select “Time chart” in the tree
• Click on “Insert” and choose Formula

Now, you should create a logical expression:

• Click in the expression text-box
• Type: fx:s1 < 10

This will create a binary time series, returning either 1 or 0:

• 1 will mean TRUE
• 0 will mean FALSE

So now you have identified when the VIX is lower than 10. The next step is to count its occurrences.

In other words, you need to sum the 1 and 0 of the previous logical series, over time.

While you could use the Aggregate analysis to do this (using Period: All as setting), we will stick to the formula language in this example. Create a second expression:

• Aggregatesum(fx:s1<10)

This will create a new time series where the value at each observation is the sum of the values of all observations preceding it.

Charting

Graph Layout

To adjust the appearance of our graph line corresponding to the condition VIX < 10:

• Open Graph Layout
• On the right half of the window, select the condition series
• Choose Fill as the graph type
• To move the Fill indicator into the background, click and drag Fill above Line

Axis value label

To add the last value on the y-axis for the aggregate sum series

• Click on this specific graph line
• On the “Presentations Properties” tab locate the “Axis value label” adjustment
• Select Show from the roll-down menu

Other changes

• Title: United States: VIX
• Subtitle: Very low volatility: number of occurrences
• The legend labels has been changed
  • VIX < 10
  • VIX<10: nb. of occurrences

Finding Data

For this example, you need to use US corporate benchmarks from Moody’s. A good way to start is to go into Country &Region.

Go to:

Country & Region > United States > Interest & Exchange Rates > Corporate Benchmarks > Moody’s > Yield.

Locate and add:

• Aaa rated, Average of period
• Baa rated, Average of period

You will also need the time series displaying recession periods. Go to

Country & Region > United States [us, Region] > Surveys & Leading Indicators > Leading Indicators > NBER

Locate and add:

• United States, NBER, Business Cycle Reference Dates

For this last series, you can also use the short code nber in Series List.

Analysis

Calculating the spread of the corporate benchmarks

The first step of this example is to calculate the spread between the two corporate benchmarks series.

A quick way to do so is to create a new expression under Series List:

• Create a new expression in series list: usrate0233 – usrate0232
• Delete the individual series (usrate0233 and usrate0232) in Series List, as we do not plan to use them.

Mean and standard deviation

You now have to calculate the mean of the spread series, and +1 / -1 standard deviation around it. You can do so using the Statistics Analysis:

• On the time chart, click on “insert” and add Statistics
• Besides the spread series click on “Add” and select Mean
• Tick the box beneath “Std. Dev.” to display a band of +1 / -1 standard deviation around the mean

In the time chart, you will now find the standard deviation as a broad stripe along the chart, with the mean value as a line in the middle.

Creating recession bands

You can now use the recession dates series to display recession bands on the chart:

• Right-click on the graph and select “Graph Layout”
• Add a Fill by clicking on it at the top of the window
• Drag and drop the nber-series beneath Fill
• Drag and drop Fill above Stripe to move it to the background

Adding an axis value label

To add the last value of the series to the y-axis:

• Click on the graph line in the chart
• On the “Presentation properties” tab, locate the window “Content”
• In the drop-down menu labeled “Axis value label”, select Show

Modifying the opacity of the bands

The recession and Stripe bands can be made slightly transparent:

• Click on any recession band
• In “Presentation properties”, change Graph style to Custom in the drop-down menu
• Click on the button labeled “...” beside Color
• Change color and opacity to your preference (for instance 30%)
• Repeat the process for the Standard deviation-stripe

Other changes

• Title: USA: Corporate Benchmarks spread
• The legend has been moved to above the chart, centered, with its analysis text removed