The Macrobond formula language

Overview

The purpose of the Macrobond formula language is to make calculations on series. The series are typically time series, and the result of a formula is a time series. By using formulas, you can combine arithmetic operations with built in functions to create powerful calculations. You can find a complete list of built-in functions here.

Important information

Formulas can be utilized in both the Series list and Formula analysis. The method of writing formulas is identical between the two except for how references to series are written. The instructions below are targeted at making calculations in the Series list. Thus, when referencing to a series, the name of the series will be used and not an alias. To learn more about aliases and the differences between using the Series list and the Formula analysis for calculations, click here.

How calculation works

Series names

You can use the name of a series from the database in formulas. A very simple example is:

sek

Which references to the Macrobond series called 'sek'.

Numerical constants

Examples of numerical constants are:

123

-123

123.456

123.456e-3

Arithmetic operations

An example of a simple formula is the following:

 sek/nok

which calculates the cross rate between the Swedish and the Norwegian krona.

The time series sek and nok are treated as vectors of values. In a vector, each value can either be a number or a null value.

Before any calculations are made, all series in the formulas are converted to the same frequency. The series also need to be the same length. This is achieved by adding null values to the beginning of the vectors until they are of equal length.

Example

The division operator / takes two series and produces a new series by dividing the corresponding values in the two series. For most calculations, the result is null when any of the values is null.

This can be illustrated using a fictional example:

Observation number sek nok sek/nok
1971-01-04 0 5.1643
1971-01-05 1 5.1628
1971-01-06 2 7.1367 5.1614 0.723219
1971-01-07 3 7.1386 5.1649 0.723517
1971-01-08 4 7.1382 5.1631 0.723306
1971-01-11 5 7.1390 5.1642 0.723379

The operators, such as +, - and * handles null in the same way.

Functions

In addition to the arithmetic operators there are also functions. A function can take zero or more parameters. Parameters are written within parentheses and separated with commas.

Example

An example of a formula using a function is the following:

 Sum(sek)

This will calculate the sum of all values in the series. With the same fictional values, the result would look like this:

Observation number sek Sum(sek)
1971-01-06 0 7.1367 42.8326
1971-01-07 1 7.1386 42.8326
1971-01-08 2 7.1382 42.8326
1971-01-11 3 7.1390 42.8326
1971-01-12 4 7.1411 42.8326
1971-01-13 5 7.1390 42.8326

Another example is the following formula:

 sek*100

The result is a new series where each value is multiplied by 100. There is no function multiplying a series with a constant. Instead, the formula interpreter will create a series where each value is 100 and then multiply these series:

Observation number sek constant sek*100
1971-01-06 0 7.1367 100 713.67
1971-01-07 1 7.1386 100 713.86
1971-01-08 2 7.1382 100 713.82
1971-01-11 3 7.1390 100 713.90
1971-01-12 4 7.1411 100 714.11
1971-01-13 5 7.1390 100 713.90

In most cases you do not need to know about the conversion of constants to series, but it can help you to understand what happens with a formula like this:

Sum(100)

If you have this formula together with the series 'sek', the result would look like this:

Observation number sek Sum(100)
1971-01-06 0 7.1367 600
1971-01-07 1 7.1386 600
1971-01-08 2 7.1382 600
1971-01-11 3 7.1390 600
1971-01-12 4 7.1411 600
1971-01-13 5 7.1390 600

You get the value 600 because the constant 100 is converted to a series with six values of 100 and then this series is summed.

Observation windows (rolling)

There are functions in Macrobond with the same name that take different number of parameters. Many of these functions take a parameter that specifies the length of a window. With them you can create rolling series.

Example

Sum(sek) This is the sum of all values in the series.
Sum(sek, 3) This is the sum of three observations including the current observation and the two previous ones.

If there are not enough data points to fill the window, the values will be null.  This is depicted in the table below.

Observation number sek Sum(sek,3)
1971-01-06 0 7.1367
1971-01-07 1 7.1386
1971-01-08 2 7.1382 21.4135
1971-01-11 3 7.1390 21.4158
1971-01-12 4 7.1411 21.4183
1971-01-13 5 7.1390 21.4191

Another useful example of a function that takes a window length is Mean(series, window). The result of this function is also known as the moving average.

Observation numbers

Each position in the series has an ordinal number called an observation number. You can get a series of the ordinal numbers with the function Counter().

Observation number sek Counter()
1971-01-06 0 7.1367 0
1971-01-07 1 7.1386 1
1971-01-08 2 7.1382 2
1971-01-11 3 7.1390 3
1971-01-12 4 7.1411 4
1971-01-13 5 7.1390 5

If it is a time series, each ordinal number is associated with a date.

Please note that you should never make any assumption about what the ordinal numbers are. Thus, you should not use an expression like At(sek, 0) to get the first value of a series. The ordinal number of the first observation can be any number. Instead, you should use FirstValid(sek), which returns the ordinal number of the first observation that is not null.

Example

There are a number of functions related to dates and ordinal numbers. A useful function is Date(year, month, day) that returns the ordinal number for a specific date.

Observation number sek Date(1971,1,8)
1971-01-06 0 7.1367 2
1971-01-07 1 7.1386 2
1971-01-08 2 7.1382 2
1971-01-11 3 7.1390 2
1971-01-12 4 7.1411 2
1971-01-13 5 7.1390 2

With the function At(series, observation) you can get the value at a specific observation number.

Observation number sek At(sek, Date(1971,1,8))
1971-01-06 0 7.1367 7.1382
1971-01-07 1 7.1386 7.1382
1971-01-08 2 7.1382 7.1382
1971-01-11 3 7.1390 7.1382
1971-01-12 4 7.1411 7.1382
1971-01-13 5 7.1390 7.1382

Logical values and functions

The logical values in a series are represented by 1 for True and 0 for False.

Example

In the following example the operator > (greater than) is used to compare a series with a lagged series. The result will be True (1) when the value has increased since the last observation.

Observation number sek Lag(sek, 1) sek > Lag(sek, 1)
1971-01-06 0 7.1367
1971-01-07 1 7.1386 7.1367 1
1971-01-08 2 7.1382 7.1386 0
1971-01-11 3 7.1390 7.1382 1
1971-01-12 4 7.1411 7.1390 1
1971-01-13 5 7.1390 7.1411 0
1971-01-14 6 7.1390

The function If is commonly used with logical expressions. The function takes three parameters. The first parameter is interpreted as a logical expression that determines if the result should be the value of the second or the third parameter.

The following example replaces any null values (missing values) with the highest value of the last ten observations

If(IsNull(sek),High(sek, 10), sek)

Comments

You can write comments in formulas by typing two slashes ' // '. Everything after the slashes to the end of the line, will be regarded as a comment and not part of the formula expression.

Operators

All operators work on constants and series. Here are the operators sorted in order of precedence. Parentheses can be used to further control the order of evaluation.

Operator Description Precedence
- Unary minus 7
* Multiplication 6
/ Division 6
+ Plus 5
- Minus 5
! Logical “not” 4
> Greater than 3
>= Greater than or equal 3
< Less than 3
<= Less than or equal 3
= Equal 3
<> Not equal 3
& Logical “and” 2
| Logical “or” 1

The formula editor

The Formula editor helps you write formulas. In the Series list, you can bring up the Formula editor by clicking on the 'fx' button to the right of an expression, or by pressing Ctrl+E on the keyboard.

Some of the useful features of the editor are:

  • You can double click on a function in the list to the right to insert a function.
  • You can filter the list of functions by typing a text in the box above the list of functions. Only functions that contain the text in the function name or description will be included in the list.
  • The tooltip of a function in the list or in an expression will show details about the function:
  • The tooltip of a series will show the title of the series: 
  • When the text cursor is just before a left parenthesis or directly after a right parenthesis , a blue shade on the background will show the area between the matching parentheses:

 

  • When the text cursor is within or next to an identifier, all instances of that identifier in the expression are shaded:

 

  • The editor will check the syntax as you write and show a red squiggle if there is an error. You can see the error text at the bottom of the editor or as a tooltip:
  • Numerical constants are dark red. Series, parameters and variables are blue. Comments are green.
  • The series used in the document will be displayed at the end of list of functionsThe tooltip will tell you the title of the series. You can double click to enter them into the formula.

  • Auto-suggestions for series name. When you start typing a series name in the Formula editor, suggestions will be shown if the text you type matches the start of a series in the document or any of the series you have marked as favorite. Use the mouse or press the down arrow to walk the list. The tooltip will give you the series title. Press 'Enter' to select.

User defined functions

User defined functions are a way to structure formula expressions and to reuse commonly used expressions. It allows you to both reduce the amount of formulas you write and save the functions for future use. For more information see: User defined formulas