• Overview
  • Why use Formulas?
• How to work with Formula analysis?
  • Aliases
  • Expression
    • Case sensitivity
  • Include input series as output
  • Applying formulas: Series list vs. Formula analysis
    • How to create a formula in the Series list?
    • How to create a formula in the analysis tree?
  • Frequency 
• How does the Formula language work?
  • Input variables
  • Dates
  • Window parameter (length)
    • What happens at the start of series?
• Which formulas should you get familiar with first?
• Example
• E-learning

Overview

The Formula analysis is used to preform calculations on time series using a formula language. Formulas and functions can be combined yielding powerful calculations in a single layer. The flexibility of the feature allows you to preform calculations that cannot be carried out using other analysis tools in Macrobond.

Why use formulas?

• Combine calculations - apply several calculations at once, to perform complex calculations, which might not be available through the predefined set of analyses.
• Customize - adjust the calculations to your needs and transform series in various ways.
• User preference - create formulas from scratch with the ‘formula language’ or choose from a library of predefined formulas.

How to work with Formula analysis?

Aliases

When Formula analysis is applied all series in the document are assigned aliases. The aliases are on the form fx:s1, fx:s2, fex:s3, … , fx:sN where N is the number of added series. You can change an alias, however, it needs to start with 'fx:'.

Expression

In the expression column, there are two ways of adding a calculation. Either write the expression in the text field or click the 'fx' button to access the formula editor. The formula editor gives you a good overview when writing longer expressions and has help mechanisms for finding functions.

Case sensitivity

You can type formulas and series codes using capital letters or lower case, the application will know what you mean.

Include input series as output

This setting is selected by default. If selected, all series, both input series and calculations, will be available in the chart or analysis following the formula layer.

Applying formulas: Series list vs. Formula analysis

You can utilize the formula language in both the Formula analysis feature as well as in Series list. However, there are some differences in how references are made.

When writing formulas in Series list, the calculations will be applied to the raw data.  When writing formulas in Formula analysis the calculations will be applied on top of any preceding analysis. This means Formula analysis lets you add formulas as an intermediate step in your overall analysis.

Because the Formula analysis is an intermediate step the way you reference to series differs. In Series list you refer to the unique name of the series. In Series list, the name of the series can be observed in the expression column. The name of a series is also displayed in time series information or by hovering over the series in the database. In formula analysis you refer to a series by its alias displayed in the alias column and not the name of the series.

How to create a formula in the Series list?

NOTE:

When in the Series list, remember to use the Macrobond series codes to identify the series.

1. Click on the 'fx' button located next to the series expression field.
2. Scroll through the list of available functions or use the filter field to narrow your search.
3. Double click to select a formula from the list.

If you want to use directly a series code as part of the formula, highlight the code first, that way it will be integrated into the formula once you select it from the list.

How to create a formula in the analysis tree?

NOTE:

The application will automatically assign aliases to all time series to indicate that analyses may already have been applied to the raw data, thus changing the series from its original form. Aliases can be customized, but must start with the prefix 'fx:'

1. Start by clicking on the point at which you want to apply the formula in the tree.
2. Click on 'Add' or 'Insert' and select Formula from the list to open the Formula editor.
   
3. Enter the relevant formula expression in the field.

By default, 'Include input series as output' is checked. This will allow you to select both the input series as well as the calculated series, in any of the analyses or presentations you apply after. When unchecked, the input series will no longer be available as output.

Frequency

Every formula produces a time series matching the frequency of the document.

How does the Formula language work?

Input variables

Formulas contain a predefined set of variables which need to be added to the expression. Different formulas require different types of variables, for instance a series, a number or an observation number. Below you will find a few important ones:

• series – represents the name of series, whether it is a series code from the Macrobond database or a reference alias when using the Formula analysis
• number – a digit expressing the value of a parameter e.g., LastValid()
• observation number – each cell in a series time table has an ordinal number called an observation number, used to reference the corresponding date. You can get a series of ordinal numbers with the formula Counter()
• window – a parameter that specifies the length of a window of observation. The input is a number. It is used for rolling types of formulas

It’s crucial to input the correct variables and to know what the name refers to, as many formulas will require several variables. For example:

Cop(series, length)

This formula is built around two variables that you need to input: the series code and the length of the period, for which the change in percentage will be calculated.

Dates

In formula language, dates can only be written in a specific format. The format is Date(YYYY, MM, DD), which contains three variables.

To show an example we can use the Swedish currency, SEK. if you would like this time series to start from a specific date, you would use the CutStart() formula. Like this:

CutStart(sek , date(1971 , 01 , 04))

This will produce a modified version of the 'sek' time series, which will now start on the 4^th of May 1971. Below is an example of how this would look in a table:

1971-01-08	4	5.1631
1971-01-07	3	5.1649
1971-01-06	2	5.1614
1971-01-05	1	5.1628
1971-01-04	0	5.1643

Notice that the earliest value in the series is assigned with the observation number ‘0’.

Window parameter (length)

Window parameter is designed for rolling calculation where said window represents moving time range. Length can be expressed as:

• Number (of observation) i.e., 4, 20, 252;
• Series i.e., an Account in-house series with numbers. If the values in a series are not natural numbers, application will round them down to the nearest natural number.
• Period with formulas i.e.,:

yearlength()
YearsLength(1)
quarterslength(4)
Monthslength(12)
Weekslength(52)

For example:

Median(usgdp, 12)
StdDev(sek, YearsLength(10))
mean(decpi, Monthslength(4))

What happens at the start of series?

The window is getting more narrow with each observation.

If you do not want these to appear we recommend using below custom formula:

.mean(series, window) = if(Counter(series)>=window, mean(series, window), NULL())

For more information how to add and use formulas with dot see User defined formulas.

Which formulas should you get familiar with first?

The Macrobond Formula library is vast, but some formulas are more common than others. Below is a list of the most popular formulas that may be useful to start with.

Date(year, month, day)

Returns the observation number of the specified date, where month is 1-12 and day is 1-31. If there is no observation at the specified date, the observation number of the closest previous observation is returned.

COP(series, length)

Returns the percentage change over the number of specified observations. This is the same calculation as 'Change over period %' in the Rate of change analysis.

Momentum(series, length)

Returns the difference between the series and a lagged series. This is the same calculation as 'Change over period - value' in the Rate of change analysis.

YearLength()

Returns the number of observations per year based on the frequency.

This is a very useful formula when you need to set a length of 1 year, as it won’t be affected by a change of frequency in the document.

Example: cop(usgdp, 4) --> with a document set to quarterly frequency, this returns a 4-quarter percentage change (i.e., 1 year % change). But changing the document’s frequency to monthly will result in a 4-month growth rate. Yearlength() always calculates the numbers of observations in a year based on the frequency of the document and will automatically update if you change the frequency.

Join(series1, series2)

Returns a series that is a combination of two series, joined at the end of series1. There are variants to this formula depending on how you want to transform the two series into one.

Counter()

In Macrobond, every date has a unique observation number.  This command returns a series of all observation numbers in the document.

Now()

Returns the observation number of today's date. You should use this function carefully since it depends on the time zone of your computer. In most cases, it is often better to use the End() or End(series) functions.

First(series) & Last(series)

Returns a series consisting of the first or the last available value of a series.

Start(series) & End(series)

Returns a series consisting of the first or the last observation number of a series. This is used to select or adjust a value at a specific point in time.

Month()

Returns a series of the month (1-12) for each observation. This helps as with a more complex formula, which requires you to identify specific months.

If(condition, value1, value2)

Returns the first value if the condition is True and the second value if it is False.

Extend(series, observation, number)

Extends a series to a specific observation in the future using the specified number.

Standardize(series)

This is also known as a Z-Score. It returns a normalized series which counts (in standard deviations) how far a value is from the mean value of the time series. The mean is represented by '0' and one standard deviation is '1'.

For more about formulas see: The Macrobond formula language and Built-in formula functions

Example

United States GDP 1 Year Sum

Here, using formula we calculated 1-year rolling sum for United States GDP.

E-learning

After reading this article you should be equipped with a basic understanding of how formulas work to be ready for next step, which is completing the exercises in our Test Your Skills section for Formula language. Doing these tests will ensure that you’re fluent in our formula language.