Formula Language¶
Formulas in VOR Stream are expr-lang expressions, not SQL and not Excel. This page is the single reference for the operators and functions available to them.
Two places in the product accept a formula: a Transformed Factor, defined in the Factors module, and a Local Transformation, defined in the Dynamic Fields panel of a structured model. They share this language but not the same set of functions, which Two contexts explains.
New to structured models?
Start with Build Your First Structured Model for a complete create-run-verify loop. Return here when you need formula syntax, function signatures or edge-case behavior.
Writing a formula¶
Reference another value by name in braces:
{House Price Index} * 1.05
A bare name without braces is rejected, so GDP * 2 is an error and
{GDP} * 2 is correct. Parentheses inside a name belong to the name, so
{House_Price_Index_(Level)} refers to a factor whose name ends in (Level).
Match the name exactly as it is defined. A Transformed Factor resolves names case-insensitively, but a Local Transformation does not, and its editor reports an unknown field when the case differs. Writing the name exactly is correct in both contexts.
Every formula must produce a single decimal number. Both contexts assert exactly that when they read the result, so text, a date, a list, a true or false value, and even a whole number all fail. The operators and functions yielding those belong inside a formula rather than at the end of one.
Two contexts¶
| Transformed Factor | Local Transformation | |
|---|---|---|
| Defined in | Factors module | Dynamic Fields panel of a structured model |
| Evaluated | once per scenario horizon | once per portfolio record, per scenario, per horizon |
| May reference | other factors | dictionary columns and factors |
| Functions | operators, scalar functions and time-horizon functions | operators and scalar functions only |
| Chaining | supported, ordered automatically by dependency | not supported |
A Transformed Factor has a time axis: many dated horizons per scenario, which is what lets the time-horizon functions reach a neighbouring period. A Local Transformation evaluates one observation with no time axis, so those functions are not available to it.
A time-horizon function saves but fails in a Local Transformation
Both kinds of formula are syntax-checked when you save, by a validator that
stubs the time-horizon functions in so they pass. That validator does not
distinguish the two contexts. A Local Transformation calling lag, lead,
dif, getval or getvaldate is therefore accepted when you save it and
fails only when the model runs.
To bring a prior-period value into a structured model, compute it as a Transformed Factor and reference that factor as a predictor instead.
Operators¶
Available in both contexts.
| Operator | Description |
|---|---|
+ |
Addition |
- |
Subtraction |
* |
Multiplication |
/ |
Division |
^ or ** |
Exponentiation |
== |
Equal to |
!= |
Not equal to |
< |
Less than |
<= |
Less than or equal to |
> |
Greater than |
>= |
Greater than or equal to |
&& |
Logical and |
|| |
Logical or |
! |
Logical not |
? : |
Conditional, see below |
?? |
Substitute when the left side is nil |
in |
Membership test |
matches |
Regular expression match |
There is no % operator for field values. It requires whole numbers and errors
on the decimal values that factors and dictionary columns hold. Use the
mod(x, y) function instead.
Conditionals¶
There is no if function. Conditional logic uses the conditional operator,
which takes the form condition ? value_if_true : value_if_false:
{CreditScore} > 700 ? 1.0 : 0.0
Write every branch as a decimal. A conditional returns whichever branch ran, with
no conversion between them, so ? 1 : 0 returns a whole number and a structured
model rejects it. Mixing the two forms is worse than getting it wrong everywhere,
because it then fails on only some records: {rate} < 0 ? 0 : {rate} works until
a rate is actually negative.
An equality or ordering comparison (==, <, <=, >, >=) against a missing
value is false, so a record with a missing input takes the value_if_false branch
and is scored with no sign that its data was absent. The two negated forms go the
other way: != against a missing value is true, and wrapping any of the false
comparisons in !(...) turns it true, so those send the same record down the
value_if_true branch instead. Either way the record is scored as though its data
were present. Where that matters, test for it first with isnull({Field}), or
substitute a value with coalesce({Field}, 0).
Combine conditions with &&, || and !, and nest by chaining. Every chain
needs a final branch; there is no form that leaves a case unanswered:
{CreditScore} >= 700 && {LTV} < 0.9 ? 1.0 : 0.0
{Province} in ["ON", "BC"] ? 1.0 : 1.25
{CreditScore} >= 750 ? 1.0 : {CreditScore} >= 650 ? 2.0 : 3.0
Prefer &&, || and ! over and, or and not
The word forms are valid in the language, but the formula editor reports
Unknown function when one is followed by an opening parenthesis, as in
not ({LTV} > 0.9), because it reads the word as a function name. The
symbol forms are never misread, so write !({LTV} > 0.9).
Which functions are available¶
The tables on this page are the whole catalog: the formula editor accepts exactly the functions listed here and rejects everything else, so availability is a fact of the list rather than a rule to test a candidate function against. Two properties shaped the list.
Most entries return a single number. The exceptions are the text functions
and the conversion helpers isnull and string, which return text or a true
or false value so the result can feed a comparison inside a larger formula.
A missing number is NaN in VOR Stream. The aggregation functions treat a
missing value the way SQL aggregates ignore NULL: they skip missing inputs and
return a missing value only when every input is missing, so summing three values
of which the middle one is missing gives the total of the other two. Everything
else propagates, and an expression with a missing operand is missing, with
sign the one exception, noted at its entry. NaN is not a word you can write
in a formula, but an expression can produce one, as sqrt(-1) does.
The expression language ships around sixty further functions of its own. The formula editor rejects every one of them. Two are worth naming because they look like natural choices and are not in the catalog:
| Function | Returns | What keeps it out |
|---|---|---|
now() |
a timestamp | reads the clock, so a record would score differently run to run |
toJSON(x) |
text | a serialization aimed at programs, with no role in a formula |
Scalar functions¶
Available in both contexts, with the exception noted under Dates.
Math¶
| Signature | Description |
|---|---|
abs(x) |
Absolute value |
cbrt(x) |
Cube root |
ceil(x) |
Round up to the nearest integer |
exp(x) |
Exponential, e to the power x |
floor(x) |
Round down to the nearest integer |
ln(x) |
Natural logarithm |
log(x) |
Base-10 logarithm |
mod(x, y) |
Remainder, works on decimal values |
pi() |
The value of pi |
power(x, n) |
x raised to the power n |
round(x) |
Round to the nearest integer |
sign(x) |
-1 when x is negative, otherwise 1 |
sqrt(x) |
Square root |
trunc(x) |
Truncate towards zero |
sign is the one function here that does not pass a missing value through: it
tests only for "less than zero", so both zero and a missing value come back as 1.
Guard the input with isnull if the difference matters.
log is base 10 and ln is the natural logarithm. Transposing them is a common
mistake and produces a plausible number, so it is worth checking when a
transformed value looks wrong by a constant factor.
Statistical¶
| Signature | Description |
|---|---|
probnorm(x) |
Standard normal CDF, the probability that Z is at most x |
probit(p) |
Inverse standard normal CDF, the quantile; p in (0, 1) |
probit is the inverse of probnorm. Together they express the standard
credit-risk transformations: probit converts a probability into a normal-space
value, and probnorm converts one back into a probability.
For a normal variable with mean {mu} and positive standard deviation {sigma},
first standardize it: probnorm(({x} - {mu}) / {sigma}).
probit is defined on the open interval only. At the boundaries it returns an
infinity, probit(0) giving -Inf and probit(1) giving +Inf, and outside
[0, 1] it returns a missing value rather than an error. A structured model fails
the record and names the offending predictor only when that value reaches the
linear combination; wrapped in probnorm it collapses back to a finite 0 or 1
and is scored silently. Clamp a PD that the data can drive to exactly 0 or 1.
Aggregation¶
Each of these skips missing inputs and returns a missing value only when every input is missing.
| Signature | Description |
|---|---|
min(a, b, ...) |
Smallest value |
max(a, b, ...) |
Largest value |
mean(a, b, ...) |
Arithmetic mean |
median(a, b, ...) |
Median value |
sum(a, b, ...) |
Sum of the values |
coalesce(a, b, ...) |
First present value |
Write mean and median the way the table shows, listing values one by
one. They also accept array arguments, in any position and mixed with plain
values, so mean([{A}, {B}]) and mean([{A}, {B}], {C}) evaluate too, each
array contributing its values; only one level is unwrapped, so an array of
arrays is an error. That form exists because these two shadow expression
functions that took a single array, and it keeps working for formulas
submitted through the API or an import. The editor does not know square
brackets: it neither highlights nor closes them, and an unbalanced one is
reported by the engine on save rather than marked as you type. The other
four aggregates take plain values only, so sum([{A}, {B}]) is an error.
Dates¶
| Signature | Description |
|---|---|
datediff(unit, date1, date2) |
date1 minus date2, as a number |
dateadd(unit, n, date) |
A date shifted by an interval |
unit is one of year, quarter, month, week, day or weekday, in any
case.
datediff subtracts its second date from its first, so it is negative whenever
the first date is the earlier one. Write the later date first to read a
duration as a positive number.
A date value is written as a quoted string with a d suffix, as in
"2025-01-01"d; a bare date is rejected and a quoted one without the suffix is
flagged. A literal is only usable in a formula the editor does not have to
validate, so prefer reaching a date through a factor or column reference.
A formula represents every date as a whole number of days since 1 January 1970.
A date factor or column is converted on the way in, and a date literal is
rewritten to the same number before the formula runs. dateadd returns a date
in that representation, so its result can be used anywhere a date is accepted:
as an argument to datediff, or as the date getvaldate looks up.
datediff('month', dateadd('month', -6, {MatDate}), {OrigDate})
That is the number of months from origination to six months before maturity, positive because the later date is written first.
dateadd is the only way to shift a date. Arithmetic on a date reference does
not work, because the converted value carries a date type rather than a plain
number, so use dateadd for days as well as for months, quarters and years.
A missing date is not treated as missing here: it converts to the epoch, so
shifting one returns a real date in 1970 rather than a missing value. datediff
has always behaved the same way.
Date handling differs between the two contexts, and a Transformed Factor is where it is most useful, because that context supplies the horizon date the arithmetic needs. See Risk Factor Transformations.
There is no function for today's date¶
A formula cannot read the clock. getdate() is rejected, and the error says why
and points at {date} instead.
A study that read today's date would return different figures each time it ran,
which defeats the point of being able to reproduce a result. {date} carries
the date the run is evaluating, which is the date a model wants in any case. It
holds the horizon date in a Transformed Factor and in a structured model driven
by a scenario. A structured model that reads no scenario has no date available.
Text¶
Text functions are most often used on a categorical dictionary column. They return text or a position, so a formula must reduce their result to a number before it finishes.
| Signature | Description |
|---|---|
hasPrefix(str, prefix) |
Whether the text starts with prefix |
hasSuffix(str, suffix) |
Whether the text ends with suffix |
indexOf(str, substring) |
Position of the first match, -1 if absent |
lastIndexOf(str, substring) |
Position of the last match, -1 if absent |
len(v) |
Length |
lower(str) |
Convert to lower case |
replace(str, old, new) |
Replace occurrences |
split(str, delimiter[, n]) |
Split on a delimiter |
substring(str, start, length) |
Extract a portion, counting from 0 |
trim(str[, chars]) |
Remove surrounding whitespace, or chars |
trimPrefix(str, prefix) |
Remove a leading prefix |
trimSuffix(str, suffix) |
Remove a trailing suffix |
upper(str) |
Convert to upper case |
Conversion¶
| Signature | Description |
|---|---|
float(v) |
Convert to a decimal number |
int(v) |
Convert to a whole number |
isnull(x) |
Whether a value is missing |
string(v) |
Convert to text |
isnull treats NaN as missing, so it is the reliable way to test for a
missing number. See Missing values.
Time-horizon functions¶
Available in a Transformed Factor only. Each reaches a different horizon of the scenario series. An offset outside the available range yields a missing value unless a default is supplied.
These need a scenario run to mean anything. Without one the Transformed Factor's formula is never evaluated, so a model referencing that factor as a predictor fails rather than scoring the record: the engine reports that the independent variable was not supplied, or that the linear model produced NaN and names the variable that did it.
| Signature | Description |
|---|---|
getval(x, offset) |
Value of x at offset horizons from the current one; negative is backward |
getval(x, offset, default) |
As above, returning default when out of range |
getval(x, offset, default, true) |
Offset measured from horizon 0, the as-of date, rather than the current horizon |
lag(x) |
Value one horizon back |
lag(x, n) |
Value n horizons back; n must be positive and is truncated towards zero |
lag(x, n, default) |
As above, returning default when out of range |
lead(x) |
Value one horizon forward |
lead(x, n) |
Value n horizons forward, with the same rules as lag |
lead(x, n, default) |
As above, returning default when out of range |
dif(x, offset) |
Current value minus the value offset horizons back; a negative offset looks forward |
dif(x, offset, default) |
As above, with a default when out of range |
getvaldate(x, date) |
Interpolated value of x at a specific date |
getvaldate(x, date, default) |
As above; the default applies only when x has no data at all |
A date outside the range of the data does not reach getvaldate's default. The
series is held flat beyond its ends, so a date before the first observation
returns the first value and a date after the last returns the last.
dif counts in the opposite direction to getval
A negative offset looks backward in getval, so getval(x, -1) is the
previous horizon. A positive offset looks backward in dif, so
dif(x, 1) is the change since the previous horizon. The two are deliberate
opposites and easy to transpose.
getval(x, 0) returns the current horizon. dif(x, 0) returns zero when the
current value exists. With a default, dif(x, 0, default) also returns zero when
the factor is absent because the same default supplies both terms. lag
and lead reject an offset of zero or less, and truncate a fractional offset
towards zero, so lag(x, 0.5) reads the current horizon.
Reaching a prior period from a model¶
A structured model has no time axis, so it cannot look backward on its own. There are three ways to give it a prior-period value, in rough order of preference:
- Define a Transformed Factor that carries the lag and reference that
factor as a predictor. A transformed factor whose formula is
lag({Unemployment Rate}, 1)is then available to any model, and the factor engine orders it correctly against its dependencies. - Supply an input column that already holds the lagged value, prepared upstream in the portfolio data.
- Compute it in a freeform or computational node ahead of the model node, writing the result as a column the model reads.
An autoregressive term needs one of these too, and which one depends on where the lagged value comes from. Only option 1 moves with the horizon: a factor is re-read at every horizon, so a Transformed Factor carrying the lag gives a term that advances across the scenario grid. Options 2 and 3 prepare a column, and a column is fixed for the life of the record, so they give one lag measured from the as-of date rather than a term that advances.
A recursive AR(1), where the model's own output for one period feeds its input for the next, is none of them. A model cannot read its own output, so that recursion has to be unrolled into a model per period.
Missing values¶
A missing number is represented by NaN, which plays the role NULL plays in
SQL. Two consequences are worth knowing.
The aggregation functions skip missing inputs: a sum over three fields of
which one is missing returns the total of the other two rather than a missing
value. This matches SUM ignoring NULL in SQL.
?? does not detect a missing number
The ?? operator substitutes only when the left side is nil, and a missing
number is NaN rather than nil. {LTV} ?? 0 still yields a missing value
when {LTV} is missing. Use coalesce({LTV}, 0) to supply a fallback, or
isnull({LTV}) to test for one. Both treat NaN as missing.
Naming a derived value¶
A Local Transformation or Lookup Reference must not be given a name that matches a dictionary column or factor name.
A name collision produces a wrong number, not an error
Names have to be unique across the Dynamic Fields panel, dictionary columns and factors. The editor does not enforce collisions with dictionary columns or factors. When a name collides, the model runs without error and one name carries two values. An exact match with a data column overwrites that column, so a predictor reading that name always gets the computed value. Names differing only in case keep both values under their own spellings, and an output queue column of that name matches both, so either can land there, record by record. Either way the result is plausible and wrong for one of the name's two meanings.
Examples¶
Transformations of a single value:
sqrt({real_gdp})
ln({LTV})
{Income} * 12
{Balance} / max({Limit}, 1)
A change between two values, and a log change:
{US_10Y_Yield} - {CPI_inflation_rate}
log({GDP}) - log({GDP_prior})
Conditional logic:
{CreditScore} > 700 ? 1.0 : 0.0
{Province} in ["ON", "BC"] ? 1.0 : 1.25
{rate} < 0 ? 0.0 : {rate}
{unemployment} > 10 ? {stressed_pd} : {base_pd}
{ltv} > 0.8 && {dti} > 0.4 ? {high_risk_pd} : {base_pd}
{score} >= 800 ? 1.0 : ({score} >= 700 ? 2.0 : 3.0)
Handling a value that may be missing:
coalesce({LTV}, 0)
coalesce({primary_rate}, {fallback_rate}, 0)
isnull({denominator}) ? 0.0 : {numerator} / {denominator}
Credit-risk transformations:
probit(0.999)
probnorm((probit({PD}) + sqrt({R}) * probit(0.999)) / sqrt(1 - {R}))
Reaching another horizon, in a Transformed Factor only:
getval({x}, -1)
getval({x}, -1, 2.3)
getval({x}, -1, 0.0, true)
lag({x}, 1)
lead({x}, 2)
dif({x}, 1, 0.0)
Where formulas are used¶
- Build Your First Structured Model is the end-to-end starting point.
- Structured Model Use Cases applies Local Transformations and Lookup References to modeling goals.
- Structured Models explains the regression and field-reference dependency rules.
- Risk Factor Transformations covers Transformed Factors, including how their execution order is derived from dependencies.
- Transformed Factors in the UI covers the editor itself.