# Built-in formula functions

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

### Built in functions

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.

Acos(value)

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

AggregateProduct(series)

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

AggregateSum(series)

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

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

AggregateSumAnnual(series)

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

This is implemented in the Macrobond formula language as:

AggregateSumAnnual(series) =

let

.a = AggregateSum(series)

in

end

Asin(value)

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

Atan(value)

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

Atan2(x, y)

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

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

CFRandomWalkFilter(series, shortPeriod, longPeriod)

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

CFStationaryFilter(series, shortPeriod, longPeriod)

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

Ceiling(value)

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

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.

Cot(value)

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

CountValid(series)

Returns the number of values that are not null.

CountValid(series, window)

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

CoVariance(series1, series2)

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

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

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.

For

Exp(value)

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

FillNullWithLinear(series)

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

FillNullWithNext(series)

Fills null values with the next value.

FillNullWithPrevious(series)

Fills null values with the previous value.

FillNullWithPreviousAsForecast(series)

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

First(series)

Returns the first number of a series.

FirstGreaterOrEqual(series, value)

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

FirstLessOrEqual(series, value)

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

FirstValid(series)

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

Floor(value)

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

GeometricMean(series)

Returns the geometric moving average.

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.

This is implemented in the Macrobond formula language as:

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

High(series)

Returns the highest number.

High(series, window)

Returns the highest number in a window of the specified length. Starting from Macrobond ver. 1.20 you can specify a vector as the length parameter in the High and Low formula functions. For example, you can get the highest value in a series using an expanding window like this: High(usgdp, Counter(eur)-Start(eur)+1).

HighObs(series)

Returns the observation number of the last highest value.

HighObs(series, window)

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

In this formula a “window” can be a number or a series. If the values in a series are not natural numbers, application will round them down to the nearest natural number.

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.

A typical value for lambda when used for quarterly data is 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.

A typical value for lambda when used for quarterly data is 1600.

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.

Intercept(series1, series2)

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

Intercept(series1, series2, window)

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

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Last(series)

Returns the last value of a series.

LastGreaterOrEqual(series, value)

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

LastLessOrEqual(series, value)

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

LastValid(series)

Returns the last valid value of a series.

LastNonForecast(series)

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

Linear(series)

Returns a line fitted using the least square method.

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.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

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.

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

LinearExtended(series, observationStart, observationEnd, observationExtended)

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

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

LinIntercept(series)

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

LinIntercept(series, window)

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

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

LinSlope(series)

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

LinSlope(series, window)

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

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Ln(value)

Returns the natural logarithm.

Log(value)

Returns the logarithm with base 10.

Low(series)

Returns the lowest number of the series.

Low(series, window)

Returns the lowest number in a window of the specified length. Starting from Macrobond ver. 1.20 you can specify a vector as the length parameter in the High and Low formula functions. For 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.

LowObs(series, window)

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

In this formula a “window” can be a number or a series. If the values in a series are not natural numbers, application will round them down to the nearest natural number.

Max(value1, value2)

Returns the largest value of a pair of values.

Mean(series)

Returns the mean value as a number.

Mean(series, window)

Returns the moving average of the specified length.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

In this formula a “window” can be a number or a series. If the values in a series are not natural numbers, application will round them down to the nearest natural number.

Median(series)

Returns the median as a number.

Median(series, window)

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

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

In this formula a “window” can be a number or a series. If the values in a series are not natural numbers, application will round them down to the nearest natural number.

Min(value1, value2)

Returns the smallest value of a pair of values.

Mod(value1, value2)

Returns the reminder of an integer division.

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.

NextGreaterOrEqual(series, value, observation)

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

Null()

Returns the null number.

Null0(value)

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

Null1(value)

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

PI()

The number π.

Pow(value, power)

Returns a value raised to a power.

PreviousGreaterOrEqual(series, value, observation)

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

PreviousLessOrEqual(series, value, observation)

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

Product(series)

Returns the product of all numbers in the series.

Product(series, window)

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

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.

Sign(value)

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

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.

Slope(series1, series2)

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

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.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Sqrt(value)

Returns the square root.

Sum(series)

Returns the sum of all numbers in the series.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Sum(series, window)

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

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

In this formula a “window” can be a number or a series. If the values in a series are not natural numbers, application will round them down to the nearest natural number.

Tan(value)

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

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

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.

Correlation(series1, series2)

Returns the correlation coefficient of series1 and series2.

Correlation(series1, series2, window)

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

CorrelationBestLag(series1, series2, startLag, endLag)

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

CoVariance(series1, series2, window)

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

FDist(value, degFreeNum, degFreeDenom)

Returns the cumulative probabilty 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.

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.

Kurtosis(series)

Returns the excess kurtosis of a series.

Kurtosis(series, length)

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

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.

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.

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.

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.

NormDist(value)

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

NormDist(value, mean, stdDev)

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

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.

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.

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.

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.

At the start of the series, before there are enough values to cover a full window, the calculation
will be made on fewer observations than specified by the window parameter. This is also the
case if there are missing values.

ExpandingPercentile(series, p, window)

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

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.

PercentRank(series, value, lenght)

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.

ExpandingPercentRank(series, value, lenght)

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

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

Skewness(series)

Returns the skewness of a series.

Skewness(series, length)

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

Standardize(series)

Returns a normalised series where the mean is 0 and the standard deviation is 1.

Standardize(series, window)

Returns a normalised series where the mean is 0 and the standard deviation is 1 percentile within a window of the specified length.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

This is implemented in the Macrobond formula language as:

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

StdDev(series)

Returns the standard deviation of the numbers in a series relative its mean value.

StdDev(series, window)

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

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

In this formula a “window” can be a number or a series. If the values in a series are not natural numbers, application will round them down to the nearest natural number.

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.

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.

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)

Available from Macrobond version 1.19

It returns the Tukey’s trimean.

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.

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.

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.

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.

Variance(series)

Returns the variance of the series.

Variance(series, window)

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

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

### Date and observation number functions

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

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

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

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

At(series, observation)

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

Counter()

Returns a series of all observation numbers.

Counter(series)

Returns a series of all observation number for the specified series.

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 should generally not use this function to find the start of a year or month. Use the StartOfYear(year) and StartOfMonth(year, month) for this purpose.

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.

Day()

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

Day(observation)

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

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

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

Days(observation1, observation2)

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

Days30E(observation1, observation2)

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

End()

Returns the last observation number for calculations.

End(series)

Returns the last observation number of the series.

EndOfMonth()

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

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

EndOfQuarter()

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

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

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

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

EndOfYear()

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

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

EndValid(series)

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

FlagForecast(value)

Returns a value as a forecast value.

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.

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

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

Length(series)

Returns the number of observations in a time series.

Month()

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

Month(observation)

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

MonthLength()
Returns the number of observations per month based on the frequency.

MonthsLength(months)
Returns the number of observations for a number of months based on the frequency.
MonthsLength(1) is the same as MonthLength()

MonthOffset()

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

MonthOffset(observation)

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

NextLessOrEqual(series, value, observation)

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

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

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.

ObservationCountPerYear()

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

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.

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.

Quarter(observation)

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

QuarterLength()
Returns the number of observations per quarter based on the frequency.

QuartersLength(quarters)
Returns the number of observations for a number of quarters based on the frequency.
QuartersLength(1) is the same as QuarterLength()

QuarterOffset()

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

QuarterOffset(observation)

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

Start()

Returns the first observation number for calculations.

Start(series)

Returns the first observation number of the series.

StartValid(series)

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

StartOfYear()

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

StartOfYear(year)

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

StartOfQuarter()

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

StartOfMonth()

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

StartOfMonth(year, month)

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

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

WeekLength()
Returns the number of observations per week based on the frequency.

WeeksLength(weeks)
Returns the number of observations for a number of weeks based on the frequency.
WeeksLength(1) is the same as WeekLength()

Year()

Returns a series of the year for each observation.

Year(observation)

Returns the year of the specified observation number.

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

YearsLength(years)
Returns the number of observations for a number of years based on the frequency.
YearsLength(1) is the same as YearLength().

YearOffset()

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

YearOffset(observation)

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

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.

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

CutEnd(series, observation)

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

CutStart(series, observation)

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

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 is filled in with the last value.

ExtendLastAsForecast(series)

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

This is implemented in the Macrobond formula language as:

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

ExtendLastYoYForecast(series) / ExtendLastYoYForecast(series, observation)

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

ExtendLinear(series, observation, number)

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

ExtendToEndOfMonth(series)

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

This is implemented in the Macrobond formula language as:

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

ExtendToEndOfQuarter(series)

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

This is implemented in the Macrobond formula language as:

ExtendToEndOfQuarter(series) = Extend(Trim(series), At(EndOfQuarter(), EndValid(series)), LastValid(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

This is implemented in the Macrobond formula language as:

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

ExtendToEndOfYear(series)

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

This is implemented in the Macrobond formula language as:

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

Join(series1, series2)

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

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.

JoinScaled(series1, series2)

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

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.

JoieScaledAppend(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.

JoinMoreHistory(series1, series2)

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

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.

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.

Trim(series)

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

### Logical functions

Count(series)

Returns the number of values that are True.

Count(series, window)

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

If(condition, value1, value2)

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

IsForecast(value)

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

IsNull(value)

Returns True if a value is null and otherwise False.

RunLength(series)

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

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.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

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.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

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.

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)

Each value is calculated as 100*(peak - value)/peak, where the peak is the most recent peak relative the value or the first value.

FMACD(series, short, long)

Returns the fast moving average convergence divergence.

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

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

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

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.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Signal(onCondition, offCondition)

Calculate a signal based on conditions when the signal is turned on (1) and when it is turned off (0)

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.

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.

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.

This is implemented in the Macrobond formula language as:

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