4,8141
Tests
the checks that pin this engine's behavior
ENGINE 04
SFS-E04-VAL · READ-ONLY VALIDATION · REV 2026-07-20 · PRODUCTION
PRODUCTION = runnable end-to-end, CI-backed, full test suite passing. All data fictional and seeded.
Validation Engine
A read-only rules engine that flags workbook defects human review misses — and can never introduce one.
The Validation Engine opens a finished Excel workbook read-only and runs six independent checks over it: whether the closing-surplus total is formula-driven rather than typed over, whether the trial balance ties out, whether stale review notes were left behind, whether the audit lineage flows inputs to formulas, whether draft-only MIN/MAX cap logic was ever replaced, and whether the sibling JSON export still agrees with the workbook. Each check yields a PASS, FAIL, or FLAG finding with a Sheet!Cell location, and the findings roll up to one verdict — PASS, REVIEW, or FAIL — plus a matching process exit code. It ships with a seeded synthetic generator so it runs end-to-end on fully fictional data, and it never writes to the file it audits.
Every count on this page is a command, not a claim. The six checks, the seven-workbook demo corpus, and the pinned test total each re-derive from the public repository.
Architecture
functional block stack · static overviewSelect a layer
Each layer is an independent stage of the read-only pipeline. Select a layer for its plain-terms and engineering detail.
Key specifications
at a glance2 (read-only)5
Read passes / workbook
opened twice read-only — formulas and cached values — never written
What it does for you
plain termsLarge, formula-heavy financial workbooks must be checked before sign-off, and the errors that survive human review are the subtle ones: a total hardcoded on top of its formula, a trial balance off by a plug, a leftover 'reviewer decision' note, inverted lineage where an evidence input holds a formula, a draft MIN/MAX cap that was never removed, or a published JSON that no longer matches the workbook it came from. Checking these by eye across many near-identical workbooks is slow and error-prone, and asking an LLM 'did you fix everything?' returns a confident 'yes' whether or not it is true. This engine replaces that with a deterministic, read-only registry of checks that runs identically every time and cannot alter the audited file.
A hardcoded total slips past review7. Maple Fund LP's closing surplus was typed in as the literal 900 instead of the formula =B3+B4+B5. expected_formula fires FAIL at Surplus-Detail!B6 ('expected a formula but found hardcoded value: 900'), lineage_direction adds a FLAG on the same cell, and the workbook verdict is FAIL.
A trial balance off by a plug8. Birchwood Op Co's trial balance no longer ties out. debit_credit_balance sums the columns and reports FAIL at Trial-Balance!B:C ('debit=885 vs credit=875, diff=10'), driving the workbook to FAIL.
A published JSON disagrees with the workbook9. Demo Holdings LLC's sibling JSON export carries closing_surplus=1725 while the workbook resolves to 1700. json_tieout fires FAIL at json:closing_surplus, catching a published figure that drifted from its source.
Functional block diagram
engineering · each block links to its sourcePlain terms
- Seeded generator. Writes fictional workbooks and JSON exports to ./samples — one clean and one per planted defect.
- Dual read-only load. Opens each workbook twice read-only (formulas and cached values) and attaches the sibling JSON export.
- expected_formula. Checks that cells which should be formula-driven hold a formula, not a typed-in number.
- debit_credit_balance. Sums the trial-balance debit and credit columns and confirms they tie out.
- forbidden_text. Scans every cell for stale review notes and internal / tool words (TODO, 'reviewer decision', tool names).
- lineage_direction. Confirms evidence cells are raw inputs (literals) and detail drivers are formulas; either inversion flags.
- cap_logic_leftover. Detects leftover draft MIN/MAX cap logic that should have been replaced before final.
- json_tieout. Cross-checks the sibling JSON export's closing_surplus against the workbook's resolved value.
- Verdict roll-up. Collapses all findings for a workbook, and then the whole run, into one verdict — worst wins.
- Reports + exit code. Emits the markdown and JSON reports and returns an exit code encoding the verdict.
Engineering
- Seeded generator. generate.generate_corpus(out_dir, seed=20240614); wipes stale *.xlsx / *.json first, then writes the clean plus six defect workbooks with sibling .json.
- Dual read-only load. engine._load_context(): load_workbook(read_only=True) twice (data_only False / True) plus json.loads(<stem>.json), wrapped in WorkbookContext.
- expected_formula. check_expected_formula() over EXPECTED_FORMULA_CELLS; a literal is a FAIL.
- debit_credit_balance. check_debit_credit_balance() sums TB_DEBIT_COL / TB_CREDIT_COL from cached values; |diff| >= 1e-6 is a FAIL.
- forbidden_text. check_forbidden_text() substring-matches FORBIDDEN_TEXT over iter_formula_cells(); a hit is a FLAG.
- lineage_direction. check_lineage_direction() over EVIDENCE_INPUT_CELLS and DETAIL_DRIVER_CELLS; an inversion is a FLAG.
- cap_logic_leftover. check_cap_logic_leftover() applies a case-insensitive MIN/MAX open-paren regex over formula cells; a match is a FLAG.
- json_tieout. check_json_tieout() compares json_data['closing_surplus'] to _closing_surplus_value(); a mismatch is a FAIL, an unresolvable value a FLAG.
- Verdict roll-up. WorkbookReport.verdict (any FAIL -> FAIL, else any FLAG -> REVIEW, else PASS), then overall_verdict() over all reports.
- Reports + exit code. build_markdown_report() / build_json_report() write the files; cli.main() returns _EXIT_BY_VERDICT[overall] (0 / 1 / 2), or 3 on a usage error.
Instruction set
every public command| Command | Operation | Output | Exit | Artifacts |
|---|---|---|---|---|
python run.py | zero-arg quickstart: regenerate the fictional corpus into ./samples, validate it, and write both reports | generation line, per-workbook findings, 'Overall verdict: FAIL (7 workbook(s))' | 0 / 1 / 2 by verdict | samples/*.xlsx + .json, validation_report.json, validation_report.md |
python -m validation_engine ./samples | validate every .xlsx in a folder (read-only) and print findings | '=== <wb> === verdict: ...' blocks + overall verdict line | 0 PASS / 1 REVIEW / 2 FAIL / 3 usage | none (stdout only) |
python -m validation_engine.generate | write the seeded fictional workbooks + JSON exports into ./samples | 'Wrote 7 fictional workbook(s) to <samples>' + names | 0 | samples/*.xlsx, samples/*.json |
python -m validation_engine ./samples --generate --json report.json --md report.md | (re)generate the corpus, validate, and write the structured JSON + markdown reports | generation line + findings + 'Wrote JSON/markdown report' | 0 / 1 / 2 by verdict | report.json, report.md |
python -m pytest -q | run the test suite (defects caught, clean passes, report shape, read-only, determinism) | pytest summary line | 0 on pass | none |
Benchmarks
measured demo results| Measure | Result |
|---|---|
| Demo workbooks validated10 | 7 workbooks the committed example run validates seven workbooks and returns overall FAIL |
| Planted defects caught11 | 6 of 6 defects, one per rule one defect per rule is planted and each is caught by its own rule; the clean workbook passes |
| Rules in registry12 | 6 checks six checks run over every workbook, each with a fixed severity |
| Engine tests13 | 4,814 tests |
Control characteristics
engineeringPlain terms
The engine only reports; it never writes to, approves, or signs off the audited file. The PASS / REVIEW / FAIL verdict and its exit code (0 / 1 / 2, or 3 for a usage error) are the handoff a human reviewer or CI pipeline consumes to decide whether to proceed.
Engineering
Deterministic envelope. seeded fictional inputs, read-only operation, offline default mode.
| Severity | Verdict | Action |
|---|---|---|
| Any FAIL finding (expected_formula, debit_credit_balance, or a json_tieout mismatch) | FAIL | exit code 2 — a hard defect is present; block sign-off |
| One or more FLAG findings and no FAIL (forbidden_text, lineage_direction, cap_logic_leftover, or an unresolvable json_tieout) | REVIEW | exit code 1 — human review before sign-off |
| Every finding PASS | PASS | exit code 0 — clean |
- Strictly read-only: every workbook is opened with read_only=True (twice) and never written, so the engine cannot introduce a defect — the audited file is byte-identical before and after, asserted by test_readonly_and_determinism.py.
- Deterministic: a fixed order over REGISTRY plus a fixed generator seed (SEED=20240614) means identical inputs always yield identical findings in identical order.
- Offline: the only third-party dependencies are openpyxl and pytest; synthetic data uses the stdlib random module — no network and no external services.
- Refuses rather than fudges: json_tieout emits a FLAG, not a fabricated comparison, when the JSON has no closing_surplus key or the workbook value cannot be resolved.
- Fail-safe roll-up: any single FAIL forces the whole run to FAIL and exit code 2 — a defect cannot be diluted by surrounding passes.
Operating limits
what it refuses to do- Never writes to an audited workbook — it opens files read-only (twice) and can only report, never introduce or fix a defect.14
- Refuses to guess a JSON tie-out: if the export has no closing_surplus key or the workbook value cannot be resolved, it emits a FLAG rather than fabricating a pass or fail comparison.15
- Only checks a fixed rule set against a fixed workbook schema (Surplus-Detail / Summary / Trial-Balance / Evidence); when an expected sheet is absent it skips rather than inventing findings.16
See it run
animated overview
Integration
how to run itDistribution: public repository, MIT license.
git clone https://github.com/sophonfinance-wq/finance-automation-portfolio.git && cd finance-automation-portfolio/audit-automation && python run.py
python -m validation_engine ./samples
Substantiation
- 1python -m pytest --collect-only -q (audit-automation/); verified 2026-07-20 and pinned in site-datasheets/counts.json
- 2audit-automation/validation_engine/engine.py — REGISTRY; README rule table
- 3audit-automation/validation_engine/engine.py — Verdict enum
- 4audit-automation/validation_engine/cli.py — _EXIT_BY_VERDICT and docstring
- 5audit-automation/validation_engine/engine.py — _load_context
- 6audit-automation/validation_report.json — workbook_count; generate.py DEFECTS
- 7audit-automation/README.md — committed example output
- 8audit-automation/README.md — committed example output
- 9audit-automation/README.md — committed example output
- 10audit-automation/validation_report.json — workbook_count: 7, overall_verdict: FAIL
- 11audit-automation/validation_engine/generate.py — DEFECTS; README example output
- 12audit-automation/validation_engine/engine.py — REGISTRY; validation_report.json registry array
- 13python -m pytest --collect-only -q (audit-automation/); verified 2026-07-20 and pinned in site-datasheets/counts.json
- 14audit-automation/validation_engine/engine.py — _load_context load_workbook(read_only=True); README
- 15audit-automation/validation_engine/engine.py — check_json_tieout
- 16audit-automation/validation_engine/engine.py — sheet-presence guards in each check
Show us where the hours go.
One conversation: you describe the work that consumes your team's month; we tell you plainly what this engine can take over, what it can't, and what a scoped first phase would cost. Your people keep approval authority.
Book a free consultation