Requirements for Outputs
All Excel files
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
Formula Evaluation: openpyxl writes formulas as strings; calculated values are produced by Excel/WPS/LibreOffice when the file is opened. Verify formulas by reading the file back and checking that cell references point to the intended cells. (The bundled recalc.py is a deprecated placeholder that always reports unsupported — do not rely on it.)
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
- Verify formulas (MANDATORY IF USING FORMULAS): Read the saved file back and check formula correctness:
- Re-open the file with openpyxl and confirm each formula string references the intended cells (correct rows/columns/sheets)
- Cross-check 2-3 key formulas against a hand-computed expected value
- Common formula errors to watch for:
#REF!: Invalid cell references
#DIV/0!: Division by zero
#VALUE!: Wrong data type in formula
#NAME?: Unrecognized formula name
- Calculated values are produced when the user opens the file in Excel/WPS/LibreOffice
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')
Formula evaluation and verification
Excel files created or modified by openpyxl contain formulas as strings but not cached calculated values. This is expected: values are recalculated automatically when the file is opened in Excel, WPS, or LibreOffice.
Verification approach (no headless recalculation):
- Re-open the saved file with
openpyxl.load_workbook(...) and assert each key formula string matches the intended references
- Hand-compute 2-3 expected results in Python and compare against the formula logic
- If a previously calculated file must be read, use
data_only=True to load cached values (do not save from that handle — formulas would be lost)
The bundled recalc.py is a deprecated placeholder: it performs no recalculation and always returns {"status": "unsupported"} with a non-zero exit code. Do not include it in any workflow.
Formula Verification Checklist
Quick checks to ensure formulas work correctly:
Essential Verification
Common Pitfalls
Formula Testing Strategy
Read-back Verification Output
When you re-open the saved file to verify, record for each key formula:
- Cell address and formula string as saved
- Expected reference(s) and whether they match
- Hand-computed expected result vs formula logic (for 2-3 key cells)
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 by openpyxl — values appear when opened in Excel/WPS/LibreOffice
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: excel-table-processing
3version: 1.0.1
4name_en: excel-table-processing
5changelog: "1.0.1: 移除 recalc.py 强制重算步骤与 LibreOffice 依赖描述(脚本已废弃恒返回 unsupported),改为读回校验公式引用;1.0.0: 初始版本"
6description: "电子表格创建、编辑和分析,支持公式、格式、数据分析和可视化。适用于处理.xlsx、.xlsm、.csv、.tsv等表格文件:创建带公式和格式的电子表格、读取分析数据、修改保留公式、数据分析可视化"
7metadata:
8 source: https://skillmd.com/skills/ahang1598/excel-table-processing
9---
10
11# Requirements for Outputs
12
13## All Excel files
14
15### Zero Formula Errors
16- Every Excel model MUST be delivered with ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)
17
18### Preserve Existing Templates (when updating templates)
19- Study and EXACTLY match existing format, style, and conventions when modifying files
20- Never impose standardized formatting on files with established patterns
21- Existing template conventions ALWAYS override these guidelines
22
23## Financial models
24
25### Color Coding Standards
26Unless otherwise stated by the user or existing template
27
28#### Industry-Standard Color Conventions
29- **Blue text (RGB: 0,0,255)**: Hardcoded inputs, and numbers users will change for scenarios
30- **Black text (RGB: 0,0,0)**: ALL formulas and calculations
31- **Green text (RGB: 0,128,0)**: Links pulling from other worksheets within same workbook
32- **Red text (RGB: 255,0,0)**: External links to other files
33- **Yellow background (RGB: 255,255,0)**: Key assumptions needing attention or cells that need to be updated
34
35### Number Formatting Standards
36
37#### Required Format Rules
38- **Years**: Format as text strings (e.g., "2024" not "2,024")
39- **Currency**: Use $#,##0 format; ALWAYS specify units in headers ("Revenue ($mm)")
40- **Zeros**: Use number formatting to make all zeros "-", including percentages (e.g., "$#,##0;($#,##0);-")
41- **Percentages**: Default to 0.0% format (one decimal)
42- **Multiples**: Format as 0.0x for valuation multiples (EV/EBITDA, P/E)
43- **Negative numbers**: Use parentheses (123) not minus -123
44
45### Formula Construction Rules
46
47#### Assumptions Placement
48- Place ALL assumptions (growth rates, margins, multiples, etc.) in separate assumption cells
49- Use cell references instead of hardcoded values in formulas
50- Example: Use =B5*(1+$B$6) instead of =B5*1.05
51
52#### Formula Error Prevention
53- Verify all cell references are correct
54- Check for off-by-one errors in ranges
55- Ensure consistent formulas across all projection periods
56- Test with edge cases (zero values, negative numbers)
57- Verify no unintended circular references
58
59#### Documentation Requirements for Hardcodes
60- Comment or in cells beside (if end of table). Format: "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]"
61- Examples:
62 - "Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]"
63 - "Source: Company 10-Q, Q2 2025, Exhibit 99.1, [SEC EDGAR URL]"
64 - "Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity"
65 - "Source: FactSet, 8/20/2025, Consensus Estimates Screen"
66
67# XLSX creation, editing, and analysis
68
69## Overview
70
71A 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.
72
73## Important Requirements
74
75**Formula Evaluation**: openpyxl writes formulas as strings; calculated values are produced by Excel/WPS/LibreOffice when the file is opened. Verify formulas by reading the file back and checking that cell references point to the intended cells. (The bundled `recalc.py` is a deprecated placeholder that always reports `unsupported` — do not rely on it.)
76
77## Reading and analyzing data
78
79### Data analysis with pandas
80For data analysis, visualization, and basic operations, use **pandas** which provides powerful data manipulation capabilities:
81
82```python
83import pandas as pd
84
85# Read Excel
86df = pd.read_excel('file.xlsx') # Default: first sheet
87all_sheets = pd.read_excel('file.xlsx', sheet_name=None) # All sheets as dict
88
89# Analyze
90df.head() # Preview data
91df.info() # Column info
92df.describe() # Statistics
93
94# Write Excel
95df.to_excel('output.xlsx', index=False)
96```
97
98## Excel File Workflows
99
100## CRITICAL: Use Formulas, Not Hardcoded Values
101
102**Always use Excel formulas instead of calculating values in Python and hardcoding them.** This ensures the spreadsheet remains dynamic and updateable.
103
104### ❌ WRONG - Hardcoding Calculated Values
105```python
106# Bad: Calculating in Python and hardcoding result
107total = df['Sales'].sum()
108sheet['B10'] = total # Hardcodes 5000
109
110# Bad: Computing growth rate in Python
111growth = (df.iloc[-1]['Revenue'] - df.iloc[0]['Revenue']) / df.iloc[0]['Revenue']
112sheet['C5'] = growth # Hardcodes 0.15
113
114# Bad: Python calculation for average
115avg = sum(values) / len(values)
116sheet['D20'] = avg # Hardcodes 42.5
117```
118
119### ✅ CORRECT - Using Excel Formulas
120```python
121# Good: Let Excel calculate the sum
122sheet['B10'] = '=SUM(B2:B9)'
123
124# Good: Growth rate as Excel formula
125sheet['C5'] = '=(C4-C2)/C2'
126
127# Good: Average using Excel function
128sheet['D20'] = '=AVERAGE(D2:D19)'
129```
130
131This applies to ALL calculations - totals, percentages, ratios, differences, etc. The spreadsheet should be able to recalculate when source data changes.
132
133## Common Workflow
1341. **Choose tool**: pandas for data, openpyxl for formulas/formatting
1352. **Create/Load**: Create new workbook or load existing file
1363. **Modify**: Add/edit data, formulas, and formatting
1374. **Save**: Write to file
1385. **Verify formulas (MANDATORY IF USING FORMULAS)**: Read the saved file back and check formula correctness:
139 - Re-open the file with openpyxl and confirm each formula string references the intended cells (correct rows/columns/sheets)
140 - Cross-check 2-3 key formulas against a hand-computed expected value
141 - Common formula errors to watch for:
142 - `#REF!`: Invalid cell references
143 - `#DIV/0!`: Division by zero
144 - `#VALUE!`: Wrong data type in formula
145 - `#NAME?`: Unrecognized formula name
146 - Calculated values are produced when the user opens the file in Excel/WPS/LibreOffice
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## Formula evaluation and verification
205
206Excel files created or modified by openpyxl contain formulas as strings but not cached calculated values. This is expected: values are recalculated automatically when the file is opened in Excel, WPS, or LibreOffice.
207
208Verification approach (no headless recalculation):
2091. Re-open the saved file with `openpyxl.load_workbook(...)` and assert each key formula string matches the intended references
2102. Hand-compute 2-3 expected results in Python and compare against the formula logic
2113. If a previously calculated file must be read, use `data_only=True` to load cached values (do not save from that handle — formulas would be lost)
212
213> The bundled `recalc.py` is a **deprecated placeholder**: it performs no recalculation and always returns `{"status": "unsupported"}` with a non-zero exit code. Do not include it in any workflow.
214
215## Formula Verification Checklist
216
217Quick checks to ensure formulas work correctly:
218
219### Essential Verification
220- [ ] **Test 2-3 sample references**: Verify they pull correct values before building full model
221- [ ] **Column mapping**: Confirm Excel columns match (e.g., column 64 = BL, not BK)
222- [ ] **Row offset**: Remember Excel rows are 1-indexed (DataFrame row 5 = Excel row 6)
223
224### Common Pitfalls
225- [ ] **NaN handling**: Check for null values with `pd.notna()`
226- [ ] **Far-right columns**: FY data often in columns 50+
227- [ ] **Multiple matches**: Search all occurrences, not just first
228- [ ] **Division by zero**: Check denominators before using `/` in formulas (#DIV/0!)
229- [ ] **Wrong references**: Verify all cell references point to intended cells (#REF!)
230- [ ] **Cross-sheet references**: Use correct format (Sheet1!A1) for linking sheets
231
232### Formula Testing Strategy
233- [ ] **Start small**: Test formulas on 2-3 cells before applying broadly
234- [ ] **Verify dependencies**: Check all cells referenced in formulas exist
235- [ ] **Test edge cases**: Include zero, negative, and very large values
236
237### Read-back Verification Output
238When you re-open the saved file to verify, record for each key formula:
239- Cell address and formula string as saved
240- Expected reference(s) and whether they match
241- Hand-computed expected result vs formula logic (for 2-3 key cells)
242
243## Best Practices
244
245### Library Selection
246- **pandas**: Best for data analysis, bulk operations, and simple data export
247- **openpyxl**: Best for complex formatting, formulas, and Excel-specific features
248
249### Working with openpyxl
250- Cell indices are 1-based (row=1, column=1 refers to cell A1)
251- Use `data_only=True` to read calculated values: `load_workbook('file.xlsx', data_only=True)`
252- **Warning**: If opened with `data_only=True` and saved, formulas are replaced with values and permanently lost
253- For large files: Use `read_only=True` for reading or `write_only=True` for writing
254- Formulas are preserved but not evaluated by openpyxl — values appear when opened in Excel/WPS/LibreOffice
255
256### Working with pandas
257- Specify data types to avoid inference issues: `pd.read_excel('file.xlsx', dtype={'id': str})`
258- For large files, read specific columns: `pd.read_excel('file.xlsx', usecols=['A', 'C', 'E'])`
259- Handle dates properly: `pd.read_excel('file.xlsx', parse_dates=['date_column'])`
260
261## Code Style Guidelines
262**IMPORTANT**: When generating Python code for Excel operations:
263- Write minimal, concise Python code without unnecessary comments
264- Avoid verbose variable names and redundant operations
265- Avoid unnecessary print statements
266
267**For Excel files themselves**:
268- Add comments to cells with complex formulas or important assumptions
269- Document data sources for hardcoded values
270- Include notes for key calculations and model sections