• Overview
  • Important information
• How calculation works?
  • Series names
  • Numerical constants
  • Arithmetic operations
  • Functions
  • Observation windows (rolling)
  • Observation numbers
  • Logical values and functions
  • Operators
• The formula editor
• User defined functions

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 functions. The 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

• 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.

• Introduction to Formula
• The Macrobond formula language
• Built-in formula functions
• Calculations with formulas
• All about extending series
• All about counting observations
• User defined formulas (User defined functions)

• Mathematical functions
• Statistical functions
• Date and observation number functions
• Series operations
• Logical functions
• Financial

Here you can find the details regarding the Macrobond formula language.

In the function descriptions below, the following convention has been used when naming parameters:

value	The parameter can be either a number or a series. The type of the result is typically the same as this parameter. For instance, Log(20) returns a number, but Log(sek) returns a series.
series	The parameter can only be a series. (See discussion earlier in this document for how the automatic conversion of numbers to series works)
other	Other parameter names, such as number, window, observation, length, must be numbers unless other information is provided. For instance, you can write Lag(sek, 10), but not Lag(sek, nok).

 

Mathematical functions

Abs(value)

Returns the absolute value.

Example: Abs(usflof_002090)

Acos(value)

Returns the angle whose cosine is the specified value. The angle is expressed in radians.

Example: Acos(0.5)

AggregateProduct(series)

Returns the aggregated product of all previous numbers. Null numbers are treated as one.

$y_t = \underset{i=0}{\overset{t}{\prod }}{x}_i$

Example: Russia's GDP is presented as 'CPPY=100' (Change Y/Y + 100). To turn it into index series you need to modify formula: AggregateProduct(1+(runaac0033-100)/100)

Example: AggregateProduct(1+0.05/100)*100 continuous growth rate of 5%

AggregateSum(series)

Returns the aggregated sum of all previous numbers. Null numbers are treated as zero.

${𝑦}_{𝑡}=\sum _{i=0}^t{𝑥}_{𝑖}$

Example: AggregateSum(secons0162)

AggregateSumAnnual(series)

Returns the aggregated sum of previous numbers from the start of each year. Null numbers are treated as zero.

Example: AggregateSumAnnual(secons0162)

This is implemented in the Macrobond formula language as:

AggregateSumAnnual(series) = 

let

.a = AggregateSum(series) 

in 

.a - Null0(At(.a, EndOfYearAhead(-1)))

end

Asin(value)

Returns the angle whose sine is the specified value. The angle is expressed in radians.

Example: Asin(0.866)

Atan(value)

Returns the angle whose tangent is the specified value. The angle is expressed in radians.

Example: Atan(1.7321)

Atan2(x, y)

Returns the angle whose tangent is the quotient of two specified values. The angle is expressed in radians.

Example: Atan2(1.7321, 1)

BKFilter(series, shortPeriod, longPeriod, leadLags)

Returns the Baxter-King bandpass filtered series with the specified properties.

Example: BKFilter(usgdp, 6, 32, 32)

CFRandomWalkFilter(series, shortPeriod, longPeriod)

Returns the series filtered with asymmetric Christiano-Fitzgerald bandpass filter for non-stationary series with the specified properties.

Example: CFRandomWalkFilter(usgdp, 6, 32)

CFStationaryFilter(series, shortPeriod, longPeriod)

Returns the series filtered with asymmetric Christiano-Fitzgerald bandpass filter for stationary series with the specified properties.

Example: CFStationaryFilter(usgdp, 6, 32)

Ceiling(value)

Returns the smallest integer greater than or equal to the specified value.

Example: Ceiling(sek)

Cos(value)

Returns the cosine of the specified angle. The angle should be expressed in radians and must be in the range -9223372036854775295 to 9223372036854775295.

Example: Cos(1.0472)

Cot(value)

Returns the cotangent of the specified angle. The angle should be expressed in radians.

Example: Cot(1.0472)

CountValid(series)

Returns the number of values that are not null.

Example: CountValid(fi10ygov)

CountValid(series, window)

Returns the number of values that are not null in a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: CountValid(fi10ygov, WeeksLength(1))

CoVariance(series1, series2)

Returns the covariance of series1 and series2.

Example: Covariance(sek, segdp)

CoVariance(series1, series2, window)

Returns the covariance of series1 and series2 within a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: Covariance(sek, segdp, YearsLength(1))

E()

The number e.

EMean(series, factor)

Returns the exponential moving average with the specified factor. The factor, α, must be a number between 0 and 1.

Example: EMean(sek, 0)

$y_0 = x_0$

For

$t > 0$

$y_t =\alpha x_i+\left(1-a\right) y_{i-1}$

Exp(value)

Returns e raised to the power of the specified value, where e is the base of the natural logarithm.

Example: Exp(sek)

FillNullWithLinear(series)

Fills null values with a linear combination of the previous and next values.

Example: Set Conversion settings > Missing value method to 'Do not fill in': FillNullWithLinear(sp600_20201080tr)

FillNullWithNext(series)

Fills null values with the next value.

Example: Set Conversion settings > Missing value method to 'Do not fill in': FillNullWithNext(sp600_20201080tr)

FillNullWithPrevious(series)

Fills null values with the previous value.

Example: Set Conversion settings > Missing value method to 'Do not fill in': FillNullWithPrevious(sp600_20201080tr)

FillNullWithPreviousAsForecast(series)

Fills null values with the previous value and flag as forecast.

Example: Set Conversion settings > Missing value method to 'Do not fill in': FillNullWithPreviousAsForecast(sp600_20201080tr)

First(series)

Returns the first number of a series.

Example: First(sek)

FirstGreaterOrEqual(series, value)

Returns the observation number of the first observation greater or equal than the specified value.

Example: if(Counter()=FirstGreaterOrEqual(sp500_500, 4200), sp500_500, Null())

FirstLessOrEqual(series, value)

Returns the observation number of the first observation less or equal than the specified value.

Example: if(Counter()=FirstLessOrEqual(sp500_500, 4200), sp500_500, Null())

FirstValid(series)

Returns the first number of a series that is not null.

Example: First(sek)

Floor(value)

Returns the largest integer less than or equal to the specified value.

Example: Floor(sek)

GeometricMean(series)

Returns the geometric mean.

Example: GeometricMean(plgdp)

This is implemented in the Macrobond formula language as:

GeometricMean(series) = Exp(Mean(Ln(series)))

GeometricMean(series, window)

Returns the geometric moving average of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: GeometricMean(plgdp, YearsLength(2))

This is implemented in the Macrobond formula language as:

GeometricMean(series, window) = Exp(Mean(Ln(series), window))

High(series)

Returns the highest number.

Example: High(usgdp)

High(series, window)

Returns the highest number in a window of the specified length. You can specify a vector as the length parameter in the High and Low formula functions.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: you can get the highest value in a series using an expanding window: High(usgdp, Counter(eur)-Start(eur)+1)

or without expanding window: High(usgdp, Yearslength(2))

HighObs(series)

Returns the observation number of the last highest value.

Example: HighObs(usgdp)

HighObs(series, window)

Returns the observation number of the last highest value within a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: HighObs(usgdp, Yearslength(2))

HPFilter(series, lambda)

Returns a series smoothed by using the Hodrick-Prescott method with the specified lambda factor, which must be a number greater than zero. We calculate the HP filter according to Hodrick–Prescott filter - Wikipedia, which is the same method as can be found in MATLAB and EViews.

The rule of thumb is to use

$𝜆=1600$

for quarterly data;

$𝜆=14400$

for monthly data; and

$𝜆=100$

for yearly data.

Example: HPFilter(brnaac0016, 1600)

HPFilterOneSided (series, lambda)

Returns a series smoothed by using a one-sided Hodrick-Prescott method with the specified lambda factor, which must be a number greater than zero.

The one-sided filter uses only past information when calculated each smoothed value. The calculation is the same as using the ordinary HP filter on all values up to the current value at each point in time.

The rule of thumb is to use

$𝜆=1600$

for quarterly data;

$𝜆=14400$

for monthly data; and

$𝜆=100$

for yearly data.

Example: HPFilterOneSided(brnaac0016, 1600)

Intercept(series1, series2)

Returns the intercept after regression correlation of series1 and series2 with series1 as the dependent.

Example: Intercept(usgdp, uspric2373)

Intercept(series1, series2, window)

Returns the intercept after regression correlation of series1 and series2 with series1 within a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: Intercept(usgdp, uspric2373, MonthsLength(20))

Last(series)

Returns the last value of a series.

Example: Last(eur)

LastGreaterOrEqual(series, value)

Returns the observation number of the last observation greater or equal than the specified value.

Example: if(Counter()=LastGreaterOrEqual(sp500_500, 4200), sp500_500, Null())

LastLessOrEqual(series, value)

Returns the observation number of the last observation less or equal than the specified value.

Example: if(Counter()=LastLessOrEqual(sp500_500, 4200), sp500_500, Null())

LastValid(series)

Returns the last valid value of a series.

Example: LastValid(eur)

LastNonForecast(series)

Returns the last value of the series that is not a forecast. 

Example: LastNonForecast(bp_oecdoilprodt)

Linear(series)

Returns a line fitted using the least square method.

Example: Linear(sek)

This is implemented in the Macrobond formula language as:

Linear(series) = LinSlope(series)*(Counter(series) - Start(series)) + LinIntercept(series)

Linear(series, observationStart, observationEnd)

Returns a line fitted using the least square method by using data from observationStart to, but not including, observationEnd.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: Linear(sek, Date(2017, 01, 01), Now())

This is implemented in the Macrobond formula language as:

Linear(series, observationStart, observationEnd) =
let
   .slice = Cut(series, observationStart, observationEnd)
in
   LinSlope(.slice)*(Counter(series) - observationStart) + LinIntercept(.slice)
end

LinearExtended(series, observationExtended)

Returns a line fitted using the least square method. The line is extended until observationExtended.

Example: LinearExtended(plgdp, Endvalid(plgdp)+YearsLength(4))

This is implemented in the Macrobond formula language as:

LinearExtended(series, observationExtended) =
let
   .slope = LinSlope(series)
   .intercept = LinIntercept(series)
   .observationStart = StartValid(series)
   .line = .slope*(Counter(series) - .observationStart) + .intercept
   .extendedValue = .slope*(observationExtended - .observationStart) + .intercept
in
   ExtendLinear(.line, observationExtended, .extendedValue)
end

For more information see: All about extending series

LinearExtended(series, observationStart, observationEnd, observationExtended)

Returns a line fitted using the least square method by using data from observationStart (start of trend calculation) to, but not including, observationEnd (end of trend calculation). The line is extended until observationExtended.

Example: LinearExtended(plgdp, Date(2010, 1, 1), Date(2015, 1, 1), Endvalid(plgdp)+YearsLength(4))

This is implemented in the Macrobond formula language as:

LinearExtended(series, observationStart, observationEnd, observationExtended) =
let
   .slice = Cut(series, observationStart, observationEnd)
   .slope = LinSlope(.slice)
   .intercept = LinIntercept(.slice)
   .line = .slope*(Counter(series) - observationStart) + .intercept
   .extendedValue = .slope*(observationExtended - observationStart) + .intercept
in
   ExtendLinear(.line, observationExtended, .extendedValue)
end

For more information see: All about extending series

LinIntercept(series)

Returns the intercept of a line fitted with the least square method .

Example: LinIntercept(dxy)

LinIntercept(series, window)

Returns the intercept of a line fitted with the least square method within a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: LinIntercept(dxy, YearsLength(1))

LinSlope(series)

Returns the slope of a line fitted with the least square method.

Example: LinSlope(dxy)

LinSlope(series, window)

Returns the slope of a line fitted with the least square method within a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: LinSlope(dxy, YearsLength(1))

Ln(value)

Returns the natural logarithm.

Example: Ln(100)

Example: Ln(sek)

Log(value)

Returns the logarithm with base 10.

Example: Log(100)

Example: Log(sek)

Low(series)

Returns the lowest number of the series.

Example: Low(sek)

Low(series, window)

Returns the lowest number in a window of the specified length. You can specify a vector as the length parameter in the High and Low formula functions.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: Low(sek, Yearslength(1))

Example: You can get the lowest value so far in a series using an expanding window like this: Low(eur, Counter(eur)-Start(eur)+1).

LowObs(series)

Returns the observation value of the last lowest number.

Example: LowObs(sek)

LowObs(series, window)

Returns the observation value of the last lowest number within a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: LowObs(sek, Yearslength(1))

Max(value1, value2)

Returns the largest value of a pair of values.

Example: Max(sek)

Mean(series)

Returns the mean value as a number.

Example: Mean(segdp)

Mean(series, window)

Returns the moving average of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: Mean(segdp, Yearslength(1))

Median(series)

Returns the median as a number.

Example: Median(segdp)

Median(series, window)

Returns the median value in a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: Median(segdp, Yearslength(1))

Min(value1, value2)

Returns the smallest value of a pair of values.

Example: Min(swedsegdpfcst, sefcst0076)

Example: Min(swedsegdpfcst, 2)

Mod(value1, value2)

Returns the reminder of an integer division.

Example: Mod(swedsegdpfcst, 2)

The calculation uses a floored division and is equal to: value1 – value2*Floor(value1/value2)

Momentum(series, length)

Returns the difference between the series and a lagged series.

Example: Momentum(plgdp, yearslength(1))

Example: When you want to disaggregate series, you can combine Momentum() with if(): if(Month()=1, dedemo0013, momentum(dedemo0013, 1))

NextGreaterOrEqual(series, value, observation)

Returns the observation number of the next observation greater or equal than the specified value.

Example: Counter()-NextGreaterOrEqual(sp500_500, sp500_500, Counter()-1) It calculates the number of days before the value was as high

NextLessOrEqual(series, value, observation)

Returns the observation number of the next observation less or equal than the specified value.

Example: Counter()-NextLessOrEqual(sp500_500, sp500_500, Counter()-1) It calculates the number of days before the value was as low

Null()

Returns the null number.

Example: You can use it inside if() condition formula: if(swedsegdpfcst>3.5, 1, Null())

Null0(value)

Returns 0 if the value is null and otherwise the value.

Example: Set Conversion settings > Missing value method to 'Do not fill in': Null0(sp600_20201080tr)

Null1(value)

Returns 1 if the value is null and otherwise the value.

Example: Set Conversion settings > Missing value method to 'Do not fill in': Null1(sp600_20201080tr)

Pi()

The number π.

Example: 2*pi()

Pow(value, power)

Returns a value raised to a power.

Example: Pow(sek, 2)

PreviousGreaterOrEqual(series, value, observation)

Returns the observation number of the previous observation greater or equal than the specified value.

Example: Counter()-PreviousGreaterOrEqual(sp500_500, sp500_500, Counter()-1) It calculates the number of days since the value was as high

PreviousLessOrEqual(series, value, observation)

Returns the observation number of the previous observation less or equal than the specified value.

Example: Counter()-PreviousLessOrEqual(sp500_500, sp500_500, Counter()-1) It calculates the number of days before the value was as low

Product(series)

Returns the product of all numbers in the series.

Example: product(Cut(uscpi, Date(2019, 1, 1), Date(2020, 1, 1)))

$y = \prod _{i=t-w+1}^l x_i$

Product(series, window)

Returns the product of all values in a window of the specified length.

Example: product(Cut(uscpi, Date(2019, 1, 1), Date(2020, 1, 1)), Quarterslength(1))

$y_t = \prod _{i=t-w+1}^t x_i$

Round(value, decimals)

Returns the value rounded to the specified number of decimals.

The number of decimals can be from 0 to 15

The value is rounded to the closest value with the specified number of decimals. If the value is exactly in between two values, it is rounded to the value that ends with an even digit. This follows the IEEE Standard 754 as specified in section 4 and is sometimes called 'banker’s rounding'. Other midpoint strategies can be obtained by using the Floor and Ceiling functions.

Example: Round(sek, 2)

Sign(value)

Returns -1 for negative values, 0 for 0 and 1 for positive values.

Example: You can wrap it around change over period (cop()) formula and check the growth: Sign(Cop(usgdp, Yearslength(1)))

Sin(value)

Returns the sine of the specified angle.

The angle should be expressed in radians and must be in the range -9223372036854775295 to 9223372036854775295.

Example: Sin(1.0472)

Slope(series1, series2)

Returns the coefficient of the regression, a.k.a. the beta, between series1 and series2 with series1 as the dependent.

Example: Slope(usgdp, uspric2373)

Slope(series1, series2, window)

Returns the coefficient of the regression, a.k.a. the beta, between series1 and series2 with series1 as the dependent within a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: Slope(usgdp, uspric2373, Yearslength(1))

Sqrt(value)

Returns the square root.

Example: Sqrt(usgdp)

Sum(series)

Returns the sum of all numbers in the series.

Example: Sum(opecsecrudeoilimp)

Sum(series, window)

Returns the sum of all numbers in a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: Sum(opecsecrudeoilimp, Yearslength(1))

Tan(value)

Returns the tangent of the specified angle. The angle should be expressed in radians.

Example: Tan(1.0472)

 

Statistical functions

ChiDist(value, degFree)

Returns the probability for a value based on the chi distribution with the specified degrees of freedom. The parameters can be either a number or series.

Example: ChiDist(sek, 2)

ChiDistInv(probability, degFree)

Returns the value for a probability based on the chi distribution with the specified degrees of freedom. The parameters can be either a number or series.

Example: ChiDistInv(0.5, 20)

Correlation(series1, series2)

Returns the correlation coefficient of series1 and series2.

Example: Correlation(uscpi, btc)

Correlation(series1, series2, window)

Returns the correlation coefficient of series1 and series2 in a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: Correlation(uscpi, btc, YearsLength(1))

CorrelationBestLag(series1, series2, startLag, endLag)

Returns lag with the highest correlation coefficient in absolute terms of series1 and series2.

Example: CorrelationBestLag(uscpi, btc, -Yearslength(1), Yearslength(1))

FDist(value, degFreeNum, degFreeDenom)

Returns the cumulative probability for a value based on the F distribution with the specified degrees of freedom for the numerator and denominator. The parameters can be either a number or series.

Example: FDist(sek, 2, 1)

FDistInv(probability, degFreeNum, degFreeDenom)

Returns the value for a probability based on the F distribution with the specified degrees of freedom for the numerator and denominator. The parameters can be either a number or series.

Example: FDist(0.5, 2, 1)

Kurtosis(series)

Returns the excess kurtosis of a series.

Example: Kurtosis(sek)

Kurtosis(series, length)

Returns the excess kurtosis of a series in a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: Kurtosis(sek, Yearslength(1))

LogNormDist(value, mean, stdDev)

Returns the probability for a value based on the log-normal distribution with the specified mean and standard deviation. The parameters can be either a number or series.

Example: LogNormDist(sek, 2, 1)

LogNormDistInv(probability, mean, stdDev)

Returns the value for a probability based on the log-normal distribution with the specified mean and standard deviation. The parameters can be either a number or series.

Example: LogNormDistInv(0.5, 2, 1)

LowerTailMean(series, p)

Returns the mean of the number of lowest values as specified by the percentage, p. The parameter p should be in the range 0 < p < 100. This is sometimes known as the tail expectation.

Example: LowerTailMean(sek, 20)

LowerTailMean(series, p, window)

Returns the mean of the number of lowest values as specified by the percentage, p, in a window of the specified length. The parameter p should be in the range 0 < p < 100. This is sometimes known as the tail expectation.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: LowerTailMean(sek, 20, Yearslength(1))

MedAbsDev(series)

Returns the median absolute deviation.

Example: MedAbsDev(usgdp)

NormDist(value)

Returns the probability for a value based on the standard normal distribution. The parameters can be either a number or series.

Example: NormDist(sek)

NormDist(value, mean, stdDev)

Returns the probability for a value based on the normal distribution with the specified mean and standard deviation.

Example: NormDist(sek, 2, 1)

This is implemented in the Macrobond formula language as:

NormDist(value, mean, stdDev) = NormDist((value - mean) / stdDev)

NormDistInv(probability)

Returns the value for a probability based on the standard normal distribution. The parameters can be either a number or series.

Example: NormDistInv(0.5)

NormDistInv(probability, mean, stdDev)

Returns the value for a probability based on the normal distribution with the specified mean and standard deviation. The parameters can be either a number or series.

Example: NormDistInv(0.5, 2, 1)

This is implemented in the Macrobond formula language as:

NormDistInv(probability, mean, stdDev) = NormDistInv(probability)*stdDev+mean

Percentile(series, p)

Returns the number of the p:th percentile from series, where p is in the range 0-100.

Is inclusive and uses linear interpolation.

Example: Percentile(sek, 1) will give you lower tail 1%

Example: Percentile(sek, 99) will give you upper tail 1%

Percentile(series, p, window)

Returns the value of the p:th percentile within a window of the specified length, where p is in the
range 0-100.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Is inclusive and uses linear interpolation.

Example: Percentile(sek, 1, YearsLength(1)) will give you lower tail 1% in a rolling yearly time frame

Example: Percentile(sek, 99, YearsLength(1)) will give you upper tail 1% in a rolling yearly time frame

ExpandingPercentile(series, p)

Returns the value of the p:th percentile within an expanding window (0 < p < 100).

Example:  Percentile(sek, 1) will give you lower tail 1% in an expanding time frame

PercentRank(series, value)

Returns the rank of a value in a series as a percentage (0..100) of the values in the series.

A missing value will be returned if the value is outside the range of values in the series.

If the value is in between values in the series, a linear interpolation will be made using the ranks of the surrounding values. The last parameter can be either a number or series.

Example:  PercentRank(sek, sek) this calculates the percentile value of the series 'sek' for each observation.

PercentRank(series, value, length)

Returns the rank of a value in a series as a percentage (0..100) of the values within a window of the specified length.

A missing value will be returned if the value is outside the range of values in the series.

If the value is in between values in the series, a linear interpolation will be made using the ranks of the surrounding values. The second parameter can be either a number or series.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example:  PercentRank(sek, sek, YearsLength(1))

ExpandingPercentRank(series, value)

Returns the rank of a value in a series as a percentage (0..100) of the values in an expanding window.
It calculates in the order from smallest to largest. This corresponds to passing 1 as the third parameter to RANK.EQ in Excel. It also uses 0 as the index of the first value.

Example:  ExpandingPercentRank(sek, Lastvalid(sek))

NormDistCdf(series)

Returns standard normal cumulative distribution function.

Example:  NormDistCdf(standardize(uscpi))

NormDistPdf(series)

Returns standard normal probability mass function.

Example:  NormDistPdf(standardize(uscpi))

Qn(series)

Returns Rousseeuw's Q dispersion.

Example:  Qn(segdp)

RankCorrelation(series1, series2)

Returns the rank correlation coefficient of series1 and series2.

Example:  RankCorrelation(usgdp,uscpi)

SeasonalAdjustmentAdditive(series, years)

Returns a seasonally adjusted series calculated by using an additive method with weights in a window of the specified number of years.

Example:  SeasonalAdjustmentAdditive(setrad0804, 10)

Skewness(series)

Returns the skewness of a series.

Example: Skewness(sek)

Skewness(series, length)

Returns the skewness of a series in a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: Skewness(sek, Yearslength(1))

Sn(series)

Returns Tukey's S dispersion.

Example: Sn(segdp)

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'.

Example:  Standardize(ussurv1055)

This is implemented in the Macrobond formula language as:

Standardize(series) = FlagForecast(Trim((series - Mean(series)) / StdDev(series)), IsForecast(series))

This formula won't give same outcome as calculation from Cross sampling/Scalar. In formula we standardize the series (value - mean)/stddev for each value while in analyses we calculate a standardized value for the whole series (or a specified interval) according to mean/stddev.

 

Standardize(series, window)

Z-Score with window parameter. It returns a normalized series which counts (in standard deviations) how far a value is from the mean value within a window of the specified length. The mean is represented by '0' and one standard deviation is '1'.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example:  Standardize(ussurv1055, MonthsLength(6))

This is implemented in the Macrobond formula language as:

Standardize(series, window) = FlagForecast((series - Mean(series, window)) / StdDev(series, window), IsForecast(series))

This formula won't give same outcome as calculation from Cross sampling/Scalar. In formula we standardize the series (value - mean)/stddev for each value while in analyses we calculate a standardized value for the whole series (or a specified interval) according to mean/stddev.

StdDev(series)

Calculates 'Sample standard deviation'. Returns the standard deviation of the numbers in a series relative its mean value. You can calculate volatility with it.

Example:  StdDev(ussurv1055)

StdDev(series, window)

Calculates 'Sample standard deviation'. Returns the standard deviation compared to the mean value within a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example:  StdDev(ussurv1055, MonthsLength(6))

TDist(value, degFree)

Returns the probability for a value based on the Student-T distribution with the specified degrees of freedom.

The parameters can be either a number or series.

Example:  TDist(sek, 2)

This is implemented in the Macrobond formula language as:

TDist(value, degFree) =
  let
    .dist = (1-FDist(value*value, 1.0, degFree))/2
  in
    if (value > 0.0, 1-.dist, .dist)
  end

TDistInv(probability, degFree)

Returns the value for a probability based on the Student-T distribution with the specified degrees of freedom. The parameters can be either a number or series.

Example:  TDist(0.5, 2)

This is implemented in the Macrobond formula language as:

TDistInv(probability, degFree) =
let
  .f = Sqrt(FDistInv(1-if (probability < 0.5, probability, 1-probability)*2, 1, degFree))
in
  if (probability < 0.5, -.f, .f)
end

Trimean(series)

It returns the Tukey’s trimean.

Example:  Trimean(sek)

TrimMean(series, p)

Returns the mean of the interior values of a series by excluding a number of values from the top and bottom tails as specified by the percentage (0 < p < 100). An equal number of values are removed from the top and bottom tails.

Example:  TrimMean(sek, 20)

TrimMean(series, p, window)

Returns the mean of the interior values of a series of moving windows of size length, by excluding a number of values from the top and bottom tails as specified by the percentage (0 < p < 100). An equal number of values are removed from the top and bottom tails.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example:  TrimMean(sek, 20, Yearslength(4))

TwoPieceNormDist(probability, mode, stdDev1, stdDev2)

Returns the probability for a value based on the 2-piece normal distribution with the specified mode, mean and standard deviations. The parameters can be either a number or series.

Example:  TwoPieceNormDist(0.5, 1, 4, 8)

This is implemented in the Macrobond formula language as:

TwoPieceNormDist(value, mode, stdDev1, stdDev2) =
let
  .norm = NormDistCdf((value-mode) / if (value > mode, stdDev2, stdDev1))
in
  if(value > mode,
    (stdDev1-stdDev2)/(stdDev1+stdDev2)+2*stdDev2/(stddev1+stdDev2)*.norm,
    2*stdDev1/(stdDev1+stdDev2)*.norm)
end

TwoPieceNormDistInv(probability, mode, stdDev1, stdDev2)

Returns the value for a probability based on the 2-piece normal distribution with the specified mode, mean and standard deviation.

Example:  TwoPieceNormDistInv(0.5, 1, 4, 8)

This is implemented in the Macrobond formula language as:

TwoPieceNormDistInv(probability, mode, stdDev1, stdDev2) =
let
  .m = stdDev1/(stdDev1+stdDev2)
  .stdsum = stdDev1+stdDev2
  .norm = NormDistInv(if (probability > .m, (probability-(stdDev1-stdDev2)/.stdsum)*.stdsum/(2*stddev2), probability*.stdsum/(2*stdDev1)))
in
  if (probability > .m, .norm*stdDev2, .norm*stdDev1)+mode
end

UpperTailMean(series, p)

Returns the mean of the number of highest values as specified by the percentage, p. The parameter p should be in the range 0 < p < 100. This is sometimes known as the tail expectation.

Example:  UpperTailMean(sek, 99)

UpperTailMean(series, p, window)

Returns the mean of the number of highest values as specified by the percentage, p, in a window of the specified length. The parameter p should be in the range 0 < p < 100. This is sometimes known as the tail expectation.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example:  UpperTailMean(sek, 99, MonthsLength(6))

Variance(series)

Returns the variance of the series.

Example:  Variance(sek)

Variance(series, window)

Returns the variance for a series within a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example:  Variance(sek, Yearslength(1))

 

Date and observation number functions

AddMonths(months)

Returns a series of all observation numbers offset by a number of months

Example:  AddMonths(12)

AddMonths(observation, months)

Returns the observation number for the specified observation number plus a number of months.

Example:  AddMonths(Counter(sek), 12)

AddYears(years)

Returns a series of all observation numbers offset by a number of years.

Example:  AddYears(12)

AddYears(observation, years)

Returns the observation number for the specified observation number plus a number of years.

Example:  AddYears(Counter(sek), 12)

At(series, observation)

Returns the value in the series at the specified observation number.

Example:  At(sek, Date(2020, 12, 11))

Counter()

Returns a series of all observation numbers.

Example: It can be used as a 'pointer' in other formulas: if(Counter()=Date(2021, 6, 1), flagforecast(sek/2), sek)

Counter(series)

Returns a series of all observation number for the specified series. It starts from '0' so to get exact number of observations you have to add +1

Example: Counter(sek)+1

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.

You shouldn't use this function to find the start of a year or month. Use the StartOfYear(year) and StartOfMonth(year, month) for this purpose.

Example: It can be used in other formulas At(sek, Date(2021, 6, 1))

DateAtOrAfter(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 next observation is returned.

Example: DateAtOrAfter(2021, 4, 1)

Day()

Returns a series of the day (1-31) for each observation.

Example: Day()

Day(observation)

Returns the day (1-31) of the specified observation number.

Example: Day(Counter(sek))

DayOfWeek()

Returns a series of day of the week for each observation.

0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday

Example: DayofWeek()=3

DayOfWeek(observation)

Returns the day of the week of the specified observation number

0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday

Example: DayofWeek(Counter(sek))

Days(observation1, observation2)

Returns the number of calendar days passed from the first observation number to the second one.

Example: Days(StartOfMonth(), EndOfMonth())+1

Days30E(observation1, observation2)

Returns the number of days passed from the first observation number to the second using the 30E convention.

Example: Days30E(StartOfMonth(), EndOfMonth())+1

End()

Returns the last observation number for calculations.

Example: if(Counter()=End(), 1, 0)

End(series)

Returns the last observation number of the series.

Example: if(Counter()=End(sek), 1, 0)

EndOfMonth()

Returns a series with the last observation number during each month.

Example: if(Counter()=EndOfMonth(), 1, 0)

EndOfMonthAhead(monthsAhead)

Returns a series with the last observation number during each month.

Example: Extend(sek, Last(EndOfMonthAhead(2)), LastValid(sek))

EndOfQuarter()

Returns a series with the last observation number during each quarter.

Example: if(Counter()=EndOfQuarter(), 1, 0)

EndOfQuarterAhead(quartersAhead)

Returns a series with the last observation number during each quarter.

Example: Extend(sek, Last(EndOfQuarterAhead(2)), LastValid(sek))

EndOfWeek(firstDayOfWeek)

Returns a series with the last observation number during each week.

0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday

Example: Extend(sek, Last(EndOfWeek(3)), LastValid(sek))

EndOfWeekAhead(firstDayOfWeek, weeksAhead)

Returns a series with the last observation number during each week.

0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday

Example: Extend(sek, Last(EndOfWeekAhead(3, 2)), LastValid(sek))

EndOfYear()

Returns a series with the last observation number during each year.

Example: if(Counter()=EndOfYear(), 1, 0)

EndOfYearAhead(yearsAhead)

Returns a series with the last observation number during each year.

Example: Extend(sek, Last(EndOfYearAhead(2)), LastValid(sek))

EndValid(series)

Returns the observation number of the last valid value of the series.

Example: if(Counter()=EndValid(sek), 1, 0)

FlagForecast(value)

Returns a value as a forecast value.

Example: FlagForecast(sek)

FlagForecast(value, condition)

Returns a value as a forecast value if the condition is True and as a non-forecast value if it is False.

Example: FlagForecast(sek, sek>9) all values higher than 9

Example: FlagForecast(sek, Counter()>Date(2020, 3, 1)) all values after 1st Mar 2020

Example: You can also turn off any forecast highlight: FlagForecast(unpoppr276constant, 0)

ImmAhead(dayOffset, position)

Returns a series with the observation numbers of IMM dates where an offset is added to each input date.

Example: ImmAhead(2, 1)

ImmAhead(observation, dayOffset, position)

Returns the observation number of the IMM date where an offset is added to the input date that corresponds to the input observation number.

Example: ImmAhead(end(sek), 2, 1)

Length(series)

Returns the number of observations in a time series.

Example: Length(sek)

Month()

Returns a series of the month (1-12) for each observation.

Example: Month()

Example: if(Month()=2|Month()=6, 1, 0)

Month(observation)

Returns the month (1-12) of the specified observation number.

Example: Month(Counter(sek))

MonthLength()
Returns the number of observations per typical month based on the frequency. The value is a constant.
See also MonthsLength(months), YearLength(), QuarterLength() and WeekLength()

MonthLength() = ObservationCountPerYear()/12

Example: cop(sek, MonthLength()*3)

MonthsLength(months)
Returns the number of observations for a number of months based on the frequency (it uses typical constant length of a month).
MonthsLength(1) is the same as MonthLength()
See also YearsLength(years), QuartersLength(quarters) and WeeksLength(weeks)

Example: cop(sek, MonthsLength(3))

MonthOffset()

Returns a series with the offset of each observation within the month.

Example: At(Monthoffset(), EndofMonth())+1

MonthOffset(observation)

Returns the offset within the month of the specified observation number.

Example: MonthOffset(Counter(sek)+1)

NextValidObservationNumber(series)

Returns a series that contains the observation number of the current observation if it is valid and otherwise the number of the next observation that is valid

Example: High(sek, NextValidObservationNumber(sek))

NextValidObservationNumber(series, observation)

Returns the observation number of the specified observation if it is valid and otherwise the number of the next observation that is valid.

Now()

Returns the observation number of today's date.

Example: if(Counter()=Now()-1, flagforecast(sek), 0) will highlight yesterday's observation

ObservationCountPerYear()

Returns the number of observations per year based on the frequency.
This is a synonym for YearLength().

Example: cop(sek, ObservationCountPerYear())

PreviousValidObservationNumber(series)

Returns a series that contains the observation number of the current observation if it is valid (not null) and otherwise the number of the previous observation that is valid.

Example: High(sek, PreviousValidObservationNumber(sek)+1)

PreviousValidObservationNumber(series, observation)

Returns the observation number of the specified observation if it is valid and otherwise the number of the previous observation that is valid.

Quarter()

Returns a series of the quarter (1-4) for each observation.

Example: Quarter()

Example: if(Quarter()=2|Quarter()=3, 1, 0)

Quarter(observation)

Returns the quarter (1-4) of the specified observation number.

Example: Quarter(Counter(sek))

QuarterLength()
Returns the number of observations per typical quarter based on the frequency. The value is a constant.
See also QuartersLength(quarters), YearLength(), MonthLength() and WeekLength()

QuarterLength() = ObservationCountPerYear()/4

Example: cop(sek, QuarterLength()*2)

QuartersLength(quarters)
Returns the number of observations for a number of quarters based on the frequency (it uses typical constant length of a quarter).
QuartersLength(1) is the same as QuarterLength()
See also YearsLength(years), MonthsLength(months) and WeeksLength(weeks)

Example: cop(sek, QuartersLength(2))

QuarterOffset()

Returns a series with the offset of each observation within the quarter.

Example: At(Quarteroffset(), EndofQuarter())+1

QuarterOffset(observation)

Returns the offset within the quarter of the specified observation number.

Example: QuarterOffset(Counter(sek)+1)

Start()

Returns the first observation number for calculations.

Example: if(Counter()=Start(), 1, 0)

Start(series)

Returns the first observation number of the series.

Example: if(Counter()=Start(sek), 1, 0)

StartValid(series)

Returns the observation number of the first valid value of the series.

Example: if(Counter()=StartValid(sek), 1, 0)

StartOfYear()

Returns a series with the first observation number during each year.

Example: if(Counter()=StartofYear(), 1, 0)

Example: StartofYear()

Example: At(sek, StartofYear()) to get a value

StartOfYear(year)

Returns the observation number of the first observation of the specified year.

Example: StartofYear(2020)

Example: At(sek, StartofYear(2020)) to get a value

StartOfQuarter()

Returns a series with the first observation number during each quarter.

Example: if(Counter()=StartofQuarter(), 1, 0)

StartOfMonth()

Returns a series with the first observation number during each month.

Example: if(Counter()=StartofMonth(), 1, 0)

StartOfMonth(year, month)

Returns the observation number of the first observation of the specified month, where the month is in the range 1-12.

Example: if(Counter()=StartofMonth(2021, 1), 1, 0)

StartOfWeek(firstDayOfWeek)

Returns a series with the first observation number during each week.

0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday

Example: At(sek, StartOfWeek(3))

WeekLength()
Returns the number of observations per typical week based on the frequency. The value is a constant.
See also WeeksLength(weeks), MonthLength(), YearLength() and QuarterLength()

Example: cop(sek, WeekLength()*2)

WeeksLength(weeks)
Returns the number of observations for a number of weeks based on the frequency (it uses typical constant length of a week).
WeeksLength(1) is the same as WeekLength()
See also YearsLength(years), QuartersLength(quarters) and MonthsLength(months)

Example: cop(sek, WeekLength(2))

Year()

Returns a series of the year for each observation.

Example: if(Year()=2020, series/2, series)

Year(observation)

Returns the year of the specified observation number.

Example: Year()

Example: Year(Counter(sek))

YearLength()
Returns the number of observations per typical year based on the frequency. The value is a constant.
See also YearsLength(years), QuarterLength(), MonthLength() and WeekLength()

YearLength() = ObservationCountPerYear()

Example: cop(sek, YearLength()*5)

YearsLength(years)
Returns the number of observations for a number of years based on the frequency (it uses typical constant length of a year).
YearsLength(1) is the same as YearLength().
See also QuartersLength(quarters), MonthsLength(months) and WeeksLength(weeks)

Example: cop(sek, YearLength(5))

YearOffset()

Returns a series with the offset of each observation within the year.

Example: ceiling((YearOffset()+1)/WeekLength())-1 it counts weeks since the start of year

Example: At(Yearoffset(), EndofYear())+1

YearOffset(observation)

Returns the offset within the year of the specified observation number.

Example: YearOffset(Counter(sek)+1)

Years(observation1, observation2)

Returns the number of years between the first observation number to the second. The calculation is made using the Actual/Actual method defined by ISDA.

Example: Years(Start(), End())

Example: Years(Start(sek), End(sek))

 

Series operations

Cut(series, observationStart, observationEnd)

Returns a series excluding observations before the specified start observation number and all observations starting with the specified end observation number.

Example: Cut(sek, Date(2010, 01, 01), Date(2021, 01, 01))

CutEnd(series, observation)

Returns a series of all observations before the specified observation number.

Example: CutEnd(sek, Date(2021, 03, 12))

CutStart(series, observation)

Returns a series without any observations before the specified observation number.

Example: CutStart(sek, Date(2020, 01, 01))

Extend(series, observation, number)

Returns a series with the specified number at the specified observation number if it is past the end.
Any values between the last value of the series and the specified future point of extension are filled in with the last value.

Example: To extend series two months after its original end (second parameter), with a 0.75 value at the end (third parameter): Extend(usrate0001, Endvalid(usrate0001)+Monthslength(2), 0.75)

For more information see: All about extending series

ExtendLastAsForecast(series)

Extends the series with the last valid value until the end of the calendar.

Example: add some other series, i.e., usrate0001 and below ExtendLastAsForecast(nolama0138)

This is implemented in the Macrobond formula language as:

ExtendLastAsForecast(series) = Join(series, FlagForecast(LastValid(series)), EndValid(series) + 1)

For more information see: All about extending series

ExtendLastYoYForecast(series) / ExtendLastYoYForecast(series, observation)

Extends the series using the % YoY change to calculate a forecast for the whole calendar range.

Example: ExtendLastYoYForecast(CutEnd(usgdp, Date(2020, 1, 1)))

Example: ExtendLastYoYForecast(usgdp, Endvalid(usgdp)+Quarterslength(3))

For more information see: All about extending series

ExtendLinear(series, observation, number)

Returns a series extended with the specified number using a linear interpolation from the end until the specified observation number.

Example: When you want to put at the end of the current year (second parameter) last value higher by 5% (third parameter): ExtendLinear(sp500_500, LastValid(EndOfYearAhead(0)), LastValid(sp500_500)*1.05) 

For more information see: All about extending series

ExtendToEndOfMonth(series)

Extends the series with the last value until the end of the month.

Example: ExtendToEndOfMonth(sek)

This is implemented in the Macrobond formula language as:

ExtendToEndOfMonth(series) = Extend(Trim(series), At(EndOfMonth(), EndValid(series)), LastValid(series)

For more information see: All about extending series

ExtendToEndOfQuarter(series)

Extends the series with the last value until the end of the quarter.

Example: ExtendToEndOfQuarter(sek)

This is implemented in the Macrobond formula language as:

ExtendToEndOfQuarter(series) = Extend(Trim(series), At(EndOfQuarter(), EndValid(series)), LastValid(series))

For more information see: All about extending series

ExtendToEndOfWeek(firstDayOfWeek, series)

Extends the series with the last value until the end of the week.

0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday

Example: ExtendToEndOfWeek(4, sek)

This is implemented in the Macrobond formula language as:

ExtendToEndOfWeek(firstDayOfWeek, series) = Extend(Trim(series), At(EndOfWeek(firstDayOfWeek), EndValid(series)), LastValid(series))

For more information see: All about extending series

ExtendToEndOfYear(series)

Extends the series with the last value until the end of the year.

Example: ExtendToEndOfYear(sek)

This is implemented in the Macrobond formula language as:

ExtendToEndOfYear(series) = Extend(Trim(series), At(EndOfYear(), EndValid(series)), LastValid(series))

For more information see: All about extending series

Join(series1, series2)

Returns a series that joins two series at the end of series1.

Example:  join(cncons5420_old2015, cncons5420)

This is implemented in the Macrobond formula language as:

Join(series1, series2) = Join(series1, series2, EndValid(series1)+1)

Join(series1, series2, observation)

Returns a series that joins two series at the specified observation number.

Example:  join(cncons5420_old2015, cncons5420, Date(2020, 1, 1))

JoinScaled(series1, series2)

Returns a series that joins two series at the end of the series1 and scales the first series. Series need to overlap.

Example:  JoinScaled(fisurv0005, fisurv0716)

This is implemented in the Macrobond formula language as:

JoinScaled(series1, series2) = JoinScaled(series1, series2, EndValid(series1))

JoinScaled(series1, series2, observation)

Returns a series that joins two series at the specified observation number and scales the first series. Series need to overlap.

Example:  JoinScaled(fisurv0005, fisurv0716, Date(2019, 1, 1))

JoinScaledAppend(series 1, series 2) / JoinScaledAppend(series1, series2, observation)

Returns a series that joins two series at the end of the series1 and scales the second series.

Example:  JoinScaledAppend(fisurv0005, fisurv0716)

Example:  JoinScaledAppend(fisurv0005, fisurv0716, Date(2019, 1, 1))

JoinMoreHistory(series1, series2)

Returns a series of all observations of series1 complemented with all values of series2 that are before the start of series1.

Example:  JoinMoreHistory(ngprod0001, imf_q69466___zf)

This is implemented in the Macrobond formula language as:

JoinMoreHistory(series1, series2) = Join(series2, series1, StartValid(series1))

JoinMoreHistoryScaled(series1, series2)

Returns a series of all observations of series1 complemented with all values of series2 that are before the start of series1. The values of series2 are scaled so that they are equal at the beginning of series1. Series need to overlap.

Example:  JoinMoreHistoryScaled(ngprod0001, imf_q69466___zf)

This is implemented in the Macrobond formula language as:

JoinMoreHistoryScaled(series1, series2) = JoinScaled(series2, series1, StartValid(series1))

Lag(series, length)

Returns the series lagged by the specified length. The length is rounded to an integer.

Example: Lag(plgdp, -5) data moved 5 observations into the past

Example: Lag(plgdp, Quarterslength(5)) data moved 5 quarters into the future

Trim(series)

Returns a series where any null values at the start or end have been excluded.

Example: Trim(sek)

 

Logical functions

Count(series)

Returns the number of values that are True.

Example: Count(sp500_500)

Count(series, window)

Returns the number of values that are True in a window of the specified length.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: Count(sp500_500, Monthslength(6))

If(condition, value1, value2)

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

Example: If value is higher than 8, draw 1, otherwise don't draw anything: If(sek > 8,1, Null())

IsForecast(value)

Returns True if a value is a forecast and otherwise False.

Example: IsForecast(unpoppr276constant)

Example: Erase values from points in time which are forecasted in first series: if(isForecast(gbfcst6701), Null(), gbpric24651)

IsNull(value)

Returns True if a value is null and otherwise False.

Example: isNull(sp600_20201080tr)

Example: !isNull(sp600_20201080tr) ! sign is a logical 'not'

RunLength(series)

Counts how many observations in a row that have been True.

Example: runlength(sp500_500)

Example: RunLength(Counter()>EndValid(sek)-34) it assigns the number 34 to the last observation, 33 to the second last, etc.

This is implemented in the Macrobond formula language as:

RunLength(series) =
let
 .a = AggregateSum(if (series, 1, 0))
 .s = Null0(series)
 .c = FillNullWithPrevious(if (!.s & lag(.s, 1), .a, null()))
in
 if (IsNull(series), Null(), .a - Null0(.c))
end

 

Financial

BollingerLower(series, nStdDev, window)

Returns the lower Bollinger band using a window of the specified length and number of standard deviations.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: BollingerLower(sek, 2, MonthsLength(12))

This is implemented in the Macrobond formula language as:

BollingerLower(series, nStdDev, window) = Mean(series, window)-nStdDev*Sqrt(Variance(series, window))

BollingerUpper(series, nStdDev, window)

Returns the upper Bollinger band using a window of the specified length and number of standard deviations.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: BollingerUpper(sek, 2, MonthsLength(12))

This is implemented in the Macrobond formula language as:

BollingerUpper(series, nStdDev, window) = Mean(series, window)+nStdDev*Sqrt(Variance(series, window))

COP(series, length)

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

Example:  Cop(usnaac0673, YearsLenght(1))

cop(series, length) always works on the number of observations - the input must be a constant value. yearlength(), quarterlength(), monthlength() which can be used here as 'length' tells you how many numbers of observations there are in a typical year, month etc. They are constant values.
If you want something that gives you the number of observation per each year, use for example:
EndOfYear()-StartOfYear()
but please note that you cannot pass this directly into the 'cop' function since it takes a constant number and not a time series as the length. You can calculate it earlier in anther Formula analysis.

This is implemented in the Macrobond formula language as:

COP(series, length) = 
let
   .lagged = lag(series, length)
in
    CutEnd((series-.lagged)/Abs(.lagged)*100, End(series)+1)
end

Drawdown(series)

This calculates the peak-to-trough decline [as 100*(peak - value)/peak] since the last peak or since the first value. The output is expressed as 'positive' percentage: 50 should be interpreted as a 50% decline since the last peak.

This formula calculates maximum drawdown (from all-time high).

Example:  Drawdown(sp500_500)

FMACD(series, short, long)

Returns the fast-moving average convergence divergence.

Examples:  FMACD(standardize(sek), 13, 21); FMACD(euronext_eco2024n_cl, 12, 26)

InvestmentStrategy(marketValue, conditionWhenInMarket)

Calculate the value of an investment by compounding the returns of the marketValue series during the periods when the conditionWhenInMarket is True. When out of the market, the value is not changed.

Example: Invest in S&P 500 only during the first six months each year: InvestmentStrategy(sp500_500tr, Month() <= 6)

This is implemented in the Macrobond formula language as:

InvestmentStrategy(marketValue, conditionWhenInMarket) =
let
  .ReturnWhenInMarket = if (Lag(conditionWhenInMarket, 1), COP(marketValue, 1)/100+1, 1)
in
  AggregateProduct(.ReturnWhenInMarket)
end

InvestmentStrategy(marketValue, buyCondition, sellCondition)

Calculate the value of an investment by compounding the returns of the marketValue series during the periods between when the buyCondition becomes True until the sellCondition becomes True. When out of the market, the value is not changed.

Example: Invest in S&P when it is 10% over the mean of the last 200 observations and sell when it is 10% below the mean: InvestmentStrategy(sp500_500tr, sp500_500tr > Mean(sp500_500tr, 200)*1.1, sp500_500tr < Mean(sp500_500tr, 200)*0.9)

This is implemented in the Macrobond formula language as:

InvestmentStrategy(marketValue, buyCondition, sellCondition) =
let
  .InMarketPeriods = Signal(buyCondition, sellCondition)              // 1 = invested, 0 = not invested
  .ReturnWhenInMarket = if (Lag(.InMarketPeriods, 1), COP(marketValue, 1)/100+1, 1)
in
  AggregateProduct(.ReturnWhenInMarket)
end

InvestmentStrategy(inMarketValue, outOfMarketValue, buyCondition, sellCondition)

Calculate the value of an investment by compounding the returns of the marketValue series during the periods between when the buyCondition becomes True until the sellCondition becomes True

Example: Invest in S&P when it is 10% over the mean of the last 200 observations, sell when it is 10% below the mean and invest in a bond index when out of the market: InvestmentStrategy(sp500_500tr, djcfin30tr, sp500_500tr > Mean(sp500_500tr, 200)*1.1, sp500_500tr < Mean(sp500_500tr, 200)*0.9)

This is implemented in the Macrobond formula language as:

InvestmentStrategy(inMarketValue, outOfMarketValue, buyCondition, sellCondition) =
let
  .InMarketPeriods = Signal(buyCondition, sellCondition)              // 1 = invested, 0 = not invested
  .ReturnWhenInMarket = if (Lag(.InMarketPeriods, 1), COP(inMarketValue, 1), COP(outOfMarketValue, 1))/100+1
in
  AggregateProduct(.ReturnWhenInMarket)
end

Note the condition is thus lagged by 1.

Rsi(series, window)

Returns the relative strength index of the series using a window of the specified length.
The relative strength is calculated by the method described by J. Welles Wilder 1978 and uses an exponential smoothing.

For more information how parameter window works, what happens at the start of series and what can you do with it see Window parameter (length).

Example: rsi(sp500_500, 25)

Signal(onCondition, offCondition)

Calculate a signal based on conditions when the signal is turned on (1) and when it is turned off (0). If both conditions are 'true' the expected result is 0. The method works best when the off and on conditions are mutually exclusive.

Example:  Signal(sek>8, sek<8)

This is implemented in the Macrobond formula language as:

Signal(onCondition, offCondition) =
let
  .OnAndOff = if (offCondition, 0, if (onCondition, 1, null())) // 1 = signal, 0 = no signal
in
  FillNullWithPrevious(.OnAndOff) // 1 = signal, 0 = no signal
end

SMACD(series, short, long, signal)

Returns the exponential average of the fast-moving average convergence divergence.

Examples: SMACD(standardize(sek), 13, 14, 2); SMACD(euronext_eco2024n_cl, 12, 26, 9)

This is implemented in the Macrobond formula language as:

SMACD(series, short, long, signal) = EMean(FMACD(series, short, long),2/(signal+1))

TotalReturnFromShortYield(yieldSeries, maturityInYears)

Estimates the total return from yield when there are no coupons using Actual/360 day count convention.

Example:  TotalReturnFromShortYield(us3mgov, 0.25)

This is implemented in the Macrobond formula language as:

TotalReturnFromShortYield(yieldSeries, maturityInYears) = AggregateProduct((yieldSeries*Days(Counter()-1, Counter())/360 - Momentum(yieldSeries, 1)*maturityInYears)/100 + 1)

TotalReturnFromYield(yieldSeries, duration)

Estimates the total return from yield given the duration.

Example:  TotalReturnFromYield(us10ygov, 10)

This is implemented in the Macrobond formula language as:

TotalReturnFromYield(yieldSeries, duration) = AggregateProduct((yieldSeries/ObservationCountPerYear()-Momentum(yieldSeries, 1) * duration ) / 100 + 1)