xlsx

# Requirements for Outputs ## All Excel files ### Professional Font - Use a consistent, professional font (e.g., Arial, Times New Roman) for all deliverables unless otherwise instructed by the user ### Zero Formula Errors - Every Excel model MUST be delivered with ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?) ### Preserve Existing Templates (when updating templates) - Study and EXACTLY match existing format, style, and conventions when modifying files - Never impose standardized formatting on files with established patterns - Existing template conventions ALWAYS override these guidelines ## Financial models ### Color Coding Standards Unless otherwise stated by the user or existing template - **Blue text (RGB: 0,0,255)**: Hardcoded inputs, and numbers users will change for scenarios - **Black text (RGB: 0,0,0)**: ALL formulas and calculations - **Green text (RGB: 0,128,0)**: Links pulling from other worksheets within same workbook - **Red text (RGB: 255,0,0)**: External links to other files - **Yellow background (RGB: 255,255,0)**: Key assumptions needing attention or cells that need to be updated ### Number Formatting Standards - **Years**: Format as text strings (e.g., "2024" not "2,024") - **Currency**: Use $#,##0 format; ALWAYS specify units in headers ("Revenue ($mm)") - **Zeros**: Use number formatting to make all zeros "-", including percentages (e.g., "$#,##0;($#,##0);-") - **Percentages**: Default to 0.0% format (one decimal) - **Multiples**: Format as 0.0x for valuation multiples (EV/EBITDA, P/E) - **Negative numbers**: Use parentheses (123) not minus -123 ### Formula Construction Rules - Place ALL assumptions in separate assumption cells - Use cell references instead of hardcoded values in formulas - Example: Use =B5\*(1+$B$6) instead of =B5\*1.05 ## XLSX creation, editing, and analysis ### Overview A user may ask you to create, edit, or analyze the contents of an .xlsx file. ### Reading and analyzing data ```python import pandas as pd df = pd.read_excel("file.xlsx") all_sheets = pd.read_excel("file.xlsx", sheet_name=None) ``` ### CRITICAL: Use Formulas, Not Hardcoded Values **Always use Excel formulas instead of calculating values in Python and hardcoding them.** #### ❌ WRONG ```python total = df["Sales"].sum() sheet["B10"] = total # Hardcodes 5000 ``` #### ✅ CORRECT ```python sheet["B10"] = "=SUM(B2:B9)" ``` ### Common Workflow 1. **Choose tool**: pandas for data, openpyxl for formulas/formatting 2. **Create/Load**: Create new workbook or load existing file 3. **Modify**: Add/edit data, formulas, and formatting 4. **Save**: Write to file 5. **Recalculate formulas (MANDATORY IF USING FORMULAS)**: ```bash python scripts/recalc.py output.xlsx ``` 6. **Verify and fix any errors** #### Creating new Excel files ```python from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment wb = Workbook() sheet = wb.active sheet["A1"] = "Hello" sheet["B1"] = "World" sheet["B2"] = "=SUM(A1:A10)" sheet["A1"].font = Font(bold=True, color="FF0000") wb.save("output.xlsx") ``` #### Editing existing Excel files ```python from openpyxl import load_workbook wb = load_workbook("existing.xlsx") sheet = wb.active sheet["A1"] = "New Value" wb.save("modified.xlsx") ``` ### Recalculating formulas ```bash python scripts/recalc.py <excel_file> [timeout_seconds] ``` The script: - Automatically sets up LibreOffice macro on first run - Recalculates all formulas in all sheets - Scans ALL cells for Excel errors - Returns JSON with detailed error locations and counts ### Formula Verification Checklist - [ ] **Test 2-3 sample references**: Verify they pull correct values - [ ] **Column mapping**: Confirm Excel columns match (e.g., column 64 = BL) - [ ] **Row offset**: Excel rows are 1-indexed (DataFrame row 5 = Excel row 6) - [ ] **NaN handling**: Check for null values with `pd.notna()` - [ ] **Division by zero**: Check denominators before using `/` ### Best Practices #### Library Selection - **pandas**: Best for data analysis, bulk operations, and simple data export - **openpyxl**: Best for complex formatting, formulas, and Excel-specific features #### Working with openpyxl - Cell indices are 1-based - Use `data_only=True` to read calculated values - **Warning**: If opened with `data_only=True` and saved, formulas are permanently lost - Formulas are preserved but not evaluated — use `scripts/recalc.py` to update values ### Code Style Guidelines - Write minimal, concise Python code without unnecessary comments - Avoid verbose variable names and redundant operations - For Excel files: add comments to cells with complex formulas, document data sources for hardcoded values ### Dependencies - **pandas**: Data analysis and manipulation - **openpyxl**: Excel file creation and editing with formulas/formatting - **LibreOffice**: Formula recalculation (via `scripts/soffice.py`)