Category: Analyzing

Forecast

Overview

The Forecast analysis enables you to add your own values to a series. The forecasting method allows the user to add both future values and overwrite historical ones. Forecasts can also be added in Series list, but this means that the forecasts will be applied to the raw data prior to any analyses. By using forecast analysis, values can be added as an intermediate step in the overall analysis.

Settings

Edit

When you press 'Edit', you will see a dialog where you can specify forecast values and specify how they should be added. 'Value method' lets you specify if the forecast should be added as an absolute value or be dependent on some other value either as a difference or as a percentage change from said value.

Date method gives you the option of defining forecasts both at a specific point in time and at a specific time horizon forward in time from the last observation or forecast.

Value preference

If a row in edit contains both an observation and a forecast, this option lets you choose which value to include in the output.

Missing value method

Here, you specify how the calculation treats missing values. This is the place to choose how to smooth line when using 'Date method' with period bigger than frequency of the document (i.e., 'Relative quarterly' while document is Monthly).

Adding forecast

You can add forecast values to a raw time series in the Series list, or after having applied some calculations to it.

Add a forecast at the beginning of the document

To add a forecast to the raw time series, before any calculations are made, click on Series list in the analysis tree. Open the tab called Forecasts and click Edit next to the series you'd like to add a forecast to.

Please note that these forecasts will be added in the original frequency of the time series, even if the document uses another frequency. Under 'Value preference' you can select which value (original or forecast) should appear when new original values will come in.

Add a forecast after some calculations

To add the forecast after having applied some calculations to it, add the forecast as an analysis later in the analysis tree.

Please note that these forecasts will be added in the original frequency of the time series, even if the document uses another frequency. Under 'Value preference' you can select which value (original or forecast) should appear when new original values will come in.

How to add forecast values?

  1. Select and add the series you want to work with, to the Series list
  2. In the Series list select the Forecasts tab or add Forecast analysis
  3. Click on the edit button to open the Edit forecast dialog box
  4. Select a Value method
    • absolute values
    • absolute differences relative other values
    • percent changes relative other values.
  5. Select a Date method
    • points in time
    • number of years since the previous forecast or value
  6. Select the scale to make it easier to enter forecasts for series expressed in millions or billions.
  7. Add new values to the relevant columns shown.
  8. All added values will be shown as forecasts in the chart, using 50% opacity.

Keep in mind

  • Forecasts are added using the original frequency of the time series
  • Changing the frequency of a document will result in a frequency conversion of all series values, including forecasts
  • Define how empty values should be treated, under Missing Value Representation in the conversion settings tab

  • If you want to apply forecasts before performing any calculation on the series, go to Forecast tab on Series list.

 

Example

Projected GDP of France

In this example, we added forecasts to the quarterly changes in the GDP of France.

Questions

How to extend series with forecast?

You can extend series using mechanism inside Forecast analysis. You can do this by:

  • points in time
  • relatively

See more information under In-app features - Forecast tab.

How to remove a forecast?

Depending how you want to proceed, there are two solutions. You can:

  • remove the forecast values entirely from a series
  • keep the values and remove the forecast indicator for them

To remove forecast values entirely, use the following expression in Series list. It replaces forecast values with null and returns only the non-forecast values of the series.

if(isForecast(series), Null(), series)

To remove the forecast indicator from a series, use the following expression in Series list. It returns the series values as non-forecast values when the condition (the second parameter) is false.

FlagForecast(series, 0)

Forecast values are usually displayed in a different color on charts, so removing the forecast indicator will make the entire series be graphed in the same color.

How to disable the special presentation of forecast values?

Forecast values are usually presented with a distinct style on charts. If you do not want to make a visual distinction of forecasted values, there are two main ways you can do this.

  • Remove the 'forecast flag' from all values in the series. (see above to question How to remove a forecast? for more information)
  • Change the graph presentation so that forecasted values look the same as those that are not forecasts

To do this select the graph in the chart you'd like to change. Under Presentation properties, change the graph style from Automatic to Custom, and click on Forecast. Here, you can change the forecast color to match that of non-forecast values.

How to hide forecast line in legend?

If you want to have one line for a series instead of default two, click on a legend and under Presentation properties > Appearance check 'Hide forecast'.

How to add forecast before the start of time series?

It's not possible. You can append values from another series with one of the join() formulas. For more information about this, click here: Joining with formula

How to smooth line created with Forecast?

If you, for example, have document in Daily frequency and used 'Relative monthly' you got some 'ugly' steps on your chart. You can smooth line by choosing a setting under 'Missing value method':

Cross variance

Overview

The Cross variance analysis allows you to calculate the covariance matrix, the correlation matrix and a matrix displaying the slope of the regression line for a number of time series. This is a useful analysis if you want to quickly assess the pairwise behavior of several time series.

Settings

Estimation sample range

Specify the limits of the sample range for the calculation. If left blank, the calculation will be performed on the entire series.

Type of calculation

Covariance

This option will output the covariance matrix. The values on the principal diagonal of the matrix are the variance of one series. All off-diagonal elements represent the covariance between two series.

Correlation

Choose this option to get the correlation matrix.

Regression slope

Choosing this option, Macrobond will perform simple linear regression on all series included in the analysis. The rows in the matrix are the dependent time series and the columns represents the explanatory ones. The values in the matrix are the slope of the regression line or β in the relationship:

y=α+βx+ϵ

where y is the dependent time series, x is the explanatory series, and the constants are found by minimizing the squared deviations (OLS).

Use only points in time with values in all series

When selected, the matrix will be calculated by only using points where values are present in all included series.

Example

Correlation and Regression slope Matrix

In this example we added the time series for the closing price of many large, listed UK companies. Then, the Cross variance analysis is used to generate the correlation matrix and the regression slope for all pairs of companies.

Correlation

Overview

The Correlation analysis calculates the correlation between series within a range of lags. This functionality is especially useful in detecting the best correlation between two series.  A standard output is a category chart with the correlation displayed on y-axis and the number of lags on the x-axis.

Possible next steps can be to apply the Lag analysis on a Time Chart or build a Regression Model using this information.

We use the standard Pearson correlation.

Settings

There are three settings that you can specify in Correlation analysis:

  • Data range (Estimation sample range) - you specify the range on which the correlation should be calculated. If left empty, the correlation will be calculated on the full common history of both series.
  • Lag range – defines the range of lags on which you want to calculate correlations. This refers to the number of observations, based on the frequency of the document.
  • Correlate to – you match series with another one to calculate the correlation between them. The series that will be lagged to measure the correlation is the one on the left side.

Sequence of calculations

This analysis has a set sequence of calculations. Understanding it will avoid wrong interpretations.

First, the estimation sample range for your series is set (in other words, the analysis will consider the history outside of this range as inexistent). Then one of the series will be moved backward & forward in time to calculate several correlation values.

Consequence:

Let’s assume an estimation sample range is set to start in 2005-01-01. The previous history will be ignored in the analysis, as if the previous values do not exist. You therefore have now:

  • Series 1 ⇒ start date: 2005-01-01
  • Series 2 ⇒ start date: 2005-01-01

Then one of the series (let’s say Series 1) will be lead / lagged by several observations. Let’s consider the correlation calculation when Series 1 is pushed forward by 12 months. The data set will be now defined as:

  • Series 1 ⇒ start date: 2006-01-01 (as the series is pushed forward by 12 months)
  • Series 2 ⇒ start date: 2005-01-01

The correlation between these two series can only be calculated when both series have values. As a consequence, the correlation is calculated on a range starting in 2006-01-01.

Relative dates

Keep in mind that when using relative dates as start date, it is the end date of the calendar that is used as reference. For example, let’s assume:

  • Series 1 ⇒ end date: 2018-10-01
  • Series 2 ⇒ end date: 2018-09-01

if we set '-10y' as Start date, the calculation will be performed since 2008-10-01 as the document’s calendar ends in 2018-10-01.

Alternative options

There are several alternatives to calculate correlation in Macrobond:

Correlation Coefficient

You can use the formula: Correlation(series1, series2)

Note only points in time where there are valid values in both series will be included in the calculation. You can use various methods to fill in missing values.

Rolling Correlation

You can either use the formula Correlation(series1, series2, window), or the Covariation analysis.

Best Lag Correlation

You can obtain the same result as the Correlation analysis by using the formula: CorrelationbestLag(series1, series2, startLag, endLag)

How to read output?

First the x-axis:

  1. If the highest correlation is in the negative side, it should be Lead.
  2. If on the positive side, it should be Lag.
    Then, which series to apply it to?

    • (3.) From the chart: the result should be applied to the series not described in brackets, here it's the ISM PMI.
    • (4.) From the analysis: the output is for the series on the left side (not the 'Correlate to…' series)

Examples

Correlation between US PMI and US Industrial Production

Here, we chose to measure how the US ISM Manufacturing PMI correlates to the US Industrial Production. We set the range of lags to be between -12 months and + 12 months. The analysis will produce a category series.

The output series is presented on a category chart: correlation is on Y axis, while the number of lagged periods is on X axis. The highest correlation appears at the #3 value. This should be read based on how you set the analysis: we chose to measure how the US ISM correlates to the US Industrial Production. Therefore, the result should we read as: The best correlation between the US ISM and the US Industrial Production is when the US ISM is lagged (i.e., meaning pushed forward) by 3 observations.

Correlation coefficient over entire period

See how to calculate correlation coefficient only for an entire period, including how to quickly narrow data range.

Questions

How do I calculate the rolling correlation between two series?

There are two main ways to achieve this:

1. Covariation analysis

The rolling correlation can be calculated using the Covariation analysis. Pick an explanatory series and define the window size by adjusting the length.

2. Formula language

Use the formula:

Correlation(series1, series2, window)

Statistics

Overview

With the Statistics analysis, you can calculate statistical measures for specific series on a time range that you define. This analysis differs from the Cross section analysis, which calculates statistical measures across a group of series at each point in time. Use it when you want to add one of these measures to a chart:

None

This method is used to include a series without any calculation applied.

Mean

Calculates the average of the values as a constant value.

Median

Calculates the middle value in sorted list of values as a constant.

Trend

Calculates the trend as a line fitted using a linear regression.

Percentile

Calculates the specified percentile of the range as a constant.

Min

Calculates the lowest value as a constant.

Max

Calculates the highest value as a constant.

Exponential trend

Calculates the trend as a line fitted using a linear regression on the logarithm of the data.

Essentially the trend line is defined as Exp(Linear(Log(series))).
If the 'min(series) < 0' then a constant is added to the series before the calculation to make it non-negative. It's subtracted again after the calculation.

Line

Calculates a straight line between two data points that you specify: the start and the end date of the range. If no dates are specified as start / end range, the first / last value of the range will be used.

It differs from the Cross section analysis, which calculates statistical measures across a group of series at each point in time.

Settings

For each calculation in statistics, you can:

  • Set the Date range in which the calculation is performed.
  • Select Extrapolate to extend the graph of the calculated output outside of the date range that you set.
  • Add a Standard deviation band and adjust its Coefficient.
    • The Coefficient defines the number of standard deviations from the mean that will be included in the confidence interval. The coefficient is the percentage of values that should fall within the boundary of the confidence interval assuming normally distributed data.

Examples

Mean and standard deviations

In this example, we calculated the means of the CAPE Shiller Index in two time windows with +1 / -1 standard deviation bands around them.

Mean per decade

Here, we calculated the average growth rate per decade for the US GDP.

Line in Statistics

We used the line statistic to draw a straight line between two data points. We chose to extrapolate the lines backwards and forwards in time.

Trend line

Using the log of the real US GDP, we calculated two linear trends: one between 2000 and 2006, and another from 2009.

Questions

Why under Coefficient there is a 68.3?

It comes from 68–95–99.7 rule known also as three-sigma rule of thumb. The 68.3 indicates one standard deviation, 95 - two, and 99.7 - three. As default trend band is set to +/- 1 standard deviation.

How do I calculate the mean of a series across several periods?

  1. Add 'Mean' as a calculation.  
  2. Enter the data range you want used in the calculation  
  3. Repeat these steps for all the time periods you are interested in.  

For each calculation, you can untick the 'Extrapolate' setting so that each line is drawn for the time period it is calculated on.

How do I add a trend channel / trend band?

Insert a 'Trend' in the Statistics analysis and tick the 'Std. dev.' checkbox.

Change the coefficient to control the number of standard deviations defining the width of the trend channel (68.3, 95, 99.7). The default is +/- 1 standard deviation. Standard deviation is calculated for the whole history, it's not a 'moving window std dev' - for this use StdDev(series, window) formula.

If you are searching for trend line chart see: How to add a trend line/best fit line?

Cross section

Overview

The Cross section analysis produces output series by performing a calculation across a set of input series at each point in time, e.g. average of multiple series.

Settings

Apart from the available calculations and their properties, there are three main settings regarding the output series and the calculation range.

Include only observations where there are values for all series

When this is checked, calculations will be performed only when all series have values. It's useful feature when series are uneven.

Do not include series used in calculations in the output

This excludes from the output all the series that were used as inputs in the calculation. Only the calculated output series and other series in your document that were not used as input will be visible on your chart or table.

Include new series automatically

When this is checked, any new series that you add to your document will be automatically included in the calculations.

Calculations

Each calculation can have different set of series. To see which series are checked click on a calculation name in Calculations panel.

High

Find the highest observation.

Low

Find the lowest observation.

Mean

Calculate the mean of the observations.

Median

Calculate the median of the observations.

Percentile

Calculate the percentile of the observations as specified by the parameter.

Product

Calculate the product of the observations.

Standard deviation

Calculate the standard deviation of the observations.

Sum

Calculate the sum of the values.

Count

Count the number of observations that are TRUE (>=0.5).

Count valid

Count the number of valid observations.

Average correlation

Calculate the average correlation of  the observations where length defines correlation window.

Average absolute correlation

Calculate the average absolute correlation of  the observations where length defines correlation window.

Lower tail mean

Calculate the mean of the lowest observations as specified by the parameter.

Upper tail mean

Calculate the mean of the highest observations as specified by the parameter.

Examples

Performing several statistical calculations across series

With Cross section you can perform several calculations across many series. In this example, we used the MFI interest rates of the main Euro Area countries.

Cutting series with Count valid

Use 'Count valid' calculation to trim all series to the first and last common point.

Average of 3 top and bottom countries

In this example we use 'Upper tail mean' and 'Lower tail mean' to calculate average of top 3 and bottom 3 countries.

Pairwise correlation

Here, we calculated the average correlation across all sectors of the S&P 500, on a rolling basis. The application will first calculate the correlation for each pair of series. Then, an average of all these correlations will be performed to get a single series.

Covariation

Overview

The Covariation analysis can measure several statistical indicators on a rolling basis:

  • standard deviation
  • correlation
  • covariance
  • linear regression intercept (alpha)
  • linear regression slope (beta)

Settings

Covariation type

Select the type of statistical indicator you want to calculate.

Explanatory

Select the series that will be used as explanatory in the calculation. This setting is not available for standard deviation.

Length

Select the rolling window for your calculation.

Annualize

Check this if you want the output series to be annualized. This is only available for standard deviation and covariance.

Examples

Rolling correlation

In this example, we used the covariation analysis to calculate the correlation between the US ISM PMI and the S&P 500 (transformed in YoY % change) on a rolling window of 5 years.

We duplicated the S&P 500 series in Series list. We did this because the series that is used in the analysis will be graphed as the rolling correlation, and we want to include the original series in the graph as well.

Regression slope (beta)

Here, we used the  analysis to measure the  slope for two stocks  to the index they belong to the S&P 500 We decided to  the  slope on a rolling window of 1 year.

Prior to adding the Covariation analysis, we applied a rate of change to transform the data into daily performance (→ Change over period %, length: 1 .)

Frequency conversion

Overview

The Frequency conversion analysis converts all your time series into one common frequency. This ensures consistent, comparable calculations and presentations of your work.

You can convert the frequency directly in Series list, but this means that the selected frequency will be applied to the entire document prior to any analyses. The main use case for the frequency conversion analysis, then, is when you want to change the frequency of your series after having applied some analysis to the raw data.

Settings

Frequency

Set the common observation frequency you want for your series.

Observations

Select which points in time should be included in the output.

  • Any series outputs only points in time for which at least one series has data
  • All points outputs at each point in time based on the frequency, even if there are skipped periods values

Date range

Specify ranges of dates that you would like to limit your output to. By default, the entire range of the longest series will be the range of your output.

When you want to convert a series to a lower frequency, a full calendar of observations is needed. For instance, to convert a daily series to monthly, a full calendar of daily observations per month is necessary to produce a monthly value. If there are only partial periods available at the start or end of range, you won’t get a value for that month.

To lower frequency conversion method

Select the method which will be used to change series to a lower frequency than the original (one value must be calculated from the many values in the period). The options available are:

  • Automatic, selects the method automatically based on the properties of the series
  • Average, uses the average all values in each period
  • First, uses the first value in each period
  • Flow, aggregates all values in each period
  • Highest, uses the highest value in each period
  • Last, uses the last value in each period
  • Lowest, uses the lowest value in each period
  • Percentage change, aggregates the percentage change of each period
  • Percentage change conditional, uses the percentage change method when it makes sense with the series properties

Partial period to lower frequency method

Select how you want to adjust incomplete periods when converting a series to a lower frequency. The methods available are:

  • Do not extend, only include full periods of lower frequency
  • Automatic (name_of_method), automatically select the method based on series properties
  • Repeat, use the last (or first) value to extend the incomplete periods
  • Flow, use the last (or first) partial average to extend the incomplete periods
  • Rate of change, use the previous (or next) year’s rate of change to extend the incomplete periods
  • Zero, extend the incomplete periods with zeroes

To higher frequency conversion method

Select the method which will be used to convert series to a higher frequency than the original (many values must be calculated from one value in each period). The methods available are:

  • Automatic, selects the method automatically based on the properties of the series
  • Cubic interpolation, uses cubic interpolation over the period
  • Distribute, distributes the value over the period
  • Linear interpolation, uses linear interpolation over the period
  • Percentage change, distributes the percentage change over the period
  • Percentage change conditional, uses the percentage change method when it makes sense with the series properties
  • Pulse, sets only the value for the first observation in the period
  • Quadratic distribution, uses quadratic interpolation to distribute the value over the period
  • Same, uses the original value for all periods

Example

Changing the frequency of a series

In this document, we wanted to calculate the average daily performance per year of the S&P 500. We need the series in daily frequency to calculate the daily performance using the rate of change analysis. Then, we used the frequency conversion analysis to transform the daily performance series into an annual series, using average as the conversion method.

(Setting the frequency to annual in Series List won't allow us to calculate the daily performance, as the data will only contain one observation per year prior to any calculations.)

Questions

Application shows ridiculously high value after using Partial period's Repeat

Let's say you have Monthly series and you use Partial period: Repeat. Empty months are filled in with last available value. That's how it should be. Next you want to change frequency from Monthly to Annual - most series' metadata is set to use 'Last' value, but some are set to 'Flow' method meaning they will aggregate all values within a year. In such cases we recommend to use Partial period: Zero.

You can see and change series settings under Frequency conversion analysis:

How can I mimic frequency conversions using formula language?

To higher frequency

To mimic the methods used to convert a series to higher frequency, you can use the At() formula which returns the value in the series at the specified observation number.

At(series, observation)

Here are a few examples on how to use the formula:

At(series, StartOfYear())

Sets all observations for every calendar year to the first observation of each year.

At(series, EndOfYear())

Sets all observations for every calendar year to the last observation of each year.

At(AggregateSumAnnual(fx:s1), EndOfYear())

Aggregates the observations within a calendar year which means the last value of each year will be the annual aggregate. The At() formula then sets all the observations for every calendar year to the last value of each year.

At(Mean(fx:s1, YearLength()), EndOfYear())

Sets all observations for every calendar year to the mean value of each year.

At(High(fx:s1, YearLength()), EndOfYear())

Sets all observations for every calendar year to the highest value of each year.

At(Low(fx:s1, YearLength()), EndOfYear())

Sets all observations for every calendar year to the lowest value of each year.

To lower frequency

To mimic the methods used to convert a series to lower frequency, you can use the if() and FillNullWithLinear() formulas.

To get Annual value (using 'Last' method) use:

FillNullWithLinear(if(Counter()=EndOfYear() | Counter()=EndValid(fx:s1), fx:s1, Null()))

To get Quarterly value (using 'Last' method) use:

FillNullWithLinear(if(Counter()=EndOfQuarter() | Counter()=EndValid(fx:s1), fx:s1, Null()))

To get Monthly value (using 'Last' method) use:

FillNullWithLinear(if(Counter()=EndOfMonth() | Counter()=EndValid(fx:s1), fx:s1, Null()))

For more information: Built in formulas and functions

Arithmetic

Overview

The Arithmetic analysis can be used to create ratios between series, express series as percentage of another series, or to calculate spreads.

The Arithmetic analysis allows you to perform simple operations on time series. You can add, divide, multiply and subtract series together or/and with any value as operand. The main benefit of the Arithmetic analysis is that you can easily apply the same operation to multiple series at once, with the Fill down button.

Settings

The analysis is divided into two blocks:

  • operations with another series (the first two columns) – here you select the type of calculation and then the series to use (series operand).
  • operations with values (last two columns) – you select the type of operation and the value you want to use.

Alternative solutions

However, with Arithmetic you can only perform operations with one other series at a time. If your calculation involves multiple series, using the formula language is a faster process. If you want to add more than two series together, we then recommend using the Cross Section analysis.

Example

United States: Corporate benchmarks - Spread Baa vs Aaa

In this example, we used the Arithmetic analysis to calculate the spread between Baa and Aaa corporate benchmarks for the US. We then multiplied the spread by 100 to express it in basis points.

Questions

How to calculate a spread?

There are two main possibilities to subtract one series from another:

  • In Arithmetic analysis:

Choose 'Subtract' from the first operation field.

NOTE: The series on the left side will be used for the calculation and won’t be available in the later parts of analysis tree.

  • With formula:

You can simply use the ' - ' (minus sign) operand in the formula language.

For more information about Formula language see: Formula

Seasonal adjustment MA

Overview

The Seasonal adjustment MA  analysis removes the seasonal pattern from a series using moving averages. The algorithm used is not as advanced as in Seasonal adjustment Census X-13, but is much faster and easier to use and works well in many cases. The method works by calculating a seasonal factor for each period and then adjusting all the values in the series with these factors. The analysis will produce the adjusted series as a time series and the seasonal factors as a category series.

Method

The trend is removed by subtracting or dividing the series with a long moving average. The number of observations in the moving average is the number of observations in a year based on the frequency. Monthly factors, however, will be used for all series with higher frequency than a month.

Seasonal factors are found by calculating the mean for each period within the estimation range. The factors are then normalized so that the sum is 0, for the additive method, or 1, for the multiplicative method.

The adjusted time series is then created by subtracting or dividing by the factors.

Settings

Method

Additive

It removes the seasonal component by subtracting the average deviation from the mean for the period. This method works best if the series is relatively constant over time.

Multiplicative

It removes the seasonal component by dividing by the average factor compared to the mean for the period. It works better if the seasonal factor is proportional to the level of the series, which is often the case when the series shows an exponential pattern.

Example

Multiplicative method

This chart shows the US Industrial Production Index series both seasonally adjusted and unadjusted. We used multiplicative method do adjust the series. In 'Seasonal weights' category chart the weights per month can be viewed.