Excel Template Contract Fill

模板 7 列(顺序不变):

合约代码, 商品名称, 交割月, 收盘价, 持仓量, 最低保证金, 最低保证金率

工作流程(四步)

Step 1 — 探测原始文件

不要把文件名写死。用户可以往 原始数据/期货数据/ 和 原始数据/合约信息/ 放任意命名的文件 —— 比如 上期所.xlsx、shfe_20260411.xlsx、SHFE行情.csv、daily.xlsx 都是合法输入。AI 的职责是 自己配对 + 自己识别交易所,不是要求用户按某种固定命名来。

本步骤分三小步:① 扫描 → ② 按文件名配对 → ③ 按内容嗅探交易所。

① 扫描两个目录

分别列出 原始数据/期货数据/ 和 原始数据/合约信息/ 下所有扩展名是 .csv / .xlsx / .xlsm / .html 的文件。忽略 ~$ 临时文件、.DS_Store、空文件。

② 按文件名成对(stem 匹配)

把每个 期货数据/ 的文件,和 合约信息/ 的文件按 stem(去掉扩展名的文件名)配对。配对时不看扩展名 —— foo.xlsx 和 foo.csv 算一对。

匹配强度分三档,按顺序尝试:

1. 精确 stem 相等(最强):期货数据/上期所.xlsx ↔ 合约信息/上期所.csv —— stem 都是 上期所
2. 规范化 stem 相等:把两边 stem 都做 lower()、去空格 - _ .、去尾部日期段 [_\-]?\d{6,8}$、去 行情/数据/参数/合约/信息 这类后缀词。例如 shfe_20260411.xlsx ↔ SHFE-行情.csv → 规范化后都是 shfe → 配对
3. 唯一剩余 1-对-1 兜底:如果前两档都匹配不上,但两个目录里都恰好只剩 1 个未配对的文件,直接把它们配对(假定用户就这一套数据),并在 缺失原因汇总.csv 里写一条 info 级记录说明是兜底配对

配对失败的处理:

• 一侧有文件另一侧没有(半对):记一条 {交易所?,*,文件,半对: 期货数据/xxx 找不到对应的 合约信息},该文件跳过,不要凭一半猜
• 同 stem 多个文件:用第一个文件名字典序最小的,其余写入缺失汇总
• 存在孤立文件(两侧都有但数量不等):多出来的那几个写入缺失汇总

③ 对每一对做"内容嗅探",确定属于哪个交易所

配对完只是说"这两个文件是一组",并不代表知道它是上期所还是大商所。交易所身份完全由 文件内容指纹 决定,不看文件名:

嗅探优先顺序:先嗅行情侧,不确定再嗅合约信息侧。两侧嗅探结果冲突 → 往缺失汇总写一条 文件对冲突: 行情疑似X,合约信息疑似Y,跳过。

文件名是最后的辅助提示,只有当内容指纹完全无法判断时(比如文件被截断)才拿文件名 stem 里的 shfe/dce/czce/gfex/上期所/大商所/郑商所/广期所 子串来兜底。

跑完 Step 1 要产出的信息

• 一个 (交易所, 行情文件路径, 合约信息文件路径) 的列表(长度 = 成功识别的交易所数量,理想是 4)
• 未成功配对 / 未成功嗅探的文件单独列一份,全部写进 缺失原因汇总.csv 里
• 如果扫完后一个合法对都没有(list 长度 0),立刻 raise,不要硬着头皮往下跑出一堆空 xlsx

文件名只是辅助提示,最终判断必须看内容指纹。

Step 2 — 字段语义映射

对每个识别出的文件,把它的列匹配到模板 7 列。用语义匹配,不要锁死列索引:

• find_col(header, ...) 应当用 包含匹配,候选名给一组(覆盖中英文 / 同义词 / 变体)
• 列索引兜底只能作为"语义匹配失败时的最后防线",并且必须在注释里写明索引来源的样本日期
• 找不到的列写进缺失汇总,不要假装算出来了

详见下方 §字段语义规则。

Step 3 — 计算可计算字段

最重要的是 最低保证金。优先级:

1. 直取金额(目前只有大商所给)
2. 价格 × 最低保证金率 × 交易单位(交易单位来自品种字典)
3. 任一项缺 → 写缺失汇总

Step 4 — 自检

跑完后必须按 §自检规则 全量检查一遍。不要用固定行数做验收(行数每天都会变),用语义化的检查项。

字段语义规则(核心)

1. 合约代码

• 形态:大写字母前缀 + 3~4 位数字 + 可选单字母后缀
• 正则:^[A-Za-z]+\d{3,4}[A-Za-z]?$
  • 例:CU2605, a2605, AP605, l2605F(大商所滚动交割)
• 大小写约定(保持各交易所原始命名习惯):
  • 上期所、大商所、广期所:小写输出
  • 郑商所:大写输出
• 权威清单:合约信息侧 是当日所有挂牌合约的权威列表,行情侧只是用来补价格和持仓量。两边应当 left join(以合约信息为主,行情为辅)。

2. 商品名称

来源优先级(从上到下):

1. 行情侧的 品种名称 / 商品名称 / Product 列(如果有)
2. 合约信息侧的 产品名称 / 品种名称 列
3. 根据合约代码前缀查内置字典(见 §品种前缀字典)
4. 都没有 → 直接用前缀字符串本身(便于后续人工发现)

⚠️ 郑商所行情文件 没有 品种列,商品名称必须从合约信息 CSV 的 产品名称 字段取。

3. 交割月

• 输出格式固定:YYYY-MM
• 来源优先级:
  1. 合约信息的 年份代码 + 月份代码 字段(目前只有郑商所 CSV 这样)
  2. 行情的 交割月份 列(若是 4 位数字)
  3. 从合约代码末尾 3~4 位数字解析:
     • 2605 → 2026-05
     • 605 → 2026-05(单位数年代码 → 加 202 前缀)
• 允许末尾带单字母后缀(l2605F 的 F 不参与解析)

4. 收盘价(价格回退链 — 非常重要)

按这个顺序取,第一个非零值就用它:

1. 收盘价 / 今收盘 / ClosePrice
2. 结算价 / 今结算 / SettlementPrice
3. 昨结算 / 前结算价 / PreSettlementPrice

0.00 一律视为"当日无交易",不算有效价。这是郑商所长尾合约的常见情况——不回退就会丢掉 60+ 条记录。

5. 持仓量

• 来源:行情的 持仓量 / 持仓手 / OpenInterest 列
• 0 是合法值(表示当日无持仓),不算缺失

6. 最低保证金率

• 来源:合约信息中名字含 保证金率 / 保证金比例 / MarginRate / 投机买 / 一般持仓买保证金率 等的列
• 统一输出 0~1 之间的小数
• 解析启发式(自动判断输入形态):

  输入形态	处理
  20.00%(带百分号字符串)	去掉 %,再 ÷100
  0.20(0~1 小数)	直接用
  15(>1 数字)	÷100(按百分比理解)

SHFE 专项 — 保证金率列名多级回退(必须照抄)

上期所合约参数表的列名没有"投机"两个字。2026 年在用的列名是 一般持仓买保证金率(%) / 一般持仓卖保证金率(%),语义上等于"投机买 / 卖保证金率"。如果你只用 "投机" in h and "保证金" in h 做判断,会一条都匹配不上。正确做法是多级回退:

ir_col = None
# ① "投机" + "保证金"(某些年份的命名)
for j, h in enumerate(ih):
    if "投机" in h and "保证金" in h:
        ir_col = j; break
# ② "一般持仓" + "买"(2026 年在用)
if ir_col is None:
    for j, h in enumerate(ih):
        if "一般持仓" in h and "买" in h:
            ir_col = j; break
# ③ "一般持仓" + "保证金"(兼容"一般持仓保证金率"合并列)
if ir_col is None:
    for j, h in enumerate(ih):
        if "一般持仓" in h and "保证金" in h:
            ir_col = j; break
# ④ 含"保证金率"但不含"套保"的任意列(最后兜底)
if ir_col is None:
    for j, h in enumerate(ih):
        if "保证金率" in h and "套保" not in h:
            ir_col = j; break
# 仍然是 None → 参数表结构变了,往缺失原因汇总写 "参数表无可用保证金率列",不要静默返回 0 行

同一只合约的买保证金率 = 卖保证金率(在我们观察到的所有年份里都一样),所以"一般持仓买"就足够,不需要再平均一次。

7. 最低保证金(核心字段)

计算优先级:

1. 直取金额:如果合约信息有 保证金金额(元/手) / 保证金(投机)金额 这类直接金额列(目前只有大商所),直接用
2. 公式计算:价格 × 最低保证金率 × 交易单位
   • 价格 = §字段 4 算出的"收盘价"
   • 交易单位 = §品种前缀字典 里的"每手数量"
3. 缺一不可:任意一项缺 → 不要写 0、不要留空 → 写进 缺失原因汇总.csv,字段写明缺哪个

反推备用(几乎用不到):如果直取到了金额但合约信息里没有比例,可以反推 保证金率 = 金额 / (价格 × 交易单位)。

交易所结构指纹

即使文件名被改了,从内容也能认出来:

上期所(SHFE)

⚠️ 上期所行情文件是最容易全盘翻车的一个。它既没有 合约代码 列、也没有 品种名称 列 —— 合约代码必须由 AI 自己拼出来。大量 AI(包括 GPT / Kimi 等)第一次跑都会在这里静默丢掉整个交易所的数据,请严格按下面伪代码处理。

🚨 两个已在真实 bug 里出现过的字面量陷阱 —— 必须照抄、不要复述本章节文字:

1. 本章节把"按品种分组的段"口语化描述为 品种段。真实文件里那一行的字面值是 商品名称:铜,不是 品种:铜。错误示范是看到文字"品种段"就写出 if "品种" in c0: ... —— "品种" 不是 "商品名称" 的子串,永远匹配不上,上期所直接 0 行出局。正确写法必须用字面量 "商品名称" 做判断(见下方伪代码 ①)。
2. 保证金率列 没有"投机"两个字。上期所 2026 年的合约参数表真实列名是 一般持仓买保证金率(%) 和 一般持仓卖保证金率(%)。历史上其他 AI 文档说"投机保证金率",导致有人写 if "投机" in h and "保证金" in h: ... —— 永远匹配不上,整表 join 失败。正确写法必须把 一般持仓买 加进候选列表,或做多级回退(见 §字段语义规则 §6 最低保证金率 §SHFE 专项)。

这两个 bug 曾让上期所产出 0 行 + 缺失汇总空,用户直到看最终产物才发现。规则没错,错的是"照着规则描述的文字做 contains 判断"这个偷懒动作 —— 看文件实际的字节,不要看 SKILL 对它的文字描述。

上期所行情解析伪代码(必须照做)

# SHFE_PRODUCTS: 中文品种名 -> (品种前缀, 每手数量)
# 例如: "铜" -> ("cu", 5), "黄金" -> ("au", 1000), "SCFIS欧线" -> ("ec", 50)
SHFE_PRODUCTS = {
    "铜": ("cu", 5), "铝": ("al", 5), "锌": ("zn", 5), "铅": ("pb", 5),
    "镍": ("ni", 1), "锡": ("sn", 1), "铸造铝合金": ("ad", 10),
    "黄金": ("au", 1000), "白银": ("ag", 15),
    "螺纹钢": ("rb", 10), "线材": ("wr", 10), "热轧卷板": ("hc", 10),
    "不锈钢": ("ss", 5), "氧化铝": ("ao", 20),
    "燃料油": ("fu", 10), "石油沥青": ("bu", 10), "天然橡胶": ("ru", 10),
    "丁二烯橡胶": ("br", 5), "纸浆": ("sp", 10), "胶版印刷纸": ("op", 40),
    "原油": ("sc", 1000), "低硫燃料油": ("lu", 10), "20号胶": ("nr", 10),
    "铜(BC)": ("bc", 5), "SCFIS欧线": ("ec", 50),
}

current_product = None   # 正在解析的品种(中文)
for row in rows[3:]:     # 跳过标题 + 说明 + 表头
    cell0 = str(row[0] or "").strip()

    # ① 品种段起始:"商品名称:铜"
    if cell0.startswith("商品名称"):
        name = cell0.split(":", 1)[1].strip() if ":" in cell0 else cell0.replace("商品名称", "").strip()
        current_product = name if name in SHFE_PRODUCTS else None
        continue

    # ② 品种段结束:小计 / 合计 → 清空 current_product,避免串到下一个品种
    if cell0 in ("小计", "合计") or cell0.startswith("小计"):
        current_product = None
        continue

    # ③ 跳过子品种(不是独立合约)
    if current_product and "TAS" in cell0.upper():  # 原油TAS
        continue
    if current_product and "期转现" in cell0:        # 铜期转现
        continue

    # ④ 合约月份行:cell0 形如 "2604" / "2605" / ...
    if current_product and cell0.isdigit() and len(cell0) in (3, 4):
        prefix, lot = SHFE_PRODUCTS[current_product]
        contract_code = f"{prefix}{cell0}"       # ← 必须在这里拼!cu + 2605 = cu2605
        close_price = pick_first_nonzero(row[5], row[6], row[1])  # 收盘价 > 结算参考价 > 前结算
        open_interest = to_int(row[11])
        yield {
            "合约代码": contract_code,
            "商品名称": current_product,
            "交割月": parse_delivery(cell0),  # 2605 -> "2026-05"
            "收盘价": close_price,
            "持仓量": open_interest,
            "_lot": lot,
            "_product_cn": current_product,
        }

然后 去合约信息里把 cu2605 对应的保证金率 join 回来,最后用 价格 × 保证金率 × lot 算出最低保证金。

如果跑完后 上期所.xlsx 的 数据 sheet 只有表头 0 行 —— 一定是上面这步"拼合约代码"没做,而不是数据缺失。不要静默输出空表。

大商所(DCE)

郑商所(CZCE)

广期所(GFEX)

品种前缀字典

脚本必须维护这几个字典(前缀 -> (中文品种名, 每手数量))。参考实现见 tools/convert_raw_futures_to_template.py 里的 SHFE_PRODUCTS / DCE_PRODUCTS / GFEX_PRODUCTS。郑商所的品种映射不靠字典,直接从合约信息的 产品名称 列取。

关于"每手数量"

它 不是 "每手对应多少吨",而是 每手的报价单位数 —— 也就是公式 价格 × 保证金率 × 交易单位 里的 交易单位。比如:

只要确保"价格 × 这个数字 × 保证金率"得到的金额单位是元,就对了。

发现新品种(字典里没有)怎么办

1. 不要 silently 跳过这个合约
2. 优先从合约信息里直接取 产品名称、交易单位,落库
3. 如果合约信息里给了"交易单位"文本(比如 10吨/手),解析出数字直接用,不依赖字典
4. 在 映射表 sheet 里标注:"该品种是字典外新品种,需要补字典"
5. 在 缺失原因汇总.csv 里写一条:<交易所>, <代码>, 品种, 新品种未登记: <前缀>
6. 跑完后人工把这个前缀加到对应的 *_PRODUCTS 字典里,下次就自动了

数字解析必须鲁棒

不管是行情还是合约信息,数字字段可能长这样:

自检规则(每次跑完必做)

不要用固定行数验收 —— 行数每天都会变。用以下这些语义检查:

🛑 硬失败检查(必须 assert / raise,不满足就中止,不能静默写空表)

脚本跑完 在写文件之前,必须对每个交易所执行以下硬检查。任何一条不过,立刻报错退出,让用户看到具体哪个交易所崩了,而不是让空文件悄悄进入下一步。

1. 每个交易所 数据 sheet 至少 ≥ 合约信息侧合约数 × 80%
   • 这是最重要的一条。如果上期所/大商所/郑商所/广期所任一交易所只剩几行甚至 0 行,几乎 100% 是解析规则写错了(列没认出、拼合约代码没做、品种段状态没维护),而不是数据真的那么少
   • 错误消息模板:[硬失败] {交易所} 只解析出 {n} 行,合约信息有 {m} 行,远低于 80% —— 参见 SKILL 的《上期所行情解析伪代码》,检查是否正确拼接了合约代码
2. 如果某交易所整表 0 行,必须 raise,同时必须往 缺失原因汇总.csv 写一条:{交易所}, *, 全表, 解析器返回 0 行 —— 规则命中失败
   • 严禁写出 只有表头、0 数据行 的 xlsx 然后静默继续
3. 合约信息里存在但行情里完全找不到的品种数 ≤ 2(偶发停牌品种是正常的,但成片找不到就是解析器漏了)

软检查(写完文件后人工或脚本 warn)

1. 完整性:每个交易所输出文件的 数据 sheet 行数,应当 ≥ 该交易所合约信息侧合约数 × 95%
   • 如果显著低于,基本可以肯定:某个前缀没识别 / 某列名变了 / 某子品种没跳过
2. 缺失汇总状态:缺失原因汇总.csv 理想情况只有表头
   • 有行就按字段分桶看:
     • 某个品种成片缺 保证金率 → 该品种的合约信息文件结构变了
     • 某个品种成片缺 价格 → 价格回退链没启用
     • 新品种未登记: xx → 加字典
3. 抽查核心合约:任选 3 个成交活跃的合约(如 cu2605、a2605、AP605),手算 价格 × 保证金率 × 交易单位 是否等于输出文件里的最低保证金
4. 品种覆盖:每个交易所输出文件中,商品名称 的 unique 值数量应当接近该交易所在交易的品种总数
   • 上期所 ~25、大商所 ~24、广期所 5、郑商所更多
   • 显著偏少 → 漏识别
5. 格式健康:
   • 交割月 全部形如 YYYY-MM
   • 收盘价 没有 0 或空(0 应当通过回退链补到非零值,真补不到的不应该出现在 数据 sheet)
   • 最低保证金率 全部在 (0, 1) 区间
   • 数据 sheet 没有空单元格——要么有值,要么这一行根本不该出现

禁止事项

• ❌ 不要让 AI 自己把数据抄到 CSV 里"代替"运行脚本

• ❌ 不要把 0 当成有效价格

• ❌ 不要跳过 缺失原因汇总.csv 的自检

• ❌ 不要在 数据 sheet 里留空单元格

• ❌ 不要臆造品种代码映射;发现新品种就加进字典,而不是猜

• ❌ 不要用固定行数做验收;每天挂牌的合约数不一样

• ❌ 不要锁死列索引;先尝试语义匹配,索引只能作为最后兜底

• ❌ 严禁静默写出"只有表头 0 数据行"的 xlsx。任一交易所解析后行数为 0,必须 raise,并在 缺失原因汇总.csv 里记一条全表级失败(见 §自检规则 §硬失败检查)。GPT / Kimi 历史上在上期所就是这样翻车的 —— 行情侧没拼合约代码 → 产出 0 行 → 静默输出空表 → 下游套利结论表里整个上期所消失。

• ❌ 不要假设"行情每一行都自带 合约代码 列"。上期所行情里根本没有这一列,必须自己拼(见 §上期所行情解析伪代码);郑商所有这一列但要统一大写。

• ❌ 字面量陷阱 — 不要根据 SKILL.md 里对文件的文字描述反推 in / contains 判断。两个历史上真实发生过的 bug,都是把文档里的口语归纳当成文件里的字节:

  1. 本 skill 把"按品种分段"口语化成"品种段",有人写了 if "品种" in c0: ... —— 而原始文件里那一行的真实字面量是 商品名称:铜,"品种" 不是 "商品名称" 的子串,整个上期所 0 行。正确写法:if "商品名称" in c0: ...
  2. 本 skill 在最低保证金率章节历史上提过"投机保证金率",有人写了 if "投机" in h and "保证金" in h: ... —— 而原始文件的真实列名是 一般持仓买保证金率(%),不含"投机",整表 join 失败。正确写法见 §字段语义规则 §6 SHFE 专项的多级回退。

  铁律:识别关键字段之前,先打印原始文件前 10 行的真实字节(print(rows[:10])),看字符串字面量,再写判断。不要从 SKILL 的中文描述"倒推"代码。

参考实现

tools/convert_raw_futures_to_template.py 是本 skill 的参考实现:

python tools/convert_raw_futures_to_template.py

依赖:openpyxl。

它会读 原始数据/期货数据/ 和 原始数据/合约信息/ 下的 8 个文件,按本 SKILL 的规则写到 合约数据(整理后)/。

它不是唯一的执行路径:

• 如果脚本因为交易所改了格式而跑挂 → 修脚本 > 让 AI 自己凑数据
• 如果你不是 Claude,而是 Kimi / GLM / 其他 AI → 照着本 SKILL 的规则 重写一份等价脚本;不同 AI 写出来的代码细节可能不一样,但输出的 4 个 xlsx + 1 个 csv 应当语义等价
• 任何重写都必须遵循"先语义匹配、再索引兜底、最后写缺失汇总"的三步法,不要走捷径

修改本 skill 的时机

每次跑脚本时,如果发现了新的交易所行为(新品种、列名变了、出现了新的子品种要跳过),除了改脚本本身,还要回头更新本 SKILL.md 的对应章节。这样下一个 AI(或者下次的你自己)看到的就是最新的规则,而不是过时的快照。