Sheet
A workbook of cells for numbers, text and formulas: a history fills a column, a matrix fills a block, and a Calculator can read a named cell.
What it answers
Sheet lays out formulas in cells, so one result can feed the next and
every step is visible. Use Sheet for a calculation with several parts,
such as a history beside its average and today's z-score. You can also
use Sheet for a model with one row per observation. A cell accepts a
number, text, or a formula that starts with =. Sheet uses the Calculator formulas, listed in
the function reference.
How to read it
- 1The workbook name, such as
Book1. A Calculator on the same page uses this name to find the workbook. Double-click the name to rename the workbook. - 2The address of the selected cell, such as
A1. To name the cell, click the address, type the name and press Enter. Type an existing name to go to its cell. - 3The formula bar shows the content of the selected cell as typed. For a formula cell, it shows the formula, and the cell shows the value. Edit the content here or in the cell.
- 4The grid. In the example, A1 contains
=iv30.series("SPY"), so column A fills down with one row per trading day, oldest on top and latest at the bottom. B1 contains the 20-day average of column A, so column B fills beside it with its first 19 rows empty. C1 returns the latest value with=iloc(A1, -1). D1 contains the z-score over the last 252 days. - 5One tab per sheet. Click
+to add a sheet, double-click a tab to rename it, and click×to delete it.
Histories and matrices
A history fills down a column, and a matrix fills a block to the right and
down. The filled cells are tinted and have no content of their own. The
formula is in the top cell only. A formula that refers to the top cell,
such as =mean(roll(A1, 20)), uses the whole history.
A matrix has no room when cells in its block have content, or when the
block passes row 10,000 or column AZ. The top cell then shows the matrix
size, such as [4×4], and a formula that refers to the cell still uses
the whole matrix. Clear the cells in the block and the matrix fills it
again. For a block past the last row or column, move the formula up or
left.
Errors
A cell that cannot compute shows its error code. Hover the cell to display the full message. The error reference lists each error with its fix.
#N/A: the site has no data for a data function, a range has no numbers, orsheetorsheet.matrefers to an empty cell. In a formula, a reference to an empty cell counts as 0.#NAME?: a name in the formula is not a function or a named cell.#CYCLE!: the formula refers back to its own cell, directly or through other cells.#REF!: the formula refers to a cell, sheet or workbook that does not exist.#SPILL!: a history has no room, because a cell below it is not empty.#SYNTAX?: the formula cannot be parsed, for example because of a missing bracket.#ERROR: any other failure.
Limits and references
Each sheet has 10,000 rows and 52 columns, A to AZ. An address outside
them returns #REF!. An empty cell used on its own counts as 0. A range
such as A1:B10 is read as a series, and its empty or text cells stay in
place as gaps.
Sheet2!A1 refers to another sheet of the same workbook. The last sheet
of a workbook cannot be deleted. Cells, ranges and other
sheets describes what a deleted sheet does to the
formulas that refer to it.
Controls
Select a cell, then:
- Precision cycles
auto,.2,.4and.6decimals. - % shows the value times 100 with a percent sign.
- , adds thousands separators.
These three controls change how the selected cell displays its value. The value and the formula do not change.
- nm opens the name manager. It lists every named cell or range in the workbook.
- In the name manager, add, rename, repoint or delete a name.
- A name starts with a letter or an underscore.
- A name contains only letters, digits and underscores.
- A name cannot look like a cell address or match a function name.
- Clock switches between
liveandstep. - In
live, a cell that uses a time function,sheetorsheet.matrecalculates once a second. - In
step, the clock stops and the+1sand+1mbuttons appear. Click+1sor+1mto move the clock forward. - A
timercell counts down. Instep, the count starts when the clock stops. Inlive, it starts at the first run of the cell formula. - The reset button shows in both modes. Click it to start every
timercount again from the current clock.
Drag across cells, or Shift+click, to select a range. The footer shows
the count, sum and average of the range. Press Delete to clear the range.
Copy and paste work across cells. A pasted formula shifts its references
by the distance it moved. A $ keeps a reference fixed. Drag a column
border to resize the column.
Sheet saves the formulas, not their values. When the page reopens, every formula runs again on the latest data. When the site publishes new data for a symbol, every cell that uses that data loads it again.
A selection of more than 20,000 cells shows no count, sum or average. Text pasted from another program is entered as written, with no shift. Sheet has no undo, row or column insert, drag-fill, or Excel import and export.
| Control | What it does |
|---|---|
| fmt-precision | Precision, decimals shown in the selected cell |
| fmt-percent | Percent display for the selected cell |
| fmt-thousands | Thousands separators for the selected cell |
| namemgr | Name manager, the workbook's named cells |
| timemode | Clock, live or step |
Use it with
A Calculator on the same page can read this
workbook. sheet("Book1", "spot") reads the cell named spot.
sheet("Book1", "Sheet2!A1:B10") reads a range on another sheet.
sheet.mat reads a block and keeps its rows and columns. In the block, an
empty cell returns #N/A and a text cell returns an error. Neither
returns 0. A reference with no sheet name reads a named cell first, then
the first sheet. It never reads the sheet on screen. The Calculator reads
the cell again when you edit the formula. While the Calculator clock is
live, it also reads the cell once a second.
The guides Analyse one series and Build a mean reversion model build a sheet step by step.