XLSX 公式模型审计

何时用

用 openpyxl 写含公式的 Excel 测算模型（财务测算、成本模型、评分卡），
且本机没有 LibreOffice / Excel、无法让它重算并回读结果时使用。

典型场景：用户说"帮我搭个测算表""公式要保留、参数可调"，你只能 data_only=False 读回公式字符串，读到的是 '=B6+B15' 而不是数值 —— 这时候唯一可行的校验方式是结构审计 + 独立复算。

铁律

1. 禁止在公式里硬编码行号。 "=B12+B13" 这种字符串，一旦你增删了上面任意一行，必然错位。
   这是本类任务的第一大 bug 来源，且静默出错——Excel 不报错，只是算错。

✅ 正确写法：先用字典记录每个字段的实际行号，公式全部由行号变量拼接。

python FIELDS = ["scale", "allin", "cpn", "spread", "int1y", "bondint", "intdiff", "fee1", "fee2", "feetot", "costtot", "delta", "rmb"] FN = {k: 4 + i for i, k in enumerate(FIELDS)} # 起始行为 4 rows = [ ("贷款规模", f"={PS}!$B$34", "#,##0", None), ("首期利率", f"={PS}!$B$38", "0.0000%", None), ("票息率", f"={PS}!$B$6", "0.0000%", None), ("利率差", f"=B{FN['allin']}-B{FN['cpn']}", "0.0000%", "..."), ("年利息", f"=B{FN['scale']}*B{FN['allin']}", "#,##0", None), ("费用合计", f"=B{FN['fee1']}+B{FN['fee2']}", "#,##0", None), ("成本合计", f"=B{FN['int1y']}+B{FN['feetot']}", "#,##0", None), ]

更稳的做法：先写完所有行、记下真实行号，再回填公式。openpyxl 无所谓顺序。

2. 不要相信 ws.max_row 判断结构对不对。 它只说明写到哪，不说明写对没写对。

3. 跨表引用的目标列要跟随真正承载数值的列。 别默认都在 B 列 —— 有合计行、
   有说明列时经常不是。

审计四件套

把这四步写成一个 verify_<name>.py，每次改完模型就跑一遍。不要靠肉眼扫公式。

① 引用解析：回读每个公式"指向的标签"

把公式里的引用解析出来，再回读目标单元格的 A 列标签，打印成
公式 → 目标单元格 [目标标签]。人眼扫一遍就能发现全部错位。

import openpyxl, re
wb = openpyxl.load_workbook(PATH, data_only=False)
CELL = re.compile(r"(?:'([^']+)'!)?\$?([A-Z]{1,3})\$?(\d+)")

def deps(ws, coord):
    v = ws[coord].value
    if not (isinstance(v, str) and v.startswith("=")):
        return []
    out = []
    for m in CELL.finditer(v):
        sh, col, row = m.group(1), m.group(2), int(m.group(3))
        # 过滤：前一个字符是字母/数字/下划线但不是 '!' → 是函数名或单元格自身，跳过
        if m.start() > 0 and v[m.start()-1] in "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_":
            if v[m.start()-1] != "!":
                continue
        out.append((sh or ws.title, f"{col}{row}"))
    return out

for r in range(1, ws.max_row + 1):
    for c in range(2, ws.max_column + 1):
        coord = ws.cell(r, c).coordinate
        for s, d in deps(ws, coord):
            lbl = wb[s][f"A{int(re.sub(r'[A-Z]', '', d))}"].value
            print(f"{coord:<5} {ws[coord].value!r:<48} -> {s}!{d}  [{lbl}]")

危险信号：引用指到了"一、/二、/三、"这种分节标题行，或指到了说明文字行
→ 说明行位算错，或者你增删了节内行数。

② 自引用 / 循环引用检测

自引用（B16 = =B15*... 写成 =B16*...）是最典型的错位症状。用 DFS 三色标记找环：

def find_cycles(ws):
    graph = {}
    for row in ws.iter_rows():
        for cell in row:
            d = deps(ws, cell.coordinate)
            if d: graph[cell.coordinate] = d
    WHITE, GRAY, BLACK = 0, 1, 2
    color = {k: WHITE for k in graph}
    hits = []
    def dfs(n, stack):
        color[n] = GRAY
        for s, m in graph.get(n, []):
            if s != ws.title or m not in graph: continue
            if color[m] == GRAY: hits.append(" -> ".join(stack + [m])); return
            if color[m] == WHITE: dfs(m, stack + [m])
        color[n] = BLACK
    for n in list(graph):
        if color[n] == WHITE: dfs(n, [n])
    return hits

③ 参数表行位 dump

把参数/假设表整表按 B{行} 标签 = 值 打出来。跨表引用错位时，
一眼就能看出引到了错误的参数行（比如"赎回规模"引成了"赎回方式"）。

④ 关键数字独立复算

新增算例前，先让复算脚本复现一个"已经过人工核定"的旧值，作为回归基准。
复现不了，说明复算脚本的算法与底稿不一致 —— 此时算出的新值一律不能用，
必须先对齐算法。这个前置步骤能挡掉绝大部分"新场景数字看着挺合理、其实是算法漂移"的事故。

例：新增"情形二"之前，先用同一脚本复现"情形一累计利息 = EUR 4,750,000"（与底稿及报告正文一致），
复现通过后才采信情形二的结果。

每个 headline 数字，在 Python 里另写一份实现重算，再与目标单元格并列打印：

  赎回溢价 EUR ★核心        8,500,000   → 03!B8
  赎回对价 EUR          820,000,000   → 03!B16

注意：这一步只能验你自己的数学，验不了单元格引用对不对 ——
所以 ① ② ③ 不可省。四步互补，缺一不可。

语义审计（结构审计查不出来的九类错）

① ② ③ ④ 只能证明"引用没指错、算的没算错"。以下各类错引用完全正确、公式完全合法、Excel 绝不报错，
只能靠语义判断 —— 用 openpyxl 建财务测算模型时实测踩过，记录在此。

⑤ 跨行"沿用上期参数"的归期断言

凡是表格用 =F{上一行} 往下传递参数（利率、汇率、折现率、税率），
必须逐行追问一句：这一行所辖的那个期段，是否真的落在该参数的生效区间内？

实战案例：过桥贷款 2025-12-31 提款、按季重定价，首期利率 3.15%。
明细表按还款事件日切分（01-31 / 03-31 / 05-31 / 06-30），重定价利率被挂在了 03-31 那一行。
但 03-31 行所辖的期段是 01-31 → 03-31，而首次重定价日就是 03-31 ——
该期段仍在首期，却被提前套用了新利率。累计利息因此虚高。

# ❌ 错：重定价利率挂在了「重定价日」那一行
SCHED = [
    ("2026-01-31", ..., None),        # 沿用首期
    ("2026-03-31", "季度重定价日", ..., "L_ALLIN2"),   # ← 这一行所辖期段仍在首期！
    ("2026-05-31", ..., None),
]
# ✅ 对：参数自「期段起点 = 重定价日」的那一行起生效
SCHED = [
    ("2026-01-31", ..., None),
    ("2026-03-31", "季度重定价日（本行所辖期段仍在首期）", ..., None),
    ("2026-05-31", ..., "L_ALLIN2"),                   # 03-31→05-31 才是第二期
]

排查动作：把明细表的"期段 = 本行日期 − 上行日期"显式写成一列印出来，
再与参数的生效区间并排看。日期标在行首很容易让人误以为"这行的日期就是它的生效起点"。

⑥ 敏感性数字必须绑定基准点

"每上行 25bp，X 变动 A"这种表述，在含 MAX()/保底/凸性的模型里 A 不是常数。

实战案例：赎回溢价每 25bp 的敏感度，在低利率区（2.60→2.95%）约 −665 万，
在基准点（3.20→3.45%）只有 −650 万 —— 因为后者已内嵌 100% 保底效应。
报告若拿低区间的数字去描述基准点，就错了 15 万。

规则：敏感性表格必须三列齐全 —— 基准值 / 变动量 / 该基准点上的边际变化。
禁止输出不标明基准点的"每 25bp 变动 X"。

⑦ 加权量禁用简单平均

余额、敞口、加权久期这类"金额 × 天数"派生量，必须用
Σ(金额 × 天数) / Σ天数，不能用 (期初 + 期末)/2，更不能拍一个"平均余额"。

实战案例：过桥贷款余额从 10 亿阶梯下降到 0，有人填"平均余额 5 亿"，
余额加权实际是 7.44 亿 —— 敏感度被低估 32.8%（62.5 万 vs 93.0 万），
连带把主结论的"量级比"从 11:1 说成了 7.4:1。

weighted_avg = sum(bal * days for bal, days in segs) / sum(d for _, d in segs)
sens = weighted_avg * delta_rate * sum(d for _, d in segs) / 360   # 或直接 Σ(bal*d)*Δr/360

⑧ 报告与底稿不一致时，不要默认底稿对

实战中出过一次：报告里的过桥累计利息实际来自早期脚本的正确逻辑，
而"最新版"底稿在改写时引入了 ⑤ 的归期错位。
惯性会让你以为"底稿是最新的所以对"，从而去改正确的报告。

遇到不一致，先独立复算，再决定改哪一边。

⑨ 复算脚本与建模脚本的「参数生效时点」语义必须一致

⑧ 的反面同样成立：先怀疑复算脚本。

实战中出过一次镜像错误：Excel 建模脚本把利率写在行上、当期生效（F26 = =L_ALLIN2），
而临时写的 Python 复算脚本用了"参数延后一行生效"的循环（if nr: rate = nr 放在循环末尾），
于是 2026-04-30 那一行在复算里仍用首期利率 → 复算比底稿低 EUR 900。

两类常见语义，写复算脚本前必须先选定并向 Excel 对齐：

# A. 行上生效（本行所辖期段适用本行标的参数）——多数明细表的写法
for dt, inflow, rate in rows:
    itr = bal * rate * days / 360          # 当期即用 rate

# B. 延后一行生效（本行只是"参数宣告日"，下期才适用）
for dt, inflow, new_rate in rows:
    itr = bal * rate * days / 360          # 先用旧 rate
    if new_rate: rate = new_rate           # 循环末尾才切换

规则：复算脚本的第一行注释里写明选的是 A 还是 B，并附一句"与底稿第 N 行一致"。

⑩ 场景切换后的旧值残留扫描（重算类任务必做）

把测算从方案 A 切到方案 B（换假设、换排程、换时点）时，计算逻辑改了，注释和对照表往往没跟上。
残留的旧金额、旧日期、旧文件名会让人误读，而且Excel 不报错、结构审计也查不出。

实战扫出的残留（切换 方案 A → 方案 B 时）：

做法：写一个残留串清单（旧金额、旧日期、旧文件名、旧倍数、旧比率），
对全部工作表全表扫描；报告正文再扫一遍。可固化为一条语义断言：

STALE = ["9,800,000", "2027-12-31", "债券测算.xlsx", "9.2", "5 亿", "750000"]
strays = [(sn, c.coordinate, pat) for sn in wb.sheetnames
          for row in wb[sn].iter_rows() for c in row
          if isinstance(c.value, str)
          for pat in STALE if pat in c.value]
assert not strays, strays

⚠️ 误报处理：修订记录/变更沿革附录里合法地引用旧值作对比（"原值 X → 修正后 Y"）。
断言要排除这些区域（如排除 01 说明 表，或只扫正文/排除引用块），否则会一直误报。
不要为了消误报而删掉合法的沿革记录。

⑪ 源表口径与自建口径必须显式勾稽

当上游给的表和你的模型对同一笔钱有两种处理方式时，直接拿两边的结果做比较必然对不上，
而且看起来像自己的模型算错了。

实战案例：源表在"款项流入"里先预留一年利息费用（按当前年利息全额预留 4,260 万），
再把剩余应用于债务本金，因此它列示的"累计还款"比毛资金小；
而自建模型不预留，改由排程逐期计提实际利息（实际只 475 万）。
两者差额 = 未用尽的预留 + 源表原分配给他处的余额：

剩余资金 41,750,000 = (预留利息 42,600,000 − 实际利息 4,750,000) + 源表额外还本 3,900,000

规则：只要两条口径并存，就在模型与报告的注释里写出勾稽等式，
并指明差额的来源（时间差？口径差？预留 vs 实付？）。
勾稽等式不能"约等于"，要能精确成立。

推论——"期限" ≠ "占用期"：额度期限 1 年、含提前还款条款，但实际 120 天结清。
凡引用"一年期成本"的地方都必须同时给出实际占用期，否则会高估约 3 倍。
期限是额度保留期，不是资金占用期。

⑫ 校验脚本自身不要写死行偏移（off-by-one 的另一种来源）

铁律 ① 管的是建模脚本，但审计脚本同样会栽在这里，而且症状更迷惑：
底稿其实是对的，校验却报 FAIL，你回头去改底稿反而把对的改错。

实战案例：排程表「表头 1 行 + 数据 5 行」，审计里写 LAST = S1 + 4（把"表头行号 + 数据行数 − 1"当成了末行），
实际末行是 S1 + 5。连带三条断言（期末余额引用、累计利息引用、重定价行比对）全部假 FAIL。

✅ 根治：不写偏移，改成按特征取样，让取样器自己找边界。

def pick(hdr_row, col=1, prefix="=DATE("):
    """从表头下一行起，取「首列以 prefix 开头」的连续行 = 该排程的数据行。"""
    out, x = [], hdr_row + 1
    while x and str(w4.cell(x, col).value).startswith(prefix):
        out.append(x)
        x += 1
    return out

sched1 = pick(hdr1)          # 末行 = sched1[-1]，不再需要 LAST 变量
sched2 = pick(hdr2)
reprice_rows = [r for r in sched1 if "$B$39" in str(w4.cell(r, 6).value)]  # 按内容找，不按序号找

判据：任何 S1 + k 形式的常量出现在断言里，都是下一个 bug。 换成取样器后，
即使以后在表头和数据之间插行、或在表尾追加行，断言自动跟随。

> 假 FAIL 的排查顺序：先 dump 底稿真实结构（for r in range(a, b): print(r, [cell values])），
> 确认底稿对错，再看断言。反过来做会把正确底稿改坏。

⑬ 分期计息的计息基数必须显式声明：单利 or 复利

只要模型里出现 期间利息 = 期初基数 × 利率 × 天数 / 360，就有一个必须明确回答、否则默认踩坑的问题：

> 期初基数取「本金」，还是「本金 ＋ 已计提未付利息」？

取后者 = 未付利息自己也在生息 = 复利。这与是否管它叫"复利"无关——
只要「利息滚入本金」且被用作下期基数，数学上就是复利。

实战案例（2026-09-17，过桥贷款排程；下表为示意值）：写的是 期末余额 = 期初余额 + 期间利息 − 入账，
「期初余额」取自上一行「期末余额」，于是逐期计息基数变成：

业务方复核时直接指出"应该是单利"。判据不是模型怎么写，而是融资安排的付息方式：

✅ 审计动作（十秒钟，必做）：

1. 打印逐期计息基数序列，与「本金」列并排比对——基数 ≠ 本金的首期，就是复利起点。
2. 回条款：找融资文件的 interest payment 条款。若资料里没有该文件（本项目就属于这种情况，
   00_原始资料/ 只有 TrustDeed 与 OC，无过桥 term sheet），则该口径无文件支撑，须向业务方/
   牵头行确认，不得自行假定。
3. 查内部矛盾：报告里若一处写"一般为利息日还款"（指向按季付息＝单利），另一处口径说明写
   "未假设期间付息"（指向滚存＝复利），先怀疑口径、再怀疑数字。
4. 全文一致性：同一报告的不同章节极易各用一套。本项目 5.2 排程用复利（季度资本化），
   6.5 压力表却用线性单利外推（欠款 = 基数 + 日息 × 天数，不资本化）——两处相差 5–12 万欧元。
5. 改口径 = 全簿扫描，不能只改"那一个表"：本项目把基数由余额改为本金时，先只改了「04 资金安排」，
   残留扫描才发现 「07 利率情景与概率加权」的敏感度单元格（B47–B49）与两段说明（D47–D49）
   也在引用同一个数字。敏感度/情景表往往是对排程结果的二次引用，最易漏。
   落地清单：grep 全部工作表 + 全部注文，扫旧值；历史修订记录里的旧值属存痕，应白名单放行，
   否则会把审计留痕一起改坏。

❌ 表述纪律：口径说明必须写清基数是什么——
写"以期初余额计"是无效表述（读者无法判断余额含不含未付利息）。
应写"以期初本金计，未付利息不另行生息"或"以期初占用总额计（利息资本化）"。

📏 量级参考：本项目差异 情形一 −0.10% / 情形二 −1.28%（结论方向不变，属精度与自洽性修正）。
但不得因为"差异小"就不改——同一份报告里两套计息口径，是审计硬伤。

报告交付前的独立复核（强烈建议）

数字进了正式报告就很难改。交付前另起一个不共用任何函数的复算脚本，
让"审计"和"建模"是两个独立的实现 —— 共用函数只能验证引用，验证不了建模假设本身。

复核至少要覆盖：
- 全部 headline 数字的独立复算（另写实现，不复用建模脚本）
- 条款/合同/规范类引用逐条回原文核对（引对了没有、编号对不对）
- 交叉引用章节号、表格列数、markdown 星号成对情况
- 口径混用：同一个量在不同章节用了不同基准日/剩余期限/币种

复核发现的每一项都要写进报告的修正记录附录（原值 / 修正后 / 性质），
而不是悄悄改掉 —— 下游会引用这些数字，需要知道口径变更过。

批量修改生成脚本：用「带断言的补丁脚本」+ 回读核验

不要依赖文件编辑工具的成功回执。 实战中一批 6 处编辑里有 4 处报告成功但文件未变更
（包括输出路径改写丢失，导致脚本仍写出旧版本号的文件，而运行日志看起来"成功"）。
这类静默失败尤其危险：脚本照跑、日志照打、只有内容没改。

做法：把成批修改写成一个临时补丁脚本，每处替换断言命中数，命中数不符即中止并写日志。

def rep(tag, old, new, n=1):
    global s
    c = s.count(old)
    if c != n:
        raise SystemExit("FAIL %s: expect %d got %d" % (tag, n, c))
    s = s.replace(old, new, n)
    log.append("OK   " + tag)

配套两条纪律：

1. 补丁脚本必须全量落盘后回读核验。改完用 Grep 搜新增关键字、搜旧关键字确认已消失；
   再跑一次生成脚本，用 Python 把关键单元格 dump 成日志（不要只 print，见下节 stdout 问题）：

python with open(LOG, "w", encoding="utf-8") as f: f.write("saved: " + OUT + "\n") for r in range(23, 28): f.write(repr([w4.cell(r, c).value for c in range(1, 10)]) + "\n")

2. 中文全文替换要注意全半角引号。报告/脚本里常有 "…"（U+201C/201D）而非 ASCII "，
   用 ASCII 引号拼 old 会匹配不到；old 里若混入 ASCII " 还会让补丁脚本本身 SyntaxError。
   补丁字符串统一用三引号（'''…'''），避免转义地狱。

openpyxl 其他坑

• =SUM(F37:F40) 这类区域引用要单独解析（正则里的 :），上面的 deps() 会漏掉。
  如果关键合计依赖区域引用，补一条专门检查或直接复算。
• 日期写字符串：ws.cell(r, c, "DATE(2026,1,31)") 写成公式文本（不写 =），
  Excel 打开后会识别为日期。配 number_format = "yyyy-mm-dd"。
• 行内不要用 PatternFill() 空对象——有些环境下会把单元格涂黑。不填就不设。
• 跨表引用带空格的工作表名必须加单引号：'02 参数与假设'!$B$5。
  写脚本时把表名存成 PS = "'02 参数与假设'" 变量，避免手误。

Windows 环境注意事项（Windows 环境实测）

• bash 工具的 shim 缺 dirname/ls/tail，基本不可用。
  改用：Glob 找文件、Grep 搜内容、Read 读文件、PowerShell 做目录操作。
• PowerShell 工具的 stdout 经常不被捕获（返回 Command completed with exit code 0 但无输出）。
  可靠做法：... > out.txt 2>&1 重定向，再用 Read 读。
• PowerShell 的 Out-File/> 默认写 UTF-16，Read 会报 binary。
  用 python -c 里的 open(..., encoding='utf-8') 或让 Python 直接写文件。
• 文件删除：Remove-Item 可能被沙箱静默拒绝。用
  python -c "import os; os.remove(f)" 可靠。
• 一次性跑通生成+校验：
  bash cd "<dir>" && python build_xlsx.py > build.log 2>&1 \ && python verify_xlsx.py > verify.txt 2>&1; echo done

交付前自检清单

• [ ] 公式里没有硬编码行号（全部来自 FN 字典或显式变量）
• [ ] ① 引用解析已跑，无指向分节标题/说明行的引用
• [ ] ② 无自引用、无循环
• [ ] ③ 参数表行位 dump 已肉眼核对
• [ ] ④ 全部 headline 数字已独立复算并一致
• [ ] ⑤ 所有 =F{上一行} 传递的参数列，已逐行核对"本行所辖期段 vs 参数生效区间"
• [ ] ⑥ 所有敏感性数字均标明基准点，无孤立的"每 ΔX 变动 Y"
• [ ] ⑦ 所有加权量用 Σ(金额×天数)/Σ天数，无简单平均
• [ ] ⑧ 报告与底稿不一致处已各自独立复算，未默认底稿正确
• [ ] ⑨ 复算脚本的"参数生效时点"语义已在注释中声明并与底稿一致
• [ ] ⑩ 场景切换后已跑残留串扫描（全工作表 + 报告正文），且未误报沿革附录
• [ ] ⑪ 源表口径与自建口径的差异已写出可精确成立的勾稽等式；"期限"与"占用期"已分别说明
• [ ] ⑫ 校验脚本本身未写死行偏移：定位目标行用的是语义锚点/字典而非裸下标；且每个「差额应为 0」的断言，都已回查该行两个口径的基数是否本就相等（相等则属无信息断言，须改到真正分叉的那一期）
• [ ] ⑬ 所有 利息 = 基数 × 利率 × 天数/360 形式的计息，已声明基数是「期初本金」（单利）还是「期初占用总额」（复利）；已打印逐期基数序列与本金并排比对；同一份报告的各定稿章节之间无两套计息口径（如排程用复利、压力表用线性单利外推）
• [ ] 批量修改生成脚本时用了带断言的补丁脚本，且落盘后已 Grep 回读 + dump 关键单元格核验
• [ ] 表内口径提示写清了"哪些是成本、哪些只是现金需求"，避免下游误读
• [ ] 报告交付前已做独立复核，发现的每一项都写进了修正记录附录