`<bmx-formula-engine>`

A spreadsheet calculation engine as an element: sheets of values and formulas - over four hundred functions, from SUMIFS and XLOOKUP to IRR, LAMBDA and dynamic arrays - kept calculated in the page, with no server. It draws nothing; it holds the numbers that other elements show.

16 properties · 3 events · 20 methods · 0 parts

Example

Your loan Amount (£) Interest rate Years

What it costs

Each month
Total repaid
Of which interest
Interest share
Paid off by
Rate if paid in 20 years

Save as Excel

Open an Excel file…

No script runs this calculator. The engine holds the sheet; inputs with data-bmx-cell edit cells, and anything else with it shows the cell's value in its format. Type 5% or £300,000 - text is read as a person means it.

Show markup

<style>
  #ex-fe { display: grid; gap: 1rem; inline-size: 100%; grid-template-columns: repeat(auto-fit, minmax(15rem, 1fr)); }
  #ex-fe fieldset { display: grid; gap: 0.75rem; margin: 0; padding: 1rem; border: 1px solid var(--bmx-border); border-radius: var(--bmx-radius-lg); background: var(--bmx-surface); }
  #ex-fe legend { padding: 0 0.25rem; font-weight: 600; }
  #ex-fe label { display: grid; gap: 0.25rem; font-size: var(--bmx-font-size-sm); color: var(--bmx-text-muted); }
  #ex-fe input { min-block-size: 2.25rem; padding: 0 0.625rem; border: 1px solid var(--bmx-border-strong); border-radius: var(--bmx-radius-md); background: var(--bmx-surface); color: var(--bmx-text); font: inherit; }
  #ex-fe dl { display: grid; grid-template-columns: 1fr auto; gap: 0.5rem 1rem; margin: 0; }
  #ex-fe dt { color: var(--bmx-text-muted); }
  #ex-fe dd { margin: 0; font-variant-numeric: tabular-nums; font-weight: 600; text-align: end; }
  #ex-fe .big { font-size: 1.5rem; color: var(--bmx-primary); }
  #ex-fe [data-bmx-error] { color: var(--bmx-danger); }
</style>

<bmx-formula-engine
  id="ex-fe-engine"
  bind="#ex-fe"
  locale="en-GB"
  sheets='[{"name":"Loan","cells":{
    "A1":"Amount","B1":250000,
    "A2":"Rate","B2":0.045,
    "A3":"Years","B3":25,
    "A4":"Monthly","B4":"=PMT(B2/12, B3*12, -B1)",
    "A5":"Total paid","B5":"=B4*B3*12",
    "A6":"Interest","B6":"=B5-B1",
    "A7":"Interest share","B7":"=IFERROR(B6/B5, 0)",
    "A8":"Paid off by","B8":"=EDATE(TODAY(), B3*12)"}}]'
></bmx-formula-engine>

<div id="ex-fe">
  <fieldset>
    <legend>Your loan</legend>
    <label>Amount (£) <input data-bmx-cell="Loan!B1" inputmode="decimal" /></label>
    <label>Interest rate <input data-bmx-cell="Loan!B2" data-bmx-format="0.00%" inputmode="decimal" /></label>
    <label>Years <input data-bmx-cell="Loan!B3" type="number" min="1" max="40" /></label>
  </fieldset>
  <fieldset>
    <legend>What it costs</legend>
    <dl aria-live="polite">
      <dt>Each month</dt>
      <dd class="big" data-bmx-cell="Loan!B4" data-bmx-format="£#,##0.00"></dd>
      <dt>Total repaid</dt>
      <dd data-bmx-cell="Loan!B5" data-bmx-format="£#,##0"></dd>
      <dt>Of which interest</dt>
      <dd data-bmx-cell="Loan!B6" data-bmx-format="£#,##0"></dd>
      <dt>Interest share</dt>
      <dd data-bmx-cell="Loan!B7" data-bmx-format="0.0%"></dd>
      <dt>Paid off by</dt>
      <dd data-bmx-cell="Loan!B8" data-bmx-format="mmmm yyyy"></dd>
      <dt>Rate if paid in 20 years</dt>
      <dd data-bmx-formula="=RATE(20*12, -Loan!B4, Loan!B1)*12" data-bmx-format="0.00%"></dd>
    </dl>
  </fieldset>
</div>

<div class="row" role="group" aria-label="Excel files">
  <bmx-button id="ex-fe-save" variant="soft">Save as Excel</bmx-button>
  <bmx-button id="ex-fe-open" variant="ghost">Open an Excel file…</bmx-button>
  <input id="ex-fe-file" type="file" accept=".xlsx" hidden />
</div>

<div class="row">
  <span class="note" id="ex-fe-out">
    <strong>No script runs this calculator.</strong> The engine holds the sheet; inputs with
    <code>data-bmx-cell</code> edit cells, and anything else with it shows the cell's value in its format.
    Type <em>5%</em> or <em>£300,000</em> - text is read as a person means it.
  </span>
</div>

<script type="module">
  // Optional: the change event, for anything the page wants to do as well.
  const engine = document.getElementById('ex-fe-engine');
  const out = document.getElementById('ex-fe-out');
  engine.addEventListener('bmxFormulaChange', event => {
    const { changes, telemetry } = event.detail;
    if (!changes.length) return;
    out.textContent = `${changes.length} cell${changes.length === 1 ? '' : 's'} changed - ${telemetry.recalculated} formula${telemetry.recalculated === 1 ? '' : 's'} recalculated in ${telemetry.ms} ms.`;
  });

  // Excel files, both ways: the formulas go out as formulas, and a workbook's come back in.
  document.getElementById('ex-fe-save').addEventListener('click', () => engine.downloadXlsx('loan.xlsx'));
  const file = document.getElementById('ex-fe-file');
  document.getElementById('ex-fe-open').addEventListener('click', () => file.click());
  file.addEventListener('change', async () => {
    if (!file.files?.[0]) return;
    await engine.loadXlsx(file.files[0]);
    out.textContent = `Opened ${file.files[0].name}: ${engine.workbook.sheetNames.join(', ')}.`;
    file.value = '';
  });
</script>

With bind, it wires up plain HTML inside that element, with no script:

edits a cell shows a formula's value

src or loadXlsx() reads an Excel file (.xlsx) as well as JSON, and toXlsx() / downloadXlsx() write one, formulas and all.

With worker, it calculates on a Web Worker, for very large models: the page stays responsive while the worker loads and recalculates.

From script, the workbook property is the BmxFormulaWorkbook itself - the full API, synchronously - and the methods below wrap the common parts. createWorkbook() makes further, independent workbooks with no element. Every change fires bmxFormulaChange with the cells that changed.

Properties

Property	Attribute	Type	Default	Description
`bind`	`bind`	`string`	—	Wires up `data-bmx-cell` and `data-bmx-formula` elements inside the element this selector finds (or `document`). Elements naming another engine with `data-bmx-engine` are left to it.
`calculation`	`calculation`	`'automatic' \| 'manual'`	`'automatic'`	`automatic` recalculates on every change; `manual` waits for `recalculate()`.
`dateSystem`	`date-system`	`1900 \| 1904`	`1900`	The date system: 1900 (default) or 1904.
`functions`	property only	`Record<string, BmxFormulaCustomFunction>`	—	Functions of your own, by name. Set from script: a function cannot travel in an attribute.
`iterative`	`iterative`	`boolean`	`false`	Iterate circular references until they settle, rather than reporting #CYCLE!.
`locale`	`locale`	`string`	—	A BCP 47 locale: decides the formula separators, how typed numbers and dates are read, and month names.
`maxChange`	`max-change`	`number`	`0.001`	With `iterative`: stop when no value moves by more than this.
`maxIterations`	`max-iterations`	`number`	`100`	With `iterative`: the most rounds of iteration.
`names`	`names`	`BmxFormulaNameData[] \| string`	—	Named ranges and named formulas: `[{ name, formula, sheet? }]`.
`requestInit`	`request-init`	`RequestInit \| string`	—	Options for the `src` request (headers, credentials), as a property or JSON.
`sheets`	`sheets`	`BmxFormulaSheetData[] \| string`	—	The sheets: `[{ name, cells, records?, formats? }]`, as a property or JSON.
`src`	`src`	`string`	—	A URL to load the workbook from: JSON in the shape `toJSON` writes, or an Excel file (.xlsx). Your server, your data.
`tables`	`tables`	`BmxFormulaTableData[] \| string`	—	Tables for structured references: `[{ name, sheet, range }]`.
`undoLimit`	`undo-limit`	`number`	`100`	How many changes `undo()` can step back through.
`workbook`	property only	`BmxFormulaWorkbook \| BmxFormulaRemoteWorkbook \| null`	`null`	The workbook: the full calculation API, synchronously. Read it to work with the engine from script, or assign a `BmxFormulaWorkbook` of your own to have the element (and anything bound to it) use that instead. With `worker`, it is a `BmxFormulaRemoteWorkbook`: reads answer at once from its mirror, changes answer with promises.
`worker`	`worker`	`string \| Worker`	—	Calculates on a Web Worker: the URL of a module worker script that calls `serveFormulaWorker()` from `bmx-formulas.mjs` (two lines; see that module), or a `Worker` set from script, which the element then owns. The page stays responsive while the worker loads and recalculates, and values are read from a mirror kept here, so bound elements and formula bars work as before. Functions of your own are given to the worker, not `functions`. A `Worker` given here stays the page's to end.

Events

Event	Detail	Description
`bmxFormulaChange`	`BmxFormulaChangeEvent`	Recalculation finished: the cells that changed, and what the calculation did.
`bmxFormulaError`	`BmxFormulaEngineErrorDetail`	The workbook could not be loaded or read.
`bmxFormulaReady`	`BmxFormulaReadyDetail`	The workbook is loaded and calculated.

Methods

Method	Signature	Description
`createWorkbook`	`createWorkbook(data?: BmxFormulaWorkbookData, options?: BmxFormulaWorkbookOptions) => Promise<BmxFormulaWorkbook>`	A new, independent workbook (not bound to this element): for calculations that need no element of their own.
`defineName`	`defineName(name: string, formula: string, sheet?: string) => Promise<void>`	Defines or redefines a name.
`downloadXlsx`	`downloadXlsx(fileName?: string) => Promise<void>`	Saves the workbook as an Excel file through the browser's download.
`enterText`	`enterText(address: string, text: string) => Promise<void>`	Sets a cell from text as a person types it: numbers, dates and percentages are read in the engine's locale.
`evaluate`	`evaluate(formula: string) => Promise<BmxFormulaValue \| BmxFormulaValue[][]>`	Works out a formula without storing it.
`getText`	`getText(address: string, format?: string) => Promise<string>`	A cell's value as text, in its number format or the one given.
`getValue`	`getValue(address: string) => Promise<BmxFormulaValue>`	The value of a cell. An error value arrives as a `BmxFormulaError` with its `code` and `message`.
`getValues`	`getValues(range: string) => Promise<BmxFormulaValue[][]>`	The values of a range as rows.
`getWorkbook`	`getWorkbook() => Promise<BmxFormulaWorkbook \| BmxFormulaRemoteWorkbook \| null>`	The workbook itself, for the full API (also the `workbook` property).
`load`	`load(url: string) => Promise<void>`	Loads the workbook from a URL: JSON in the shape `toJSON` writes, or an Excel file (.xlsx), told apart by the response's content type or the URL's extension.
`loadXlsx`	`loadXlsx(source: Blob \| ArrayBuffer \| Uint8Array \| string) => Promise<void>`	Loads an Excel file (.xlsx): from a `File` a reader chose, a `Blob`, its bytes, or a URL. Cells, formulas, number formats, names, tables, the date system and iterative calculation come across.
`recalculate`	`recalculate() => Promise<BmxFormulaTelemetry \| null>`	Recalculates every formula (in manual mode, runs what is waiting).
`redo`	`redo() => Promise<boolean>`
`registerFunction`	`registerFunction(name: string, def: BmxFormulaCustomFunction) => Promise<void>`	Adds a function of your own: `engine.registerFunction('VAT', { fn: x => x * 0.2 })`.
`setCell`	`setCell(address: string, input: BmxFormulaInput) => Promise<void>`	Sets one cell: a value, or text starting with `=` for a formula.
`setCells`	`setCells(entries: Record<string, BmxFormulaInput>) => Promise<void>`	Sets several cells, recalculating once: `{ "A1": 1, "B1": "=A1*2" }`.
`setData`	`setData(data: BmxFormulaWorkbookData) => Promise<void>`	Replaces the workbook with JSON.
`toJSON`	`toJSON(values?: boolean) => Promise<BmxFormulaWorkbookData>`	The whole workbook as JSON; with `values`, each sheet's calculated values as well.
`toXlsx`	`toXlsx() => Promise<Blob>`	The workbook as an Excel file: formulas, their values, number formats, names and tables.
`undo`	`undo() => Promise<boolean>`