The purpose of the Macrobond formula language is to make calculations on series of values. The  series are typically time series. The result of a formula is a time series.

The formula language is available in Macrobond version 1.0.34 and later. This document refers to version 1.13 and if you use an older version of Macrobond, you might find that some functions are not available.


Here you can find the list of the functions of the Macrobond formula language.

How calculation works

Series names

You can use names of series from the database in formulas. A very simple example is the following:
sek
which evaluates to the series called sek.

Numerical constants

Examples of numerical constants are:

123

-123

123.456

123.456e-3

Formulas and series

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. Each value can be a number or a null value.

Before any calculations are made, all series in the formulas are converted to the same frequency. They are also treated as series of the same length by adding null values to the beginning if the length differs.

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. With some fictional series values the result can look like this:

Observation numberseknoksek/nok
1971-01-0405.1643
1971-01-0515.1628
1971-01-0627.13675.16140.723219
1971-01-0737.13865.16490.723517
1971-01-0847.13825.16310.723306
1971-01-1157.13905.16420.723379
1971-01-1267.14115.16430.723180
1971-01-1377.13905.16150.723000

In addition to operators, such as +, -, * and /, there are also functions. Functions can take zero or more parameters. Parameters are written within parentheses and separated with commas. 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 numbersekSum(sek)
1971-01-0607.136742.8326
1971-01-0717.138642.8326
1971-01-0827.138242.8326
1971-01-1137.139042.8326
1971-01-1247.141142.8326
1971-01-1357.139042.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 numbersekconstantsek*100
1971-01-0607.1367100713.67
1971-01-0717.1386100713.86
1971-01-0827.1382100713.82
1971-01-1137.1390100713.90
1971-01-1247.1411100714.11
1971-01-1357.1390100713.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 fictional series sek, the result would look like this:

Observation numbersekSum(100)
1971-01-0607.1367600
1971-01-0717.1386600
1971-01-0827.1382600
1971-01-1137.1390600
1971-01-1247.1411600
1971-01-1357.1390600

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

Many functions are available in a form that takes a parameter that specifies the length of a window.

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

The blue numbers in the table below show which numbers are included in the sum for one of the resulting values.

Observation numbersekSum(sek,3)
1971-01-0607.1367
1971-01-0717.1386
1971-01-0827.138221.4135
1971-01-1137.139021.4158
1971-01-1247.141121.4183
1971-01-1357.139021.4191

Another 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 that is called an observation number. You can get a series of the ordinal numbers with the function Counter().

Observation numbersekCounter()
1971-01-0607.13670
1971-01-0717.13861
1971-01-0827.13822
1971-01-1137.13903
1971-01-1247.14114
1971-01-1357.13905

If it is a time series, each ordinal number is associated with a date. 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 numbersekDate(1971,1,8)
1971-01-0607.13672
1971-01-0717.13862
1971-01-0827.13822
1971-01-1137.13902
1971-01-1247.14112
1971-01-1357.13902

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

Observation numbersekAt(sek, Date(1971,1,8))
1971-01-0607.13677.1382
1971-01-0717.13867.1382
1971-01-0827.13827.1382
1971-01-1137.13907.1382
1971-01-1247.14117.1382
1971-01-1357.13907.1382

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. Instead you should use FirstValid(sek).

Logical values and functions

The logical values True and False are represented by 1 and 0.

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 numbersekLag(sek, 1)sek > Lag(sek, 1)
1971-01-0607.1367
1971-01-0717.13867.13671
1971-01-0827.13827.13860
1971-01-1137.13907.13821
1971-01-1247.14117.13901
1971-01-1357.13907.14110
1971-01-1367.1390

The function If is commonly used with logical expressions. 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),Hi(sek, 10), sek)

Operators

All operators work on constants and series. Here are the operators sorted in order of precedence.

OperatorDescriptionPrecedence
-Unary minus7
*Multiplication6
/Division6
+Plus5
-Minus5
!Logical “not”4
>Greater than3
>=Greater than or equal3
<Less than3
<=Less than or equal3
=Equal3
<>Not equal3
&Logical “and”2
|Logical “or”1

Parentheses can be used to further control the order of evaluation.

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.

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:

User defined functions

User defined functions are used as a way to structure formula expressions and to reuse commonly used expressions. The names of user defined functions and variables must start with a period “.” followed by a letter. The rest of the name can contain letters, numbers and underscore “_”.

The names of parameters follow the same rule, except that they should not begin with a period.

In-line functions and variables

Sometimes you want to use the same sub expression several times in a formula. In this example, the sub expression nok/sek is used twice:
segdp*nok/sek + sem1*nok/sek

You can make the expression shorter and easier to read by using an inline variable. In-line variables and functions are declared by using the let -in-end construct. The expression above can be rewritten like this:
let
  .fx = nok/sek
in
  segdp*.fx + sem1*.fx
end

In the new expression, a variable called .fx is declared and assigned the result of nok/sek and then used in the expression.

You could write this as one line if you like, but in general it is easier to read if you use line breaks.

In addition to variables, you can declare in-line functions. Let us take this example:
usgdp/lag(usgdp, 1)-1 + usm1/lag(usm1, 1)-1

Since the same calculation is done for two different series, this can be written like:
let
  .ret(series) = series/lag(series, 1)-1
in
  .ret(usgdp) + .ret(usm1)
end

In this example, a local function called .ret is declared: .ret(series) = series/lag(series, 1)-1
It is declared to take one parameter.

You can define several variables and functions in one expression:
let
  .ret(series) = series/lag(series, 1)-1
  .prop = 0.6
in
  .ret(usgdp)*.prop + .ret(usm1)*(1-.prop)
end

Reusable functions

The Function editor

If you have a function definition that you want to use in several places, you can add them to the list of functions in the Function editor. You bring up the editor by selecting “User defined functions…” on the Edit menu when the Analytics activity is active.

FunctionEditor 1

You can store your functions in one of the different stores. Functions stored in the Company account will be available for all users in the company. Functions in the Personal account store are only available to you. You can also store functions that should only be available in the current document. For some users there is a store called Library. This store is available to all users in the company, but only some users, with special access rights, can write to it.

You add a new function to a store by pressing the Add button. The new function can then be edited in the editor. You can also select what group it belongs to and you can type a description of the function. This description will be available as a tooltip in the Formula editor.

You can delete a function by selecting it in the list to the left and then press the Del key or select Delete from the context menu. Copy, Cut and Paste can be used in the list to move or duplicate functions.

Function order

Two functions with the same name, but with a different number of parameters, are regarded as two separate functions.

A function with the same name and same number of parameters can be defined several times. When you use a user defined function in a formula in the Series List, the formula evaluator will try to find the definition of the function by looking in the following order and pick the first instance:

  1. In-line function definitions
  2. In the Document store
  3. In the Private account store
  4. In the Company account store
  5. In the Library store (if available)

The stores in the Function editor are presented with the store it will look in first at the bottom and the last store at the top of the list.

If the function is defined several times within a store, the last definition will be used. The last definition is the one closest to the bottom of the list in the Function editor.

When user defined functions are used in other user defined functions, the evaluator will look for definitions earlier in the same store and then in the earlier stores, if there are any.

Sharing documents that uses user defined functions

In order to make it possible to share documents with other users that do not have access to the same function stores, a hidden copy of all user defined functions used in the document, is stored in the document.

When a user opens a document that uses user defined functions, and the user does not have access to the original function stores, the document is still functional. You can change analysis, charts and tables in the document, but you cannot edit the expressions in the Series List.

As an example, if one user has a private function called .ret(series) and uses this in a document in a formula like .ret(sek) and then sends this document to another user, then the other user will see this information when looking at the Series List:

SharingDocs with functions 1

It is important to note that even if the other user has also defined a function with the same name in his private store, it will not be regarded as the same function since it is not stored in the same store. You will still get the yellow bar and the original function will be used.

You can unlock the document by clicking on the yellow bar. The relevant functions will then be copied to the Document function store and you are free to edit the expressions.