Matrices and regression

Build and slice matrices, multiply, invert and solve them, decompose a covariance matrix, and fit a least-squares regression on data series.

What the group is for

Matrix functions build a grid of numbers, take it apart, and run linear algebra on it. The statistics functions turn several data series into a covariance or correlation matrix, and ols fits a least-squares regression of one series on others. Every function here works the same way in the Calculator and the Sheet.

A Matrix is a grid of 1 to 256 rows and 1 to 256 columns, stored row by row. Every cell is a number: a matrix has no empty cells. A result of 1 row and 1 column comes back as a plain number, so no function ever returns a 1 by 1 Matrix.

Build a matrix

The formula language has no bracket syntax for a matrix. Build one with a function.

mat(rows, cols, values, ...): a Matrix of the given size, filled row by row from the values. The count of values must equal rows times cols.

mat(2, 3, 1, 2, 3, 4, 5, 6) => a 2 by 3 Matrix
iloc(mat(2, 3, 1, 2, 3, 4, 5, 6), 1, 0) => 4

identity(n): an n by n Matrix with ones on the diagonal and zeros elsewhere.

zeros(rows, cols) and ones(rows, cols): a Matrix of the given size filled with zeros or with ones.

identity(3) => a 3 by 3 Matrix
zeros(2, 3) => a 2 by 3 Matrix
ones(4, 1) => a 4 by 1 Matrix

diag(m): from a vector (one row or one column), a square Matrix with the vector on its diagonal. From a square Matrix, its diagonal as a column.

diag(mat(3, 1, 4, 5, 6)) => a 3 by 3 Matrix
diag(mat(2, 2, 4, 1, 1, 9)) => a 2 by 1 Matrix

vec(series): the values of a Series as a column, one row per point, dates dropped. A Series with an empty point returns E_NO_DATA; the point is never replaced with 0.

vec(plot(x^2, x, 1, 3, 3)) => a 3 by 1 Matrix

sheet.mat(book, ref): the cells of a Sheet range as a Matrix, with the range's rows and columns. Every cell in the range must contain a number. An empty cell returns E_NO_DATA, a text cell E_TYPE, and a cell showing an error E_REF. An unknown book or tab also returns E_REF. The Sheet page describes books and ranges.

Pick parts of a matrix

Positions start at 0. A negative position counts from the end: -1 is the last row or column.

row(m, i): row i of m, as a Matrix of 1 row.

col(m, j): column j of m, as a Matrix of 1 column.

iloc(m, i, j): the number in row i, column j. The matrix form needs both positions. With a Series and one position, iloc returns one point of the Series, as described in Series.

row(mat(2, 2, 1, 2, 3, 4), 1) => a 1 by 2 Matrix
col(mat(2, 2, 1, 2, 3, 4), -1) => a 2 by 1 Matrix
iloc(mat(2, 2, 1, 2, 3, 4), 0, 0) => 1
iloc(mat(2, 2, 1, 2, 3, 4), -1, -1) => 4
row(mat(2, 1, 5, 6), 1) => 6

The last line returns a number: row 1 of a one-column matrix has 1 row and 1 column.

Matrix algebra

Operators

The operators +, -, *, / and ^ work cell by cell. Between two matrices, both must have the same rows and columns. Between a matrix and a number, the number applies to every cell. A leading minus negates every cell. Comparisons and % do not take a matrix.

mat(2, 2, 1, 2, 3, 4) * mat(2, 2, 1, 2, 3, 4) => a 2 by 2 Matrix
iloc(mat(2, 2, 1, 2, 3, 4) * mat(2, 2, 1, 2, 3, 4), 1, 1) => 16
iloc(mat(2, 2, 1, 2, 3, 4) ^ 2 + 1, 0, 1) => 5

Products and transposes

mmul(a, b): the matrix product of a and b. b must have as many rows as a has columns. The result has the rows of a and the columns of b.

transpose(m): m with rows and columns swapped.

mmul(mat(2, 3, 1, 2, 3, 4, 5, 6), mat(3, 2, 1, 0, 0, 1, 1, 1)) => a 2 by 2 Matrix
transpose(mat(2, 3, 1, 2, 3, 4, 5, 6)) => a 3 by 2 Matrix
mmul(transpose(mat(3, 1, 1, 2, 3)), mat(3, 1, 1, 2, 3)) => 14

The last line is a dot product: a 1 by 3 row times a 3 by 1 column is a single number.

Inverse, solve and determinant

These functions take a square matrix.

minv(m): the inverse of m.

solve(A, b): the x in A x = b. A is square and b has as many rows as A. b can have several columns; x has one column per column of b. solve(A, b) works from the LU factors of A and does not form the inverse.

det(m): the determinant of m, a number. A singular matrix returns 0.

trace(m): the sum of the diagonal of m, a number.

iloc(minv(mat(2, 2, 4, 7, 2, 6)), 0, 0) => 0.6
solve(mat(2, 2, 2, 1, 1, 3), mat(2, 1, 3, 5)) => a 2 by 1 Matrix
iloc(solve(mat(2, 2, 2, 1, 1, 3), mat(2, 1, 3, 5)), 1, 0) => 1.4
det(mat(2, 2, 4, 7, 2, 6)) => 10
det(mat(2, 2, 1, 2, 2, 4)) => 0
trace(mat(2, 2, 1, 2, 3, 4)) => 5

minv, solve, det and ols treat a matrix as singular when elimination finds a pivot of at most 0.000000000001 times the largest absolute value in the matrix.

norm(m): the square root of the sum of the squares of every cell, a number. For a vector this is its length.

norm(mat(2, 1, 3, 4)) => 5

Eigen decomposition

Both functions take a symmetric matrix, such as a covariance or correlation matrix.

eigvals(m): the eigenvalues, as a column from largest to smallest.

eigvecs(m): the eigenvectors, each of length 1, one per column, in the order of eigvals. In each column, the first entry with the largest absolute value is positive, so the signs are the same on every run.

eigvals(mat(2, 2, 2, 1, 1, 2)) => a 2 by 1 Matrix
iloc(eigvals(mat(2, 2, 2, 1, 1, 2)), 0, 0) => 3
eigvecs(mat(2, 2, 2, 1, 1, 2)) => a 2 by 2 Matrix
iloc(eigvecs(mat(2, 2, 2, 1, 1, 2)), 1, 1) => -0.707

The share of total variance in the first component is the largest eigenvalue over the trace. For the implied volatility of three funds:

iloc(eigvals(cov(iv30.series("SPY"), iv30.series("QQQ"), iv30.series("IWM"))), 0, 0) / trace(cov(iv30.series("SPY"), iv30.series("QQQ"), iv30.series("IWM"))) => a number from 0 to 1

Statistics of several series

cov and corr take two or more Series, or one Matrix whose columns are the variables. With Series, a date counts only when every series has a value on it. The other dates are left out for all of the series.

cov(a, b, ...): the covariance matrix, one row and one column per series. The diagonal is each variance. The divisor is N, the number of dates used.

corr(a, b, ...): with two Series, the correlation as one number from -1 to 1. With three or more Series, or with one Matrix of two or more columns, the correlation matrix, with exactly 1 on the diagonal.

cov(iv30.series("SPY"), iv30.series("QQQ")) => a 2 by 2 Matrix
corr(iv30.series("SPY"), iv30.series("QQQ")) => a number from -1 to 1
corr(iv30.series("SPY"), iv30.series("QQQ"), iv30.series("IWM")) => a 3 by 3 Matrix
iloc(cov(mat(3, 2, 1, 2, 2, 4, 3, 6)), 0, 0) => 0.667
corr(mat(3, 2, 1, 2, 2, 4, 3, 6)) => a 2 by 2 Matrix

The variance of 1, 2 and 3 with divisor N is 2/3, shown as 0.667.

Regression

ols(y, x, ...): the coefficients of a least-squares fit of y on one or more x, as a column. y comes first. Every fit includes an intercept: do not add a column of ones. Row 0 is the intercept. Row 1 is the slope on the first x, row 2 the slope on the second x, and so on in argument order. With k regressors the result is a k + 1 by 1 Matrix.

ols.r2(y, x, ...): the R squared of the same fit, a number from 0 to

  1. It is the share of the variance of y that the fit explains.

ols.se(y, x, ...): the residual standard error of the same fit, in the units of y. The divisor is n - k - 1, where n is the number of observations and k the number of regressors.

All arguments are Series, or all are vectors of one row or one column. Series are matched on the dates where every one of them has a value. Vectors must have the same length and are matched by position. The fit needs more than k + 1 observations.

ols(mat(4, 1, 3, 5, 7, 9), mat(4, 1, 1, 2, 3, 4)) => a 2 by 1 Matrix
iloc(ols(mat(4, 1, 3, 5, 7, 9), mat(4, 1, 1, 2, 3, 4)), 0, 0) => 1
iloc(ols(mat(4, 1, 3, 5, 7, 9), mat(4, 1, 1, 2, 3, 4)), 1, 0) => 2
ols.r2(mat(4, 1, 3, 5, 7, 9), mat(4, 1, 1, 2, 3, 4)) => 1
ols(iv30.series("IWM"), iv30.series("SPY"), iv30.series("QQQ")) => a 3 by 1 Matrix

The data fits y = 1 + 2x exactly: intercept 1, slope 2, R squared 1. The last line regresses IWM on SPY and QQQ: row 1 is the slope on SPY and row 2 the slope on QQQ.

How a matrix is displayed

In the Calculator, a Matrix result is shown as a grid, one line per row, with the columns right-aligned. The precision button formats every cell the same way as a single number.

In the Sheet, a Matrix fills a block down and to the right of the cell that contains the formula. When the block has no room, the cell shows the size, such as [3×3], and keeps the Matrix. A formula that refers to that cell uses the whole Matrix in both cases. Sheet cells describes how results fill cells.

Cell Formula Shows
A1 =cov(iv30.series("SPY"), iv30.series("QQQ")) the 2 by 2 covariance matrix in A1:B2
D1 =det(A1) the determinant of the covariance matrix
D2 =B1 the covariance of SPY and QQQ, the cell in row 0, column 1

Combine with other groups

The hedge ratio of QQQ implied volatility on SPY implied volatility is the slope of ols with QQQ as y and SPY as x. It is the number of points QQQ implied volatility moves per point of SPY implied volatility. The data functions come from Data functions.

  1. Click Add widget and select Calculator.

  2. Type the fit and check that it returns two rows.

    ols(iv30.series("QQQ"), iv30.series("SPY")) => a 2 by 1 Matrix

  3. Wrap the fit in iloc to show the slope alone.

    iloc(ols(iv30.series("QQQ"), iv30.series("SPY")), 1, 0) => a number of QQQ points per SPY point

  4. Replace the formula with the R squared and the residual standard error to judge the fit.

    ols.r2(iv30.series("QQQ"), iv30.series("SPY")) => a number from 0 to 1
ols.se(iv30.series("QQQ"), iv30.series("SPY")) => a number in implied volatility units

With one regressor, the slope equals the covariance of the two series over the variance of SPY, both computed on the same dates:

iloc(cov(iv30.series("QQQ"), iv30.series("SPY")), 0, 1) / iloc(cov(iv30.series("QQQ"), iv30.series("SPY")), 1, 1) => a number equal to the ols slope

Compare two or more series aligns the same histories and charts their spread. Build a mean reversion model fits a model to one spread in a Sheet. Function reference lists every signature.

Errors

The Calculator shows the code and a message on the result line. The Sheet shows #N/A for E_NO_DATA, #REF! for E_REF, and #ERROR for every other code below. Errors lists every code.

  • E_DIM: the sizes do not fit. Causes: two matrices of different sizes under a cell by cell operator, or mmul with inner sizes that differ. Also solve with b of the wrong height, or ols on vectors of different lengths. Also a square-only function on a matrix that is not square, or a size that is not a whole number from 1 to 256.
  • E_DOMAIN: the input is outside the range the function accepts. Causes: minv or solve on a singular matrix, eigvals or eigvecs on a matrix that is not symmetric, or a position outside the matrix or not a whole number. For the statistics: corr on a series of zero variance, ols with too few observations or with regressors that repeat each other, and ols.r2 with a constant y.
  • E_TYPE: an argument is the wrong kind. Causes: a number where a function needs a matrix, or a Series and a Matrix in one operator. Also an ols argument list that mixes Series with vectors, or that includes a matrix of more than one row and column.
  • E_ARITY: mat got a count of values other than rows times cols, iloc on a matrix got one position, or cov, corr got one Series.
  • E_NO_DATA: vec met an empty point, or cov or corr found fewer than 2 dates that every series has, or got a Matrix of one row. Also sheet.mat on an empty cell.
  • E_REF: sheet.mat met a cell showing an error, or an unknown book or tab.