lidge-jun/xlsx_original

Requirements for Outputs

All Excel files

Professional Font

• Use a consistent, professional font (e.g., Arial, Times New Roman) unless otherwise instructed

Zero Formula Errors

• Deliver with zero formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)

Preserve Existing Templates

• Study and match existing format, style, and conventions when modifying files
• Existing template conventions override these guidelines

Financial models

Color Coding Standards

Unless otherwise stated by the user or existing template:

• Blue text (0,0,255): Hardcoded inputs and scenario-adjustable numbers
• Black text (0,0,0): Formulas and calculations
• Green text (0,128,0): Links pulling from other worksheets within same workbook
• Red text (255,0,0): External links to other files
• Yellow background (255,255,0): Key assumptions needing attention

Number Formatting Standards

• Years: Format as text strings ("2024" not "2,024")
• Currency: Use $#,##0 format; specify units in headers ("Revenue ($mm)")
• Zeros: Display as "-" 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 (growth rates, margins, multiples) in separate cells; use cell references instead of hardcoded values in formulas
  • Example: Use =B5*(1+$B$6) instead of =B5*1.05
• Verify all cell references, check for off-by-one errors in ranges
• Ensure consistent formulas across all projection periods
• Test with edge cases (zero values, negative numbers)
• Comment hardcoded values with source: "Source: [System], [Date], [Reference], [URL if applicable]"

XLSX creation, editing, and analysis

Use Formulas, Not Hardcoded Values

Use Excel formulas instead of calculating values in Python and hardcoding them — this keeps the spreadsheet dynamic and updateable.

❌ Wrong — hardcoded

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

✅ Correct — formula

sheet['B10'] = '=SUM(B2:B9)'
sheet['C5'] = '=(C4-C2)/C2'
sheet['D20'] = '=AVERAGE(D2:D19)'

This applies to all calculations — totals, percentages, ratios, differences.

Common Workflow

1. Choose tool: pandas for data analysis, 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 (when using formulas): python scripts/recalc.py output.xlsx
6. Verify and fix errors:
   • The script returns JSON with error details
   • If status is errors_found, check error_summary for specific error types and locations
   • Fix identified errors and recalculate again

LibreOffice is available for recalculation via scripts/recalc.py. The script auto-configures LibreOffice on first run.

Creating new Excel files

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
sheet = wb.active
sheet['A1'] = 'Hello'
sheet.append(['Row', 'of', 'data'])
sheet['B2'] = '=SUM(A1:A10)'
sheet['A1'].font = Font(bold=True, color='FF0000')
sheet['A1'].fill = PatternFill('solid', start_color='FFFF00')
sheet.column_dimensions['A'].width = 20
wb.save('output.xlsx')

Editing existing Excel files

from openpyxl import load_workbook

wb = load_workbook('existing.xlsx')
sheet = wb.active  # or wb['SheetName']
sheet['A1'] = 'New Value'
sheet.insert_rows(2)
new_sheet = wb.create_sheet('NewSheet')
wb.save('modified.xlsx')

Recalculating formulas

python scripts/recalc.py <excel_file> [timeout_seconds]

The script:

• Recalculates all formulas in all sheets
• Scans all cells for Excel errors (#REF!, #DIV/0!, etc.)
• Returns JSON with detailed error locations and counts

Formula Verification Checklist

• [ ] Test 2-3 sample references before building full model
• [ ] Confirm column mapping (e.g., column 64 = BL, not BK)
• [ ] Account for row offset (DataFrame row 5 = Excel row 6)
• [ ] Handle NaN with pd.notna()
• [ ] Check denominators before / in formulas
• [ ] Verify cross-sheet references use correct format (Sheet1!A1)
• [ ] Start small: test formulas on 2-3 cells before applying broadly

Interpreting recalc.py Output

{
  "status": "success",
  "total_errors": 0,
  "total_formulas": 42,
  "error_summary": {}
}

Library Selection

• pandas: Data analysis, bulk operations, simple data export
• openpyxl: Complex formatting, formulas, Excel-specific features

openpyxl tips

• Cell indices are 1-based (row=1, column=1 = A1)
• Use data_only=True to read calculated values — but saving afterward replaces formulas with values permanently
• For large files: use read_only=True or write_only=True

pandas tips

• Specify data types to avoid inference issues: pd.read_excel('file.xlsx', dtype={'id': str})
• For large files: pd.read_excel('file.xlsx', usecols=['A', 'C', 'E'])
• Handle dates: pd.read_excel('file.xlsx', parse_dates=['date_column'])