学生用案例操作手册｜量化多因子策略（FF5 框架、A 股、零编程）

本手册面向零基础同学，指导你“自己到 CSMAR 抓数 + 用 Excel/WPS 完成横截面多因子小案例”。你将独立完成：数据下载、口径对齐、因子工程（winsor→标准化→规模中性化→合成）、Rank‑IC 与分组评估。全流程不写代码。

为兼容工具差异，每一步均提供两套做法：

A 版：Excel 365/Excel 2021/WPS 新版（支持 XLOOKUP、FILTER、动态数组）
B 版：Excel 2016/2019/WPS 经典版（无动态数组/XLOOKUP），用 INDEX/MATCH、数据透视与辅助列实现

注：学校没有 CSMAR 行业分类权限。本手册附“静态行业映射表”，请手工建表使用；或退化为“全市场标准化”。

0. 目标产出与所需数据

你将产出一个完整工作簿（建议命名：MF-FF5-Ashare.xlsx），含以下工作表：
- Data：月度行情与市值、行业、次期收益
- Financials_Annual：年度权益、资产、净利润与投资率
- Factors_Raw → Standardize → Neutralize → Composite → Research_Eval
因子与控制变量（横截面当月、用于预测次月收益）：
- 价值 HML： $BM = \frac{\text{BookEquity}}{\text{MktCap}}$
- 盈利 RMW： $ROE = \frac{\text{NetIncome}}{\text{BookEquity}}$
- 投资 CMA： $AG = \frac{\text{TotalAssets}_t}{\text{TotalAssets}_{t-1}} - 1$ ，课堂取负方向 AG_pos = -AG
- 规模控制 SMB：LogMktCap = LN(MktCap)（仅用于中性化，不进合成）
评估指标：
- Rank‑IC：当期 Score vs 次期收益 Ret_t1 的秩相关
- 五分位组合：Q1..Q5 次期收益与 Q5−Q1 长短差

建议样本：20 只 A 股 × 24–36 个月月度数据（名单见第 1 节）。

1. 样本股票与行业映射（手工建表，替代 CSMAR 行业）

请新建工作表 Industry_Lookup，建立两列：Ticker、Industry，手工录入下表（或复制粘贴）：

Ticker	NameCN	Industry
600519.SH	贵州茅台	食品饮料
000858.SZ	五粮液	食品饮料
603288.SH	海天味业	食品饮料
600036.SH	招商银行	银行
601318.SH	中国平安	保险
000333.SZ	美的集团	家电
300750.SZ	宁德时代	电力设备
002594.SZ	比亚迪	汽车
601012.SH	隆基绿能	电力设备
600309.SH	万华化学	化工
600276.SH	恒瑞医药	医药生物
300760.SZ	迈瑞医疗	医疗器械
002415.SZ	海康威视	电子/安防
600031.SH	三一重工	机械
601888.SH	中国中免	商贸零售
601899.SH	紫金矿业	有色金属
601668.SH	中国建筑	建筑
002714.SZ	牧原股份	农林牧渔
000002.SZ	万科A	房地产
600660.SH	福耀玻璃	汽车零部件

若你不想使用行业：把 Industry 一列全部填为 All，后续就做“全市场标准化”。

CSMAR 文本筛选用股票代码列表（直接粘贴）

含交易所后缀（部分网页端支持）：

600519.SH
000858.SZ
603288.SH
600036.SH
601318.SH
000333.SZ
300750.SZ
002594.SZ
601012.SH
600309.SH
600276.SH
300760.SZ
002415.SZ
600031.SH
601888.SH
601899.SH
601668.SH
002714.SZ
000002.SZ
600660.SH

仅六位数字（多数 CSMAR 客户端“Stkcd”字段用六位数）：

2. 从 CSMAR 抓取数据（你自己操作）

以下为常见的 CSMAR 客户端菜单路径。不同高校界面略有差异，请以本地为准。

2.1 月度交易与市值（TRD_Mnth）

菜单路径（示例）：金融市场数据库 → 股票市场 → 交易 → 股票月度（或 TRD_Mnth/股票月度交易数据）
条件：
- 股票代码：勾选上述 20 只（可粘贴代码筛选）
- 时间：近 24–36 个自然月
勾选字段（中英可能略有差异）：
- 证券代码 Stkcd
- 交易月份 Trdmnt
- 月度收益率（复权）Mretwd（若无此字段，可选“月收益率”或后续用价格自行计算）
- 月末总市值 Msmvttl
- 可选：月末收盘价 Close（或前复权收盘价，便于自算收益）
导出为 Excel：命名 TRD_Monthly.xlsx

说明：优先用 Mretwd（复权月收益）；若没有，将用“前复权收盘价”自算。

2.2 年度财务（资产负债表 + 利润表，合并报表口径）

资产负债表（年度）
- 菜单：上市公司财务 → 财务报表（合并）→ 资产负债表
- 条件：证券代码为 20 只；报表期选择每年的 12-31（年度报表）；时间范围覆盖目标样本年份的前后 1–2 年
- 字段：证券代码 Stkcd；报表期 Accper；资产总计 Totassets；归属于母公司股东的权益 Parentequity（或所有者权益合计）
- 导出为 BS_Annual.xlsx
利润表（年度）
- 菜单：上市公司财务 → 财务报表（合并）→ 利润表
- 条件同上
- 字段：证券代码 Stkcd；报表期 Accper；归属于母公司股东的净利润 Netprofit_parent（或净利润）
- 导出为 IS_Annual.xlsx

小贴士：如果“归母权益/净利润”字段命名不同，请选择含义相同的口径；确保为“合并报表、年度”。

3. 新建工作簿与导入原始表

新建 MF-FF5-Ashare.xlsx，创建以下工作表并导入数据。

Data：导入 TRD_Monthly.xlsx
- 重命名/保留列：Ticker（Stkcd）、Date（Trdmnt）、Mret（Mretwd）、MktCap（Msmvttl）、可选 Close
- 确保 Date 为日期格式（建议转为每月月末日期）
Financials_Annual：合并 BS_Annual.xlsx 与 IS_Annual.xlsx
- 建立列：Ticker、FiscalYear（由 Accper 提取年份）、BookEquity（Parentequity）、TotalAssets、NetIncome
- 同一 Ticker + FiscalYear 的两张表按键合并成一行

将三张表都转换为“表格”（Ctrl+T），命名：

TblData、TblFin、TblInd

合并 BS_Annual.xlsx 与 IS_Annual.xlsx（生成 Financials_Annual / TblFin）

目标：把两份“年度财务”合成一张标准表，列为：Ticker, FiscalYear, BookEquity, TotalAssets, NetIncome，并转为表对象 TblFin。

1) 导入两张源表并规范列

在 MF-FF5-Ashare.xlsx 中：
- 新建工作表 BS_Annual，将 BS_Annual.xlsx 的数据粘贴/导入，Ctrl+T 转为表，命名 TblBS。
- 新建工作表 IS_Annual，将 IS_Annual.xlsx 的数据粘贴/导入，Ctrl+T 转为表，命名 TblIS。
将关键列重命名为统一口径：
- TblBS：Stkcd → Ticker，Accper → Accper，Parentequity（或权益）→ BookEquity，Totassets → TotalAssets
- TblIS：Stkcd → Ticker，Accper → Accper，Netprofit_parent（或净利润）→ NetIncome
数据类型检查：
- Ticker 保留前导零：如果变成数值，插入新列 =TEXT([@Ticker],"000000") 后覆盖原列。
- Accper 若是日期/文本均可，下面会统一出年份。

2) 在两张表中提取 FiscalYear

在 TblBS 和 TblIS 各自新增列 FiscalYear：
- 若 Accper 是真正的日期：=YEAR([@Accper])
- 若是 8 位数字 yyyymmdd：=INT([@Accper]/10000)
- 若是文本 "yyyy-mm-dd"：=YEAR(DATEVALUE([@Accper]))

3) 以资产负债表为“底表”，添加净利润

新建工作表 Financials_Annual，把 TblBS 的四列复制过来（或引用）：Ticker, FiscalYear, BookEquity, TotalAssets，Ctrl+T 转为表，命名 TblFin。
在 TblFin 新增列 NetIncome，用同键（Ticker+FiscalYear）从 TblIS 查回：
- A 版（有 XLOOKUP，推荐）：
```
=XLOOKUP(1, (TblIS[Ticker]=[@Ticker])*(TblIS[FiscalYear]=[@FiscalYear]), TblIS[NetIncome], NA())
```
- B 版（普适，INDEX/MATCH；需先建键列）：
  1. 在 TblIS 新增 Key = [@Ticker]&[@FiscalYear]
  2. 在 TblFin 新增 Key = [@Ticker]&[@FiscalYear]
  3. NetIncome 列填：
```
=IFERROR(INDEX(TblIS[NetIncome], MATCH([@Key], TblIS[Key], 0)), NA())
```
  4. 填好后可删除/隐藏 Key 列

4) 去重与排序

选择 TblFin → 数据 → 删除重复项 → 勾选 Ticker, FiscalYear
按 Ticker、FiscalYear 升序排序

5) 质检要点

随机抽 1–2 支，核对某年的 BookEquity / TotalAssets / NetIncome 与原文件一致
若个别年在 BS 有而 IS 缺（或反之），会出现 NetIncome=NA()；这是正常的“缺报”，可保留 NA

完成后，TblFin 即为后续计算 AG 的基础表。

可直接复制的关键公式

FiscalYear（通用日期）：
```
=YEAR([@Accper])
```
FiscalYear（数字 yyyymmdd）：
```
=INT([@Accper]/10000)
```

XLOOKUP 合并净利润：

=XLOOKUP(1, (TblIS[Ticker]=[@Ticker])*(TblIS[FiscalYear]=[@FiscalYear]), TblIS[NetIncome], NA())

INDEX/MATCH（Key）：

' 在 TblIS：Key = [@Ticker]&[@FiscalYear]
' 在 TblFin：Key = [@Ticker]&[@FiscalYear]
=IFERROR(INDEX(TblIS[NetIncome], MATCH([@Key], TblIS[Key], 0)), NA())

4. 预处理与口径对齐（避免前视）

4.1 在 Data 表创建辅助列

Year_Fin = YEAR([@Date]) - 1（对应上一财报年度）
Industry：用 Industry_Lookup 映射

A 版（XLOOKUP）：

=IFERROR(XLOOKUP([@Ticker], TblInd[Ticker], TblInd[Industry], "All"), "All")

B 版（INDEX/MATCH）：

=IFERROR(INDEX(TblInd[Industry], MATCH([@Ticker], TblInd[Ticker], 0)), "All")

Ret_t1（次期月收益）
- 若已有 Mret：对同一股票向后位移一行（注意按 Ticker+Date 排序）

A 版（动态数组，无需辅助列）：

=LET(
  tk, [@Ticker],
  dt, [@Date],
  rnext, XLOOKUP(dt, FILTER(TblData[Date], TblData[Ticker]=tk),
                     FILTER(TblData[Mret], TblData[Ticker]=tk), , 1),
  rnext
)

B 版（OFFSET + 分组判断）：

=IF(AND([@Ticker]=OFFSET([@Ticker],-1,0), [@Date]>OFFSET([@Date],-1,0)),
     OFFSET([@Mret],-1,0),
     NA())

如果没有 Mret 字段：用“前复权收盘价”计算 Ret = AdjClose/上期AdjClose - 1，再右移一月得到 Ret_t1。

LogMktCap = LN([@MktCap])

4.2 在 Financials_Annual 计算年度投资率 AG

新增列 AG（按同一股票相邻年份）：

A 版（XLOOKUP+FILTER）：

=LET(
 tk, [@Ticker],
 yr, [@FiscalYear],
 lastA, XLOOKUP(yr-1, FILTER(TblFin[FiscalYear], TblFin[Ticker]=tk),
                     FILTER(TblFin[TotalAssets], TblFin[Ticker]=tk)),
 IFERROR([@TotalAssets]/lastA - 1, NA())
)

B 版（辅助键列）：

在 TblFin 新增 Key = Ticker & FiscalYear
用：

=IFERROR([@TotalAssets] / INDEX(TblFin[TotalAssets], MATCH([@Ticker]&([@FiscalYear]-1), TblFin[Key], 0)) - 1, NA())

4.3 将上一年度财务映射到月度 Data

在 TblData 中新增三列：BookEquity、NetIncome、AG（均来自 Year_Fin 对应年度）

A 版：

= XLOOKUP(1, (TblFin[Ticker]=[@Ticker])*(TblFin[FiscalYear]=[@Year_Fin]), TblFin[BookEquity])
= XLOOKUP(1, (TblFin[Ticker]=[@Ticker])*(TblFin[FiscalYear]=[@Year_Fin]), TblFin[NetIncome])
= XLOOKUP(1, (TblFin[Ticker]=[@Ticker])*(TblFin[FiscalYear]=[@Year_Fin]), TblFin[AG])

B 版（用 Key）：

在 TblData 新增 KeyFin = Ticker & Year_Fin
在 TblFin 已有 Key = Ticker & FiscalYear

=INDEX(TblFin[BookEquity], MATCH([@KeyFin], TblFin[Key], 0))
=INDEX(TblFin[NetIncome], MATCH([@KeyFin], TblFin[Key], 0))
=INDEX(TblFin[AG], MATCH([@KeyFin], TblFin[Key], 0))

检查点：随机抽 1 只股票，确认 2023 年财报值被用于 2024 年 1–12 月，避免前视。

5. 构造原始描述子（Factors_Raw）

将 TblData 的关键列复制/引用到 Factors_Raw：Date, Ticker, Industry, MktCap, LogMktCap, Ret_t1, BookEquity, NetIncome, AG

新增三个描述子（“越大越好”）：

BM     = IFERROR([@BookEquity]/[@MktCap], NA())
ROE    = IFERROR([@NetIncome]/[@BookEquity], NA())
AG_pos = IFERROR(-1*[@AG], NA())

注意：若 BookEquity <= 0，ROE 可能异常，后续 winsor 会处理极端值。

6. 行业内去极值（1%–99% Winsor）

在 Standardize 表拷入 Factors_Raw 的列，并对每个 Date × Industry 分组分别处理三列：BM、ROE、AG_pos。

A 版（FILTER）：

=LET(
 d, [@Date], ind, [@Industry],
 x, FILTER(Standardize[BM], (Standardize[Date]=d)*(Standardize[Industry]=ind)),
 P1, PERCENTILE.INC(x, 0.01),
 P99, PERCENTILE.INC(x, 0.99),
 MIN(MAX([@BM], P1), P99)
)

B 版（数组公式，输入后按 Ctrl+Shift+Enter；WPS 经典版同理）：

=MIN( MAX([@BM],
  PERCENTILE.INC(IF((Standardize[Date]=[@Date])*(Standardize[Industry]=[@Industry]), Standardize[BM]), 0.01)),
  PERCENTILE.INC(IF((Standardize[Date]=[@Date])*(Standardize[Industry]=[@Industry]), Standardize[BM]), 0.99))
)

对 ROE、AG_pos 同样处理，得到 BM_win, ROE_win, AGpos_win。

如果你将 Industry 全设为 “All”，上述“组内”即为“全市场”。

7. 行业内秩→正态分位标准化

将 winsor 后的数值转为近似标准正态分数 $z$ 。以 BM_win 为例：

A 版：

=LET(
 d, [@Date], ind, [@Industry],
 x, FILTER(Standardize[BM_win], (Standardize[Date]=d)*(Standardize[Industry]=ind)),
 r, RANK.AVG([@BM_win], x, 0),
 n, ROWS(x),
 NORM.S.INV((r-0.5)/n)
)

B 版（CSE）：

=LET(
 r, RANK.AVG([@BM_win],
      IF((Standardize[Date]=[@Date])*(Standardize[Industry]=[@Industry]), Standardize[BM_win]), 0),
 n, COUNTIFS(Standardize[Date],[@Date], Standardize[Industry],[@Industry]),
 NORM.S.INV((r-0.5)/n)
)

对 ROE_win、AGpos_win 同理，得到 z_BM、z_ROE、z_AGpos。

8. 规模中性化（对数市值残差）

对每个 Date，将 z_* 对 LogMktCap 做单变量回归，取残差： $z^{\perp} = z - \beta \cdot \log(\text{MktCap})$ ，其中 $\beta = \frac{\mathrm{Cov}(z, \log M)}{\mathrm{Var}(\log M)}$ 。

A 版（全市场维度）：

=LET(
 d, [@Date],
 z, FILTER(Standardize[z_BM], Standardize[Date]=d),
 s, FILTER(Standardize[LogMktCap], Standardize[Date]=d),
 beta, COVARIANCE.S(z, s)/VAR.S(s),
 [@z_BM] - beta*[@LogMktCap]
)

B 版（CSE）：

=[@z_BM] - (
   COVARIANCE.S(
     IF(Standardize[Date]=[@Date], Standardize[z_BM]),
     IF(Standardize[Date]=[@Date], Standardize[LogMktCap])
   )
   / VAR.S(IF(Standardize[Date]=[@Date], Standardize[LogMktCap]))
) * [@LogMktCap]

对 z_ROE、z_AGpos 同理，得到 z_BM_perp、z_ROE_perp、z_AGpos_perp。

进阶：也可选择在“Date × Industry”维度做规模中性化（更严谨，但样本少时不稳）。

9. 合成综合分数（等权）

在 Composite 表计算：

Score = AVERAGE([@z_BM_perp], [@z_ROE_perp], [@z_AGpos_perp])

课堂默认等权；IR 加权、风险平价等进阶放到后续课程。

10. 研究级评估（一）：每月 Rank‑IC

在 Composite 表按 Date 组内分别对 Score 与 Ret_t1 排秩，再求相关。

新增辅助列：

Rank_Score：

A 版：

=LET(d, [@Date],
 x, FILTER(Composite[Score], Composite[Date]=d),
 RANK.AVG([@Score], x, 0))

B 版（CSE）：

=RANK.AVG([@Score], IF(Composite[Date]=[@Date], Composite[Score]), 0)

Rank_Ret 同理，将 Score 换为 Ret_t1。

在 Research_Eval 建立“唯一月份”列表：

A 版：=UNIQUE(Composite[Date])
B 版：插入“数据透视表”，把 Date 拖到“行”生成唯一日期；或“数据”→“删除重复项”

对每个 Month 计算 Rank‑IC：

A 版：

=CORREL(
  FILTER(Composite[Rank_Score], Composite[Date]=MonthCell),
  FILTER(Composite[Rank_Ret],   Composite[Date]=MonthCell)
)

B 版（透视替代）：

方法 1：在 Composite 新增列 MonthKey = TEXT(Date,"yyyy-mm")，再用 SUMIFS/COUNTIFS 与手动相关函数计算（较麻烦）
方法 2（推荐）：用“数据透视表”展示每月分组的 Rank_Score 与 Rank_Ret 明细，再用“数据分析加载项 → 相关”逐月计算；或将每月数据复制到同一列后用 CORREL。

汇总：

平均 IC：=AVERAGE(RankIC_series)
可选 12 期滚动均值：=AVERAGE(OFFSET(first_cell, ROW()-ROW(first_cell)-11, 0, 12, 1))

11. 研究级评估（二）：五分位分组与多空组合回报

在 Composite 表打分组标签（每个 Date 组内按 Score 分 5 组）：

A 版：

Perc_Score = LET(d,[@Date], x, FILTER(Composite[Score], Composite[Date]=d),
                 PERCENTRANK.INC(x, [@Score]))
Bucket     = 1 + INT(5 * [@Perc_Score])

B 版（CSE）：

Perc_Score = PERCENTRANK.INC(IF(Composite[Date]=[@Date], Composite[Score]), [@Score])
Bucket     = 1 + INT(5 * [@Perc_Score])

在 Research_Eval 汇总每月各组次期收益：

Ret_Qk_t   = AVERAGEIFS(Composite[Ret_t1], Composite[Date], MonthCell, Composite[Bucket], k)
LongShort  = Ret_Q5_t - Ret_Q1_t

累计曲线：

Cum_Q5 = PRODUCT(1 + range_Q5) - 1
Cum_Q1 = PRODUCT(1 + range_Q1) - 1
Cum_LS = PRODUCT(1 + range_LS) - 1

或逐期累计列：

=IF(ROW()=ROW(first), 1+Ret_Q5_t, previous_cell*(1+Ret_Q5_t))

绘图：插入折线图展示 Rank‑IC、Q1..Q5、Q5−Q1 累计收益。

12. 质量控制清单（交作业前逐条自检）

数据对齐
- 2023 年财报是否用于 2024 年 1–12 月？（随机抽查 2 只）
缺失/异常
- BM、ROE、AG 缺失的行是否自动排除？极端值是否被 winsor 限定？
分组颗粒
- 每月每行业至少 5 支为佳；不足可退化为全市场分组
结果理性
- 平均 Rank‑IC 是否在 0 附近或略正？Q5−Q1 累计是否有趋势（可能随样本不同而变化）？

15. 关键公式速查（复制即用）

年度投资率（TblFin）：

A版：=LET(tk,[@Ticker],yr,[@FiscalYear],lastA,XLOOKUP(yr-1,FILTER(TblFin[FiscalYear],TblFin[Ticker]=tk),FILTER(TblFin[TotalAssets],TblFin[Ticker]=tk)), IFERROR([@TotalAssets]/lastA-1,NA()))
B版：=IFERROR([@TotalAssets]/INDEX(TblFin[TotalAssets], MATCH([@Ticker]&([@FiscalYear]-1), TblFin[Key], 0))-1, NA())

映射财务到月度（TblData）：

A版：=XLOOKUP(1,(TblFin[Ticker]=[@Ticker])*(TblFin[FiscalYear]=[@Year_Fin]), TblFin[BookEquity])
B版：=INDEX(TblFin[BookEquity], MATCH([@KeyFin], TblFin[Key], 0))

次期收益（有 Mret 字段）：

A版：=LET(tk,[@Ticker],dt,[@Date], XLOOKUP(dt, FILTER(TblData[Date],TblData[Ticker]=tk), FILTER(TblData[Mret],TblData[Ticker]=tk), , 1))
B版：=IF(AND([@Ticker]=OFFSET([@Ticker],-1,0), [@Date]>OFFSET([@Date],-1,0)), OFFSET([@Mret],-1,0), NA())

Winsor（行业内 1%–99%）：

A版：=LET(d,[@Date],ind,[@Industry],x,FILTER(Standardize[BM],(Standardize[Date]=d)*(Standardize[Industry]=ind)), MIN(MAX([@BM],PERCENTILE.INC(x,0.01)), PERCENTILE.INC(x,0.99)))
B版(CSE)：=MIN(MAX([@BM], PERCENTILE.INC(IF((Standardize[Date]=[@Date])*(Standardize[Industry]=[@Industry]),Standardize[BM]),0.01)), PERCENTILE.INC(IF((Standardize[Date]=[@Date])*(Standardize[Industry]=[@Industry]),Standardize[BM]),0.99))

秩→正态：

A版：=LET(d,[@Date],ind,[@Industry],x,FILTER(Standardize[BM_win],(Standardize[Date]=d)*(Standardize[Industry]=ind)), r,RANK.AVG([@BM_win],x,0), n,ROWS(x), NORM.S.INV((r-0.5)/n))
B版(CSE)：=LET(r,RANK.AVG([@BM_win], IF((Standardize[Date]=[@Date])*(Standardize[Industry]=[@Industry]),Standardize[BM_win]),0), n,COUNTIFS(Standardize[Date],[@Date],Standardize[Industry],[@Industry]), NORM.S.INV((r-0.5)/n))

规模中性化：

A版：=LET(d,[@Date], z,FILTER(Standardize[z_BM],Standardize[Date]=d), s,FILTER(Standardize[LogMktCap],Standardize[Date]=d), beta,COVARIANCE.S(z,s)/VAR.S(s), [@z_BM]-beta*[@LogMktCap])
B版(CSE)：=[@z_BM]-( COVARIANCE.S(IF(Standardize[Date]=[@Date],Standardize[z_BM]), IF(Standardize[Date]=[@Date],Standardize[LogMktCap])) / VAR.S(IF(Standardize[Date]=[@Date],Standardize[LogMktCap])) )*[@LogMktCap]

Rank‑IC（按月）：

A版：=CORREL(FILTER(Composite[Rank_Score], Composite[Date]=MonthCell), FILTER(Composite[Rank_Ret], Composite[Date]=MonthCell))

五分位分组：

A版：Perc=LET(d,[@Date],x,FILTER(Composite[Score],Composite[Date]=d), PERCENTRANK.INC(x,[@Score])) ; Bucket=1+INT(5*Perc)
B版(CSE)：Perc=PERCENTRANK.INC(IF(Composite[Date]=[@Date],Composite[Score]), [@Score]) ; Bucket=1+INT(5*Perc)