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
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
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 recalc.py script
python recalc.py output.xlsx
- Verify and fix any errors:
1---2name: xlsx-official3description: 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, ....4category: Document Processing5source: antigravity6tags: [python, xlsx, claude, ai, workflow, template, document, spreadsheet, rag, cro]7url: https://github.com/sickn33/antigravity-awesome-skills/tree/main/skills/xlsx-official8metadata:9 source: https://skillmd.com/skills/sickn33/xlsx-official10---111213# Requirements for Outputs1415## All Excel files1617### Zero Formula Errors18- Every Excel model MUST be delivered with ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)1920### Preserve Existing Templates (when updating templates)21- Study and EXACTLY match existing format, style, and conventions when modifying files22- Never impose standardized formatting on files with established patterns23- Existing template conventions ALWAYS override these guidelines2425## Financial models2627### Color Coding Standards28Unless otherwise stated by the user or existing template2930#### Industry-Standard Color Conventions31- **Blue text (RGB: 0,0,255)**: Hardcoded inputs, and numbers users will change for scenarios32- **Black text (RGB: 0,0,0)**: ALL formulas and calculations33- **Green text (RGB: 0,128,0)**: Links pulling from other worksheets within same workbook34- **Red text (RGB: 255,0,0)**: External links to other files35- **Yellow background (RGB: 255,255,0)**: Key assumptions needing attention or cells that need to be updated3637### Number Formatting Standards3839#### Required Format Rules40- **Years**: Format as text strings (e.g., "2024" not "2,024")41- **Currency**: Use $#,##0 format; ALWAYS specify units in headers ("Revenue ($mm)")42- **Zeros**: Use number formatting to make all zeros "-", including percentages (e.g., "$#,##0;($#,##0);-")43- **Percentages**: Default to 0.0% format (one decimal)44- **Multiples**: Format as 0.0x for valuation multiples (EV/EBITDA, P/E)45- **Negative numbers**: Use parentheses (123) not minus -1234647### Formula Construction Rules4849#### Assumptions Placement50- Place ALL assumptions (growth rates, margins, multiples, etc.) in separate assumption cells51- Use cell references instead of hardcoded values in formulas52- Example: Use =B5*(1+$B$6) instead of =B5*1.055354#### Formula Error Prevention55- Verify all cell references are correct56- Check for off-by-one errors in ranges57- Ensure consistent formulas across all projection periods58- Test with edge cases (zero values, negative numbers)59- Verify no unintended circular references6061#### Documentation Requirements for Hardcodes62- Comment or in cells beside (if end of table). Format: "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]"63- Examples:64 - "Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]"65 - "Source: Company 10-Q, Q2 2025, Exhibit 99.1, [SEC EDGAR URL]"66 - "Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity"67 - "Source: FactSet, 8/20/2025, Consensus Estimates Screen"6869# XLSX creation, editing, and analysis7071## Overview7273A 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.7475## Important Requirements7677**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 run7879## Reading and analyzing data8081### Data analysis with pandas82For data analysis, visualization, and basic operations, use **pandas** which provides powerful data manipulation capabilities:8384```python85import pandas as pd8687# Read Excel88df = pd.read_excel('file.xlsx') # Default: first sheet89all_sheets = pd.read_excel('file.xlsx', sheet_name=None) # All sheets as dict9091# Analyze92df.head() # Preview data93df.info() # Column info94df.describe() # Statistics9596# Write Excel97df.to_excel('output.xlsx', index=False)98```99100## Excel File Workflows101102## CRITICAL: Use Formulas, Not Hardcoded Values103104**Always use Excel formulas instead of calculating values in Python and hardcoding them.** This ensures the spreadsheet remains dynamic and updateable.105106### ❌ WRONG - Hardcoding Calculated Values107```python108# Bad: Calculating in Python and hardcoding result109total = df['Sales'].sum()110sheet['B10'] = total # Hardcodes 5000111112# Bad: Computing growth rate in Python113growth = (df.iloc[-1]['Revenue'] - df.iloc[0]['Revenue']) / df.iloc[0]['Revenue']114sheet['C5'] = growth # Hardcodes 0.15115116# Bad: Python calculation for average117avg = sum(values) / len(values)118sheet['D20'] = avg # Hardcodes 42.5119```120121### ✅ CORRECT - Using Excel Formulas122```python123# Good: Let Excel calculate the sum124sheet['B10'] = '=SUM(B2:B9)'125126# Good: Growth rate as Excel formula127sheet['C5'] = '=(C4-C2)/C2'128129# Good: Average using Excel function130sheet['D20'] = '=AVERAGE(D2:D19)'131```132133This applies to ALL calculations - totals, percentages, ratios, differences, etc. The spreadsheet should be able to recalculate when source data changes.134135## Common Workflow1361. **Choose tool**: pandas for data, openpyxl for formulas/formatting1372. **Create/Load**: Create new workbook or load existing file1383. **Modify**: Add/edit data, formulas, and formatting1394. **Save**: Write to file1405. **Recalculate formulas (MANDATORY IF USING FORMULAS)**: Use the recalc.py script141 ```bash142 python recalc.py output.xlsx143 ```1446. **Verify and fix any errors**: 145 - The script returns