Xlsx Toolkit

Xlsx Toolkit

Audit .xlsx files using the standard library only — no openpyxl required. Reads OOXML directly via zipfile + xml.etree.

Table of Contents

• Keywords
• Quick Start
• Core Workflows
• Tools
• Reference Guides
• Templates
• Best Practices

Keywords

xlsx, Excel, spreadsheet, workbook, financial model, formula audit, hidden sheets, external references, named ranges, data validation

Clarify First

Before running the audit, confirm these inputs. If any is unknown or vague, ASK — do not assume:

• [ ] Audit purpose (pre-send leak check, financial-model review, or handoff portability check) — selects the workflow and what you flag
• [ ] Recipient context (external partner, another team, their machine) — sets the tolerance for hidden sheets and external links
• [ ] Which sheets are inputs vs calculations (for model review) — drives where to focus formula-density reading

Stop rule: ask only the 2-3 that most change the output. If the user says "just draft it," proceed and list your assumptions at the top of the artifact.

Quick Start

python scripts/xlsx_auditor.py model.xlsx

Outputs: sheet count and names, hidden-sheet count, cell count per sheet, formula count per sheet, external link count, named range count, data validation rule count.

Core Workflows

Workflow 1: Pre-Send Workbook Audit

Goal: Catch the issues that embarrass the sender — leftover hidden sheets, broken external links, unused named ranges, formulas referencing local file paths.

Steps:

• Run audit
• Hidden sheets > 0 → confirm intentional or delete
• External links > 0 → verify links point to public / shared sources, not your local drive
• Named-range count anomalies (very high) → likely cruft from prior model versions; clean up
• Re-run until clean

Time Estimate: 5-10 minutes per workbook.

Workflow 2: Financial Model Review

Goal: Quantify the rough complexity of a financial model before reading cell-by-cell.

Steps:

• Run audit; capture per-sheet cell counts and formula counts
• Sheets with formula density > 70% are calculation sheets; should be well-structured
• Sheets with formula density 0-10% are inputs; should be obviously labeled
• Sheets with formula density 10-70% are mixed — easiest place for errors to hide
• Cross-reference with references/financial_model_audit_guide.md

Time Estimate: 30-60 minutes per model audit (audit + targeted reading).

Workflow 3: Workbook Handoff Check

Goal: Ensure a workbook handed off to another team or partner won't break on their machine.

Steps:

• Run audit
• External links → re-link to shared paths (OneDrive, SharePoint, S3) or hard-code values
• Custom named ranges → document if recipient is expected to extend; remove if internal
• Macros (xlsm) → audit shows non-.xlsx extension expected; convert if recipient cannot run macros
• File size > 10 MB → consider splitting or removing image / chart blobs

Time Estimate: 10-20 minutes per workbook.

Tools

xlsx_auditor.py

Reads a .xlsx file as a ZIP archive and parses OOXML directly.

python scripts/xlsx_auditor.py model.xlsx
python scripts/xlsx_auditor.py model.xlsx --json

Reports:

• Sheet list with name, hidden status, cell count, formula count, formula density %
• Total cell and formula counts
• Named ranges and their scopes
• External link references (file paths or URLs)
• Data validation rule count
• File size

Limits:

• Does not evaluate formulas. To check whether formulas are *correct*, use Excel itself or a financial-model-checker library.
• Does not read cell values for non-shared-string cells beyond counting; full value extraction requires more parsing than this tool does.

Reference Guides

• references/financial_model_audit_guide.md — Patterns for auditing financial models; common error categories; defensive structure tips

Templates

• assets/workbook_handoff_checklist.md — Pre-send xlsx sign-off checklist

Best Practices

• Hide internal-only sheets only when intended. If a sheet is hidden because it's WIP, delete it before sending.
• Avoid external links across handoffs. A formula referencing 'C:\Users\you\Desktop\old-model.xlsx' is the workbook equivalent of leaving your laptop name in the document author field.
• Name your inputs. Cells like Inputs!B7 mean nothing. Named ranges like WACC and RevenueGrowth survive structural changes.
• One model, one purpose. Workbooks that calculate, present, and serve as a database of records always end up broken.

Integration Points

• Pairs with finance/ skills for financial-model review
• Pairs with c-level-advisor/cfo-advisor for board-pack workbook review
• Used by data-analytics/ for ad-hoc analytics handoff