xlsx

Output Requirements

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)

When modifying files, carefully study and precisely match existing formatting, styles, and conventions
Never impose standardized formatting on files with established patterns
Existing template conventions always take precedence over these guidelines

Financial Models

Color Coding Standards

Unless otherwise specified by the user or existing template

Industry-standard color conventions

Blue text (RGB: 0,0,255): Hardcoded inputs, and numbers the user will change for different scenarios
Black text (RGB: 0,0,0): All formulas and calculations
Green text (RGB: 0,128,0): Links pulled from other sheets within the same workbook
Red text (RGB: 255,0,0): External links to other files
Yellow background (RGB: 255,255,0): Key assumptions that need attention or cells that need updating

Number Format Standards

Required formatting rules

Years: Format as text strings (e.g., "2024" rather than "2,024")
Currency: Use $#,##0 format; always specify units in headers ("Revenue ($mm)")
Zero values: Use number formats to display all zeros as "-", including percentages (e.g., "$#,##0;($#,##0);-")
Percentages: Use 0.0% format by default (one decimal place)
Multiples: Valuation multiples (EV/EBITDA, P/E) formatted as 0.0x
Negative numbers: Use parentheses (123) rather than a minus sign -123

Formula Construction Rules

Assumption Placement

Place all assumptions (growth rates, margins, multiples, etc.) in separate assumption cells
Use cell references rather than hardcoded values in formulas
Example: Use =B5*(1+$B$6) rather than =B5*1.05

Formula Error Prevention

Verify that all cell references are correct
Check for off-by-one errors in ranges
Ensure formulas are consistent across all forecast periods
Test with edge cases (zero values, negative numbers)
Verify there are no unintended circular references

Documentation Requirements for Hardcoded Values

Add a comment or note next to the cell (or at the end of the table if applicable). Format: "Source: [System/Document], [Date], [Specific Reference], [URL (if applicable)]"
Example:
- "Source: Company 10-K, FY2024, p. 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 estimate screening"

XLSX creation, editing, and analysis

Overview

Users may ask you to create, edit, or analyze the contents of .xlsx files. You have different tools and workflows available for different tasks.

Important requirements

Formula recalculation requires LibreOffice: You can assume LibreOffice is installed for recalculating formula values via the recalc.py script. The script automatically configures LibreOffice on first run

Reading and analyzing data

Using pandas for data analysis

For data analysis, visualization, and basic operations, use pandas, which provides powerful data processing capabilities:

import pandas as pd

# 读取 Excel
df = pd.read_excel('file.xlsx')  # 默认：第一个工作表
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)  # 所有工作表作为字典

# 分析
df.head()      # 预览数据
df.info()      # 列信息
df.describe()  # 统计信息

# 写入 Excel
df.to_excel('output.xlsx', index=False)

Excel file workflow

Key: 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 updatable.

❌ Wrong - hardcoded calculated values

# 错误：在 Python 中计算并硬编码结果
total = df['Sales'].sum()
sheet['B10'] = total  # 硬编码 5000

# 错误：在 Python 中计算增长率
growth = (df.iloc[-1]['Revenue'] - df.iloc[0]['Revenue']) / df.iloc[0]['Revenue']
sheet['C5'] = growth  # 硬编码 0.15

# 错误：Python 计算平均值
avg = sum(values) / len(values)
sheet['D20'] = avg  # 硬编码 42.5

✅ Correct - use Excel formulas

# 正确：让 Excel 计算总和
sheet['B10'] = '=SUM(B2:B9)'

# 正确：增长率作为 Excel 公式
sheet['C5'] = '=(C4-C2)/C2'

# 正确：使用 Excel 函数计算平均值
sheet['D20'] = '=AVERAGE(D2:D19)'

This applies to all calculations - totals, percentages, ratios, differences, etc. The spreadsheet should be able to recalculate when the source data changes.

Common workflow

Choose tools: pandas for data, openpyxl for formulas/formatting
Create/load: Create a new workbook or load an existing file
Modify: Add/edit data, formulas, and formatting
Save: Write to file
Recalculate formulas (required when using formulas): Use the recalc.py script
```
python recalc.py output.xlsx
```
Verify and fix any errors：
- The script returns JSON containing error details
- If status is errors_found, check error_summary for the specific error type and location
- Fix the identified errors and recalculate again
- Common errors that need fixing:
  - #REF!: Invalid cell reference
  - #DIV/0!: Division by zero
  - #VALUE!: Data type error in formula
  - #NAME?: Unrecognized formula name

Create a new Excel file

# 使用 openpyxl 处理公式和格式
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
sheet = wb.active

# 添加数据
sheet['A1'] = 'Hello'
sheet['B1'] = 'World'
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['A1'].alignment = Alignment(horizontal='center')

# 列宽
sheet.column_dimensions['A'].width = 20

wb.save('output.xlsx')

Edit an existing Excel file

# 使用 openpyxl 保留公式和格式
from openpyxl import load_workbook

# 加载现有文件
wb = load_workbook('existing.xlsx')
sheet = wb.active  # 或使用 wb['SheetName'] 获取特定工作表

# 处理多个工作表
for sheet_name in wb.sheetnames:
    sheet = wb[sheet_name]
    print(f"工作表: {sheet_name}")

# 修改单元格
sheet['A1'] = 'New Value'
sheet.insert_rows(2)  # 在位置 2 插入行
sheet.delete_cols(3)  # 删除第 3 列

# 添加新工作表
new_sheet = wb.create_sheet('NewSheet')
new_sheet['A1'] = 'Data'

wb.save('modified.xlsx')

Recalculate formulas

Excel files created or modified by openpyxl contain formula strings but not calculated values. Use the provided recalc.py script to recalculate formulas:

python recalc.py <excel文件> [超时秒数]

Example:

python recalc.py output.xlsx 30

The script:

Automatically sets up the LibreOffice macro on first run
Recalculates all formulas in all worksheets
Scans all cells for Excel errors (#REF!, #DIV/0!, etc.)
Returns JSON containing detailed error locations and counts
Works on both Linux and macOS

Formula verification checklist

Quick checks to ensure formulas work properly:

Basic verification

Test 2-3 sample references: Verify they extract the correct values before building the full model
Column mapping: Confirm Excel column matches (for example, column 64 = BL, not BK)
Row offset: Remember Excel rows are indexed starting from 1 (DataFrame row 5 = Excel row 6)

Common pitfalls

NaN handling: Use pd.notna() to check for null values
Rightmost columns: Fiscal year data is usually in columns 50+
Multiple matches: Search all occurrences, not just the first
Division by zero: Check the denominator before using / in a formula (#DIV/0!)
Invalid references: Verify that all cell references point to the intended cells (#REF!)
Cross-worksheet references: Use the correct format (Sheet1!A1) to link worksheets

Formula testing strategy

Start small: Test the formula on 2-3 cells before applying it broadly
Verify dependencies: Check that all cells referenced in the formula exist
Test edge cases: Include zero values, negative numbers, and very large values

Interpreting recalc.py output

The script returns JSON containing error details:

{
  "status": "success",           // 或 "errors_found"
  "total_errors": 0,              // 错误总数
  "total_formulas": 42,           // 文件中的公式数量
  "error_summary": {              // 仅在发现错误时出现
    "#REF!": {
      "count": 2,
      "locations": ["Sheet1!B5", "Sheet1!C10"]
    }
  }
}

Best practices

Library selection

pandas: Best for data analysis, batch operations, and simple data export
openpyxl: Best for complex formatting, formulas, and Excel-specific features

Using openpyxl

Cell indexing starts from 1 (row=1, column=1 points to cell A1)
Use data_only=True to read calculated values:load_workbook('file.xlsx', data_only=True)
Warning: If you use data_only=True to open and save, formulas will be replaced with values and permanently lost
For large files: use read_only=Truewhen reading, and use write_only=True
Formulas are preserved but not calculated - use recalc.py to update values

Using 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 correctly:pd.read_excel('file.xlsx', parse_dates=['date_column'])

About this skill