Excel Model Authoring with openpyxl: A Reference for Auditable Financial Workbooks

Overview

This reference describes how to produce an auditable, flexible .xlsx file on disk using openpyxl, following banker-grade conventions so the model is reviewable by someone other than its builder. The approach is adapted from Anthropic's xlsx-author and audit-xls skills, dropping MCP/Office-JS/Cowork-specific branches and assuming a headless Python environment.

Use this method when building financial models (DCF, 3-statement, LBO) as .xlsx files in a headless Python environment, when every derived cell must be a live formula rather than a hardcoded value, when models need to be auditable via color-coding, named ranges, cell comments, and a balance checks tab, when generating sensitivity tables with a true center cell equal to the base case, or when downstream tools or CI need computed values requiring a recalc step after writing formulas.

Do not use this skill when the user is in a live Excel session with an Office MCP available (drive their live workbook instead), for pure tabular data export with no formulas (use csv or pandas.to_excel), or for dashboards and charts with heavy interactivity (use a real BI tool).

Prerequisites

• Python environment with openpyxl installed. Install it with:

pip install "openpyxl>=3.0"

• Headless Python environment with no MCP/Office-JS/Cowork dependencies.
• For recalc: LibreOffice installed (for headless --convert-to) or access to the Python recalc helper script at scripts/recalc.py.
• Access to source data for hardcoded inputs (e.g., 10-K, market data) to document in cell comments.

Output Contract

Write the final workbook to ./out/.xlsx; create ./out/ if it does not exist. Return the relative path in the final message so downstream tools can pick it up. Create one logical model per file; do not append to an existing workbook unless explicitly asked.

Core Conventions

Font Colors

Three font colors are non-negotiable:

• Blue font (Font(color="0000FF")) for hardcoded human-entered inputs: revenue drivers, WACC inputs, terminal growth, market data.
• Black font (default) for all formula cells; every derived cell must be a live Excel formula string.
• Green font (Font(color="006100")) for links to another sheet or external file.

Formula Strings

Never write a Python-computed number as a cell value; always write a formula string. For example, write "=D19*(1+$B$8)" instead of revenue_prior_year * (1 + growth). The wrong and correct approaches:

# WRONG — silent bug waiting to happen
ws["D20"] = revenue_prior_year * (1 + growth)

# CORRECT — flexes when the user changes the assumption
ws["D20"] = "=D19*(1+$B$8)"

Permit hardcoded numbers only for three categories: raw historical inputs (actual revenues, reported EBITDA), assumption drivers the user flexes (growth rates, WACC inputs, terminal g), and current market data (share price, debt balance). The latter must have a cell comment documenting source and date.

Named Ranges

Use named ranges for any figure referenced from another sheet, deck, or memo. Define them via DefinedName and reference by name in formulas:

from openpyxl.workbook.defined_name import DefinedName
wb.defined_names["WACC"] = DefinedName("WACC", attr_text="Inputs!$C$8")
# then elsewhere:
calc["D30"] = "=D29/WACC"

Checks Tab

Include a Checks tab that ties everything and surfaces TRUE/FALSE. It must cover balance sheet balances (assets = liabilities + equity), cash flow ties to period-over-period cash change on the BS, sum-of-parts ties to consolidated totals, and no rogue hardcodes inside calc ranges. Example:

checks = wb.create_sheet("Checks")
checks["A2"] = "BS balances"
checks["B2"] = "=IS!D20-IS!D21-IS!D22"
checks["C2"] = "=ABS(B2)<0.01"  # TRUE/FALSE

Cell Comments

Add a cell comment to every hardcoded input at the moment of creation, not later. Format: Source: [System/Document], [Date], [Reference], [URL if applicable]. Never defer sourcing, and never write TODO: add source. Example:

from openpyxl.comments import Comment
ws["C2"] = 1_250_000_000
ws["C2"].font = Font(color="0000FF")
ws["C2"].comment = Comment("Source: 10-K FY2024, p.47, revenue line", "analyst")

Building the Model Skeleton

Import the required modules and define style constants, then create the workbook and tabs:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.comments import Comment
from openpyxl.utils import get_column_letter
from pathlib import Path

BLUE = Font(color="0000FF")
BLACK = Font(color="000000")
GREEN = Font(color="006100")
BOLD = Font(bold=True)
HEADER_FILL = PatternFill("solid", fgColor="1F4E79")
HEADER_FONT = Font(color="FFFFFF", bold=True)

wb = Workbook()

# --- Inputs tab ---
inp = wb.active
inp.title = "Inputs"
inp["A1"] = "MARKET DATA & KEY INPUTS"
inp["A1"].font = HEADER_FONT
inp["A1"].fill = HEADER_FILL
inp.merge_cells("A1:C1")

inp["B3"] = "Revenue FY2024"
inp["C3"] = 1_250_000_000
inp["C3"].font = BLUE
inp["C3"].comment = Comment("Source: 10-K FY2024 p.47", "model")

inp["B4"] = "Growth Rate"
inp["C4"] = 0.12
inp["C4"].font = BLUE

# --- Calc tab ---
calc = wb.create_sheet("DCF")
calc["B2"] = "Projected Revenue"
calc["C2"] = "=Inputs!C3*(1+Inputs!C4)"   # formula, black

# --- Checks tab ---
chk = wb.create_sheet("Checks")
chk["A2"] = "BS balances"
chk["B2"] = "=ABS(BS!D20-BS!D21-BS!D22)<0.01"

Path("./out").mkdir(exist_ok=True)
wb.save("./out/model.xlsx")

Create the Inputs tab as the active sheet, title it "Inputs", and add the header "MARKET DATA & KEY INPUTS" with HEADER_FONT and HEADER_FILL, merging cells A1:C1. Add input rows with labels in column B and values in column C, set font to BLUE, and add a Comment for market data. Create the Calc tab with wb.create_sheet("DCF"), add labels and formula cells (black font) referencing Inputs with named ranges or direct references. Create the Checks tab with wb.create_sheet("Checks"), add check labels and formulas returning TRUE/FALSE (e.g., "=ABS(B2)<0.01"). Ensure ./out exists with Path("./out").mkdir(exist_ok=True) and save with wb.save("./out/model.xlsx").

Section Headers with Merged Cells

When merging cells, set the value on the top-left cell of the merge range, apply HEADER_FONT to that cell, merge the range, then style the full range separately by looping over columns and applying HEADER_FILL to each cell. This is an openpyxl quirk: styling only the top-left cell leaves the rest of the range unstyled. Example:

ws["A7"] = "CASH FLOW PROJECTION"
ws["A7"].font = HEADER_FONT
ws.merge_cells("A7:H7")
for col in range(1, 9):  # A..H
    ws.cell(row=7, column=col).fill = HEADER_FILL

Model Layout Planning

Before writing any formula, define ALL section row positions. Then write ALL headers and labels, then ALL section dividers and blank rows, and THEN write formulas using the locked row positions. This prevents cascading-formula-breakage from inserting header rows after formulas are written.

Building Sensitivity Tables

Use loops to populate the table, not hardcoded formulas per cell. Ensure an odd number of rows and columns (e.g., 5×5 or 7×7) to guarantee a true center cell. Set the center row/col header to the model's actual WACC and terminal growth so the center output equals the base-case implied share price as a sanity check. Highlight the center cell with medium-blue fill (PatternFill("solid", fgColor="BDD7EE")) and bold font. Populate every cell with a full recalculation formula, never an approximation, such as a SUMPRODUCT of the FCF range discounted plus a terminal value formula. Example:

# 5x5 WACC (rows) x terminal growth (cols) sensitivity
wacc_axis = [0.08, 0.085, 0.09, 0.095, 0.10]        # center row = base 9.0%
term_axis = [0.02, 0.025, 0.03, 0.035, 0.04]        # center col = base 3.0%

start_row = 40
ws.cell(row=start_row, column=1).value = "Implied Share Price ($)"
ws.cell(row=start_row, column=1).font = BOLD

for j, g in enumerate(term_axis):
    ws.cell(row=start_row+1, column=2+j).value = g
    ws.cell(row=start_row+1, column=2+j).font = BLUE

for i, w in enumerate(wacc_axis):
    r = start_row + 2 + i
    ws.cell(row=r, column=1).value = w
    ws.cell(row=r, column=1).font = BLUE
    for j, g in enumerate(term_axis):
        c = 2 + j
        # Full DCF recalc formula (simplified for illustration).
        # In a real model this references the full projection block.
        ws.cell(row=r, column=c).value = (
            f"=SUMPRODUCT(FCF_range,1/(1+{w})^year_offset) + "
            f"FCF_terminal*(1+{g})/({w}-{g})/(1+{w})^terminal_year"
        )

# Highlight center cell (base case)
center = ws.cell(row=start_row+2+len(wacc_axis)//2,
                 column=2+len(term_axis)//2)
center.fill = PatternFill("solid", fgColor="BDD7EE")
center.font = BOLD

In the example above, the center row header is 9.0% (the base WACC) and the center column header is 3.0% (the base terminal growth), so the center cell equals the base-case implied share price. The sensitivity table uses odd dimensions and full recalculation formulas, never approximations.

Recalculate Before Delivery

openpyxl writes formula strings but does not compute them. Excel recalculates on open, but downstream consumers (auto-check scripts, CI) need computed values. Run LibreOffice headless recalc:

# LibreOffice headless recalc
libreoffice --headless --calc --convert-to xlsx ./out/model.xlsx --outdir ./out/

Alternatively, use a Python recalc helper at scripts/recalc.py.

Verify Step-by-Step with the User

For large models (DCFs, 3-statement, LBO), stop and show intermediate artifacts before continuing. Checkpoint after the Inputs block to show raw inputs and confirm before projecting. Checkpoint after Revenue projections to confirm top line plus growth. Checkpoint after the FCF build to confirm the full schedule. Checkpoint after WACC to confirm inputs. Checkpoint after valuation to confirm the equity bridge. THEN build sensitivity tables.

Failure Modes

• Writing a Python-computed number as a cell value instead of a formula string: a silent bug when assumptions change.
• Inserting header rows after formulas are written, shifting downstream references and breaking formulas.
• Forgetting to add cell comments to hardcoded inputs, or deferring sourcing with TODO: add source.
• Building sensitivity tables with even dimensions, losing the true center cell.
• Using approximations in sensitivity table cells instead of full recalculation formulas.
• Not running a recalc step, leaving formula cells with no computed values for downstream scripts/CI.
• Merging cells and only styling the top-left cell, leaving the rest of the range unstyled.
• Appending to an existing workbook when not explicitly asked, violating the one-model-per-file rule.
• Using named ranges incorrectly (e.g., wrong attr_text) causing cross-sheet references to fail.
• Skipping user verification checkpoints, leading to rework if assumptions are wrong.

Parameters

• Output path: The file is always written to ./out/.xlsx; the directory is created if missing. The relative path must be returned in the final message. Required.
• Font colors: Blue for hardcoded inputs, black for formulas, green for cross-sheet/external links. Non-negotiable. Required.
• Formula strings: Every calculation cell must be a formula string, never a Python-computed number. Only raw historical inputs, assumption drivers, and current market data (with comment) may be hardcoded. Required.
• Named ranges: Use DefinedName for any figure referenced from another sheet, deck, or memo; reference by name in formulas. Optional.
• Checks tab: A dedicated tab with TRUE/FALSE checks for balance sheet balance, cash flow tie-out, sum-of-parts tie-out, and no rogue hardcodes. Required.
• Cell comments: Every hardcoded input must have a comment at creation time with format Source: [System/Document], [Date], [Reference], [URL if applicable]. Required.
• Sensitivity table dimensions: Must be odd (e.g., 5×5 or 7×7) so the center cell is the base case; center row/col headers must equal actual WACC and terminal growth. Required.
• Recalc step: openpyxl does not compute formulas; run LibreOffice headless or a Python recalc helper before delivery to provide computed values to downstream consumers. Required.

Example: A Simple Skeleton Model

A minimal complete model has an Inputs tab with revenue and growth rate, a DCF tab with a projected revenue formula, a Checks tab with a balance sheet balance check, and is saved to ./out/model.xlsx. The full skeleton code is shown in the "Building the Model Skeleton" section above. In that example, the revenue input is $1,250,000,000 (from the 10-K), the growth rate is 12%, and the projected revenue formula references the Inputs tab directly. The Checks tab uses =ABS(BS!D20-BS!D21-BS!D22)<0.01 to return TRUE/FALSE. The output path ./out/model.xlsx is returned in the final message for downstream tools.