Generate and Validate Excel Files with Formulas

This XLSX skill guides the creation, editing, and analysis of Excel workbooks, with particular requirements for financial models. It separates data analysis from workbook-specific editing: pandas handles analysis, bulk operations, and simple exports, while openpyxl handles formulas, formatting, and Excel features. The workflow is to choose the tool, create or load a workbook, modify it, save it, recalculate formulas, and inspect the result for errors.

Calculated results should remain Excel formulas rather than numbers computed in Python and written as fixed values. This applies to totals, percentages, ratios, differences, and other calculations, so the workbook can update when source data changes. Assumptions such as growth rates, margins, and multiples belong in separate cells referenced by formulas. Hardcoded inputs need source documentation in comments or adjacent cells, with the system or document, date, specific reference, and a URL when applicable.

Use the skill for an XLSX creation, modification, or analysis request, including financial workbooks that need consistent formulas and presentation. When updating an established template, study and match its formatting, style, and conventions. Existing template rules and explicit user instructions override the default formatting guidance.

A workbook containing formulas needs recalculation before delivery. Openpyxl preserves formulas as strings but does not evaluate them. The supplied recalc.py workflow uses LibreOffice, recalculates every sheet, scans cells for Excel errors, and returns JSON with counts and locations. The guide requires fixing reported errors and recalculating again, with zero formula errors as the delivery requirement.

python recalc.py output.xlsx

Environment-specific validation and expert review still matter. Missing inputs, permissions, safety boundaries, or success criteria call for clarification before proceeding.

Inputs can be an existing workbook, source data, a template, assumptions, or requested formulas and formatting. Outputs are saved Excel files with the requested data and workbook structure, plus recalculation diagnostics when formulas are used. Workbook operations include reading one or all sheets, inspecting data, exporting data, creating a workbook, styling cells, adjusting column widths, editing cells, inserting rows, deleting columns, and adding sheets.

For financial models, the default colors distinguish hardcoded inputs in blue, formulas in black, links to other worksheets in green, external-file links in red, and assumptions needing attention with a yellow background. Number formatting keeps years as text, identifies currency units in headers, displays zeros as dashes, uses one decimal for percentages and valuation multiples, and puts negative numbers in parentheses.

Verification includes correct cell references, range boundaries, consistent projection formulas, and unintended circular references. Start with sample cells before applying formulas broadly. Check column mapping, spreadsheet row offsets, null values, repeated matches, cross-sheet references, and denominators. Test zero, negative, and very large inputs rather than relying on a typical case alone.

The workflow uses pandas, openpyxl, LibreOffice, and the provided recalc.py script. Recalculation support is described for Linux and macOS. Its JSON output distinguishes success from errors_found and identifies affected cells through an error summary.

Openpyxl uses one-based cell indices. Its data_only option reads cached calculated values, but saving a workbook loaded that way loses the formulas. For large workbooks, the guide points to read-only or write-only modes. With pandas, explicit data types help preserve identifiers, selected columns limit reads, and date parsing handles date fields. Workbook comments should explain complex formulas, important assumptions, sources, and key model calculations.