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

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

total = df["Sales"].sum()
sheet["B10"] = total  # Hardcodes 5000

✅ CORRECT

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):

   python scripts/recalc.py output.xlsx
   

6. Verify and fix any errors

Creating new Excel files

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

from openpyxl import load_workbook

wb = load_workbook("existing.xlsx")
sheet = wb.active
sheet["A1"] = "New Value"
wb.save("modified.xlsx")

Recalculating formulas

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)