Requirements for Outputs

1---
2name: xlsx
3description: "Comprehensive spreadsheet creation, editing, and analysis with support for formulas, formatting, data analysis, and visualization. When Claude needs to work with spreadsheets (.xlsx, .xlsm, .csv, .tsv, etc) for: (1) Creating new spreadsheets with formulas and formatting, (2) Reading or analyzing data, (3) Modify existing spreadsheets while preserving formulas, (4) Data analysis and visualization in spreadsheets, or (5) Recalculating formulas"
4license: Proprietary. LICENSE.txt has complete terms
5---
6 
7# Requirements for Outputs
8 
9## All Excel files
10 
11### Zero Formula Errors
12- Every Excel model MUST be delivered with ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)
13 
14### Preserve Existing Templates (when updating templates)
15- Study and EXACTLY match existing format, style, and conventions when modifying files
16- Never impose standardized formatting on files with established patterns
17- Existing template conventions ALWAYS override these guidelines
18 
19## Financial models
20 
21### Color Coding Standards
22Unless otherwise stated by the user or existing template
23 
24#### Industry-Standard Color Conventions
25- **Blue text (RGB: 0,0,255)**: Hardcoded inputs, and numbers users will change for scenarios
26- **Black text (RGB: 0,0,0)**: ALL formulas and calculations
27- **Green text (RGB: 0,128,0)**: Links pulling from other worksheets within same workbook
28- **Red text (RGB: 255,0,0)**: External links to other files
29- **Yellow background (RGB: 255,255,0)**: Key assumptions needing attention or cells that need to be updated
30 
31### Number Formatting Standards
32 
33#### Required Format Rules
34- **Years**: Format as text strings (e.g., "2024" not "2,024")
35- **Currency**: Use $#,##0 format; ALWAYS specify units in headers ("Revenue ($mm)")
36- **Zeros**: Use number formatting to make all zeros "-", including percentages (e.g., "$#,##0;($#,##0);-")
37- **Percentages**: Default to 0.0% format (one decimal)
38- **Multiples**: Format as 0.0x for valuation multiples (EV/EBITDA, P/E)
39- **Negative numbers**: Use parentheses (123) not minus -123
40 
41### Formula Construction Rules
42 
43#### Assumptions Placement
44- Place ALL assumptions (growth rates, margins, multiples, etc.) in separate assumption cells
45- Use cell references instead of hardcoded values in formulas
46- Example: Use =B5*(1+$B$6) instead of =B5*1.05
47 
48#### Formula Error Prevention
49- Verify all cell references are correct
50- Check for off-by-one errors in ranges
51- Ensure consistent formulas across all projection periods
52- Test with edge cases (zero values, negative numbers)
53- Verify no unintended circular references
54 
55#### Documentation Requirements for Hardcodes
56- Comment or in cells beside (if end of table). Format: "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]"
57- Examples:
58  - "Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]"
59  - "Source: Company 10-Q, Q2 2025, Exhibit 99.1, [SEC EDGAR URL]"
60  - "Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity"
61  - "Source: FactSet, 8/20/2025, Consensus Estimates Screen"
62 
63# XLSX creation, editing, and analysis
64 
65## Overview
66 
67A user may ask you to create, edit, or analyze the contents of an .xlsx file. You have different tools and workflows available for different tasks.
68 
69## Important Requirements
70 
71**LibreOffice Required for Formula Recalculation**: You can assume LibreOffice is installed for recalculating formula values using the `recalc.py` script. The script automatically configures LibreOffice on first run
72 
73## Reading and analyzing data
74 
75### Data analysis with pandas
76For data analysis, visualization, and basic operations, use **pandas** which provides powerful data manipulation capabilities:
77 
78```python
79import pandas as pd
80 
81# Read Excel
82df = pd.read_excel('file.xlsx')  # Default: first sheet
83all_sheets = pd.read_excel('file.xlsx', sheet_name=None)  # All sheets as dict
84 
85# Analyze
86df.head()      # Preview data
87df.info()      # Column info
88df.describe()  # Statistics
89 
90# Write Excel
91df.to_excel('output.xlsx', index=False)
92```
93 
94## Excel File Workflows
95 
96## CRITICAL: Use Formulas, Not Hardcoded Values
97 
98**Always use Excel formulas instead of calculating values in Python and hardcoding them.** This ensures the spreadsheet remains dynamic and updateable.
99 
100### ❌ WRONG - Hardcoding Calculated Values
101```python
102# Bad: Calculating in Python and hardcoding result
103total = df['Sales'].sum()
104sheet['B10'] = total  # Hardcodes 5000
105 
106# Bad: Computing growth rate in Python
107growth = (df.iloc[-1]['Revenue'] - df.iloc[0]['Revenue']) / df.iloc[0]['Revenue']
108sheet['C5'] = growth  # Hardcodes 0.15
109 
110# Bad: Python calculation for average
111avg = sum(values) / len(values)
112sheet['D20'] = avg  # Hardcodes 42.5
113```
114 
115### ✅ CORRECT - Using Excel Formulas
116```python
117# Good: Let Excel calculate the sum
118sheet['B10'] = '=SUM(B2:B9)'
119 
120# Good: Growth rate as Excel formula
121sheet['C5'] = '=(C4-C2)/C2'
122 
123# Good: Average using Excel function
124sheet['D20'] = '=AVERAGE(D2:D19)'
125```
126 
127This applies to ALL calculations - totals, percentages, ratios, differences, etc. The spreadsheet should be able to recalculate when source data changes.
128 
129## Common Workflow
1301. **Choose tool**: pandas for data, openpyxl for formulas/formatting
1312. **Create/Load**: Create new workbook or load existing file
1323. **Modify**: Add/edit data, formulas, and formatting
1334. **Save**: Write to file
1345. **Recalculate formulas (MANDATORY IF USING FORMULAS)**: Use the recalc.py script
135   ```bash
136   python recalc.py output.xlsx
137   ```
1386. **Verify and fix any errors**: 
139   - The script returns JSON with error details
140   - If `status` is `errors_found`, check `error_summary` for specific error types and locations
141   - Fix the identified errors and recalculate again
142   - Common errors to fix:
143     - `#REF!`: Invalid cell references
144     - `#DIV/0!`: Division by zero
145     - `#VALUE!`: Wrong data type in formula
146     - `#NAME?`: Unrecognized formula name
147 
148### Creating new Excel files
149 
150```python
151# Using openpyxl for formulas and formatting
152from openpyxl import Workbook
153from openpyxl.styles import Font, PatternFill, Alignment
154 
155wb = Workbook()
156sheet = wb.active
157 
158# Add data
159sheet['A1'] = 'Hello'
160sheet['B1'] = 'World'
161sheet.append(['Row', 'of', 'data'])
162 
163# Add formula
164sheet['B2'] = '=SUM(A1:A10)'
165 
166# Formatting
167sheet['A1'].font = Font(bold=True, color='FF0000')
168sheet['A1'].fill = PatternFill('solid', start_color='FFFF00')
169sheet['A1'].alignment = Alignment(horizontal='center')
170 
171# Column width
172sheet.column_dimensions['A'].width = 20
173 
174wb.save('output.xlsx')
175```
176 
177### Editing existing Excel files
178 
179```python
180# Using openpyxl to preserve formulas and formatting
181from openpyxl import load_workbook
182 
183# Load existing file
184wb = load_workbook('existing.xlsx')
185sheet = wb.active  # or wb['SheetName'] for specific sheet
186 
187# Working with multiple sheets
188for sheet_name in wb.sheetnames:
189    sheet = wb[sheet_name]
190    print(f"Sheet: {sheet_name}")
191 
192# Modify cells
193sheet['A1'] = 'New Value'
194sheet.insert_rows(2)  # Insert row at position 2
195sheet.delete_cols(3)  # Delete column 3
196 
197# Add new sheet
198new_sheet = wb.create_sheet('NewSheet')
199new_sheet['A1'] = 'Data'
200 
201wb.save('modified.xlsx')
202```
203 
204## Recalculating formulas
205 
206Excel files created or modified by openpyxl contain formulas as strings but not calculated values. Use the provided `recalc.py` script to recalculate formulas:
207 
208```bash
209python recalc.py <excel_file> [timeout_seconds]
210```
211 
212Example:
213```bash
214python recalc.py output.xlsx 30
215```
216 
217The script:
218- Automatically sets up LibreOffice macro on first run
219- Recalculates all formulas in all sheets
220- Scans ALL cells for Excel errors (#REF!, #DIV/0!, etc.)
221- Returns JSON with detailed error locations and counts
222- Works on both Linux and macOS
223 
224## Formula Verification Checklist
225 
226Quick checks to ensure formulas work correctly:
227 
228### Essential Verification
229- [ ] **Test 2-3 sample references**: Verify they pull correct values before building full model
230- [ ] **Column mapping**: Confirm Excel columns match (e.g., column 64 = BL, not BK)
231- [ ] **Row offset**: Remember Excel rows are 1-indexed (DataFrame row 5 = Excel row 6)
232 
233### Common Pitfalls
234- [ ] **NaN handling**: Check for null values with `pd.notna()`
235- [ ] **Far-right columns**: FY data often in columns 50+ 
236- [ ] **Multiple matches**: Search all occurrences, not just first
237- [ ] **Division by zero**: Check denominators before using `/` in formulas (#DIV/0!)
238- [ ] **Wrong references**: Verify all cell references point to intended cells (#REF!)
239- [ ] **Cross-sheet references**: Use correct format (Sheet1!A1) for linking sheets
240 
241### Formula Testing Strategy
242- [ ] **Start small**: Test formulas on 2-3 cells before applying broadly
243- [ ] **Verify dependencies**: Check all cells referenced in formulas exist
244- [ ] **Test edge cases**: Include zero, negative, and very large values
245 
246### Interpreting recalc.py Output
247The script returns JSON with error details:
248```json
249{
250  "status": "success",           // or "errors_found"
251  "total_errors": 0,              // Total error count
252  "total_formulas": 42,           // Number of formulas in file
253  "error_summary": {              // Only present if errors found
254    "#REF!": {
255      "count": 2,
256      "locations": ["Sheet1!B5", "Sheet1!C10"]
257    }
258  }
259}
260```
261 
262## Best Practices
263 
264### Library Selection
265- **pandas**: Best for data analysis, bulk operations, and simple data export
266- **openpyxl**: Best for complex formatting, formulas, and Excel-specific features
267 
268### Working with openpyxl
269- Cell indices are 1-based (row=1, column=1 refers to cell A1)
270- Use `data_only=True` to read calculated values: `load_workbook('file.xlsx', data_only=True)`
271- **Warning**: If opened with `data_only=True` and saved, formulas are replaced with values and permanently lost
272- For large files: Use `read_only=True` for reading or `write_only=True` for writing
273- Formulas are preserved but not evaluated - use recalc.py to update values
274 
275### Working with pandas
276- Specify data types to avoid inference issues: `pd.read_excel('file.xlsx', dtype={'id': str})`
277- For large files, read specific columns: `pd.read_excel('file.xlsx', usecols=['A', 'C', 'E'])`
278- Handle dates properly: `pd.read_excel('file.xlsx', parse_dates=['date_column'])`
279 
280## Code Style Guidelines
281**IMPORTANT**: When generating Python code for Excel operations:
282- Write minimal, concise Python code without unnecessary comments
283- Avoid verbose variable names and redundant operations
284- Avoid unnecessary print statements
285 
286**For Excel files themselves**:
287- Add comments to cells with complex formulas or important assumptions
288- Document data sources for hardcoded values
289- Include notes for key calculations and model sections