Write a formula

The formula language of the Calculator and the Sheet: numbers, names, operators and precedence, value kinds, date alignment of series, display and errors.

The language

The Calculator and the Sheet evaluate the same formula language. A formula is one expression: numbers, names, function calls and operators. It returns one value. This page describes the language as the Calculator accepts it. Cells, ranges and other sheets adds what a Sheet cell accepts on top.

Spaces, tabs and line breaks between parts of a formula are ignored.

Numbers and strings

Number: digits with an optional decimal part and an optional exponent. 12, 1.5, .5, 1.5e-3 and 2E3 are numbers. A number cannot start with a minus sign. -3 is unary minus applied to 3.

String: text between double quotes, such as "SPY". A string has no escape characters and ends at the next double quote. Strings are arguments to functions, such as a symbol or a series name. A string in arithmetic gives E_TYPE.

1.5e-3 * 1000 => 1.5
.5 + .25 => 0.75
2E3 / 4 => 500

Names

Name: a letter or underscore, then letters, digits or underscores. Function and constant names ignore case, so SQRT(16) and sqrt(16) are the same call.

Constant: a name used without brackets. The language has two, pi and e. Calling a constant, as in pi(), gives E_TYPE.

Dotted name: names joined by dots, such as iv30.series or ou.kappa. A dotted name is one function name. iv30 returns the latest value and iv30.series returns the history.

Function call: a name, then arguments in brackets, separated by commas. now() takes no arguments. A call with the wrong number of arguments gives E_ARITY. For most functions the check happens before the formula runs; mat checks its element count when it runs.

SQRT(16) => 4
sqrt(2) => 1.414
2 * pi => 6.283
max(3, 9, 4) => 9
sin(30) => 0.5

sin(30) is 0.5 because angles are in degrees by default. The Calculator angle button switches trigonometry to radians. A Sheet always uses degrees.

Data function arguments

A data function, such as iv30 or fred.series, fetches a value for the arguments you type. Its arguments must be literals: a string in quotes or a number, with an optional leading minus. A cell reference, a name or a formula as an argument gives E_SLOT_ARGS. Symbols are converted to upper case, so iv30("spy") and iv30("SPY") fetch the same value. A symbol with no data on the site gives E_NO_DATA when the formula runs.

iv30("SPY") => a number that is the latest 30-day implied volatility
iv30("spy") => a number equal to the line above
iv30.series("SPY") => a Series of one point per trading day

Operators

Operators are listed from tightest binding to loosest. Every binary operator groups from the left.

Operator Meaning Precedence Associativity
f(...), ( ) function call, grouping 1 (tightest) none
x% percent: x divided by 100 2 postfix
-x, +x unary minus, unary plus 3 prefix
^ power 4 left
*, /, % multiply, divide, remainder 5 left
+, - add, subtract 6 left
==, !=, <, >, <=, >= comparison 7 (loosest) left 
2 + 3 * 4 => 14
(2 + 3) * 4 => 20
-2^2 => 4
2^3^2 => 64

-2^2 is (-2)^2, because unary minus binds tighter than ^. 2^3^2 is (2^3)^2. Unary plus changes nothing. The language has no implicit multiplication, so 2(3+4) gives E_SYNTAX. Write 2*(3+4).

Percent and remainder

% has two meanings. A % followed by a number, a string, a name or an opening bracket is the remainder. Any other % is a percent.

  • x% returns x divided by 100.
  • a + b% returns a plus b percent of a. a - b% returns a minus b percent of a.
  • a % b returns the remainder of a divided by b. The remainder takes the sign of a.
10% => 0.1
50 + 10% => 55
50 - 10% => 45
50 * 10% => 5
10% - 3 => -2.9
7 % 3 => 1
-7 % 3 => -1
10 % (-3) => 1

10 % -3 is 10% minus 3, because a minus sign does not start a term. Put a negative divisor in brackets: 10 % (-3).

Comparison and logic

A comparison returns 1 when it is true and 0 when it is false. The language has no true or false values. Comparisons bind loosest, so 1 + 2 == 3 compares 3 with 3.

A comparison with NaN on either side follows the IEEE rule. ==, <, >, <= and >= return 0, and != returns 1. For example, 0/0 == 1 returns 0.

1 + 2 == 3 => 1
3 > 2 => 1
3 > 2 > 1 => 0

3 > 2 > 1 is (3 > 2) > 1, which is 1 > 1. Combine conditions with the functions and, or and not. Choose a value with if(condition, then, else). if evaluates only the branch it returns: if(2 > 1, 10, 1/0) returns 10. Any number other than 0 counts as true.

and(3 > 2, 2 > 1) => 1
or(0, 0) => 0
not(0) => 1
if(2 > 1, 10, 20) => 10

Value kinds

A formula returns one of these kinds.

  • Number: a double-precision floating-point number. 1/0 returns infinity and 0/0 returns NaN. Neither is an error.
  • Series: a list of points, each a date and a value. A .series data function returns one point per trading day, oldest first. A value is empty on a day with no data.
  • Rolling window: what roll(series, n) returns. It is not a Series. Only mean, sum, median, std, zscore, min, max and pctile accept it, and the Sheet aliases average and stdevp. Each returns a Series of the full length, with the first n-1 points empty.
  • Matrix: a grid of numbers, at most 256 by 256; a larger matrix gives E_DIM. A result that would be a 1 by 1 matrix is returned as a number.
  • String: a quoted literal, used as a function argument.

Arithmetic between kinds

+, -, *, / and ^ work on numbers, series and matrices. The other operators take numbers only.

Left Right Result
number number number
number Series a Series with the same dates. The number applies to every point.
Series number a Series with the same dates. The number applies to every point.
Series Series a Series, when both have the same dates in the same order. Else E_DIM.
number Matrix a Matrix of the same size. The number applies to every element.
Matrix number a Matrix of the same size. The number applies to every element.
Matrix Matrix a Matrix, when both have the same rows and columns. Else E_DIM.
Series Matrix E_TYPE
rolling window anything E_TYPE. Aggregate the window first.

Matrix and Series arithmetic is elementwise. * multiplies matching elements and never computes a matrix product. Use mmul for the matrix product, described in Matrices. An empty point in either series gives an empty point in the result.

Unary minus works on a number or a Matrix. On a Series it gives E_TYPE, so write 0 - s or -1 * s.

iv30.series("SPY") * 100 => a Series of implied volatility in percent
iv30.series("SPY") - hv30.series("SPY") => a Series of the daily spread
-1 * iv30.series("SPY") => a Series
mat(2, 2, 1, 2, 3, 4) * 2 => a 2 by 2 Matrix
-mat(2, 2, 1, 2, 3, 4) => a 2 by 2 Matrix
mmul(mat(1, 2, 1, 2), mat(2, 1, 3, 4)) => 11

Two series with different dates

iv30.series and hv30.series come from the same daily history, so their dates match. A FRED series has different dates, so fred.series("SOFR") - iv30.series("SPY") gives E_DIM.

align(a, b) returns the points of a whose dates also appear in b. Cut both sides to the shared dates, then combine them:

align(fred.series("SOFR"), iv30.series("SPY")) - align(iv30.series("SPY"), fred.series("SOFR")) => a Series on the dates both have

When the two series share no date, align gives E_NO_DATA.

How results are displayed

The Calculator displays the result below the formula field.

  • Number: up to 10 significant digits, trailing zeros removed. A value of 100000 or more, or below 0.001, is shown with an exponent, such as 1.5e5 or 2.5e-4. Infinity is shown as ∞ and -∞, and NaN as NaN.
  • Series: the number of points, such as 1163 samples, and a line chart of the values below.
  • Matrix: a grid, one line per row, columns aligned.

The precision button switches numbers between automatic and two, four or six decimals. Rounding is half up on the decimal digits, so 1.005 at two decimals is shown as 1.01. The button changes the display and keeps the value. Calculator describes each button.

Errors

A formula that fails returns an error. The Calculator shows the code, the position and a message, such as E_SYNTAX @4: unexpected end of expression for 1 +. The position counts characters from 1. An error raised inside a function shows position 0, such as E_TYPE @0 for mean(5). The Sheet shows a short form in the cell, such as #SYNTAX?. Errors lists every code and its fix.

These codes come from reading and running a formula:

  • E_SYNTAX: the formula cannot be read. Causes are an unexpected character, an unclosed string or bracket, or an operator with no operand. Two values with no operator between them, an empty formula, or brackets nested more than 200 deep also give it.
  • E_UNKNOWN_NAME: a function or a name that does not exist.
  • E_ARITY: a call with too many or too few arguments.
  • E_TYPE: an argument or operand of the wrong kind. Examples are a string in arithmetic, a number where a function needs a series, and a constant called as a function.
  • E_SLOT_ARGS: a data function argument that is not a literal string or number.
  • E_NO_DATA: a data function with no data for its arguments, or an aggregate over a series whose values are all empty. iloc, at and vec give it on an empty point.
  • E_DIM: two series with different dates or two matrices of different sizes in one operation. An mmul with mismatched inner sizes also gives it, as do a non-square matrix where a square one is needed and a matrix above 256 by 256.
  • E_DOMAIN: an argument outside the range a function accepts, such as a window longer than the series. A plot with fewer than 2 or more than 2001 points also gives it.
  • E_INTERNAL: a fault in the formula engine itself.

The Sheet adds E_REF, E_CYCLE and E_SPILL, described in Cells, ranges and other sheets. The Calculator TVM solver adds E_NO_SOLUTION.

Calculator and Sheet

A Sheet formula starts with = and can refer to cells. Cells, ranges and other sheets describes the differences.

Function groups

  • Data functions: latest values and histories for a symbol or a FRED series.
  • Series: rolling windows, aggregates, z-scores, percentiles and date alignment.
  • Math: arithmetic functions, trigonometry, constants and plot.
  • Matrices: matrix building, products, inverses, eigenvalues, covariance and least-squares regression.
  • Mean reversion: Ornstein-Uhlenbeck fits, paths, first passage times and position sizing.
  • Time: the clock, durations and countdowns.
  • Cells, ranges and other sheets: cell references, ranges, names and the Sheet functions.

Function reference lists every function with its full signature.