python scripts/extract_formulas.py /mnt/user-data/uploads/<filename>.xlsx

This produces JSON with: all formulas, cell dependencies, calculation chains, and formula complexity metrics.

2b. Validate Extraction Output

Before proceeding, verify JSON output contains expected keys:

Structure: sheets, named_ranges, tables, external_links, data_validation, conditional_formatting, vba_present
Formulas: formulas, dependencies, calculation_chain, complexity_metrics

If keys are missing or malformed, note limitations in final report.

3. Semantic Analysis

With structure and formula data, perform semantic analysis:

Purpose Detection: Infer file purpose from:

Sheet names and structure patterns
Named range naming conventions
Formula patterns (financial, statistical, lookup-heavy)
Data shapes and header labels

Pattern Recognition: Match against known archetypes (see references/patterns.md):

Financial models (DCF, budget, P&L)
Operational trackers (inventory, scheduling, CRM)
Reporting templates (dashboards, KPI rollups)
Data transformation pipelines

4. Error Detection

Identify issues in order of severity:

Category	Issues	Severity
Hard Errors	#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?, #NULL!, #NUM!; Circular references (unless intentional); Broken external links	Critical - file is broken
Soft Errors	Hardcoded values that should be inputs; Inconsistent formula patterns; Volatile function overuse (NOW, TODAY, RAND, INDIRECT, OFFSET); Missing IFERROR on lookups; Implicit intersection risks	Warning - file works but fragile
Smells	Magic numbers; Excessive nesting (>3 levels); Very long formulas (>200 chars); Mixed units without labels; Color-coded logic without legend; Hidden sheets with active dependencies	Info - maintainability concerns

5. Generate Report

Produce structured output using the template in references/report_template.md.

Output Formats

Default: Markdown report in chat On request: Generate .md or .docx file with full report On request: Annotated copy of Excel with comments on flagged cells

Handling Edge Cases

Very Large Files (>10MB):

Sample analysis of first 1000 formulas
Focus on structure and high-level patterns
Note that full audit requires sampling

Password Protected:

Cannot audit, inform user

VBA Present:

Note VBA exists but cannot audit macro logic
Flag as elevated risk for maintainability

Binary .xls Format:

Attempt conversion or note limitations

Error Response Templates

When no issues found:

"This file appears well-structured with no formula errors detected. [summary of what it does]"

When issues found:

"I found [N] issues requiring attention. The most critical: [top issue]. Full audit below."

When file is severely broken:

"This file has significant structural issues that prevent complete analysis. [list blocking issues]"

excel-auditor

Excel Auditor

Core Workflow

1. Extract Structure

2. Extract Formulas