from pathlib import Path
import re, math, html, json, csv
from collections import OrderedDict, defaultdict
import openpyxl
from openpyxl.formula import Tokenizer
from openpyxl.utils.cell import range_boundaries, get_column_letter
SRC = Path('/Users/neo/.hermes/cache/documents/doc_259cbd37e734_万传安徽_V9_v2.1_R66拆3sub+12sub行全拆开_20260512.xlsx')
PATCHED = Path('/Users/neo/.hermes/audits/cashflow-asset-hook-20260512-083049/wanchuan_anhui_v9_v2_1_long_asset_R29_R58_internal_formula_SAFE_20260513-074532.xlsx')
OUTDIR = Path('/Users/neo/.hermes/audits/cashflow-asset-hook-20260512-083049')
STAMP = '20260513-084155'
REPORT = OUTDIR / f'phase7_deep_bottom_trace_and_formula_atomicity_{STAMP}.md'
LEAF_CSV = OUTDIR / f'phase7_deep_bottom_leaf_table_{STAMP}.csv'
ATOM_CSV = OUTDIR / f'phase7_R29_R58_formula_atomicity_audit_{STAMP}.csv'
wb_f = openpyxl.load_workbook(SRC, data_only=False, read_only=False, keep_links=True)
wb_v = openpyxl.load_workbook(SRC, data_only=True, read_only=False, keep_links=True)
wp_f = openpyxl.load_workbook(PATCHED, data_only=False, read_only=False, keep_links=True)
wp_v = openpyxl.load_workbook(PATCHED, data_only=True, read_only=False, keep_links=True)
BUSINESS = {
('现金流量表','D38'): '标题行：长期资产循环校验，不参与计算。',
('现金流量表','D39'): '年初长期资产账面/净额合计，来自各长期资产年初净额及其他非流动资产/开发支出。',
('现金流量表','D40'): '本年增加：以购建长期资产现金流出为起点，剔除进项税、预付/应付购建款、资本化利息等不应作为本年增加净贡献的项目。',
('现金流量表','D41'): '其他变动：源表当前为空/0。',
('现金流量表','D42'): '折旧摊销：固定资产、投房、使用权资产、无形资产、长摊的非现金减少。',
('现金流量表','D43'): '本年减少：处置/减少相关的净额，按D23整体、C198/C199加回、L13、减值、销项税抵消共同计算。',
('现金流量表','D44'): '年末推算数：D39:D43合计。',
('现金流量表','D45'): '空白分隔行，不参与计算。',
('现金流量表','D46'): '期末长期资产账面/净额合计。',
('现金流量表','D47'): '校验差异：循环推算年末数与账面数之差。',
}
ROW_LABEL_HINTS = {}
for sh in ['现金流量表底稿','固定资产','在建工程情况表','无形资产变动表','长期待摊费用变动明细表','投资性房地产','使用权资产 ','试算表','附注','资产减值明细表','数据源_CF小合并','长期应付款明细']:
if sh in wb_f.sheetnames:
ws=wb_f[sh]
for r in range(1, min(ws.max_row, 1300)+1):
labels=[]
for c in range(1, min(ws.max_column, 6)+1):
v=ws.cell(r,c).value
if isinstance(v,str) and v.strip():
labels.append(v.strip())
if labels:
ROW_LABEL_HINTS[(sh,r)]=' | '.join(labels[:3])
def value(sheet, cell, wbv=wb_v):
try:
v = wbv[sheet][cell].value
except Exception:
return None
return v
def formula(sheet, cell, wbf=wb_f):
try:
v = wbf[sheet][cell].value
except Exception:
return None
return v if isinstance(v, str) and v.startswith('=') else None
def raw(sheet, cell, wbf=wb_f):
try:
return wbf[sheet][cell].value
except Exception:
return None
def nfmt(v):
if v is None or v == '': return ''
if isinstance(v, (int,float)):
if abs(v) < 0.0000005: v=0
return f'{v:,.2f}'
return str(v)
def normalize_ref(ref, current_sheet):
ref = ref.strip()
# remove structured table refs / named ranges crudely
if '[' in ref and '!' not in ref:
return None
if '!' in ref:
sh, addr = ref.rsplit('!', 1)
sh = sh.strip("'")
else:
sh, addr = current_sheet, ref
addr = addr.replace('$','')
if sh.startswith('[') or '[' in sh:
return ('EXTERNAL', sh, addr)
return (sh, addr)
def expand_addr(addr):
addr = addr.replace('$','')
# remove workbook sheet prefix if accidentally remains
if '!' in addr:
addr = addr.rsplit('!',1)[1]
if ':' in addr:
try:
min_col, min_row, max_col, max_row = range_boundaries(addr)
except Exception:
return [addr]
if None in (min_col, min_row, max_col, max_row):
return [addr]
cells=[]
count=(max_col-min_col+1)*(max_row-min_row+1)
if count > 300:
return [addr]
for r in range(min_row, max_row+1):
for c in range(min_col, max_col+1):
cells.append(f'{get_column_letter(c)}{r}')
return cells
return [addr]
def refs_in_formula(formula_text, current_sheet):
if not formula_text or not isinstance(formula_text, str) or not formula_text.startswith('='):
return []
out=[]
try:
toks=Tokenizer(formula_text).items
except Exception:
return out
for t in toks:
if t.type == 'OPERAND' and t.subtype == 'RANGE':
nr = normalize_ref(t.value, current_sheet)
if not nr: continue
out.append(nr)
return out
def direct_ref_count(formula_text, current_sheet, expand_ranges=False):
refs = refs_in_formula(formula_text, current_sheet)
n=0
for sh, addr in refs:
if sh == 'EXTERNAL':
n += 1
elif expand_ranges:
n += len(expand_addr(addr))
else:
n += 1
return n
class Trace:
def __init__(self):
self.lines=[]
self.leaves=OrderedDict()
self.nodes=OrderedDict()
self.node_count=0
def add_leaf(self, root, sheet, cell, kind, val, form_or_raw, depth, note=''):
key=(root, sheet, cell, kind, str(form_or_raw), note)
if key not in self.leaves:
self.leaves[key]={
'root': root, 'sheet': sheet, 'cell': cell, 'kind': kind,
'value': val, 'formula_or_raw': form_or_raw, 'note': note, 'depth': depth,
'label': ROW_LABEL_HINTS.get((sheet, int(re.findall(r'\d+', cell)[0])),'') if re.search(r'\d+', cell) else ''
}
def trace_cell(self, root_label, sheet, cell, depth=0, max_depth=8, visited=None):
if visited is None: visited=set()
indent=' '*depth
if sheet == 'EXTERNAL':
self.lines.append(f'{indent}- EXTERNAL `{cell}`')
self.add_leaf(root_label, sheet, cell, 'EXTERNAL_REF', None, cell, depth)
return
if sheet not in wb_f.sheetnames:
self.lines.append(f'{indent}- UNKNOWN_SHEET `{sheet}!{cell}`')
self.add_leaf(root_label, sheet, cell, 'UNKNOWN_SHEET', None, '', depth)
return
node=(sheet,cell)
v=value(sheet,cell)
f=formula(sheet,cell)
rv=raw(sheet,cell)
label=ROW_LABEL_HINTS.get((sheet, int(re.findall(r'\d+', cell)[0])),'') if re.search(r'\d+', cell) else ''
labtxt=f' — {label}' if label else ''
if node in visited:
self.lines.append(f'{indent}- `{sheet}!{cell}` = {nfmt(v)} [循环引用/已展开]{labtxt}')
return
if f:
self.lines.append(f'{indent}- `{sheet}!{cell}` = {nfmt(v)} ; `{f}`{labtxt}')
if depth >= max_depth:
self.add_leaf(root_label,sheet,cell,'MAX_DEPTH_FORMULA',v,f,depth,label)
return
visited.add(node)
refs=refs_in_formula(f,sheet)
if not refs:
self.add_leaf(root_label,sheet,cell,'FORMULA_NO_CELL_REF',v,f,depth,label)
return
for ref_tuple in refs:
if len(ref_tuple) == 3 and ref_tuple[0] == 'EXTERNAL':
_, ext_book, ext_addr = ref_tuple
self.lines.append(f'{indent} - EXTERNAL `{ext_book}!{ext_addr}`')
self.add_leaf(root_label,'EXTERNAL',f'{ext_book}!{ext_addr}','EXTERNAL_REF',None,f'{ext_book}!{ext_addr}',depth+1)
continue
rsh, addr = ref_tuple
cells=expand_addr(addr)
# for ranges, show range header then children
if len(cells)>1:
self.lines.append(f'{indent} - range `{rsh}!{addr}` ({len(cells)} cells)')
for c in cells:
self.trace_cell(root_label,rsh,c,depth+1 if len(cells)==1 else depth+2,max_depth,visited.copy())
else:
kind='MANUAL_OR_BLANK'
if rv is None or rv == '': kind='BLANK_LEAF'
elif isinstance(rv,str) and rv.startswith('['): kind='EXTERNAL_LITERAL'
self.lines.append(f'{indent}- `{sheet}!{cell}` = {nfmt(v)} ; raw=`{rv}` [{kind}]{labtxt}')
self.add_leaf(root_label,sheet,cell,kind,v,rv,depth,label)
def make_trace(root_sheet, root_cell):
t=Trace()
t.trace_cell(f'{root_sheet}!{root_cell}', root_sheet, root_cell, 0)
return t
# Trace roots. Include intermediate rows that D40/D43/D42 depend on.
roots = [('现金流量表', c) for c in ['D38','D39','D40','D41','D42','D43','D44','D45','D46','D47']]
extra_roots = [('现金流量表','D28'),('现金流量表底稿','C251'),('现金流量表','D23'),('现金流量表底稿','C204'),('现金流量表','L8'),('现金流量表','L9'),('现金流量表','L10'),('现金流量表','L11'),('现金流量表','L12'),('现金流量表','L13')]
traces=[]
all_leaves=[]
for sh,cell in roots+extra_roots:
if sh not in wb_f.sheetnames: continue
t=make_trace(sh,cell)
traces.append((sh,cell,t))
all_leaves.extend(t.leaves.values())
# Write leaf csv.
with LEAF_CSV.open('w', newline='', encoding='utf-8-sig') as f:
writer=csv.DictWriter(f, fieldnames=['root','sheet','cell','kind','value','formula_or_raw','label','note','depth'])
writer.writeheader(); writer.writerows(all_leaves)
# Formula atomicity audit for R29:R58, source and patched.
def atomicity_rows(name, wbf, wbv):
rows=[]
ws=wbf['长期资产循环_全量穿透校验']; wsv=wbv['长期资产循环_全量穿透校验']
for r in range(29,59):
for c in range(1,15):
cell=ws.cell(r,c); val=wsv.cell(r,c).value; x=cell.value
if isinstance(x,str) and x.startswith('='):
coord=cell.coordinate
refs=refs_in_formula(x,'长期资产循环_全量穿透校验')
expanded=sum(1 if sh=='EXTERNAL' else len(expand_addr(addr)) for sh,addr in refs)
direct=len(refs)
col=cell.column_letter
is_control = col in ['K','L','M','N']
# Parent/summary rows allowed to aggregate child cells in K or section parent C:J rows.
parentish = (r in [33,45,50,56,58] and (col in list('CDEFGHIJK'))) or col in ['K','M','N']
issue=[]
if not is_control and not parentish and expanded>1:
issue.append('子项单元格含多个引用/范围，需判断是否可拆')
if '现金流量表底稿!' in x:
issue.append('仍停在现金流量表底稿，需下钻到底层来源')
if '长期资产循环校验_公式明细' in x:
issue.append('引用旧helper，不是底层/内部公式')
if direct==0 and not any(fn in x.upper() for fn in ['SUM','N(','IF(']):
issue.append('公式无可解析引用')
rows.append({
'workbook': name, 'cell': coord, 'row': r, 'col': col,
'item': ws.cell(r,2).value, 'formula': x, 'value': val,
'direct_refs': direct, 'expanded_refs': expanded,
'issue': '；'.join(issue),
})
return rows
atom_rows = atomicity_rows('source_before_patch', wb_f, wb_v) + atomicity_rows('delivered_safe_patch', wp_f, wp_v)
with ATOM_CSV.open('w', newline='', encoding='utf-8-sig') as f:
writer=csv.DictWriter(f, fieldnames=['workbook','cell','row','col','item','formula','value','direct_refs','expanded_refs','issue'])
writer.writeheader(); writer.writerows(atom_rows)
# manual proposed split table (strict atomicity)
proposals = [
('R32','I32/J32','I32=附注!F364；J32=试算表!B71；删除B61影响','D39原公式有两个独立底层项；现有I/J两格可承载。'),
('R34','D34 + 新增/空余格','D34=在建工程情况表!D24；另列/另行=-在建工程情况表!Q24','D34当前D24-Q24是两个业务项；现有列含义不完全匹配，最好新增“减：在建工程转固/转出”子行。'),
('R36','C36/D36','C36=现金流量表底稿!C240 或 附注!C691；D36=-附注!C691','进项税进入D28后又被D40扣除，应展示为一正一负而非一个净额公式。'),
('R37','C37/D37','C37=现金流量表底稿!C241；D37=-现金流量表底稿!C241','利息资本化在D28中出现后被D40扣除，净额0应由两个格子呈现。'),
('R38','C38/D38','C38=现金流量表底稿!C242；D38=-现金流量表底稿!C242','预付账款变动同理。'),
('R39','C39/D39','C39=现金流量表底稿!C243；D39=-现金流量表底稿!C243','应付购建变动同理。'),
('R40','I40/J40','I40=附注!E364；J40=-附注!F364','其他非流动资产期末-期初可拆。'),
('R41','C41/D41','C41=附注!D756；D41=-附注!C756','长期应付款期末-期初可拆。'),
('R42','H42/I42','H42=-长期应付款明细!G23；I42=长期应付款明细!H23','融资租赁租入长期资产可拆。'),
('R43','C43/D43','C43=-附注!H1244；D43=附注!I1244','其他应付设备工程款变动可拆；且H1244/I1244还要标注外部AP OP叶子。'),
('R44','H44/I44/J44','H44=现金流量表底稿!C248；I44=C249；J44=C250','C248:C250不应一个SUM塞在H44。'),
('R51','C:J各列','C51=-C188；G51=-C189；E51=-C190；F51=-C193；H51=-C200；D51=-C201；I51=-C202；J51=-C203','D43中D23非抵消处置净值/其他项可用现有8列拆开；但C188等还需在附表继续下钻到资产明细。'),
('R52','C/D/E/G/J','C52=-C191；E52=-C192；D52=-C194；G52=-C195；J52=试算表!Y22','处置损益与L13/Y22抵消应拆格展示。'),
('R53','C53/D53','C53=-C196；D53=-附注!C1067','报废毁损与L13/C1067抵消应拆格展示。'),
('R54','C54/D54','C54=-C197；D54=附注!C689','销项税在D23中与D43 +附注C689抵消，应拆格展示。'),
('R55','C55/D55/E55','已基本可拆：-资产减值明细表!C23/C24/C25','如果要更底层，还需追资产减值明细表C23:C25是否有公式。'),
]
# Build report.
lines=[]
lines.append('# Phase 7 — 现金流 D38:D47 深度下钻到底层 + R29:R58 公式原子性全面调研')
lines.append('')
lines.append(f'- 源文件：`{SRC}`')
lines.append(f'- 上一版安全修补文件（本报告审计其仍未足够原子化之处）：`{PATCHED}`')
lines.append(f'- 叶子明细 CSV：`{LEAF_CSV}`')
lines.append(f'- R29:R58 原子性审计 CSV：`{ATOM_CSV}`')
lines.append('')
lines.append('## 0. 对上轮工作的更正')
lines.append('你指出得对：上一版报告/修补虽然把主行 `K=L` 做平了，但很多公式链只停在 `现金流量表底稿`，没有继续下钻到资产明细、附注、试算表、数据源或外部/手工叶子；另外我把多项业务公式塞进单个单元格（例如修补版 `C51`），不符合“一个格子尽量承载一个业务公式/一个底层项”的检查要求。以下为重做的只读深度调研。')
lines.append('')
lines.append('## 1. 现金流量表 D38:D47 全公式、业务含义、数值验证')
lines.append('| 单元格 | 公式/内容 | cached值 | 业务含义 |')
lines.append('|---|---|---:|---|')
for c in ['D38','D39','D40','D41','D42','D43','D44','D45','D46','D47']:
f = formula('现金流量表',c) or raw('现金流量表',c)
lines.append(f'| {c} | `{f if f is not None else ""}` | {nfmt(value("现金流量表",c))} | {BUSINESS[("现金流量表",c)]} |')
lines.append('')
lines.append('### 1.1 复算校验')
# Manual computed values from cached dependencies.
d39 = sum((value('试算表',x) or 0) for x in ['B54','B57','B70','B75','B42','B71','B66']) + (value('附注','F364') or 0)
d40 = (value('现金流量表','D28') or 0) - (value('附注','C691') or 0) - (value('现金流量表底稿','C242') or 0) - (value('现金流量表底稿','C243') or 0) - (value('现金流量表底稿','C241') or 0)
d42 = -sum((value('现金流量表',x) or 0) for x in ['L9','L11','L12','L8','L10'])
d43 = -((value('现金流量表','D23') or 0) - (value('现金流量表底稿','C198') or 0) - (value('现金流量表底稿','C199') or 0)) - (value('现金流量表','L13') or 0) - sum((value('资产减值明细表',x) or 0) for x in ['C23','C24','C25']) + (value('附注','C689') or 0)
d44 = d39+d40+0+d42+d43
d46 = sum((value('试算表',x) or 0) for x in ['H54','H57','H70','H75','H42','H71','H66']) + (value('附注','E364') or 0)
d47 = d44-d46
for cell,calc in [('D39',d39),('D40',d40),('D42',d42),('D43',d43),('D44',d44),('D46',d46),('D47',d47)]:
srcv=value('现金流量表',cell) or 0
lines.append(f'- `{cell}` 复算 `{nfmt(calc)}`；源表 `{nfmt(srcv)}`；差异 `{nfmt(calc-srcv)}`。')
lines.append('')
lines.append('结论：现金流侧公式和值仍然能复算正确；本次新增的问题是**底层穿透和目标 sheet 公式拆分粒度不足**。')
lines.append('')
# Deep trace sections. Limit lines in report but enough.
lines.append('## 2. 递归下钻到底层（不再停在现金流量表底稿）')
lines.append('说明：下面 trace 中 `MANUAL_OR_BLANK/BLANK_LEAF` 是 workbook 内可见的底层手工/空白叶子；`EXTERNAL_REF` 是无法在本 workbook 内继续追的外部来源。完整叶子清单另见 CSV。')
for sh,cell,t in traces:
if (sh,cell) not in roots and (sh,cell) not in extra_roots:
continue
title = f'{sh}!{cell}'
# include main roots fully; extra roots shorter.
if (sh,cell) in roots or cell in ['C251','C204']:
lines.append(f'### 2.x {title}')
lines.extend(t.lines[:260])
if len(t.lines)>260:
lines.append(f'- ...（该 trace 共 {len(t.lines)} 行，完整叶子见 CSV）')
lines.append('')
# Summarize critical bottom paths.
lines.append('## 3. 关键底层叶子结论')
crit = [
('D40/C251','C238', '购入主项：投房D8 + 固资H7 + 无形I7 + 在建D24 + 长摊F17 - 在建Q24，未停在C238；其中固资H7继续为固定资产B7:G7，CIP继续为在建工程D6:D23/Q6:Q23。'),
('D40/C240','附注!C691', '进项税底层是附注手工/输入值 56,238,565.00；在D40中应被扣除，净贡献为0。'),
('D40/C247','附注!H1244/I1244', '其他应付款设备工程款变动继续下钻到外部 `[1]AP OP` B8/C8；这是外部叶子，不可在本 workbook 内继续追。'),
('D42','现金流量表底稿C408/C412/C416/C420/C424', '均已继续下钻到固定资产H30、投房D29、使用权G21、无形I27、长摊L17，再到各自明细行/空白叶子。'),
('D43/C204','C188:C203', 'C188/C189/C190/C200继续下钻到资产明细；C191/C192/C194/C195继续下钻到附注/试算表；C196/C197/C202分别到附注C1067/C689/C1051；C201是手工数 -49,437.52；C193/C198/C199/C203为空叶子。'),
('D46','试算表H54/H57/H70/H75/H42/H71/H66 + 附注E364', '试算表项继续下钻到试算表组成项，再到数据源_CF小合并或明细；附注E364为空叶子。'),
]
for anchor, leaf, meaning in crit:
lines.append(f'- **{anchor} → {leaf}**：{meaning}')
lines.append('')
# Atomicity summary
lines.append('## 4. R29:R58 公式原子性审计：哪里仍然不够细')
lines.append('判断口径：K/M/N 等控制列允许是合计/校验公式；父行允许 `SUM(子项)`；但子项格如果同时承载多个底层业务项，或仍引用 `现金流量表底稿`/旧 helper，就需要继续拆。')
# collect significant atom issues delivered patch
sig=[r for r in atom_rows if r['workbook']=='delivered_safe_patch' and r['issue']]
lines.append(f'- delivered SAFE 版本中被标记的问题公式格：`{len(sig)}` 个（详见 CSV）。')
lines.append('- 最重要的问题不是主行是否已经打平，而是明细格是否能解释到“一个格子一个底层项/一个业务公式”。上一版仍有多处未达标。')
lines.append('')
lines.append('### 4.1 可在现有 R29:R58 内拆开的公式')
lines.append('| 位置 | 建议承载格 | 拆分建议 | 原因 |')
lines.append('|---|---|---|---|')
for loc,cells,split,reason in proposals:
lines.append(f'| {loc} | {cells} | `{split}` | {reason} |')
lines.append('')
lines.append('### 4.2 现有范围不够承载，需要新增明细区/附表的公式')
lines.append('- `固定资产!H7/H15/H30/H37/H54` 这类本身又是 `SUM(B:G)` 或多项差额。若要求彻底到底层且一个格子只承载一个底层项，R29:R58 的 8 个资产列不够同时展示固定资产内部的原值、累计折旧、减值、处置、折旧等所有拆分；需要新增“固定资产底层展开”明细区或另一个 MD/worksheet。')
lines.append('- `在建工程情况表!D24/Q24/E24` 继续展开为 `D6:D23/Q6:Q23/E6:E23`；现有 R34/R37 只是一行级承载，不足以每个工程明细一格。')
lines.append('- `附注!H1244/I1244` 是外部 `[1]AP OP` 引用， workbook 内无法继续追；应在报告中标 `EXTERNAL_LEAF`，不能假装底层已在本表内。')
lines.append('- `试算表!H54/H57/...` 继续下钻到 `数据源_CF小合并`；如果要求完全 trace，R57 不能只写 `试算表!H54`，必须有单独底层明细区列出 `H51-H52-H53` 等组成。')
lines.append('')
lines.append('## 5. 对上一版修补文件的结论')
lines.append('- 上一版修补文件可以作为“主行打平版”：R32/R33/R45/R49/R50/R56/R57/R58 都 `K=L`。')
lines.append('- 但按你现在强调的严格标准，它**不是最终版**：因为很多子项格仍是净额公式或大公式，且有些公式仍引用 `现金流量表底稿` 而未在 sheet 内继续展示到底层。')
lines.append('- 正确下一步应先做“公式原子化设计”：在 R29:R58 内能拆的先拆；不够承载的新增明细区/附表，不要再把多项塞进一个格子。')
lines.append('')
lines.append('## 6. 下一步修正口径建议')
lines.append('1. 保留 `L` 列作为 benchmark（现金流 D列/底稿），`K/M/N` 作为合计与校验。')
lines.append('2. R33/R50 等父行仍必须等于可见子项合计，但子项不再用净额大公式；改为“正项一格、抵消项一格”。')
lines.append('3. R34:R44、R51:R55 内现有空格先用来拆；如果还不够，新增一个 `长期资产循环_底层穿透明细` sheet 或在本 sheet 下方新增明细区。')
lines.append('4. 对所有外部引用（如 `[1]AP OP`）标注 `EXTERNAL_LEAF`，对手工数（如 `现金流量表底稿!C201=-49,437.52`）标注 `MANUAL_LEAF`。')
lines.append('5. 修 workbook 前先按本报告的拆分表确认 layout，否则继续在同一格里塞公式会再次违背你的要求。')
REPORT.write_text('\n'.join(lines), encoding='utf-8')
print(json.dumps({'report':str(REPORT),'leaf_csv':str(LEAF_CSV),'atom_csv':str(ATOM_CSV),'leaf_rows':len(all_leaves),'atom_rows':len(atom_rows)},ensure_ascii=False,indent=2))