Series

Work with dated histories in formulas: arithmetic by date, align, at, rolling windows, and the summaries mean, std, zscore and pctile.

The Series group works on histories: one value per date. It lines up two histories on their shared dates and picks the value on one date. It also summarises a history in one number and computes a rolling statistic on every date.

What a Series is

A Series is a list of points. Each point has a date and a value. Every function that ends in .series returns one, with one point per trading day, oldest first. The dates are text in the form YYYY-MM-DD.

iv30.series("SPY") => a Series of daily values
hv30.series("QQQ") => a Series of daily values

A point is empty when plot gets no finite value, at the head of a rolling result, or where an operator meets an empty point. An empty point keeps its place, so every Series keeps one point per date.

plot also returns a Series, with numbers in place of dates. This page uses it to show exact results on small inputs: plot(x, x, 1, 10, 10) is the Series 1, 2, ..., 10. A point where the expression has no finite value is empty, so plot(sqrt(x - 3), x, 1, 10, 10) has empty first and second points. See Time and plots for plot.

plot(x, x, 1, 10, 10) => a Series of 10 points

A Series in the Calculator

The Calculator draws a Series as a line chart, and the Sheet spills it down a column, oldest date at the top. In the chart, dated points are spaced evenly, one per date, with the dates as axis labels. plot points go on a number axis. The precision button changes number and Matrix results. See Calculator and Sheet.

Arithmetic on a Series

The operators +, -, *, / and ^ work on a Series point by point, and the result is a Series.

Series and a number: the number applies to every point. The result has the same dates as the Series.

iv30.series("SPY") * 100 => a Series of daily values in percent
sum(plot(x, x, 1, 10, 10) * 2) => 110

Series and Series: both must have the same dates, in the same order. The result has those dates. When the dates differ in any point, the formula stops with E_DIM. Operators never line up dates; use align first (see Line up two histories). Two data series of one symbol from the same data source share their dates, so iv30.series and hv30.series of SPY combine directly.

iv30.series("SPY") - hv30.series("SPY") => a Series of daily differences
sum(plot(x, x, 1, 10, 10) + plot(x, x, 1, 10, 10)) => 110

Empty points: when either side is empty on a date, the result is empty on that date. A division by zero gives an infinite point. Summarise a history lists which summaries skip it.

Other operators stop with E_TYPE on a Series: %, the comparisons, and the minus sign in front of a value. To negate a Series, subtract it from 0: 0 - iv30.series("SPY"), or multiply it by -1. An operator also stops with E_TYPE on roll(...): apply a summary to the window first.

Line up two histories

align(a, b, ...): returns Series a cut down to the dates that every other argument also has. Values come from a only, and empty points of a stay in place. Returns a Series; stops with E_NO_DATA when the series share no dates.

To combine two histories with an operator, cut each one to the other's dates. The two sides then have the same dates in the same order.

align(iv30.series("QQQ"), iv30.series("IWM")) - align(iv30.series("IWM"), iv30.series("QQQ")) => a Series of daily spreads
sum(align(plot(x, x, 1, 10, 10), plot(x, x, 1, 10, 4))) => 22

In the second line, plot(x, x, 1, 10, 4) has the points 1, 4, 7 and 10, so align keeps those four points of the first Series.

align with three or more arguments keeps the dates that all of them share. Compare two or more series works through a spread step by step.

Pick a value

at(series, date): returns the latest non-empty value dated on or before date, a number. date is text, "YYYY-MM-DD". On a weekend or holiday it returns the previous trading day's value. It never uses a point after date. Stops with E_NO_DATA when no point qualifies.

at(iv30.series("SPY"), "2026-08-01") => a number

To pick a point by position, such as the latest, use iloc(series, n) from the Matrices group: iloc(s, -1) is the latest point.

Summarise a history

Each summary below takes a plain Series and returns one number. Empty points and infinite points are skipped. When every point is empty, the summary stops with E_NO_DATA. Given roll(...) in place of a Series, each one returns a rolling Series instead (see Roll a window).

mean(series): the average.

median(series): the middle value; with an even count, the average of the two middle values.

min(series) and max(series): the smallest and largest value. With two or more numbers, min(a, b, ...) and max(a, b, ...) compare the numbers. A Series cannot be mixed with numbers in one call.

sum(series): the total.

std(series, n): the standard deviation, dividing by the number of points N (population standard deviation). n is optional: with it, only the last n points count.

mean(plot(x, x, 1, 10, 10)) => 5.5
median(plot(x, x, 1, 10, 10)) => 5.5
min(plot(x, x, 1, 10, 10)) => 1
max(plot(x, x, 1, 10, 10)) => 10
sum(plot(x, x, 1, 10, 10)) => 55
std(plot(x, x, 1, 10, 10)) => 2.872
std(plot(x, x, 1, 10, 10), 3) => 0.816
mean(plot(sqrt(x - 3), x, 1, 10, 10)) => 1.685
max(iv30.series("SPY")) => a number
std(hv30.series("IWM"), 20) => a number

mean(plot(sqrt(x - 3), x, 1, 10, 10)) averages the eight non-empty points and skips the two empty ones.

Two summaries rank the latest point against its history:

zscore(series, n): the latest point minus the mean, divided by the standard deviation (divisor N). n is optional: with it, the mean and standard deviation use the last n points, the latest included.

pctile(series, n): the percentage of the last n points at or below the latest point, from 0 to 100. Only points with a finite value count. The latest point counts itself, so with 252 finite points the lowest rank is 100 / 252. n is required with a plain Series.

zscore(plot(x, x, 1, 10, 10)) => 1.567
zscore(plot(x, x, 1, 10, 10), 3) => 1.225
pctile(plot(x, x, 1, 10, 10), 4) => 100
pctile(plot(11 - x, x, 1, 10, 10), 4) => 25
zscore(iv30.series("IWM"), 252) => a number
pctile(iv30.series("QQQ"), 252) => a number

Both stop with E_NO_DATA when the latest point is empty or infinite. zscore stops with E_DOMAIN when every point in the window has the same value. The window n must be a whole number from 1 to the length of the Series, or the formula stops with E_DOMAIN.

Roll a window

roll(series, n): a trailing window of n points over a plain Series. roll returns a rolling window over the input. Pass it to mean, median, min, max, sum, std, zscore or pctile. Each one then returns a Series with one result per date.

A roll(...) alone is not a result:

  • The Calculator shows the message roll(...) is a window: wrap it in mean, std, median, sum, zscore, pctile, min or max.
  • A Sheet cell shows the window length, such as [roll 20], and does not spill. When A1 contains =roll(B1, 20), =mean(A1) returns the rolling mean.

The rules for every rolling result:

  • Window: on each date, the window is the n points that end at that date, the point on that date included.
  • First points: the first n - 1 points are empty, so the result has the same dates as the input.
  • Empty points: a window skips its empty points and summarises the rest. A window with no values gives an empty point.
  • zscore and pctile: each date ranks its own point within its window. When that point is empty, the result is empty there. When a window has zero variance, zscore gives an empty point there.
mean(roll(hv30.series("QQQ"), 20)) => a Series of 20-day averages
zscore(roll(iv30.series("SPY"), 252)) => a Series of daily z-scores
pctile(roll(iv30.series("IWM"), 252)) => a Series of daily ranks
iloc(mean(roll(plot(x, x, 1, 10, 10), 3)), 2) => 2
iloc(mean(roll(plot(x, x, 1, 10, 10), 3)), -1) => 9
iloc(mean(roll(plot(sqrt(x - 3), x, 1, 10, 10), 3)), 2) => 0

The fourth line returns the third point, the first one with a full window: the mean of 1, 2 and 3. In the last line the third window contains two empty points and 0, so its mean is 0.

pctile takes a single argument over roll(...); the window length comes from roll. std and zscore also take one argument over roll(...).

n must be a whole number from 1 to the length of the Series. roll accepts a plain Series only: roll(roll(...), n) stops with E_TYPE.

Analyse one series charts a 20-day average and ranks today's value step by step.

Combine with other groups

Spread z-score of two data series: cut both histories with align, subtract, then rank the latest spread against the last 252 points, or on every date with roll.

zscore(align(iv30.series("QQQ"), iv30.series("IWM")) - align(iv30.series("IWM"), iv30.series("QQQ")), 252) => a number
zscore(roll(align(iv30.series("QQQ"), iv30.series("IWM")) - align(iv30.series("IWM"), iv30.series("QQQ")), 60)) => a Series of daily z-scores

Implied minus realised volatility, ranked: the two data series of one symbol share their dates, so no align is needed.

pctile(iv30.series("SPY") - hv30.series("SPY"), 252) => a number

Correlation: corr(a, b) from the Matrices group returns the correlation of two Series over the dates both have. roll does not support a rolling correlation: corr accepts plain Series only and stops with E_TYPE on roll(...).

corr(iv30.series("SPY"), hv30.series("SPY")) => a number from -1 to 1

For full signatures, see the Function reference.

Errors

  • E_DIM: two Series in one operator have different dates. Wrap each side in align with the other as its second argument.
  • E_TYPE: a Series met an operator other than + - * / ^, a roll(...) met an operator or corr, or a function that needs a Series got a number. pctile with one argument stops here unless that argument is roll(...).
  • E_DOMAIN: a window is not a whole number from 1 to the length of the Series, or zscore met a window with zero variance.
  • E_NO_DATA: every point is empty, the latest point is empty for zscore or pctile, align found no shared dates, or at found no point on or before the date.

The meaning and the fix for each code are in Errors.