working-with-spreadsheets

This skill provides a structured framework for programmatically managing Excel files using Python, focusing on precision, readability, and financial modeling best practices. It is designed for developers and financial analysts who need to automate the creation, modification, and data analysis of .xlsx files while ensuring that logic remains transparent through the use of native Excel formulas rather than hardcoded Python results. By leveraging libraries such as openpyxl for cell-level manipulation and pandas for efficient data processing, the skill enables consistent file generation that meets professional audit standards.

• Automated Excel file generation and modification with full support for formulas, styles, and data validation.

• Integration of strict financial modeling standards, including color-coded cell conventions for inputs, formulas, cross-sheet links, and external file references.

• Advanced formatting capabilities for currency, percentages, and custom valuation multiples to ensure outputs are report-ready.

• Support for data analysis workflows using pandas to read and process large datasets before writing to structured Excel workbooks.

• Integrated formula verification and debugging using custom scripts like recalc.py to identify errors such as #REF!, #DIV/0!, and #VALUE! before finalizing files.

• Efficient memory management techniques for large files, including read_only and write_only modes for performance optimization.

• Always prioritize Excel formulas (=SUM, =IF, etc.) over Python-calculated values to maintain model flexibility.

• Adhere to color coding: blue for inputs, black for calculations, and green for cross-sheet references to maximize readability.

• Use pandas for initial data ingestion and validation, then transition to openpyxl for final styling and formula injection.

• Regularly run the included verification script to check for formula errors and stale cell references.

• Be mindful of common pitfalls such as zero-division errors, data type mismatches, and incorrect cross-sheet link syntax.