Excel Intelligence & Controls

01 · RequirementsContext and problem definition

What enterprise Excel really is

In many companies, Excel does more than record data. It is the business system actually running the process. A mature payroll workbook, cost model, or inventory ledger often looks like this:

It carries real money: payroll, cost reporting, commissions, and budgets. It runs every month. When it fails, someone may be underpaid or the company may overspend. Yet it has no version control, tests, documentation, or change history. Its original author may have left, and the current maintainer may know only that “these columns must not be touched.”

Four common dilemmas

Why general AI fails when reading Excel directly

Sending an xlsx file directly to a general-purpose model creates five failure modes. Together, they explain why this product exists:

02 · RequirementsGoals and boundaries

Success measures

Five workbook issue types the method can find

These checks can find errors in the workbook itself, not only explain how its formulas work.

03 · RequirementsUsers and scenarios

Key user stories

04 · ConceptCore metaphor: the workbook is code

The methodology is built on one metaphor. It is not rhetoric, but an operational mapping:

Three deductions

05 · ConceptThree design principles

Principle one · Verification first

This is the foundation of the methodology. Every conclusion about a workbook must rest on an AI interpretation that has passed full-dataset regression testing. The hard rules are:

• The row-level pass rate must be 100%; 98% is not a pass
• Workbook-level checks must have zero exceptions, or every exception must have an explicit explanation
• Tolerances must never be widened to make a report look better: 0.01 for amounts and 0.0001 for ratios
• Every mismatch must be assessed individually: is the interpretation wrong, or is the workbook wrong?

The tooling enforces this discipline. A formula that scaffold cannot translate is marked TODO. If verify detects a TODO, it refuses to run and returns exit code 2. Verification cannot begin with an incomplete interpretation.

Principle two · Human–machine division of work

Steps 2 and 3 are deliberately not automated. A script can extract =D4+E4-G4, but it cannot infer that “column G is the employee share of social insurance, capped by local base limits, and not deducted in the termination month.” The value of this step lies precisely in what cannot be automated.

Principle three · Progressive disclosure

Nine archetypes, nineteen rule types, and four structural shapes would dilute attention if loaded all at once. The documentation is therefore layered. The output from profile tells AI which reference to read next:

SKILL.md (core document, about 650 lines)
  └─ Method skeleton + when to open each reference
       ├─ references/patterns/xxx.md   ← read only after profile identifies that archetype
       ├─ references/table_shapes.md   ← read the relevant section when the shape is not long
       ├─ references/check_library.md  ← consult when configuring table_checks
       └─ references/spec_schema.md    ← consult when writing a spec

06 · MethodSix steps, plus two extensions

If a workbook is code, the standard process for understanding unfamiliar code maps directly to the product workflow:

① Identify the kind of project      → Step 0  Profile archetype and structure
② Read and format the code          → Step 1  Convert to structured Markdown
③ Build the symbol table            → Step 2  Define the field ontology
④ Draw the call graph               → Step 3  Reconstruct formulas and lineage
⑤ Run the test suite                → Step 4  Verify the full dataset ★
⑥ Change or analyze only after pass → Step 5  Deliver and run scenarios

07 · MethodWhy verification needs two layers

Row-level verification with checks recalculates every row using the AI interpretation and compares it with the workbook's existing result column:

AI interpretation: gross = base + allowance + commission − deduction
                                      ↓ substitute each row
                                9745.14  ←→  9865.14 (Excel value)
                                      ↓
                                difference −120.00  ❌

This can prove that each row calculates correctly, but not whether the workbook contains duplicate employee IDs, whether the March closing inventory matches the April opening balance, whether a department exists in master data, whether a total row equals its details, or whether completion contains an impossible value such as 110%. Each affected row can look valid on its own; these issues appear only when the workbook is tested as a whole.

What workbook-level checks add

A concrete proof

Conclusion: both verification layers are essential. For formula-free files such as rosters, master data, and CSV exports, checks is empty—all verification value comes from workbook-level checks.

Formula breakpoints: narrow the root cause to one cell

verify records each field's column address, row range, and formula coverage, then detects formula breakpoints: cells that are constants while most cells in the same column contain formulas.

This is the only physical trace left by a manually overwritten formula. When that cell intersects a failed row, “this column has an issue” becomes “cell H7 has the issue.”

How to read results: do not assume the interpretation is wrong

08 · Methodspec.json: an executable workbook interpretation

spec.json records the AI's interpretation of a business workbook in an executable form. It turns “I believe this is how the workbook calculates” into rules that can be tested.

It turns “how this workbook calculates” from oral tribal knowledge into an executable asset. Always deliver spec.json to the user.

09 · MethodHow one method handles different workbooks

The method separates three dimensions: archetype × structure × rule set. Their combination determines how to read and verify a workbook.

Dimension one · Archetype determines what to verify

Dimension two · Structure determines how to feed the data

Dimension three · Nineteen workbook-level checks determine how to verify

The management-reporting domain package (M) adds six types for multi-workbook scenarios in which dozens or hundreds of business units feed one management report. The definitions match references/check_library.md:

Two enhancements apply to existing rules. When the new parameters are not used, old specs behave byte-for-byte the same:

These six types reject a misconfigured green light from the start. A misspelled column name can make a rule test zero rows and still return a clean ✅. This is the most dangerous false green: no error, no warning, and a report that looks clean. An unresolved alias therefore causes the system to refuse to run with exit code 2. This gate was later extended to the first thirteen types.

Three distinctions that are easy to confuse

• unique vs duplicate: the former reports the earlier row when a value appears a second time and suits a hard constraint; the latter reports every combination appearing more than once and suits “possible duplicate—please review”
• checks vs identity: the former compares the AI interpretation with an Excel result column and verifies the interpretation; the latter compares two places in the workbook that should agree and verifies the data
• identity vs balance: the former requires equality row by row; the latter requires equality of full-table totals, even if individual rows differ—the correct expression of an accounting identity

Four questions for an unfamiliar workbook

10 · Program structureDirectories and module layers

Skill package directory

excel-ai-analyst/
├── SKILL.md                          Core methodology for AI, about 650 lines
├── README.md                         Two-minute quick start
├── docs/index.html                   Complete single-file offline documentation
├── scripts/excel_ai.py               One-file tool with 18 subcommands, about 7,500 lines
├── references/                       Progressive-disclosure reference layer
│   ├── patterns/00-index.md           Nine-archetype index + four universal questions
│   ├── patterns/{payroll,finance,inventory,sales,budget,
│   │             project,production,roster,mreport}.md
│   ├── table_shapes.md                Long / wide / matrix / block handling
│   ├── check_library.md               Full semantics for 19 workbook-level checks
│   ├── spec_schema.md                 Complete spec.json field reference
│   ├── ontology_template.md           Field-ontology template
│   └── lineage_template.md            Formula-chain and lineage template
└── examples/
    ├── run_demo.sh                   Run the full workflow and dashboard in one command
    ├── make_demo_workbook.py         Four-archetype demo generator with one seeded issue per sheet
    ├── spec_{payroll,inventory,budget}.json
    └── payroll_case.md               Complete walkthrough case

Internal layers of excel_ai.py

The single file has four bottom-up layers. Eighteen cmd_* entry points are dispatched through argparse subcommands:

┌─ Command layer ─────────────────────────────────────────────────────┐
│ main() → 18 argparse subcommands                                   │
│   Core six   profile / tomd / scaffold / verify / output / analyze │
│   Extensions graph / dashboard / blame / sensitivity / merge       │
│   M package  links / mask / reconcile / batch / restate / questions│
│   Ontology   context (Step -1: load published ontology only)        │
├─ Domain layer ──────────────────────────────────────────────────────┤
│ profile_sheet / suggest_checks        Archetype, shape, column roles│
│ sheet_to_md / translate_formula       Markdown, formula translation │
│ run_table_checks                      Nineteen-rule library          │
│ build_provenance / find_formula_holes Cell lineage and breakpoints  │
│ build_ontology / ontology_mermaid     T-Box + attribute DAG         │
│ plan_propagation                      What-If DAG propagation        │
├─ Infrastructure layer ──────────────────────────────────────────────┤
│ safe_eval(AST allowlist) / num(conversion) / resolve(column refs)   │
│ load_rows / expr_deps(AST dependency extraction)                    │
├─ Excel adapter layer ───────────────────────────────────────────────┤
│ formula_map(data_only=False) / merged_ranges / expand_merges        │
│ join_headers / guess_header_rows / detect_data_start / detect_shape │
│ detect_blocks / is_title_row / is_total_row / cell_type             │
└─────────────────────────────────────────────────────────────────────┘

Key reuse points: safe_eval is shared by derived, checks, table_checks, analysis.rules, and what_if. The same AST traversal in expr_deps serves both DAG construction and What-If propagation.

Key algorithm details

11 · Program structureData flow

      Source Excel (.xlsx)
             │
       ┌─────┴──────┐
       ▼            ▼
   profile        tomd
       │            │
 profile.json   01_raw_md/*.md ──────► [AI cognitive work]
       │            │                   Step 2 Field ontology
       └──►scaffold─┘                   Step 3 Formula chain and lineage
              │                              │
        spec.draft.json ───[AI completes]────► spec.json  ★ core asset
                                                 │
                          ┌──────────────────────┼──────────────────┐
                          ▼                      ▼                  ▼
                       verify                 output             analyze
                          │                      │                  │
        ┌─────────────────┼──────────┐    AI-results.xlsx      analysis-report.md
        ▼                 ▼          ▼                          whatif.json
  verification-report.md         verify_result.json               │
  mismatches.csv  detail.csv          │                           │
  table_check_issues.csv              │                           │
                                      ▼                           │
                                   graph ──► ontology.json ───┐    │
                                             ontology.mmd     │    │
                                             ontology-report.md│    │
                                                              ▼    ▼
                                                          dashboard
                                                              │
                                                        dashboard.html (single file)

Artifact directory convention

00_profile/     Step 0  Profile report + profile.json
01_raw_md/      Step 1  Structured Markdown + index
02_ontology/    Step 2/3 Ontology, formula chain, lineage (AI authored)
03_verify/      Step 4  Report + row detail + workbook exceptions + verify_result.json
05_graph/       Step 4b ontology.json + ontology.mmd + ontology-report.md
04_output/      Step 5  Result Excel + analysis report + whatif.json
06_dashboard/   Step 6  dashboard.html (single shareable file)
07_blame/       E1      Reverse fault-isolation report
08_sensitivity/ E1      Sensitivity-ranking report
09_merge/       E1      Cross-workbook ontology merge candidates
10_links/       M       External-link graph with broken and circular references
11_masked/      M       Shareable structure package + MANIFEST self-check; raw occurrences must be 0
12_reconcile/   M       Two-definition reconciliation: routing layer + attribution candidates
13_questions/   M       Open-question list for business experts
spec.json               Formal AI interpretation of the business logic

Never modify the user's original workbook. Write every artifact to a separate output directory.

12 · Program structureData contract: spec.json

spec.json is the only interface between AI and the tool. Minimal skeleton:

{
  "workbook": "/abs/path/workbook.xlsx",
  "sheet": "Payroll Detail",
  "header_rows": 4,                              // Header depth; one wrong row invalidates the model
  "keys":       {"ID": "Employee ID", "NAME": "Name"}, // Column label or zero-based index
  "dimensions": {"DEPT": "Level 1 Department"},
  "fields":     {"A": "Base Salary A", "B1": 36, "G_x": "Gross Pay G"},
  "derived":    {"B": "B1+B2+B3", "C": "C1+C2"}, // Evaluated in declaration order (topological order)
  "checks": [
    {"name": "Gross Pay G", "target": "G_x",     // target = existing Excel result column
     "expr": "round(A+B-C+D+E-F, 2)", "tolerance": 0.01}
  ],
  "table_checks": [{"type": "unique", "name": "Employee ID unique", "fields": ["ID"]}],
  "skip_when": {"empty": ["ID"], "label_in": ["Summary", "Subtotal", "Total"]}
}

Complete field reference

Column-reference rules

"fields": {
  "A":   "Base Salary A", // String: exact match first, then containment, against joined headers
  "B1":  36,              // Integer: zero-based column index, the safest option
  "G_x": "Gross Pay G"    // Existing Excel result column compared with the AI result
}

If a string matches multiple columns, fail immediately and list the candidates. This is deliberate: silently selecting the first match can verify the wrong column without any warning.

Expression syntax and security boundary

derived, checks[].expr, the left/right fields in table_checks, analysis.rules[].when, and what_if[].set share one restricted evaluator.

Numeric-conversion rules

Common expression patterns

"expr": "round(A + B - C, 2)"                       // Keep two decimal places
"expr": "ACT/TGT if TGT else 0"                     // Divide-by-zero guard
"expr": "0.10 if ACH >= 1.2 else (0.08 if ACH >= 1.0 else (0.05 if ACH >= 0.8 else 0))"
"expr": "min(max(BASE, 3500), 28000) * 0.08"        // Lower and upper bounds
"when": "COEF == 0 and B1 > 0"                      // Business-rule exception

13 · UsageThree ways to use it

14 · UsageCommand-line reference

Extensions: E1 logic layer

Management-reporting package (M) · One workflow, many workbooks

Ontology awareness (E7)

scaffold --context writes published rules into table_checks automatically and aligns business aliases to the current workbook using the column mappings saved during preservation. The analyst writes no configuration. Unresolved aliases are named explicitly and never guessed, because a guessed rule could test the wrong column and still return a clean ✅.

# Step 0-1 · Profile and convert to Markdown
python3 scripts/excel_ai.py profile workbook-a.xlsx workbook-b.xlsx -o ./00_profile
python3 scripts/excel_ai.py tomd    workbook-a.xlsx workbook-b.xlsx -o ./01_raw_md

# Step 1b · Generate a draft; optional, and must be completed by a person or AI
python3 scripts/excel_ai.py scaffold workbook-a.xlsx --sheet Detail -o spec.draft.json

# Step 4 · Verify: exit 0 pass / 1 issues found / 2 refused because of TODO, alias, or rule configuration
python3 scripts/excel_ai.py verify spec.json -o ./03_verify

# Step 4b · Ontology graph
python3 scripts/excel_ai.py graph spec1.json spec2.json --verify ./03_a ./03_b -o ./05_graph

# Step 5 · Deliver
python3 scripts/excel_ai.py output  spec.json -d ./03_verify/verification_detail.csv -o ./04_output
python3 scripts/excel_ai.py analyze spec.json -d ./03_verify/verification_detail.csv -o ./04_output

# Step -1 · Load published ontology and inject rules for zero-config reuse next period
python3 scripts/excel_ai.py context  --registry ./registry --domain mreport -o ./ontology
python3 scripts/excel_ai.py scaffold management-report.xlsx --context ./ontology/context.json -o ./specs

# Management reporting · many workbooks; inspect external links first
python3 scripts/excel_ai.py links management-report.xlsx financial-report.xlsx -o ./10_links
python3 scripts/excel_ai.py mask  management-report.xlsx financial-report.xlsx --anonymize-columns -o ./11_masked

# Step 6 · Dashboard
python3 scripts/excel_ai.py dashboard \
  --verify ./03_a ./03_b --ontology ./05_graph/ontology.json \
  --whatif ./04_a/whatif.json --profile ./00_profile/profile.json --raw-md ./01_raw_md \
  --title "Example Project · Excel Reverse-Engineering Dashboard" -o ./06_dashboard/dashboard.html

Automatic What-If propagation through the DAG

The old form required a person to write recompute: ["B","C","D"]. Omit one item and the result is silently wrong. Now only set is required. The script decides which intermediate values to recalculate, selects affected result columns automatically, and reports every unverifiable path as unable to simulate instead of forcing a result.

## What-If: increase base salary by 10%
**Changed input**: `A`
**Automatically recalculated intermediate values** in topological order: `INCOME`
> ⚠️ The following path cannot be simulated and is excluded instead of forced:
>   `R_net`: net pay needs cumulative tax withholding, but the monthly workbook lacks cross-period state
Gross Pay E total: 152,517.24 → 165,824.50 (+13,307.26)

This changes What-If from “it may calculate incorrectly” to “it tells you when it cannot calculate.”

15 · Run guideInstall and run end to end

Dependencies

# Required
pip3 install pandas openpyxl        # Some systems require --break-system-packages

# Strongly recommended for formula-cache recalculation and legacy .xls conversion
# macOS:   brew install --cask libreoffice
# Ubuntu:  apt install libreoffice-calc
# Windows: download the installer from the official website

See the complete workflow with one command

cd excel-ai-analyst
bash examples/run_demo.sh ./demo_run
open ./demo_run/06_dashboard/dashboard.html

The script generates the demo workbook → profiles and converts it to Markdown → writes three specs → verifies them with cell-level provenance → builds the ontology graph → runs analysis and What-If → creates the web dashboard → and finally prints the result of each of the five seeded-issue detection paths.

Use it on your own workbook in nine steps

1. Run profile, then read the matching archetype under references/patterns/
2. If the structure is not long, read the relevant section of references/table_shapes.md and preprocess first
3. Run tomd and understand the header structure, input columns, result columns, and check columns
4. Optionally run scaffold, then complete the draft: correct aliases, resolve TODOs, and add table_checks
5. Write the field ontology and formula chain from the templates in references/; this step cannot be automated
6. Run verify on the full dataset. The workbook is understood only at 100% row-level pass and zero unexplained workbook-level exceptions
7. Run graph to lift the spec into an object graph and attribute DAG
8. Run output + analyze for deliverables and DAG-propagated What-If
9. Run dashboard to create a single web file that can be sent directly to colleagues

Rerun each month

sed -i 's|current-month-payroll|next-month-payroll|' spec.json
python3 scripts/excel_ai.py verify spec.json -o ./03_verify && \
python3 scripts/excel_ai.py output spec.json -d ./03_verify/verification_detail.csv -o ./04_output

16 · Run guideAcceptance: all five detection paths must hit

The demo workbook multi_type.xlsx has four sheets covering four structures and archetypes. Each sheet contains one seeded issue common in real business workbooks. Acceptance requires all four issues to be found and all five detection paths to hit. Paths ① and ⑤ detect the same issue: H7 was replaced by a constant. One issue found by two independent paths demonstrates defense in depth; it must not be counted as two issues.

If any path misses, the script, spec, or environment has a problem.

Measured baseline from the v3 demo workbook

Key payroll location: row 7, csid1003, has gross pay AI=9745.14 / Excel=9865.14 / difference −120.00. Formula breakpoint H7, with formulas in 11 of 12 cells in the column, is the root-cause location.

Ontology-graph baseline: 4 object types · 1 link · 8 functions, 2 verified · 32 attribute-DAG nodes / 35 edges · 1 path marked unable to simulate.

Workflow-level acceptance: nine checks

Final report: a plain-language conclusion with an owner

This review covered 3 business sheets, referenced 1 master-data sheet, included 35 records and 58 validation points, and produced row-level pass rates of 91.67% / 100% / 80.00%. It found 4 issues:

1. Gross pay for csid1003 was manually overwritten and is 120 too high; the row formula was replaced by a fixed value. Confirm whether this was an approved supplemental payment. Owner: payroll specialist.
2. “Strategy” is absent from department master data, but 4 employees are assigned to it, preventing cost allocation to a valid cost center. Owner: HR master-data manager.
3. SKU-B has a 37-unit gap between period 3 closing and period 4 opening inventory, indicating an unrecorded gain or transfer. Owner: warehouse operations.
4. The Actual Total formula in the lease-expense row omits June and understates the result by 9,310.24. Owner: budget preparer.

Issues 1 and 4 are calculation errors in the workbook that directly affect reported numbers. Issues 2 and 3 are data-management process problems that require additional controls.

17 · Run guideFAQ and common pitfalls

General pitfall checklist

18Deliverables and roadmap

Deliverables from one analysis

Version evolution

This table ends at v3; it is not the current state. Later historical deliveries added the E1 logic layer (blame / sensitivity / merge), management-reporting package M, and the ontology registry. That preserved method baseline reached 18 subcommands, 7 registry commands, 9 archetype libraries, and 19 workbook-level check types. The current stable authority is the 1.34 five-Skill product described on the system architecture page.

Roadmap