Calculating moving average & adding recession bands

Objectives

Using the Smoothing analysis

Displaying recession series as fill range

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)

Worksheet

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

Rate of change analysis

Smoothing analysis

Adding an alternate scale to charts

Graph layout

Finding Data

In this example, we are using three time series on Germany: Retail Trade, Consumer expectations and a recession indicator.

Therefore, we will use the “Country & Region” view.

 

To find the Retail Trade series, go to:

Country & Region > Germany > Trade > Domestic Trade > Retail Trade > Constant Prices 

Locate and select the following series, which has an exclamation mark:

• Retail Trade, Except Motor Vehicles & Motorcycles

Add the series to a new document.

 

To find the Consumer expectations series, go to:

Country & Region > Germany > Surveys & Leading Indicators > Consumer Surveys > GfK > Consumer Climate

Locate and select:

• Economic Expectations

Add it to the document you just created.

To find the recession indicator, go to:

Country & Region > Germany > Surveys & Leading Indicators > Leading Indicators > Macrobond

Locate and select:

• Business Cycle Reference Dates

Add it to the document.

Analysis

Rate of change

• Select “Time chart” in the tree
• Click the “Insert” button
• Select Rate of Change

In the analysis tree, go to Rate of Change, and apply these settings for the Retail Trade series only

• “Method” is set to Change over period %
• “Length” is set to Year and specified to be 1 year

Smoothing

By looking at the Time chart, you can notice that the Retail Trade is difficult to read as it fluctuates a lot. You can smooth it by calculating a 1 year moving average.

• Select “Time chart” in the tree
• Click the “Insert” button
• Select Smoothing

In the analysis tree, go to Smoothing, and apply these settings for the Retail Trade & the Consumer Expectations series (for harmonization purposes):

• Click on the “Add” button for the first series
• Select “Moving average”
• Set the length to “1 year”
• Apply the same settings for the Consumer Expectations series.

Charting

Alternate Scale

The Consumer Expectation series has been displayed on an alternate scale.

Graph Layout

The recession indicator has either the value 1 or value 0, with value 1 indicating a recession. Instead of displaying it as a line, you should represent it as a Fill, so that it highlights the recession period in the chart.

• Go to Graph Layout
• Select the recession indicator under the tab “Graph” (RHS)
• Click on “Fill”

Make sure the order is set so that the “Fill” graph is listed above the “Line” graph.

Adding transparency to the "fill" series

You should add transparency to the recession indicator. To do so:

• Click on the series to activate its properties in the Presentation Tab
• Go to the tab "Appearance", and choose "Custom" as graph style
• Click on the "…" button close to the color section
• In the new window, set opacity to 30%

Other changes

The following changes have also been applied to the document:

• Title
• Subtitle
• Horizontal line to 0

Using Correlation to determine the best lag

Objectives

• Finding the optimal lag based on the correlation between series
• Using the Lag analysis
• Circling several values of a series

Solution

If you get stuck, use the video or 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.

Video (Note that since making this video we have further developed some elements in app and our structure of the data)

Worksheet

Learning resources for this case

Graph layout

Correlation analysis

Category chart

Lag analysis

Working with observation labels

 

 

Finding Data

In this case, we will reuse the document from Case #3 which can be downloaded here. As such, all data needed for this exercise can be found in the document.

Document preparation

Reverting adjustments

As we are reusing a document, some adjustments need to be reverted from the earlier case.

• Select “Time chart” in the tree
• Open the chart layout and change Industrial production from “Area” to “Line”

Analysis

Correlation

You need to use the Correlation analysis to determine the best lag correlation between the two series.

• Select the Rate of Change analysis
• Add a Correlation analysis

In this case we are interested in the correlation between the two series from the year 2000.

• Input 2000 as the start of the “Calculation range” and leave the end value blank
• Change the "Lag Range" from -12 to 12.
• For the ISM PMI, in the “Correlate to” drop-down menu, choose Industrial production
• Leave the “Correlate to” setting to None for the Industrial production

Visualizing the result

• Select “Correlation” in the tree
• Click on “Add” and pick Category chart

By looking at the Category chart, you can see that the best correlation is reached when the value of the x-axis is 4. This means that lagging the ISM PMI by 4 observations (or months in our case) gives the best correlation between the series.

Lag

Having found the optimal lag, you can now come back to the Time chart and insert a Lag analysis.

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

For ISM PMI, choose Lag as the “Method” and 4 obs as the “Base”

Charting

Time axis adjustment

As we are interested in the correlation beyond the year 2000:

• Select the x-axis
• On the “Presentation properties” tab set the display range to start at 2000

Highlighting a range of values

You can use some chart annotations to circle these values.

• Go to the “Chart annotations” tab and select Observation label
• On the ISM PMI graph line, click on the last observation
• Select the annotation by clicking on the textbox containing date and value
• In the “Presentation properties” tab, change the start of the range to -3
  • This will include the lagged observations in the range, indexed by: 0,1,2,3
• Tick the “Custom style” text-box and in “Anchor appearance” change the style to Ellipse
• Finally, change the background color of the annotation
  • Set the opacity to 50% or lower so that the line remains visible.

Changing the observation label text

To change the text of the observation label, simply double-click on the textbox and a customization window will appear.

Finding Data

In this example, we are using two main indicators for Japan's GDP and Industrial Production.

One easy way to find these data is to use the “Concept & Category” database, as it limits the amount of data available in the tree.

To find the series go to:

Concept & Category > National Accounts > National Sources > GDP > Constant Prices > By Frequency > Best Frequency

Locate and select:

• Japan, Gross Domestic Product, Total, Constant Prices, SA, AR, JPY

Add the series to a new document.

 

The industrial production data is located in:

Concept & Category > Production > National Sources > Industrial Production

Locate and select:

• Japan, Industrial Production, Production, Total, Production, Mining & Manufacturing, Constant Prices, SA, Index

Add it to the document you just created.

Data preparation

Frequency adjustment

The series added to the document have different original frequencies: the GDP data is quarterly while the Industrial production is reported monthly. To ensure that calculations are done in a consistent way, Macrobond will convert the data to a common frequency. To adjust the frequency:

• Go to “series list” in the analysis tree
• Locate the “Frequency” drop-down menu on the top
• Make sure Highest (Monthly) is selected for this example

The drop-down menu gives you several options when deciding on a frequency for the chart.

Frequency conversion

When working with series that have different frequencies, the first step is to decide which frequency you want to work with. The second step is to decide by which method you want the series to be converted to a higher or lower frequency.

• Select “series list” in the tree
• Go to the “Conversion settings” tab
• Locate the “To higher frequency conversion method” drop-down menu
• Pick Linear interpolation instead of Automatic

Analysis

Rate of change

• Select “Time chart” in the tree
• Click the “Insert” button
• Select Rate of Change

In the analysis tree, go to Rate of Change, and apply these settings for both series:

• “Method” is set to Change over period %
• “Length” is set to Year and specified to be 1 year

Charting

Alternate Scale

As the value-ranges for the two series differ in magnitude, you would probably need to add an additional Y-axis.

• Select “Time chart” in the tree
• Click on the graph line for the GDP in the chart
• Above the chart, open the “Presentation properties” tab
• Check the Use alternate scale tick-box

Synchronizing the scale of the axes

To align the zero-level of both axes, you have two options:

1. Click on one vertical axis, and manually edit the range and step length in the Presentation Properties tab
2. Click on one vertical axis, and use the Synchronize setting in the Presentation Properties tab

In the latter case, since the series have different scales, you want to select “Grid” to synchronize the axes.

Appearance

Finally, change the title in the chart by double-clicking on the text.

Working with series that have different frequencies

Objectives

• Combining data with different frequencies
• Adjusting the frequency conversion method

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)

Worksheet

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

Changing the frequency of a series

Graph layout

Finding Data

You need to find data on loans to households for all the Euro area countries. In this example we’ve used harmonized series from the ECB. There are two approaches you can try.

• Concept & Region

1. Select Concept & Category and then the following:
   Concept & Category > Money, Banking & Credit > ECB > Monetary & Financial Statistics > Monetary Statistics > Aggregated Balance Sheet > MFIs Excluding ESCB > Assets > Loans Adjusted for Sales & Securitisation (A20T) > All Maturities > All Currencies Combined > Counterpart Area: Domestic (Home or Reference Area) > Households & Non-Profit Institutions Serving Households (S.14 & S.15) > Index of Notional Stocks > Change Y/Y
2. Select all series, except Estonia and Lithuania.

Analysis

Cross Section

The purpose of this case is learning how to preform calculations on a group of series. The cross-section analysis allows you to do this.

In the analysis tree, go to Time Chart and insert the Cross Section analysis.

• Step 1:
  • Add the first calculation: Mean
  • Exclude the Euro Area series from the calculation

• Step 2:
  • Add a Percentile calculation, with % set to 60
  • Make sure the Euro Area series is excluded from the calculation

• Step 3:
  • repeat step 2 for the percentiles 40%, 75%, and 25%.

• Step 4:
  • Untick the setting “include only observations where there are values for all series”

Charting

Graph Layout

Select each percentile pair (for instance 60% and 40%) and display them as stripes.

Make sure the graph order is correct. Both stripes should be drawn behind the lines, and the stripe for 75-25% should be drawn behind the stripe for 60-40%.

In graph layout, you should have the following order:

• Stripe [75-25]
• Stripe [60-40]
• Line

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 to ‘0’ in the Presentation Properties tab and tick the ‘Foreground’ setting to make it appear above the graphs.

Other changes:

• Change the chart title to “Loans to Households in Euro Area”
• Make sure the legend texts are the same as in the example
• Place the legend in the chart area.

Using Cross Section to analyze a group of series

Objectives

• Finding data using Concept & Category
• Using the Cross Section analysis on several series
• Using stripes as layout to represent percentiles

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)

Worksheet

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

Cross section analysis

Graph layout

Create a Scatter Chart

Objectives

• Creating a scatter chart
• Editing the appearance of the chart
• Using basic dynamic properties to write text

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)

Worksheet

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

Scatter Chart

Graph layout

Dynamic Text

 

Finding Data

To find the business surveys used for the IFO Clock, go to:

Country & Region > Germany > Surveys & Leading Indicators > Business Surveys > Ifo > Business Surveys > Trade & Industry > Germany

Locate and select the following series:

• Business Situation, Index, SA
• Business Expectations, Index, SA

Add them to a new document.

Data presentation

Scatter Chart

For this case, you do not need to add any calculations. So, your work in the analysis tree will be limited to adding the presentation.

• Select Series list in the tree
• Click on Add
• Under the heading Presentations, select Scatter chart

Charting

Assigning series to an axis

You should have the Business Situation series on the X-axis, and the Business Expectations series on the Y-axis.

• Go to Graph Layout
• Under Graph (RHS) you’ll see the two series
• First series = X-axis, second series = Y-axis.
• Drag and drop the series, if you need to change the order

How to define the time range

• Click on a data point in the scatter plot to activate the presentation properties
• Look for “Data Range”, on the right side
• Type ‘-10y’ as the start date of the range

Markers

Next you need to add markers and adjust the size of the line, to highlight the data points.

Click on a data point in the scatter plot to activate the presentation properties

Under “Appearance”, locate the graph style setting, and select “Custom”

Set the size of the line and add markers

Using dynamic properties for the axis titles

Dynamic properties automatically generates text based on the series properties you select. If there are any changes to the series you used in a chart, the text will automatically be updated to reflect this.

• Click on one of the axes to activate the presentation properties.
• Look for the title settings and click edit
• Hover over the grey tab that says “dynamic properties”
• Select “graph text” from the available series properties by clicking on the red square

Repeat these steps for the other axis

Location of the vertical axis

• Click on the vertical axis to activate the presentation properties settings, you can find the “Main Scale” setting, and choose “Left”

Observation Labels

In the example chart we used dynamic properties so that the observation label always shows the last value once the data in the chart is updated.

Adding the observation label:

• Right click on the last datapoint and select “Add observation label” from the context menu.

The default text is the date and value for the series, in this case there are two per observation.

To edit the text:

• Double-click on the label.
• Before selecting the dynamic property, type “Last value:“
• To avoid duplicating the information, specify which series you want the information to refer to
• Expand the All series node at the top of the list, under s1, select and add “date”

To get the observation label to shift with new values:

• Single-click on the observation label to activate the settings in presentation properties
• Under “Range” use the drop down to select “start” and “end” as the range parameters
• Set the start to +0, and leave the end blank

Appearance

The final changes that need to be made are:

• Set the title
• Add a subtitle
• Add horizontal & vertical lines to 100
• Remove the legend