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
Industry-Standard Color Conventions
- 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
Required Format Rules
- 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
Assumptions Placement
- Place ALL assumptions (growth rates, margins, multiples, etc.) in separate assumption cells
- Use cell references instead of hardcoded values in formulas
- Example: Use =B5*(1+$B$6) instead of =B5*1.05
Formula Error Prevention
- Verify all cell references are correct
- Check for off-by-one errors in ranges
- Ensure consistent formulas across all projection periods
- Test with edge cases (zero values, negative numbers)
- Verify no unintended circular references
Documentation Requirements for Hardcodes
- Comment or in cells beside (if end of table). Format: "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]"
- Examples:
- "Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]"
- "Source: Company 10-Q, Q2 2025, Exhibit 99.1, [SEC EDGAR URL]"
- "Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity"
- "Source: FactSet, 8/20/2025, Consensus Estimates Screen"
XLSX creation, editing, and analysis
Overview
A 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.
Important Requirements
LibreOffice Required for Formula Recalculation: You can assume LibreOffice is installed for recalculating formula values using the scripts/recalc.py script. The script automatically configures LibreOffice on first run, including in sandboxed environments where Unix sockets are restricted (handled by scripts/office/soffice.py)
CSV Encoding Rules
CRITICAL: Always use utf-8-sig (UTF-8 with BOM) when writing CSV files that may be opened in Excel (any platform).
Excel identifies a CSV file's encoding by checking for a BOM at the start of the file. Without a BOM, Excel falls back to the system locale encoding (GBK on Chinese Windows), which causes Chinese characters to display as garbled text.
❌ WRONG - Missing encoding or BOM
df.to_csv('output.csv') # Uses system locale — unreliable cross-platform
df.to_csv('output.csv', encoding='utf-8') # No BOM — Excel opens as GBK, Chinese becomes garbage
✅ CORRECT - UTF-8 with BOM (for Excel)
df.to_csv('output.csv', encoding='utf-8-sig') # BOM included — Excel recognises UTF-8 correctly
macOS Compatibility Warning
utf-8-sig works correctly with Excel for Mac and LibreOffice Calc on macOS, but has known issues with other macOS tools:
- macOS Numbers.app: Older versions may show the BOM as a visible
 prefix in the first cell of the first row. If the output CSV is intended for Numbers, use plain utf-8 instead and instruct the user to open it via File > Open to select encoding manually.
- macOS command-line tools (
cat, awk, grep, sort, etc.): The 3-byte BOM (\xef\xbb\xbf) will appear at the start of the first line, breaking pattern matches and column splits that target column 1.
- Python
read_csv with encoding='utf-8': The BOM appears as \ufeff prepended to the first column name, causing column-name mismatches. Always read back with encoding='utf-8-sig' to strip the BOM automatically.
Decision guide — which encoding to use when writing CSV:
| Target consumer |
Encoding |
| Excel (Windows or Mac) |
utf-8-sig |
| Excel for Mac + LibreOffice Calc |
utf-8-sig |
| macOS Numbers.app |
utf-8 (no BOM) |
| macOS / Linux command-line tools or scripts |
utf-8 (no BOM) |
| Unknown / general purpose |
utf-8-sig (safest for human users) |
If the target is unknown, prefer utf-8-sig — it is transparent to most modern applications and essential for Windows Excel.
Reading CSV files
When reading a CSV that may have been created on a Chinese Windows system, detect the encoding first:
import chardet
with open('input.csv', 'rb') as f:
raw = f.read()
encoding = chardet.detect(raw)['encoding'] or 'utf-8'
df = pd.read_csv('input.csv', encoding=encoding)
When reading a CSV that was written with utf-8-sig, use the matching encoding to strip the BOM:
df = pd.read_csv('input.csv', encoding='utf-8-sig') # BOM stripped automatically
Rule summary
| Scenario |
Required encoding |
| Writing CSV for Excel (any platform) |
utf-8-sig |
| Writing CSV for Numbers.app (macOS) |
utf-8 (no BOM) |
| Writing CSV for command-line / scripts |
utf-8 (no BOM) |
| Writing CSV for unknown / general use |
utf-8-sig |
| Reading CSV from unknown source |
detect with chardet, fallback to utf-8 |
| Reading CSV known to be UTF-8 with BOM |
utf-8-sig |
| Reading CSV from Chinese Windows |
gbk or gb18030 |
Reading and analyzing data
Data analysis with pandas
For data analysis, visualization, and basic operations, use pandas which provides powerful data manipulation capabilities:
import pandas as pd
# Read Excel
df = pd.read_excel('file.xlsx') # Default: first sheet
all_sheets = pd.read_excel('file.xlsx', sheet_name=None) # All sheets as dict
# Analyze
df.head() # Preview data
df.info() # Column info
df.describe() # Statistics
# Write Excel
df.to_excel('output.xlsx', index=False)
Excel File Workflows
CRITICAL: Use Formulas, Not Hardcoded Values
Always use Excel formulas instead of calculating values in Python and hardcoding them. This ensures the spreadsheet remains dynamic and updateable.
❌ WRONG - Hardcoding Calculated Values
# Bad: Calculating in Python and hardcoding result
total = df['Sales'].sum()
sheet['B10'] = total # Hardcodes 5000
# Bad: Computing growth rate in Python
growth = (df.iloc[-1]['Revenue'] - df.iloc[0]['Revenue']) / df.iloc[0]['Revenue']
sheet['C5'] = growth # Hardcodes 0.15
# Bad: Python calculation for average
avg = sum(values) / len(values)
sheet['D20'] = avg # Hardcodes 42.5
✅ CORRECT - Using Excel Formulas
# Good: Let Excel calculate the sum
sheet['B10'] = '=SUM(B2:B9)'
# Good: Growth rate as Excel formula
sheet['C5'] = '=(C4-C2)/C2'
# Good: Average using Excel function
sheet['D20'] = '=AVERAGE(D2:D19)'
This applies to ALL calculations - totals, percentages, ratios, differences, etc. The spreadsheet should be able to recalculate when source data changes.
Common Workflow
- Choose tool: pandas for data, openpyxl for formulas/formatting
- Create/Load: Create new workbook or load existing file
- Modify: Add/edit data, formulas, and formatting
- Save: Write to file
- Recalculate formulas (MANDATORY IF USING FORMULAS): Use the scripts/recalc.py script
python scripts/recalc.py output.xlsx
- Verify and fix any errors:
- The script returns JSON with error details
- If
status is errors_found, check error_summary for specific error types and locations
- Fix the identified errors and recalculate again
- Common errors to fix:
#REF!: Invalid cell references
#DIV/0!: Division by zero
#VALUE!: Wrong data type in formula
#NAME?: Unrecognized formula name
Creating new Excel files
# Using openpyxl for formulas and formatting
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
wb = Workbook()
sheet = wb.active
# Add data
sheet['A1'] = 'Hello'
sheet['B1'] = 'World'
sheet.append(['Row', 'of', 'data'])
# Add formula
sheet['B2'] = '=SUM(A1:A10)'
# Formatting
sheet['A1'].font = Font(bold=True, color='FF0000')
sheet['A1'].fill = PatternFill('solid', start_color='FFFF00')
sheet['A1'].alignment = Alignment(horizontal='center')
# Column width
sheet.column_dimensions['A'].width = 20
wb.save('output.xlsx')
Editing existing Excel files
# Using openpyxl to preserve formulas and formatting
from openpyxl import load_workbook
# Load existing file
wb = load_workbook('existing.xlsx')
sheet = wb.active # or wb['SheetName'] for specific sheet
# Working with multiple sheets
for sheet_name in wb.sheetnames:
sheet = wb[sheet_name]
print(f"Sheet: {sheet_name}")
# Modify cells
sheet['A1'] = 'New Value'
sheet.insert_rows(2) # Insert row at position 2
sheet.delete_cols(3) # Delete column 3
# Add new sheet
new_sheet = wb.create_sheet('NewSheet')
new_sheet['A1'] = 'Data'
wb.save('modified.xlsx')
Recalculating formulas
Excel files created or modified by openpyxl contain formulas as strings but not calculated values. Use the provided scripts/recalc.py script to recalculate formulas:
python scripts/recalc.py <excel_file> [timeout_seconds]
Example:
python scripts/recalc.py output.xlsx 30
The script:
- Automatically sets up LibreOffice macro on first run
- Recalculates all formulas in all sheets
- Scans ALL cells for Excel errors (#REF!, #DIV/0!, etc.)
- Returns JSON with detailed error locations and counts
- Works on both Linux and macOS
Formula Verification Checklist
Quick checks to ensure formulas work correctly:
Essential Verification
Common Pitfalls
Formula Testing Strategy
Interpreting scripts/recalc.py Output
The script returns JSON with error details:
{
"status": "success", // or "errors_found"
"total_errors": 0, // Total error count
"total_formulas": 42, // Number of formulas in file
"error_summary": { // Only present if errors found
"#REF!": {
"count": 2,
"locations": ["Sheet1!B5", "Sheet1!C10"]
}
}
}
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 (row=1, column=1 refers to cell A1)
- Use
data_only=True to read calculated values: load_workbook('file.xlsx', data_only=True)
- Warning: If opened with
data_only=True and saved, formulas are replaced with values and permanently lost
- For large files: Use
read_only=True for reading or write_only=True for writing
- Formulas are preserved but not evaluated - use scripts/recalc.py to update values
Working with pandas
- Specify data types to avoid inference issues:
pd.read_excel('file.xlsx', dtype={'id': str})
- For large files, read specific columns:
pd.read_excel('file.xlsx', usecols=['A', 'C', 'E'])
- Handle dates properly:
pd.read_excel('file.xlsx', parse_dates=['date_column'])
Code Style Guidelines
IMPORTANT: When generating Python code for Excel operations:
- Write minimal, concise Python code without unnecessary comments
- Avoid verbose variable names and redundant operations
- Avoid unnecessary print statements
For Excel files themselves:
- Add comments to cells with complex formulas or important assumptions
- Document data sources for hardcoded values
- Include notes for key calculations and model sections
1---
2name: xlsx
3description: "Use this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like \"the xlsx in my downloads\") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved."
4license: Proprietary. LICENSE.txt has complete terms
5metadata:
6 source: https://skillmd.com/skills/infometa/xlsx
7---
8
9# Requirements for Outputs
10
11## All Excel files
12
13### Professional Font
14- Use a consistent, professional font (e.g., Arial, Times New Roman) for all deliverables unless otherwise instructed by the user
15
16### Zero Formula Errors
17- Every Excel model MUST be delivered with ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)
18
19### Preserve Existing Templates (when updating templates)
20- Study and EXACTLY match existing format, style, and conventions when modifying files
21- Never impose standardized formatting on files with established patterns
22- Existing template conventions ALWAYS override these guidelines
23
24## Financial models
25
26### Color Coding Standards
27Unless otherwise stated by the user or existing template
28
29#### Industry-Standard Color Conventions
30- **Blue text (RGB: 0,0,255)**: Hardcoded inputs, and numbers users will change for scenarios
31- **Black text (RGB: 0,0,0)**: ALL formulas and calculations
32- **Green text (RGB: 0,128,0)**: Links pulling from other worksheets within same workbook
33- **Red text (RGB: 255,0,0)**: External links to other files
34- **Yellow background (RGB: 255,255,0)**: Key assumptions needing attention or cells that need to be updated
35
36### Number Formatting Standards
37
38#### Required Format Rules
39- **Years**: Format as text strings (e.g., "2024" not "2,024")
40- **Currency**: Use $#,##0 format; ALWAYS specify units in headers ("Revenue ($mm)")
41- **Zeros**: Use number formatting to make all zeros "-", including percentages (e.g., "$#,##0;($#,##0);-")
42- **Percentages**: Default to 0.0% format (one decimal)
43- **Multiples**: Format as 0.0x for valuation multiples (EV/EBITDA, P/E)
44- **Negative numbers**: Use parentheses (123) not minus -123
45
46### Formula Construction Rules
47
48#### Assumptions Placement
49- Place ALL assumptions (growth rates, margins, multiples, etc.) in separate assumption cells
50- Use cell references instead of hardcoded values in formulas
51- Example: Use =B5*(1+$B$6) instead of =B5*1.05
52
53#### Formula Error Prevention
54- Verify all cell references are correct
55- Check for off-by-one errors in ranges
56- Ensure consistent formulas across all projection periods
57- Test with edge cases (zero values, negative numbers)
58- Verify no unintended circular references
59
60#### Documentation Requirements for Hardcodes
61- Comment or in cells beside (if end of table). Format: "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]"
62- Examples:
63 - "Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]"
64 - "Source: Company 10-Q, Q2 2025, Exhibit 99.1, [SEC EDGAR URL]"
65 - "Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity"
66 - "Source: FactSet, 8/20/2025, Consensus Estimates Screen"
67
68# XLSX creation, editing, and analysis
69
70## Overview
71
72A 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.
73
74## Important Requirements
75
76**LibreOffice Required for Formula Recalculation**: You can assume LibreOffice is installed for recalculating formula values using the `scripts/recalc.py` script. The script automatically configures LibreOffice on first run, including in sandboxed environments where Unix sockets are restricted (handled by `scripts/office/soffice.py`)
77
78## CSV Encoding Rules
79
80**CRITICAL: Always use `utf-8-sig` (UTF-8 with BOM) when writing CSV files that may be opened in Excel (any platform).**
81
82Excel identifies a CSV file's encoding by checking for a BOM at the start of the file. Without a BOM, Excel falls back to the system locale encoding (GBK on Chinese Windows), which causes Chinese characters to display as garbled text.
83
84### ❌ WRONG - Missing encoding or BOM
85
86```python
87df.to_csv('output.csv') # Uses system locale — unreliable cross-platform
88df.to_csv('output.csv', encoding='utf-8') # No BOM — Excel opens as GBK, Chinese becomes garbage
89```
90
91### ✅ CORRECT - UTF-8 with BOM (for Excel)
92
93```python
94df.to_csv('output.csv', encoding='utf-8-sig') # BOM included — Excel recognises UTF-8 correctly
95```
96
97### macOS Compatibility Warning
98
99`utf-8-sig` works correctly with **Excel for Mac** and **LibreOffice Calc on macOS**, but has known issues with other macOS tools:
100
101- **macOS Numbers.app**: Older versions may show the BOM as a visible `` prefix in the first cell of the first row. If the output CSV is intended for Numbers, use plain `utf-8` instead and instruct the user to open it via File > Open to select encoding manually.
102- **macOS command-line tools** (`cat`, `awk`, `grep`, `sort`, etc.): The 3-byte BOM (`\xef\xbb\xbf`) will appear at the start of the first line, breaking pattern matches and column splits that target column 1.
103- **Python `read_csv` with `encoding='utf-8'`**: The BOM appears as `\ufeff` prepended to the first column name, causing column-name mismatches. Always read back with `encoding='utf-8-sig'` to strip the BOM automatically.
104
105**Decision guide — which encoding to use when writing CSV:**
106
107| Target consumer | Encoding |
108|----------------|---------|
109| Excel (Windows or Mac) | `utf-8-sig` |
110| Excel for Mac + LibreOffice Calc | `utf-8-sig` |
111| macOS Numbers.app | `utf-8` (no BOM) |
112| macOS / Linux command-line tools or scripts | `utf-8` (no BOM) |
113| Unknown / general purpose | `utf-8-sig` (safest for human users) |
114
115If the target is unknown, prefer `utf-8-sig` — it is transparent to most modern applications and essential for Windows Excel.
116
117### Reading CSV files
118
119When reading a CSV that may have been created on a Chinese Windows system, detect the encoding first:
120
121```python
122import chardet
123
124with open('input.csv', 'rb') as f:
125 raw = f.read()
126 encoding = chardet.detect(raw)['encoding'] or 'utf-8'
127
128df = pd.read_csv('input.csv', encoding=encoding)
129```
130
131When reading a CSV that was written with `utf-8-sig`, use the matching encoding to strip the BOM:
132
133```python
134df = pd.read_csv('input.csv', encoding='utf-8-sig') # BOM stripped automatically
135```
136
137### Rule summary
138
139| Scenario | Required encoding |
140|----------|------------------|
141| Writing CSV for Excel (any platform) | `utf-8-sig` |
142| Writing CSV for Numbers.app (macOS) | `utf-8` (no BOM) |
143| Writing CSV for command-line / scripts | `utf-8` (no BOM) |
144| Writing CSV for unknown / general use | `utf-8-sig` |
145| Reading CSV from unknown source | detect with `chardet`, fallback to `utf-8` |
146| Reading CSV known to be UTF-8 with BOM | `utf-8-sig` |
147| Reading CSV from Chinese Windows | `gbk` or `gb18030` |
148
149---
150
151## Reading and analyzing data
152
153### Data analysis with pandas
154For data analysis, visualization, and basic operations, use **pandas** which provides powerful data manipulation capabilities:
155
156```python
157import pandas as pd
158
159# Read Excel
160df = pd.read_excel('file.xlsx') # Default: first sheet
161all_sheets = pd.read_excel('file.xlsx', sheet_name=None) # All sheets as dict
162
163# Analyze
164df.head() # Preview data
165df.info() # Column info
166df.describe() # Statistics
167
168# Write Excel
169df.to_excel('output.xlsx', index=False)
170```
171
172## Excel File Workflows
173
174## CRITICAL: Use Formulas, Not Hardcoded Values
175
176**Always use Excel formulas instead of calculating values in Python and hardcoding them.** This ensures the spreadsheet remains dynamic and updateable.
177
178### ❌ WRONG - Hardcoding Calculated Values
179```python
180# Bad: Calculating in Python and hardcoding result
181total = df['Sales'].sum()
182sheet['B10'] = total # Hardcodes 5000
183
184# Bad: Computing growth rate in Python
185growth = (df.iloc[-1]['Revenue'] - df.iloc[0]['Revenue']) / df.iloc[0]['Revenue']
186sheet['C5'] = growth # Hardcodes 0.15
187
188# Bad: Python calculation for average
189avg = sum(values) / len(values)
190sheet['D20'] = avg # Hardcodes 42.5
191```
192
193### ✅ CORRECT - Using Excel Formulas
194```python
195# Good: Let Excel calculate the sum
196sheet['B10'] = '=SUM(B2:B9)'
197
198# Good: Growth rate as Excel formula
199sheet['C5'] = '=(C4-C2)/C2'
200
201# Good: Average using Excel function
202sheet['D20'] = '=AVERAGE(D2:D19)'
203```
204
205This applies to ALL calculations - totals, percentages, ratios, differences, etc. The spreadsheet should be able to recalculate when source data changes.
206
207## Common Workflow
2081. **Choose tool**: pandas for data, openpyxl for formulas/formatting
2092. **Create/Load**: Create new workbook or load existing file
2103. **Modify**: Add/edit data, formulas, and formatting
2114. **Save**: Write to file
2125. **Recalculate formulas (MANDATORY IF USING FORMULAS)**: Use the scripts/recalc.py script
213 ```bash
214 python scripts/recalc.py output.xlsx
215 ```
2166. **Verify and fix any errors**:
217 - The script returns JSON with error details
218 - If `status` is `errors_found`, check `error_summary` for specific error types and locations
219 - Fix the identified errors and recalculate again
220 - Common errors to fix:
221 - `#REF!`: Invalid cell references
222 - `#DIV/0!`: Division by zero
223 - `#VALUE!`: Wrong data type in formula
224 - `#NAME?`: Unrecognized formula name
225
226### Creating new Excel files
227
228```python
229# Using openpyxl for formulas and formatting
230from openpyxl import Workbook
231from openpyxl.styles import Font, PatternFill, Alignment
232
233wb = Workbook()
234sheet = wb.active
235
236# Add data
237sheet['A1'] = 'Hello'
238sheet['B1'] = 'World'
239sheet.append(['Row', 'of', 'data'])
240
241# Add formula
242sheet['B2'] = '=SUM(A1:A10)'
243
244# Formatting
245sheet['A1'].font = Font(bold=True, color='FF0000')
246sheet['A1'].fill = PatternFill('solid', start_color='FFFF00')
247sheet['A1'].alignment = Alignment(horizontal='center')
248
249# Column width
250sheet.column_dimensions['A'].width = 20
251
252wb.save('output.xlsx')
253```
254
255### Editing existing Excel files
256
257```python
258# Using openpyxl to preserve formulas and formatting
259from openpyxl import load_workbook
260
261# Load existing file
262wb = load_workbook('existing.xlsx')
263sheet = wb.active # or wb['SheetName'] for specific sheet
264
265# Working with multiple sheets
266for sheet_name in wb.sheetnames:
267 sheet = wb[sheet_name]
268 print(f"Sheet: {sheet_name}")
269
270# Modify cells
271sheet['A1'] = 'New Value'
272sheet.insert_rows(2) # Insert row at position 2
273sheet.delete_cols(3) # Delete column 3
274
275# Add new sheet
276new_sheet = wb.create_sheet('NewSheet')
277new_sheet['A1'] = 'Data'
278
279wb.save('modified.xlsx')
280```
281
282## Recalculating formulas
283
284Excel files created or modified by openpyxl contain formulas as strings but not calculated values. Use the provided `scripts/recalc.py` script to recalculate formulas:
285
286```bash
287python scripts/recalc.py <excel_file> [timeout_seconds]
288```
289
290Example:
291```bash
292python scripts/recalc.py output.xlsx 30
293```
294
295The script:
296- Automatically sets up LibreOffice macro on first run
297- Recalculates all formulas in all sheets
298- Scans ALL cells for Excel errors (#REF!, #DIV/0!, etc.)
299- Returns JSON with detailed error locations and counts
300- Works on both Linux and macOS
301
302## Formula Verification Checklist
303
304Quick checks to ensure formulas work correctly:
305
306### Essential Verification
307- [ ] **Test 2-3 sample references**: Verify they pull correct values before building full model
308- [ ] **Column mapping**: Confirm Excel columns match (e.g., column 64 = BL, not BK)
309- [ ] **Row offset**: Remember Excel rows are 1-indexed (DataFrame row 5 = Excel row 6)
310
311### Common Pitfalls
312- [ ] **NaN handling**: Check for null values with `pd.notna()`
313- [ ] **Far-right columns**: FY data often in columns 50+
314- [ ] **Multiple matches**: Search all occurrences, not just first
315- [ ] **Division by zero**: Check denominators before using `/` in formulas (#DIV/0!)
316- [ ] **Wrong references**: Verify all cell references point to intended cells (#REF!)
317- [ ] **Cross-sheet references**: Use correct format (Sheet1!A1) for linking sheets
318
319### Formula Testing Strategy
320- [ ] **Start small**: Test formulas on 2-3 cells before applying broadly
321- [ ] **Verify dependencies**: Check all cells referenced in formulas exist
322- [ ] **Test edge cases**: Include zero, negative, and very large values
323
324### Interpreting scripts/recalc.py Output
325The script returns JSON with error details:
326```json
327{
328 "status": "success", // or "errors_found"
329 "total_errors": 0, // Total error count
330 "total_formulas": 42, // Number of formulas in file
331 "error_summary": { // Only present if errors found
332 "#REF!": {
333 "count": 2,
334 "locations": ["Sheet1!B5", "Sheet1!C10"]
335 }
336 }
337}
338```
339
340## Best Practices
341
342### Library Selection
343- **pandas**: Best for data analysis, bulk operations, and simple data export
344- **openpyxl**: Best for complex formatting, formulas, and Excel-specific features
345
346### Working with openpyxl
347- Cell indices are 1-based (row=1, column=1 refers to cell A1)
348- Use `data_only=True` to read calculated values: `load_workbook('file.xlsx', data_only=True)`
349- **Warning**: If opened with `data_only=True` and saved, formulas are replaced with values and permanently lost
350- For large files: Use `read_only=True` for reading or `write_only=True` for writing
351- Formulas are preserved but not evaluated - use scripts/recalc.py to update values
352
353### Working with pandas
354- Specify data types to avoid inference issues: `pd.read_excel('file.xlsx', dtype={'id': str})`
355- For large files, read specific columns: `pd.read_excel('file.xlsx', usecols=['A', 'C', 'E'])`
356- Handle dates properly: `pd.read_excel('file.xlsx', parse_dates=['date_column'])`
357
358## Code Style Guidelines
359**IMPORTANT**: When generating Python code for Excel operations:
360- Write minimal, concise Python code without unnecessary comments
361- Avoid verbose variable names and redundant operations
362- Avoid unnecessary print statements
363
364**For Excel files themselves**:
365- Add comments to cells with complex formulas or important assumptions
366- Document data sources for hardcoded values
367- Include notes for key calculations and model sections