Generate and Validate Excel Files with Formulas
Creates, edits and analyzes .xlsx files with zero formula errors, financial-model color/formatting standards, and mandatory recalculation.
Why it matters
This asset ensures the creation and modification of Excel files adhere to strict quality standards, including zero formula errors and precise formatting. It leverages Python libraries and external tools to automate the generation, recalculation, and verification of complex financial models.
Outcomes
What it gets done
Generate Excel files with correct formulas and formatting.
Recalculate formulas in existing Excel files.
Verify Excel files for formula errors and adherence to standards.
Automate the process of creating dynamic and error-free Excel spreadsheets.
Install
Add it to your toolbox
Run in your project directory:
curl -fsSL https://spark.entire.vc/get/ag-xlsx-official | bash Overview
Requirements for Outputs
A skill for creating, editing, and analyzing .xlsx files that enforces zero formula errors, live Excel formulas instead of hardcoded values, industry-standard financial-model color and number formatting, and mandatory recalculation via a bundled recalc.py/LibreOffice script. Use it whenever building or editing an Excel file programmatically, always writing dynamic formulas rather than pre-computed values and always recalculating with recalc.py before delivering the file.
What it does
This skill handles creating, editing, and analyzing .xlsx files, with every delivered Excel model required to have zero formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?) and, when modifying an existing template, to exactly match its established format and conventions rather than imposing standardized formatting.
For financial models it applies industry-standard color coding (blue text for hardcoded inputs, black for formulas/calculations, green for same-workbook cross-sheet links, red for external file links, yellow background for key assumptions needing attention) and number formatting rules: years as text strings, currency as $#,##0 with units specified in headers, zeros displayed as "-", percentages at one decimal by default, valuation multiples as 0.0x, and negative numbers in parentheses rather than with a minus sign. Formula construction rules require placing all assumptions in separate cells referenced by formulas rather than hardcoded (=B5*(1+$B$6) not =B5*1.05), verifying references and ranges, testing edge cases, checking for circular references, and documenting any hardcoded value's source in a comment formatted as "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]".
For reading and analysis it uses pandas (pd.read_excel, df.head()/info()/describe(), df.to_excel()). The critical rule for building spreadsheets is to always use Excel formulas rather than calculating values in Python and hardcoding the result, so the spreadsheet stays dynamic:
# Good: Let Excel calculate the sum
sheet['B10'] = '=SUM(B2:B9)'
# Good: Growth rate as Excel formula
sheet['C5'] = '=(C4-C2)/C2'
# Good: Average using Excel function
sheet['D20'] = '=AVERAGE(D2:D19)'
The common workflow is: choose pandas for data or openpyxl for formulas/formatting, create or load the workbook, modify data/formulas/formatting, save, and - mandatory whenever formulas are used - recalculate with python recalc.py output.xlsx, since openpyxl writes formulas as strings without computing their values. LibreOffice is required (assumed installed) for this recalculation step. The script auto-configures LibreOffice on first run, recalculates all sheets, scans every cell for Excel errors, and returns JSON with error counts and locations (e.g. #REF! at specific cell addresses) to fix and re-run against.
When to use - and when NOT to
Use it whenever a user asks to create, edit, or analyze an .xlsx file's contents, choosing pandas for bulk data analysis/export and openpyxl for formulas, formatting, and Excel-specific features. Always verify formulas on 2-3 sample cells before applying broadly, watch for 1-indexed row/column offsets between DataFrames and Excel, check for NaN values and division-by-zero before building formulas, and use the correct Sheet1!A1 syntax for cross-sheet references.
Avoid opening a workbook with data_only=True and then saving it - this permanently replaces formulas with their last-calculated values. Do not skip the mandatory recalculation step after writing formulas, and do not hardcode any calculated value in place of a live formula.
Inputs and outputs
Input is an existing .xlsx file to read/edit/analyze, or a specification for a new workbook. Output is a saved .xlsx file with dynamic Excel formulas (not pre-computed Python values), proper formatting per the financial-model conventions when applicable, and a zero-error recalculation result - recalc.py's JSON response reports status (success or errors_found), total_formulas, and, if present, an error_summary keyed by error type with cell locations.
Integrations
Uses pandas for data analysis and bulk operations, openpyxl for formula/formatting/multi-sheet work (loading with load_workbook, styling with Font/PatternFill/Alignment), and LibreOffice (via the bundled recalc.py script) for headless formula recalculation and error scanning on both Linux and macOS.
Who it's for
Anyone building or editing Excel files programmatically, especially financial models that must ship with zero formula errors, industry-standard color coding, and formulas that stay dynamic rather than hardcoded snapshots.
FAQ
Common questions
Discussion
Questions & comments · 0
Sign In Sign in to leave a comment.