User defined formulas (User defined functions)

Purpose

Not everything can be pre-programmed in Macrobond formula language but from these blocks you can build exactly what you need. Writing custom functions is also useful when you want to re-use custom formulas multiple times or share them with your colleagues.

How to create and save your own formula?

Create new formula

Select Edit from the main menu at the top of the application and click 'User defined functions.' The Function editor window will pop up. On the left you have a list with all User defined formulas and on the right formulas from Macrobond formula language. The main window allows you to define a function.

To start the process, select which account you want to save it in, and press 'Add' under left panel.

Note that User defined formula always starts from a dot (point) to differentiate it from standard formulas.

Simple example

A simple example could be:

.gdpru(x)=(x/rugdp)*100

This function calculates what % of the Russian GDP is covered by series x. In this case, '.gdpru' is the formula’s name, and (x) is the parameter it takes in. After saving this formula, you can use it in the Series list by substituting x for any series. For example:

.gdpru(rugpfi0026)

Which will yield the Russian debt to GDP ratio.

Advance example

You can use let/in/end to nest formulas:

.PerformanceYearsBack(series, years) =
let
.end = End(series)
.start = DateAtOrAfter(Year(.end)-years, Month(.end), Day(.end))
.short = CutStart(series, .start+1)
.prev = At(series, .start)
in
(.short - .prev) / .prev * 100
end

This function calculates the performance since a point a number of years back. After saving this formula, you can use it in the Series list by substituting  series and years. For example:

.PerformanceYearsBack(sek, 20)

Incorrect formula

If any part of the formula is written incorrectly that portion will be underlined in red, with a description of the problem being shown in the text field at the bottom of the editor.

Description

To finish off the process, add a description for your new formula, in the field above. Then choose a formula group. And press Save.

Finding and applying saved formula

It works as any other built-in formula. Begin writing the name in the formula expression field. Don’t forget that you need to include the point at the start. Otherwise select it from the formula list on the right.

How to copy user defined formula to other accounts?

It is not possible to move formula to another account. You need to copy & paste formula manually. The easiest way is to use CTrl+C/Ctrl+V as below:

Or you can use this method:

  1. Go to Edit > User defined formula...
  2. Copy formula.
  3. Click on the existing account (Personal/Department/Company) where you want to have this formula.
  4. Press 'Add'
  5. Paste formula in new formula window.
  6. Press 'Save'.

Examples

Growth by Annual rate

In this example we used user defined formula which creates a series with a growth rate specified by an annual rate from end of one series to the end of another.

Growth by rate

In this example we used user defined formula which calculates series based on a growth rate (which can also be a negative number).

Oil Brent futures with lag

In this example we used user defined formula to output last observation from many series.

Winsorizing (winsorization)

Formula which limits extreme values.

Tags: custom formulaformula