Calculations with formulas

How to:

Annualize a monthly P/P series with a formula?

Use the following expression:

pow((1+(series/100)), YearLength())-1

The expression '1+ (series / 100)' will be raised to the power of 'yearlength()'. After which 1 will be subtracted from the result.

Change the color of a series when it’s below / above 0 (or any other value)?

  1. Create two series, one containing only the values above 0 and another containing the values below 0  and then graph each series in a different color. Do the following:
    a) In the series list, type the expressions:

    if(series >0, series, Null())
    
    if(series <0, series, Null())

    b) In the chart, click on a graph and open the Presentation properties tab. Select 'Custom' from the graph style drop-down menu in the appearance group. Select the color of your choice.

  2. Flag the values below 0 as forecast values, and change the color in which forecast values are graphed, by doing the following:
    a) In the series list, type the expression:

    if(series > 0, series, flagforecast(series))

    b) In the chart, click on the graph of the series and open the presentation properties tab. Select 'custom' from the graph style drop-down menu in the appearance group. Click 'forecast.' Select the color of your choice.

  3. Flag the values above 0 as forecast values, and change the color in which forecast values are graphed, by doing the following:
    a) In the series list, type the expression:

    FlagForecast(fx:s1, fx:s1>0)

b) In the chart, click on the graph of the series and open the presentation properties tab. Select 'custom' from the graph style drop-down menu in the appearance group. Click 'forecast.' Select the color of your choice.

Create an if condition/statement?

Formula language can be used in both the series list and the formula analysis. The if-statement is a formula requiring three parameters:

if(condition, value1, value2)

which can be expanded on as:

if(condition, if_TRUE_return_this , if_FALSE_return_this)

For example:

if(sek > 8, sek, 0)

Which returns a series with values of 0 on days when the series is below 8 and values equaling the series when the currency pair is above 8.

Create a continuous growth rate?

To create constantly growing line use formula for compound interest:

AggregateProduct(1+VALUE/100)*100

for example, 5% continuous growth rate would be:

AggregateProduct(1+0.05/100)*100

Combine an if condition/statement with the logical operators and/or?

To include 'and' in the function use the ' & ' sign as such:

if(condition1 & condition2, value1, value2)

which can be expanded on as:

if(condition1 and condition2, if_TRUE_return_this , if_FALSE_return_this)

For example:

if(sek>7 & sek<8, sek, 0)

which returns the values of the series on days when the series is between 7 and 8. For all other observations, the value of the series will be 0.

To include 'or' in the function use the ' | ' sign as such:

if(condition1|condition2, value1, value2)

which can be expanded on as:

if(condition1 OR condition2, , if_TRUE_return_this , if_FALSE_return_this)

For example:

if(sek>7 | sek<9, sek, 0)

which returns the values of the series on days when the series is above 7 or below 9. For all other observations, the value of the series will be 0.

Make an index out of P/P series?

To create an index from a return series, use formula for compound interest:

AggregateProduct(1+(series)/100)*100

The ' *100 ' creates here starting base value.

Note that for different series you might need to transform this calculation.

Disaggregate a series?

Series which are aggregated annually can be 'disaggregated' by using an If() statement in the formula language.

Example:

If(Counter()=StartOfYear(), cngpfi0091, Momentum(cngpfi0091, 1))

The logic of this formula is: keep the first value of each year as given, but the subsequent values in that year are calculated by subtracting the previous value from the current value.

Replace null values for 0 in a time series?

You can use the function:

Null0(series)