Formula language
TS 2.11.14. Formulas calculate new time series from other series — like Excel formulas, except that the operation is applied to all values of the series at once. Add a formula in an analysis by choosing a formula series (see Analyses); formulas also work in the Excel add-in.
Syntax
| Element | Example | Explanation |
|---|---|---|
| Name of another series of the analysis | s1, gdp |
The formula name (short name) of a series in the same analysis |
| Number | 100, 0.5, 1e3 |
The decimal separator in formulas is a point |
| Operators | + - * / ** |
Addition, subtraction, multiplication, division, power; -s changes the sign |
| Parentheses | (s1 - s2) / s2 |
Set the order of operations |
| Function | yoy(s1), convert(s1, "Q", "sum") |
Any operation by its name |
| Named parameters | cycle(s1, "hp", output="pct") |
A parameter can be given by name |
| Text | "Q", "2019-Q4" |
Text is written in quotes |
| List | [0.6, 0.4] |
E.g. weights |
| Database series | S("ESTAT/namq_10_gdp/Q.CLV10_MEUR.SCA.B1GQ.LT") |
Loads any series by its key (shown on the series page) |
| User series | U("gdp_forecast") |
Loads one of your saved user series |
| Constants | pi, e |
Mathematical constants |
Function parameters follow the series: by position (change(s, "pct", "year")) or by name (change(s, kind="log")). Optional parameters can be left out — their defaults apply.
How calculations work
- Series and number: the operation is applied to every value of the series (
s * 100). - Two series: values of the same period are combined. The series must have the same frequency — otherwise an error suggests using
convert. The result covers all periods of both series; where a value of either series is missing, the result is missing too. - Functions can be nested:
yoy(convert(s1, "Q", "average")). - The title of the result is the formula text unless you give the series your own label.
- Formulas may use other formulas; they are calculated in the necessary order. Circular references (formulas referring to each other in a circle) are not allowed.
Examples
Share of GDP, % — general government debt relative to GDP:
debt / gdp * 100
Trade balance:
exports - imports
Real wage growth (exact formula):
((1 + yoy(wages) / 100) / (1 + yoy(cpi) / 100) - 1) * 100
GDP per capita, EUR (GDP in EUR million, population in thousands):
gdp / pop * 1000
Real series from a nominal one and a deflator (2015 = 100):
nominal / deflator * 100
Quarterly average of monthly data and its year-on-year change:
yoy(convert(s1, "Q", "average"))
Output gap (deviation from the HP trend, %):
cycle(gdp_real, "hp", output="pct")
Weighted average of three countries:
wavg(lv, ee, pl, weights=[0.5, 0.3, 0.2])
Inflation differential between Lithuania and the euro area (percentage points):
S("ESTAT/prc_hicp_manr/M.RCH_A.CP00.LT") - S("ESTAT/prc_hicp_manr/M.RCH_A.CP00.EA")
(Copy the exact keys from the series pages — the keys in this example are illustrative.)
Replacing missing values with zero:
if_missing(s1, 0)
Restrictions and safety
The formula language is deliberately simple and safe: only numbers, quoted text, lists, series names, operators and permitted functions are allowed. Comparisons, conditions, variable assignment and other programming constructs are not used. A formula can be up to 2,000 characters long.
Error messages
An invalid formula shows a message with an explanation and the position in the formula, for example:
| Message | Cause |
|---|---|
| Unknown name | The short name of a series is misspelt or no such series exists in the analysis |
| Unknown function | The function name is misspelt — see the function reference |
| Different frequencies | An operation between series of different frequencies — use convert |
| Syntax error | A missing parenthesis, quote or comma |
| Series not found | A mistake in the S("…") key — copy the key from the series page |
The full list of functions with parameters: Function reference.