excel-author

1---2name: excel-author3description: Build auditable financial workbooks headless via openpyxl.4version: 1.0.05author: Anthropic (adapted by Nous Research)6license: Apache-2.07platforms: [linux, macos, windows]8metadata:9  hermes:10    tags: [excel, openpyxl, finance, spreadsheet, modeling]11    related_skills: [xlsx, pptx-author, dcf-model, comps-analysis, lbo-model, 3-statement-model]12---13 14# excel-author15 16Produce an .xlsx file on disk using `openpyxl`. Follow the banker-grade conventions below so the model is auditable, flexible, and reviewable by someone other than the person who built it.17 18Adapted from Anthropic's `xlsx-author` and `audit-xls` skills in the [anthropics/financial-services](https://github.com/anthropics/financial-services) repo. The MCP / Office-JS / Cowork-specific branches of the originals are dropped — this skill assumes headless Python.19 20## Output contract21 22- Write to `./out/<name>.xlsx`. Create `./out/` if it does not exist.23- Return the relative path in your final message so downstream tools can pick it up.24- One logical model per file. Do not append to an existing workbook unless explicitly asked.25 26## Setup27 28```bash29pip install "openpyxl>=3.0"30```31 32## Core conventions (non-negotiable)33 34### Blue / black / green cell color35- **Blue** (`Font(color="0000FF")`) — hardcoded input a human entered. Revenue drivers, WACC inputs, terminal growth, market data.36- **Black** (default) — formula. Every derived cell is a live Excel formula.37- **Green** (`Font(color="006100")`) — link to another sheet or external file.38 39A reviewer can then scan the sheet and immediately see what's an assumption vs. what's computed.40 41### Formulas over hardcodes42Every calculation cell MUST be a formula string, never a number computed in Python and pasted as a value.43 44```python45# WRONG — silent bug waiting to happen46ws["D20"] = revenue_prior_year * (1 + growth)47 48# CORRECT — flexes when the user changes the assumption49ws["D20"] = "=D19*(1+$B$8)"50```51 52The only hardcoded numbers permitted:531. Raw historical inputs (actual revenues, reported EBITDA, etc.)542. Assumption drivers the user is meant to flex (growth rates, WACC inputs, terminal g)553. Current market data (share price, debt balance) — with a cell comment documenting source + date56 57If you catch yourself computing a value in Python and writing the result, stop.58 59### Named ranges for cross-sheet references60Use named ranges for any figure referenced from another sheet, a deck, or a memo.61 62```python63from openpyxl.workbook.defined_name import DefinedName64wb.defined_names["WACC"] = DefinedName("WACC", attr_text="Inputs!$C$8")65# then elsewhere:66calc["D30"] = "=D29/WACC"67```68 69### Balance checks tab70Include a `Checks` tab that ties everything and surfaces TRUE/FALSE:71- Balance sheet balances (assets = liabilities + equity)72- Cash flow ties to period-over-period cash change on the BS73- Sum-of-parts ties to consolidated totals74- No rogue hardcodes inside calc ranges75 76Example:77```python78checks = wb.create_sheet("Checks")79checks["A2"] = "BS balances"80checks["B2"] = "=IS!D20-IS!D21-IS!D22"81checks["C2"] = "=ABS(B2)<0.01"  # TRUE/FALSE82```83 84### Cell comments on every hardcoded input85Add the comment AS you create the cell, not later.86 87```python88from openpyxl.comments import Comment89ws["C2"] = 1_250_000_00090ws["C2"].font = Font(color="0000FF")91ws["C2"].comment = Comment("Source: 10-K FY2024, p.47, revenue line", "analyst")92```93 94Format: `Source: [System/Document], [Date], [Reference], [URL if applicable]`.95 96Never defer sourcing. Never write `TODO: add source`.97 98## Skeleton: typical financial model99 100```python101from openpyxl import Workbook102from openpyxl.styles import Font, PatternFill, Alignment, Border, Side103from openpyxl.comments import Comment104from openpyxl.utils import get_column_letter105from pathlib import Path106 107BLUE = Font(color="0000FF")108BLACK = Font(color="000000")109GREEN = Font(color="006100")110BOLD = Font(bold=True)111HEADER_FILL = PatternFill("solid", fgColor="1F4E79")112HEADER_FONT = Font(color="FFFFFF", bold=True)113 114wb = Workbook()115 116# --- Inputs tab ---117inp = wb.active118inp.title = "Inputs"119inp["A1"] = "MARKET DATA & KEY INPUTS"120inp["A1"].font = HEADER_FONT121inp["A1"].fill = HEADER_FILL122inp.merge_cells("A1:C1")123 124inp["B3"] = "Revenue FY2024"125inp["C3"] = 1_250_000_000126inp["C3"].font = BLUE127inp["C3"].comment = Comment("Source: 10-K FY2024 p.47", "model")128 129inp["B4"] = "Growth Rate"130inp["C4"] = 0.12131inp["C4"].font = BLUE132 133# --- Calc tab ---134calc = wb.create_sheet("DCF")135calc["B2"] = "Projected Revenue"136calc["C2"] = "=Inputs!C3*(1+Inputs!C4)"   # formula, black137 138# --- Checks tab ---139chk = wb.create_sheet("Checks")140chk["A2"] = "BS balances"141chk["B2"] = "=ABS(BS!D20-BS!D21-BS!D22)<0.01"142 143Path("./out").mkdir(exist_ok=True)144wb.save("./out/model.xlsx")145```146 147## Section headers with merged cells148 149openpyxl quirk: when you merge, set the value on the top-left cell and style the full range separately.150 151```python152ws["A7"] = "CASH FLOW PROJECTION"153ws["A7"].font = HEADER_FONT154ws.merge_cells("A7:H7")155for col in range(1, 9):  # A..H156    ws.cell(row=7, column=col).fill = HEADER_FILL157```158 159## Sensitivity tables160 161Build with loops, not hardcoded formulas per cell. Rules:162 163- **Odd number of rows/cols** (5×5 or 7×7) — guarantees a true center cell.164- **Center cell = base case.** The middle row/col header must equal the model's actual WACC and terminal g so the center output equals the base-case implied share price. That's the sanity check.165- **Highlight the center cell** with medium-blue fill (`"BDD7EE"`) and bold.166- Populate every cell with a full recalculation formula — never an approximation.167 168```python169# 5x5 WACC (rows) x terminal growth (cols) sensitivity170wacc_axis = [0.08, 0.085, 0.09, 0.095, 0.10]        # center row = base 9.0%171term_axis = [0.02, 0.025, 0.03, 0.035, 0.04]        # center col = base 3.0%172 173start_row = 40174ws.cell(row=start_row, column=1).value = "Implied Share Price ($)"175ws.cell(row=start_row, column=1).font = BOLD176 177for j, g in enumerate(term_axis):178    ws.cell(row=start_row+1, column=2+j).value = g179    ws.cell(row=start_row+1, column=2+j).font = BLUE180 181for i, w in enumerate(wacc_axis):182    r = start_row + 2 + i183    ws.cell(row=r, column=1).value = w184    ws.cell(row=r, column=1).font = BLUE185    for j, g in enumerate(term_axis):186        c = 2 + j187        # Full DCF recalc formula (simplified for illustration).188        # In a real model this references the full projection block.189        ws.cell(row=r, column=c).value = (190            f"=SUMPRODUCT(FCF_range,1/(1+{w})^year_offset) + "191            f"FCF_terminal*(1+{g})/({w}-{g})/(1+{w})^terminal_year"192        )193 194# Highlight center cell (base case)195center = ws.cell(row=start_row+2+len(wacc_axis)//2,196                 column=2+len(term_axis)//2)197center.fill = PatternFill("solid", fgColor="BDD7EE")198center.font = BOLD199```200 201## Recalculating before delivery202 203openpyxl writes formula strings but does not compute them. Excel recalculates on open, but downstream consumers (auto-check scripts, CI) need computed values.204 205Run LibreOffice or a dedicated recalc step before delivery:206 207```bash208# LibreOffice headless recalc209libreoffice --headless --calc --convert-to xlsx ./out/model.xlsx --outdir ./out/210```211 212Or use a Python recalc helper (see `scripts/recalc.py` in this skill).213 214## Model layout planning215 216Before writing any formula:2171. Define ALL section row positions2182. Write ALL headers and labels2193. Write ALL section dividers and blank rows2204. THEN write formulas using the locked row positions221 222This prevents the cascading-formula-breakage pattern where inserting a header row after formulas are written shifts every downstream reference.223 224## Verify step-by-step with the user225 226For large models (DCFs, 3-statement, LBO), stop and show the user intermediate artifacts before continuing. Catching a wrong margin assumption before you've built downstream sensitivity tables saves an hour.227 228Checkpoint pattern:229- After Inputs block → show raw inputs, confirm before projecting230- After Revenue projections → confirm top line + growth231- After FCF build → confirm the full schedule232- After WACC → confirm inputs233- After valuation → confirm the equity bridge234- THEN build sensitivity tables235 236## When NOT to use this skill237 238- Users in a live Excel session with an Office MCP available — drive their live workbook instead.239- Pure tabular data export with no formulas — `csv` or `pandas.to_excel` is simpler.240- Dashboards / charts with heavy interactivity — use a real BI tool.241 242## Attribution243 244Conventions (blue/black/green, formulas-over-hardcodes, named ranges, sensitivity rules) adapted from Anthropic's Claude for Financial Services plugin suite, Apache-2.0 licensed. Original: https://github.com/anthropics/financial-services/tree/main/plugins/vertical-plugins/financial-analysis/skills/xlsx-author245