Excel File Modeling and Analysis

Problem to solve

When generating .xlsx files with Python, the hard part is often not writing cells but producing a file that remains professional and maintainable: values are hardcoded instead of formula-driven, cross-sheet links are not visually distinguished, hard inputs lack source notes, and the workbook opens with errors such as #REF!, #DIV/0!, or #VALUE!. For financial models, formatting and reference conventions also matter for later audit, updates, and collaboration.

How the skill works

The skill separates Excel work into data analysis, workbook editing, and formula verification:
- Use pandas to read, clean, and batch-process data, especially for read_excel, column filtering, and type constraints;
- Use openpyxl to write formulas, colors, and number formats while preserving dynamic Excel calculations;
- Run scripts/recalc.py after saving to recalculate through LibreOffice and return a JSON error summary, then fix issues by error type such as #REF! or #DIV/0!.

It favors formulas over Python-precomputed constants, for example moving growth rates into standalone assumption cells and referencing them with =B5*(1+$B$6). Financial output follows color conventions: blue for user inputs, black for formulas, green for cross-sheet links within the workbook, red for links to external files, and yellow highlighting for key assumptions. Number formatting is also enforced, such as years as text, currency with units, zeros rendered as -, and percentages using 0.0%.

Scope and caveats

The skill is aimed at deliverable workbooks and financial-model quality, making it useful for creating reports, updating templates, analyzing data, and building formula-driven calculations. When modifying an existing template, the original layout, naming, and formatting take priority; a generic standard should not override established team conventions. With openpyxl, avoid opening a file with data_only=True and then saving it, because formulas can be permanently replaced by values. Hardcoded numbers should also carry source, date, and specific reference notes in the workbook.