Worksheet – Case #8 – Using Cross Section to analyze a group of series

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.