Skip to content

formula

The Excel formula language used by EXCEL and HYBRID modes. Nothing on this path calls eval(): the source is tokenized, parsed into an AST, and walked by an evaluator that implements Excel's coercion and error-propagation rules.

Parser

parser

ParseError

Bases: FormulaError

parse 

parse(text: str) -> Node
Source code in src/gridcalc/formula/parser.py 
266
267
268
def parse(text: str) -> Node:
    tokens = tokenize(text)
    return _Parser(tokens).parse()

Lexer

lexer

Token dataclass 

Token(kind: str, value: Any, pos: int)

tokenize 

tokenize(text: str) -> list[Token]
Source code in src/gridcalc/formula/lexer.py 
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
def tokenize(text: str) -> list[Token]:
    s = text.lstrip()
    if s.startswith("="):
        s = s[1:]
    offset = len(text) - len(s)
    tokens: list[Token] = []
    i = 0
    n = len(s)
    while i < n:
        ch = s[i]
        if ch.isspace():
            i += 1
            continue
        pos = i + offset
        # error literal
        m = _ERROR_LIT_RE.match(s, i)
        if m:
            err = parse_error_literal(m.group(0))
            if err is None:
                raise FormulaError(f"unknown error literal {m.group(0)!r} at {pos}")
            tokens.append(Token(ERROR_LIT, err, pos))
            i = m.end()
            continue
        # cellref (must come before IDENT and NUMBER; handles $A$1 etc.)
        if ch == "$" or ch.isalpha():
            cr = _parse_cellref(s[i:])
            if cr is not None:
                end, col, row, ac, ar = cr
                # Validate it's not followed by an alpha/digit that would extend it
                # (e.g., A1B should NOT be a cellref). Already guaranteed because we
                # match greedy letters then digits; what follows must not be alnum
                # for the cellref to be standalone. Also: a cellref-shaped token
                # followed by `!` is actually a sheet name (e.g. `Sheet1!A1`),
                # so emit an IDENT instead and let the parser handle the prefix.
                next_idx = i + end
                if next_idx < n and (s[next_idx].isalnum() or s[next_idx] == "_"):
                    cr = None  # fall through to IDENT
                elif next_idx < n and s[next_idx] == "!":
                    cr = None  # sheet prefix; let IDENT branch consume it
                else:
                    tokens.append(Token(CELLREF, (col, row, ac, ar), pos))
                    i += end
                    continue
        # identifier (function name, named range, bool, py keyword)
        if ch.isalpha() or ch == "_":
            m2 = _IDENT_RE.match(s, i)
            if m2 is None:
                raise FormulaError(f"unexpected character {ch!r} at {pos}")
            ident = m2.group(0)
            up = ident.upper()
            if up == "TRUE":
                tokens.append(Token(BOOL, True, pos))
            elif up == "FALSE":
                tokens.append(Token(BOOL, False, pos))
            else:
                tokens.append(Token(IDENT, ident, pos))
            i = m2.end()
            continue
        # number
        if ch.isdigit() or (ch == "." and i + 1 < n and s[i + 1].isdigit()):
            m3 = _NUMBER_RE.match(s, i)
            if m3 is None:
                raise FormulaError(f"invalid number at {pos}")
            tokens.append(Token(NUMBER, float(m3.group(0)), pos))
            i = m3.end()
            continue
        # string "..." with "" escape
        if ch == '"':
            j = i + 1
            buf: list[str] = []
            while j < n:
                if s[j] == '"':
                    if j + 1 < n and s[j + 1] == '"':
                        buf.append('"')
                        j += 2
                        continue
                    break
                buf.append(s[j])
                j += 1
            else:
                raise FormulaError(f"unterminated string at {pos}")
            tokens.append(Token(STRING, "".join(buf), pos))
            i = j + 1
            continue
        # 'Sheet Name'!A1 -- a quoted sheet name, with '' for a literal
        # apostrophe. Emitted as IDENT so the parser's existing
        # `IDENT BANG CELLREF` path handles it: quoting is a lexical device for
        # names that are not identifiers (spaces, punctuation, a leading
        # digit), not a different kind of reference. A single quote means
        # nothing else in this grammar -- Excel strings use double quotes --
        # so there is no ambiguity to resolve here.
        if ch == "'":
            j = i + 1
            name: list[str] = []
            while j < n:
                if s[j] == "'":
                    if j + 1 < n and s[j + 1] == "'":
                        name.append("'")
                        j += 2
                        continue
                    break
                name.append(s[j])
                j += 1
            else:
                raise FormulaError(f"unterminated sheet name at {pos}")
            tokens.append(Token(IDENT, "".join(name), pos))
            i = j + 1
            continue
        # multi-char operators
        if ch == "<" and i + 1 < n and s[i + 1] == "=":
            tokens.append(Token(LE, "<=", pos))
            i += 2
            continue
        if ch == ">" and i + 1 < n and s[i + 1] == "=":
            tokens.append(Token(GE, ">=", pos))
            i += 2
            continue
        if ch == "<" and i + 1 < n and s[i + 1] == ">":
            tokens.append(Token(NE, "<>", pos))
            i += 2
            continue
        # single-char
        single = {
            "(": LPAREN,
            ")": RPAREN,
            ",": COMMA,
            ":": COLON,
            ".": DOT,
            "+": PLUS,
            "-": MINUS,
            "*": STAR,
            "/": SLASH,
            "^": CARET,
            "&": AMP,
            "%": PERCENT,
            "=": EQ,
            "<": LT,
            ">": GT,
            "!": BANG,
            "#": HASH,  # spill-range operator (A1#); error literals matched above
        }
        kind = single.get(ch)
        if kind is not None:
            tokens.append(Token(kind, ch, pos))
            i += 1
            continue
        raise FormulaError(f"unexpected character {ch!r} at {pos}")

    tokens.append(Token(EOF, None, len(text)))
    return tokens

Evaluator

evaluator

Env 

Env(
    cell_value: Callable[..., object],
    builtins: dict[str, Callable[..., Any]],
    named_ranges: dict[str, Node] | None = None,
    py_registry: dict[str, Callable[..., Any]]
    | None = None,
    cell_is_formula: Callable[..., bool] | None = None,
    cell_spill_value: Callable[..., object] | None = None,
    cell_formula_text: Callable[..., object] | None = None,
)
Source code in src/gridcalc/formula/evaluator.py 
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
def __init__(
    self,
    cell_value: Callable[..., object],
    builtins: dict[str, Callable[..., Any]],
    named_ranges: dict[str, Node] | None = None,
    py_registry: dict[str, Callable[..., Any]] | None = None,
    cell_is_formula: Callable[..., bool] | None = None,
    cell_spill_value: Callable[..., object] | None = None,
    cell_formula_text: Callable[..., object] | None = None,
) -> None:
    # `cell_value` is `(c, r, sheet=None) -> object`; `cell_is_formula`
    # is `(c, r, sheet=None) -> bool`. The single-sheet case keeps
    # `sheet=None` so existing callers that pass two-arg lambdas
    # still work.
    self.cell_value = cell_value
    # `cell_spill_value(c, r, sheet=None)` returns the whole spilled
    # array anchored at (c, r) as a Vec (or the scalar for a
    # non-array cell). Backs the `A1#` operator. Defaults to
    # `cell_value` so an Env built without spill support degrades to
    # reading the anchor's own value.
    self.cell_spill_value = cell_spill_value or cell_value
    self._builtins = {k.lower(): v for k, v in builtins.items()}
    self._named = {k.lower(): v for k, v in (named_ranges or {}).items()}
    self.py_registry = py_registry or {}
    self.cell_is_formula = cell_is_formula or (lambda _c, _r, _s=None: False)
    # `cell_formula_text(c, r, sheet=None)` returns the formula text
    # (leading '=' included) of a formula cell, else None. Backs
    # FORMULATEXT.
    self.cell_formula_text = cell_formula_text or (lambda _c, _r, _s=None: None)
    # `refs_used` keys are `(sheet, c, r)`; sheet is None for refs
    # that resolve against the formula's home sheet.
    self.refs_used: set[tuple[str | None, int, int]] = set()
    # Set by recalc before evaluating each formula. Functions in
    # `RAW_ARG_FUNCS` (e.g. ROW(), COLUMN()) consult this when called
    # with no arguments.
    self.current_cell: tuple[int, int] | None = None
    # Per-recalc cache for materialised range Vecs. Key is
    # `(sheet, c1, r1, c2, r2)`. Cleared at the start of each recalc
    # pass -- downstream consumers re-evaluate when sources change,
    # so cache liveness is bounded by the closure pass.
    self._range_cache: dict[tuple[str | None, int, int, int, int], Any] = {}
    # Lexical scope stack for LET bindings. Each frame maps a
    # lowercased local name to an already-evaluated value. Searched
    # top-down so inner LETs shadow outer ones and named ranges.
    self._local_scopes: list[dict[str, Any]] = []
push_scope 
push_scope() -> dict[str, Any]

Push a fresh local scope and return it so a caller can add bindings incrementally. Must be paired with pop_scope.

Source code in src/gridcalc/formula/evaluator.py 
 97
 98
 99
100
101
102
def push_scope(self) -> dict[str, Any]:
    """Push a fresh local scope and return it so a caller can add
    bindings incrementally. Must be paired with ``pop_scope``."""
    scope: dict[str, Any] = {}
    self._local_scopes.append(scope)
    return scope
lookup_local 
lookup_local(name: str) -> tuple[bool, Any]

Resolve a LET-bound local. Returns (found, value) so a legitimately-bound None is distinguishable from a miss.

Source code in src/gridcalc/formula/evaluator.py 
107
108
109
110
111
112
113
114
def lookup_local(self, name: str) -> tuple[bool, Any]:
    """Resolve a LET-bound local. Returns ``(found, value)`` so a
    legitimately-bound ``None`` is distinguishable from a miss."""
    key = name.lower()
    for scope in reversed(self._local_scopes):
        if key in scope:
            return True, scope[key]
    return False, None
eval_node 
eval_node(node: Node) -> Any

Evaluate a sub-expression AST node in this environment. Used by reference-aware functions to compute their non-reference arguments.

Source code in src/gridcalc/formula/evaluator.py 
130
131
132
133
def eval_node(self, node: Node) -> Any:
    """Evaluate a sub-expression AST node in this environment. Used by
    reference-aware functions to compute their non-reference arguments."""
    return _eval(node, self)
resolve_ref 
resolve_ref(node: Node) -> Reference | None

Resolve an AST node to a Reference, or None if it is not a reference. Cell/range refs resolve statically; a call (e.g. a nested OFFSET) is evaluated and accepted only if it yields a Reference.

Source code in src/gridcalc/formula/evaluator.py 
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
def resolve_ref(self, node: Node) -> Reference | None:
    """Resolve an AST node to a Reference, or None if it is not a
    reference. Cell/range refs resolve statically; a call (e.g. a
    nested OFFSET) is evaluated and accepted only if it yields a
    Reference."""
    if isinstance(node, CellRef):
        return Reference(node.col, node.row, node.col, node.row, node.sheet)
    if isinstance(node, RangeRef):
        s, e = node.start, node.end
        return Reference(
            min(s.col, e.col),
            min(s.row, e.row),
            max(s.col, e.col),
            max(s.row, e.row),
            s.sheet,
        )
    if isinstance(node, Name):
        target = self.lookup_name(node.name)
        return self.resolve_ref(target) if target is not None else None
    if isinstance(node, (Call, Apply)):
        v = _eval(node, self)
        return v if isinstance(v, Reference) else None
    return None

Reference dataclass 

Reference(
    c1: int,
    r1: int,
    c2: int,
    r2: int,
    sheet: str | None = None,
)

A location (single cell or rectangular range), distinct from the value(s) it points at. Produced by OFFSET and consumed by the reference-aware functions (ROW/COLUMN/ROWS/COLUMNS/ FORMULATEXT/AREAS). Anywhere a plain value is expected -- a normal function argument, an arithmetic operand, a formula's result -- a Reference materialises to a scalar (1x1) or a Vec, via _deref. Coordinates are 0-based inclusive.

LambdaValue 

LambdaValue(
    params: tuple[str, ...],
    body: Node,
    env: Env,
    captured: list[dict[str, Any]],
)

A first-class function produced by LAMBDA(param..., body).

Closes over the local scopes in effect where it was defined (a shallow snapshot, since scopes are mutated and popped as evaluation proceeds). Calling it swaps that captured scope stack in for the duration of the body, then restores the caller's -- so lexical scoping and re-entrancy both hold. refs_used and the range cache stay on the shared Env so cell reads inside a lambda body are still tracked as dependencies.

Source code in src/gridcalc/formula/evaluator.py 
174
175
176
177
178
179
180
def __init__(
    self, params: tuple[str, ...], body: Node, env: Env, captured: list[dict[str, Any]]
) -> None:
    self.params = params
    self.body = body
    self.env = env
    self.captured = captured

Dependencies

Static reference extraction, which is what makes topological recalculation possible.

deps

Static dependency extraction over the formula AST.

Used by Grid to maintain forward/reverse dependency indexes for topological recalc. Pure-AST analysis: no evaluation.

extract_refs 

extract_refs(
    node: Node,
    named_ranges: dict[str, Node] | None = None,
    formula_sheet: str | None = None,
) -> set[tuple[str | None, int, int]]

Return the set of (sheet, col, row) cells that node reads.

Range references expand to the full rectangular set. Named ranges are resolved through named_ranges; unknown names are ignored.

Sheet identity per ref
  • if the ref carries an explicit sheet (Sheet2!A1), use it;
  • otherwise the ref resolves against formula_sheet (the sheet containing the formula). When formula_sheet is None, the returned key is (None, c, r) -- correct for the single-sheet case before phase 1's Sheet class lands and sufficient for any caller that doesn't differentiate sheets.

Does not detect dynamic-ref functions; use has_dynamic_refs.

Source code in src/gridcalc/formula/deps.py 
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
def extract_refs(
    node: Node,
    named_ranges: dict[str, Node] | None = None,
    formula_sheet: str | None = None,
) -> set[tuple[str | None, int, int]]:
    """Return the set of (sheet, col, row) cells that `node` reads.

    Range references expand to the full rectangular set. Named ranges
    are resolved through `named_ranges`; unknown names are ignored.

    Sheet identity per ref:
      - if the ref carries an explicit sheet (``Sheet2!A1``), use it;
      - otherwise the ref resolves against ``formula_sheet`` (the
        sheet containing the formula). When ``formula_sheet`` is None,
        the returned key is ``(None, c, r)`` -- correct for the
        single-sheet case before phase 1's Sheet class lands and
        sufficient for any caller that doesn't differentiate sheets.

    Does not detect dynamic-ref functions; use ``has_dynamic_refs``.
    """
    out: set[tuple[str | None, int, int]] = set()
    _walk(node, named_ranges or {}, out, formula_sheet)
    return out

has_dynamic_refs 

has_dynamic_refs(node: Node) -> bool

True if node contains a call whose read set depends on a value.

Cells matching this need always-recompute treatment in topo recalc.

Source code in src/gridcalc/formula/deps.py 
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
def has_dynamic_refs(node: Node) -> bool:
    """True if `node` contains a call whose read set depends on a value.

    Cells matching this need always-recompute treatment in topo recalc.
    """
    if isinstance(node, Call):
        up = node.name.upper()
        if up in DYNAMIC_REF_FUNCS or up in VOLATILE_FUNCS:
            return True
        return any(has_dynamic_refs(a) for a in node.args)
    if isinstance(node, Apply):
        return has_dynamic_refs(node.func) or any(has_dynamic_refs(a) for a in node.args)
    if isinstance(node, PyCall):
        return True  # py.* gateway can read arbitrary cells
    if isinstance(node, BinOp):
        return has_dynamic_refs(node.left) or has_dynamic_refs(node.right)
    if isinstance(node, (UnaryOp, Percent)):
        return has_dynamic_refs(node.operand)
    return False

Errors

errors

ExcelError

Bases: Enum

FormulaError

Bases: Exception

parse_error_literal 

parse_error_literal(text: str) -> ExcelError | None
Source code in src/gridcalc/formula/errors.py 
28
29
def parse_error_literal(text: str) -> ExcelError | None:
    return _BY_TEXT.get(text.upper())

first_error 

first_error(*values: object) -> ExcelError | None
Source code in src/gridcalc/formula/errors.py 
32
33
34
35
36
def first_error(*values: object) -> ExcelError | None:
    for v in values:
        if isinstance(v, ExcelError):
            return v
    return None

AST nodes

ast_nodes

Node module-attribute 

Node = (
    Number
    | String
    | Bool
    | ErrorLit
    | CellRef
    | RangeRef
    | SpillRef
    | Name
    | Call
    | PyCall
    | Apply
    | BinOp
    | UnaryOp
    | Percent
)

Number dataclass 

Number(value: float)
value instance-attribute 
value: float

String dataclass 

String(value: str)
value instance-attribute 
value: str

Bool dataclass 

Bool(value: bool)
value instance-attribute 
value: bool

ErrorLit dataclass 

ErrorLit(error: ExcelError)
error instance-attribute 
error: ExcelError

CellRef dataclass 

CellRef(
    col: int,
    row: int,
    abs_col: bool,
    abs_row: bool,
    sheet: str | None = None,
)
col instance-attribute 
col: int
row instance-attribute 
row: int
abs_col instance-attribute 
abs_col: bool
abs_row instance-attribute 
abs_row: bool
sheet class-attribute instance-attribute 
sheet: str | None = None

RangeRef dataclass 

RangeRef(start: CellRef, end: CellRef)
start instance-attribute 
start: CellRef
end instance-attribute 
end: CellRef

SpillRef dataclass 

SpillRef(anchor: CellRef)

The spill-range operator A1#: the whole dynamic array that the formula in anchor spilled, as opposed to A1 which reads only the top-left scalar.

anchor instance-attribute 
anchor: CellRef

Name dataclass 

Name(name: str)
name instance-attribute 
name: str

Call dataclass 

Call(name: str, args: tuple[Node, ...])
name instance-attribute 
name: str
args instance-attribute 
args: tuple[Node, ...]

PyCall dataclass 

PyCall(name: str, args: tuple[Node, ...])
name instance-attribute 
name: str
args instance-attribute 
args: tuple[Node, ...]

Apply dataclass 

Apply(func: Node, args: tuple[Node, ...])

Application of an expression's result to arguments, e.g. LAMBDA(x, x+1)(5). func evaluates to a first-class lambda value; distinct from Call, whose callee is a static name.

func instance-attribute 
func: Node
args instance-attribute 
args: tuple[Node, ...]

BinOp dataclass 

BinOp(op: str, left: Node, right: Node)
op instance-attribute 
op: str
left instance-attribute 
left: Node
right instance-attribute 
right: Node

UnaryOp dataclass 

UnaryOp(op: str, operand: Node)
op instance-attribute 
op: str
operand instance-attribute 
operand: Node

Percent dataclass 

Percent(operand: Node)
operand instance-attribute 
operand: Node