Excel Auditor

Excel Auditor

Analyze unknown Excel files to understand purpose, audit formulas, detect errors, and generate documentation.

Core Workflow

1. Extract Structure

Run the structure extraction script on the uploaded file:

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

This produces JSON with: sheets, named ranges, tables, external links, data validation rules, conditional formatting, and VBA presence.

2. Extract Formulas

Run formula extraction to build dependency graph:

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:

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]"