Systems and records
Financial spreadsheet lineage dataset: trace a board metric to its source
By SourceX Editorial · Updated
9 sections
Short answer
To trace a board metric to source evidence, walk the metric cell's precedents through formulas, workbook links and queries until each branch ends in an identified system export, an owned assumption or a flagged hard-coded value. Record nodes and labeled edges, and annotate every leaf with its source report, parameters, extraction date and tie-out status. Those traces form a financial spreadsheet lineage dataset.
Provenance trail
Financial spreadsheet
Key takeaways
- A trace is complete only when every branch ends in an identified export, an owned assumption or a flagged override.
- Excel's tracer arrows help you walk the graph but stop at closed workbooks and keep no record.
- OpenLineage's DIRECT and INDIRECT edge types fit spreadsheet functions once arguments are split by role.
- Every leaf needs an evidence record that lets a second person test accuracy and completeness.
- Split the dataset by workbook family, because monthly copies share near-identical formulas.
How do you trace a board metric through a workbook to source evidence?
Start at the cell that feeds the board slide and walk its precedents backwards until every branch ends in one of three places: a system export you can identify and rerun, a documented assumption with an owner, or a hard-coded number you must flag. Record each step as a node and an edge, and attach an evidence annotation to every leaf that says where the number came from, when it was pulled and whether it ties to the system of record.
That record is what a financial spreadsheet lineage dataset is made of. The finished trace answers the question a director asks, where does this number come from, and the same trace, repeated across many workbooks and metrics, becomes training and evaluation data for agents that audit, explain or rebuild financial models.
This guide gives the annotation vocabulary: node types, edge labels borrowed from an open lineage standard, the evidence fields each leaf needs, an illustrative worked trace, the exception that breaks most traces, and how to turn traces into dataset tasks.
Why formula auditing alone stops short of the source
Excel's own tools trace cell relationships inside a workbook, but they stop at the workbook's edge and leave no durable record. Microsoft's support documentation describes Trace Precedents and Trace Dependents as drawing tracer arrows one level at a time, and notes that the arrows disappear when formulas change or rows, columns or cells are inserted, deleted or moved. Links into another workbook can be followed only when that workbook is open.
Workbook links have their own syntax: with the source open, the reference shows the workbook name in square brackets, then the sheet and range, and with the source closed the full path appears. Power Query adds another layer, with dependencies between queries and data sources shown in its own diagram view rather than in cell arrows. A trace that stops at the first external reference or query table has not reached the source.
Research on spreadsheet errors explains why the full trace is worth the effort. Raymond Panko's review of the literature concludes that errors are rare per cell but that in large spreadsheets at least one incorrect bottom-line value is very likely, and that developers are overconfident about accuracy. Formula graphs in real workbooks are also large: the TACO paper by Tang and colleagues set out to compress them because querying and maintaining them was slow enough to hurt interactivity.
Which node types does a workbook lineage graph need?
Use a small fixed set of node types so that two annotators tracing the same metric produce the same graph. Every cell, range or object on the path gets exactly one type, and the type alone tells a reviewer whether the trace can stop there.
| Node type | What it is | Can the trace stop here? | What to record |
|---|---|---|---|
| Metric cell | The cell or chart series quoted in the board material | No; it is the start | Sheet, cell address, label and reporting period |
| Formula cell | A cell whose value is computed from other cells | No | Formula text and the ranges it reads |
| Named range or table | A defined name or structured table referenced by formulas | No | Name, scope and the address it resolves to |
| Workbook link | A reference into another workbook file | No; continue in that file | Target path, sheet, range and file version |
| Query output | A table loaded by Power Query or a data connection | Only after the query's source is recorded | Query name, source system, filters and refresh time |
| System export | Pasted or loaded data from a named report or extract | Yes, if the export is identified and reproducible | Report name, parameters, run date and row count |
| Assumption input | A typed driver such as a growth rate, labeled as an assumption | Yes, if an owner and approval are recorded | Owner, rationale and date approved |
| Hard-coded override | A typed number replacing a formula or export value | Yes, but flagged as unsupported | Who typed it if known, and any note or comment |
How should the edges between cells be labeled?
Label each edge by how the input affects the output, using the column lineage vocabulary from the OpenLineage specification so the dataset can be read by lineage tools. OpenLineage distinguishes DIRECT edges, where the output value is derived from the input, from INDIRECT edges, where the input affects the output without being part of its value. Its DIRECT subtypes are IDENTITY, TRANSFORMATION and AGGREGATION; its INDIRECT subtypes include JOIN, GROUP_BY, FILTER, SORT, WINDOW and CONDITIONAL.
Spreadsheet formulas map onto those labels cleanly once you split each function's arguments by role. A SUMIFS formula, for example, has a sum range that is a direct aggregation and criteria ranges that act as filters. The mapping below is this guide's convention, not part of the OpenLineage specification.
| Spreadsheet construct | Argument or role | Edge label | Annotation note |
|---|---|---|---|
| Plain reference such as =Data!C14 | The referenced cell | DIRECT / IDENTITY | Value carried through unchanged |
| Arithmetic such as =C14*(1-C3) | Each operand | DIRECT / TRANSFORMATION | Note which operand is an assumption |
| SUM, SUMIFS, AVERAGEIFS | Sum or average range | DIRECT / AGGREGATION | Record the full range, not only the visible rows |
| SUMIFS, COUNTIFS criteria | Criteria ranges and values | INDIRECT / FILTER | Record the criteria value, such as a period or segment |
| XLOOKUP, VLOOKUP, INDEX with MATCH | Lookup key and lookup array | INDIRECT / JOIN | The returned column is a separate DIRECT edge |
| IF, IFS, IFERROR | Condition argument | INDIRECT / CONDITIONAL | IFERROR fallbacks often hide broken links; flag them |
| Pivot table | Row and column fields | INDIRECT / GROUP_BY | Values field is a DIRECT / AGGREGATION edge |
The evidence annotation record for every leaf
Every leaf node needs an evidence record that a second person can check without asking the author. The standard to aim for is the one auditors use for company-produced information: PCAOB AS 1105 requires an auditor relying on information produced by the company to test its accuracy and completeness, or the controls over it. A leaf annotation should make that test possible.
Fill the tie-out status last, after comparing the leaf to the system of record. A leaf that cannot be compared is not a failure of the annotator; it is information the dataset must keep.
| Field | What it holds | Illustrative value | Check before accepting |
|---|---|---|---|
| leaf_id | Stable id of the node | wb03.Data!C2:C9000 | Unique within the trace |
| source_kind | System export, assumption or override | System export | Matches the node type |
| source_system | System of record the data came from | ERP general ledger | Named system, not a person |
| report_or_query | Report name, saved search or query | Trial balance by department | Can be found in the system |
| parameters | Period, entity, filters used | Period 9, all entities, posted only | Reproduce with the same parameters |
| extracted_at | When the data was pulled | Day 4 after period close | Before the board pack date |
| completeness_check | How completeness was confirmed | Row count and control total agree with system | Evidence attached or referenced |
| tie_out_status | Tied, tied with explained difference, untied, not testable | Tied with explained difference | Difference reason recorded |
| owner | Role accountable for the leaf | Revenue accountant | Role exists and can confirm |
Worked trace (illustrative): a net revenue retention figure
The example below is illustrative and not drawn from a real company. It uses percentages, counts and days only.
The finished trace ends in four leaves: two tied exports, one export tied with an explained difference and one unsupported override, which is precisely the kind of finding the board slide cannot show by itself.
- Metric cell: Board!F12 shows net revenue retention of 112 percent for the trailing twelve months.
- F12 is a formula dividing current recurring revenue from a starting cohort by that cohort's recurring revenue a year earlier: two DIRECT / TRANSFORMATION edges to Calc!D40 and Calc!D41.
- Calc!D40 is a SUMIFS over a pasted revenue table: the amount column is DIRECT / AGGREGATION, the cohort flag and period columns are INDIRECT / FILTER.
- The pasted table is a system export from the billing system's subscription revenue report, extracted 3 days after period close; its row count matches the report and it ties to the system.
- The cohort flag comes from an XLOOKUP into a customer list workbook: an INDIRECT / JOIN edge on customer id, followed into the second file, whose list is a CRM export that ties with an explained difference of 2 records merged after extraction.
- Calc!D41 points to a prior-year workbook that was not available; the trace follows the link path, finds the archived file and continues to a tied export.
- One cell in the revenue table, a single customer's month, has been typed over a formula. It is labeled hard-coded override, untied, with no comment explaining it.
- Result: the metric is traceable, with one unsupported override that changes the figure by less than half a percentage point, recorded for the finance owner to resolve.
The exception: a pasted value with no surviving source
A trace commonly breaks at a block of pasted values whose originating report cannot be identified. Do not guess a source from the column headers or from a similar export in a neighbouring folder. Label the node a system export with an unknown source, set tie-out status to not testable, and record what was searched.
If an older version of the workbook or an email attaching the export exists, use it, and record the version used, because later versions may have been edited. If nothing exists, the honest label is the useful one: a lineage dataset that marks unknown sources teaches an agent to report a gap rather than invent provenance.
Stop and narrow the scope when a workbook depends on dozens of unidentified pastes; trace one metric fully rather than many partially. Ask the finance owner before labeling an override as an error, because some overrides are approved top-side adjustments with documentation held elsewhere.
How traces become dataset tasks, labels and splits
One trace yields several tasks. Given a metric cell, an agent can be asked to produce the leaf set, to label each edge, to find untied or hard-coded leaves, or to explain the metric in plain language with citations to leaves. Grading compares the agent's graph to the annotated graph node by node, and its findings to the recorded tie-out statuses.
Split by workbook family, not by cell. Monthly copies of the same model share most formulas, and the TACO authors observed tabular locality in real spreadsheets, meaning nearby cells tend to have similar formula structure. A random split by cell or by month would put near-identical formulas in both training and test data.
- Keep every version of a workbook used in a trace, and record which version each node came from.
- Store formulas as text alongside computed values, so a task can test reasoning about the formula rather than reading a cached number.
- Record annotator agreement on node types and edge labels; disagreements usually reveal an ambiguous construct worth a guideline.
- Include fully tied traces as controls, so an agent is also graded on not inventing problems.
Licensing finance workbooks and their traces
Board packs and the models behind them can contain material nonpublic information, compensation data and customer names, so selection and de-identification come before anything else, and counsel should review what may be shared. De-identified workbooks still need a rights decision: the company must be entitled to license them, and removing names does not settle that.
Where a finance team has kept years of versioned models and the exports behind them, SourceX can carry that history through rights review, contracting, preparation and delivery as a licensed dataset for AI developers. Qualification is checked first with a short assessment that needs no uploads, and nothing moves until the company has signed off the scope and terms.
Frequently asked questions
What is a financial spreadsheet lineage dataset?
It is a collection of traced workbooks in which each reported metric is linked, node by node, to the formulas, links, queries and source exports that produce it, with evidence annotations on every leaf. It is used to train and evaluate agents that explain, audit or rebuild financial models.
Can Excel's Trace Precedents produce the lineage automatically?
It helps you walk the graph, but it draws arrows one level at a time inside open workbooks and keeps no record. A lineage dataset needs the nodes, edges and evidence written down, across every linked workbook and query.
How should hard-coded numbers be labeled?
Label them as hard-coded overrides with tie-out status untied unless documentation shows an approved adjustment. Record any cell comment and who typed the value if that is known, and ask the finance owner before calling it an error.
Why use OpenLineage labels for spreadsheet edges?
Its DIRECT and INDIRECT types and subtypes already distinguish values carried through from inputs that only filter, join or condition the result. Reusing them makes the dataset readable by lineage tools and keeps edge labels consistent across annotators.
Do board materials need special handling before licensing?
Yes. They can contain material nonpublic information, personal compensation data and confidential customer terms. Select scope narrowly, de-identify, and have counsel review what may be shared before any license is signed.
Sources
- Microsoft says Trace Precedents and Trace Dependents draw tracer arrows a level at a time, that arrows disappear when formulas change or cells are inserted, deleted or moved, and that links to another workbook trace only when it is open. Source
- Microsoft documents that a workbook link shows the source workbook name in square brackets when the source is open and the full path when it is closed. Source
- Microsoft's Power Query documentation describes diagram view, which shows query steps and lets users highlight related queries and dependencies. Source
- The OpenLineage column lineage facet defines DIRECT transformations (IDENTITY, TRANSFORMATION, AGGREGATION) and INDIRECT ones (JOIN, GROUP_BY, FILTER, SORT, WINDOW, CONDITIONAL). Source
- PCAOB AS 1105 requires an auditor using information produced by the company to test its accuracy and completeness, or the controls over it. Source
- Panko's EuSpRIG 2015 paper concludes that spreadsheet errors are rare per cell but that large spreadsheets very likely contain at least one incorrect bottom-line value, and that developers are overconfident. Source
- The TACO paper reports that spreadsheet formula graphs are often large and slow to query and maintain, and builds compression on tabular locality, the tendency of nearby cells to share formula structure. Source
See if your company qualifies
A short company assessment. No data uploads are needed.