The Macrobond formula language
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.
- How calculation works
- The formula editor
- User defined functions
Here you can find the list of the functions of the Macrobond formula language.
How calculation works
You can use names of series from the database in formulas.
A very simple example is the following:
which evaluates to the series called sek.
Examples of numerical constants are:
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:
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:
This will calculate the sum of all values in the series. With the same fictional values, the result would look like this:
Another example is the following formula:
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:
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:
If you have this formula together with the fictional series sek, the result would look like this:
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.
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.
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.
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().
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.
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))|
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 number||sek||Lag(sek, 1)||sek > Lag(sek, 1)|
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)
All operators work on constants and series. Here are the operators sorted in order of precedence.
|>=||Greater than or equal||3|
|<=||Less than or equal||3|
Parentheses can be used to further control the order of evaluation.
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:
- You can double click on a function in the list to the right in order 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:
- Numerical constants are dark red. Series, parameters and variables are blue. Comments are green.
- When the caret 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 caret 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:
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:
.fx = nok/sek
segdp*.fx + sem1*.fx
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:
.ret(series) = series/lag(series, 1)-1
.ret(usgdp) + .ret(usm1)
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:
.ret(series) = series/lag(series, 1)-1
.prop = 0.6
.ret(usgdp)*.prop + .ret(usm1)*(1-.prop)
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.
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.
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:
- In-line function definitions
- In the Document store
- In the Private account store
- In the Company account store
- 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:
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.