在电子表格中搭建一个简单的回测
在电子表格中逐列搭建一个完整的移动平均线规则回测，并检查其中的错误。
你将学到什么
- 电子表格回测的标准列布局。
- 信号、仓位、策略收益、资金曲线和回撤的公式。
- 如何把仓位下移一行以避免前视偏差。
- 能发现大多数电子表格错误的五项检查。
讲解
对于大型测试而言，电子表格回测速度较慢，但每个数字都清晰可见，这使它成为最好的学习场所。你将测试第 2 课中的移动平均线规则，这里使用 3 个月简单移动平均线（SMA），以便让表格保持简短。
列布局
第 1 行是标题；第 1 个月在第 2 行，因此第 m 个月位于第 m+1 行。单元格 K1 存放每次仓位变化的成本（这里为 0.2%）。
| 列 | 内容 | 公式（第 5 行 = 第 4 个月） |
|---|---|---|
| A | 月份（或日期） | 数据 |
| B | 复权收盘价 | 数据 |
| C | 3 个月 SMA | =AVERAGE(B3:B5) |
| D | 收盘时的信号（1 = 持仓，0 = 现金；数值相等时保持前一个信号） | =IF(B5>C5,1,IF(B5<C5,0,D4)) |
| E | 当月持有的仓位 | =D4 |
| F | 资产收益 | =B5/B4-1 |
| G | 扣除成本后的策略收益 | =E5*F5-IF(E5<>E4,$K$1,0) |
| H | 资金（起始为 100） | =H4*(1+G5) |
| I | 滚动峰值 | =MAX($H$4:H5) |
| J | 回撤 | =H5/I5-1 |
在德语版电子表格中，函数名称和分隔符有所不同，例如用 MITTELWERT 代替 AVERAGE，用 WENN 代替 IF，并用分号代替逗号。
关键思路：下移一行
E 列复制的是上一行的信号。信号只有在第 m 个月收盘时才知道，因此它最早只能获得第 m+1 个月的收益。如果你把同一行的信号与同一行的收益相乘，测试就会在做出决定之前“知道”当月的结果。这一个错误就会让许多电子表格回测看起来好得离谱。
资金与回撤
资金曲线是账户价值随时间的变化，这里以从 100 起始的指数表示。收益按复利计算：每个月都乘以前一个数值。回撤是相对于迄今为止达到的最高资金（滚动峰值）的百分比跌幅；回撤和恢复的详细计算已在课程《风险与交易管理》中介绍过。
示例演算
假设示例：月收盘价为 €100、€104、€108、€106、€101、€97、€99、€104、€109、€112。成本：每次仓位变化 0.2%（作为点差和费用的简单替代，第 8 课）。税费和现金利息不计在内。资金从第 3 个月收盘时的 100 开始，这是第一个有 SMA 的月份；在此之前的仓位为 0。
| 月份 | 收盘价 | SMA | 信号 | 仓位 | 资产收益 | 策略收益 | 资金 | 回撤 |
|---|---|---|---|---|---|---|---|---|
| 3 | 108 | 104.00 | 1 | 0 | – | – | 100.00 | 0.00% |
| 4 | 106 | 106.00 | 1（相等） | 1 | −1.85% | −2.05% | 97.95 | −2.05% |
| 5 | 101 | 105.00 | 0 | 1 | −4.72% | −4.72% | 93.33 | −6.67% |
| 6 | 97 | 101.33 | 0 | 0 | −3.96% | −0.20% | 93.14 | −6.86% |
| 7 | 99 | 99.00 | 0（相等） | 0 | +2.06% | 0.00% | 93.14 | −6.86% |
| 8 | 104 | 100.00 | 1 | 0 | +5.05% | 0.00% | 93.14 | −6.86% |
| 9 | 109 | 104.00 | 1 | 1 | +4.81% | +4.61% | 97.43 | −2.57% |
| 10 | 112 | 108.33 | 1 | 1 | +2.75% | +2.75% | 100.11 | 0.00% |
对其中三行的核对：
- 第 4 个月：仓位为 1（来自第 3 个月），产生入场成本：−1.85% − 0.20% = −2.05%。资金：100 × (1 − 0.0205) = 97.95。
- 第 6 个月：仓位变为 0，只产生出场成本：−0.20%。资金：93.33 × 0.998 ≈ 93.14。回撤：93.14 ÷ 100 − 1 = −6.86%。
- 第 9 个月：重新入场：+4.81% − 0.20% = +4.61%。资金：93.14 × 1.0461 ≈ 97.43。
结果：资金为 100.11（+0.11%），共 3 次仓位变化，最大回撤为 −6.86%。从第 3 个月到第 10 个月买入并持有：€112 ÷ €108 − 1 = +3.70%。如果使用更简单的公式 =IF(B5>C5,1,0)，第 4 个月的数值相等就意味着提前一个月出场，结果约为 +5.07%。一个边界情况就让结果相差约 5 个百分点。八个月的数据对这条规则什么也证明不了；这里的目的是掌握操作方法。
常见错误
=D5*F5 而不是 =E5*F5，会让策略获得信号形成当月的收益。请始终问自己：“在与之相乘的收益出现之前，这个单元格的值是否已经知道？”练习活动
适用于任何电子表格回测的五项检查。请在纸上把它们应用到上表，并写下如果测试正确，你会看到什么。
- 统计仓位变化次数，并与所收取的成本进行比较。
- 随机选一行，用手工重新计算。
- 把成本设为 0，并把所有仓位设为 1：结果必须等于买入并持有。
- 检查第一个仓位行使用的是更早一行的信号。
- 检查回撤从不为正。
查看答案
1. 在第 4、6 和 9 个月发生变化 = 3 次；收取的成本合计为 3 × 0.20% = 0.60%。一致。
2. 以第 5 个月为例：仓位 1 × (€101 ÷ €106 − 1) = −4.72%，没有变化，因此没有成本；97.95 × 0.9528 ≈ 93.33。一致。
3. 仓位为 1 且无成本时，资金变为 100 × 112 ÷ 108 ≈ 103.70，即买入并持有的 +3.70% 结果。
4. 第 4 个月的仓位（1）来自第 3 个月的信号。正确。
5. 所有回撤均为 0.00% 或负值。正确。
局限性：该表格假设按固定成本在月末收盘时交易；真实的成交发生在下一个开盘时，点差和滑点是变化的（第 8 课）。
要点总结
- 信号和仓位使用不同的列，并把仓位下移一行。
- 把收益按复利计入资金曲线，并根据滚动峰值计算回撤。
- 在仓位发生变化时收取成本，而不是每个月都收取。
- 在相信任何电子表格结果之前，先完成这五项检查。
下一步
在下一课中，你将把这样一条资金曲线转化为标准的业绩指标：CAGR、回撤、夏普比率和盈利因子。
自测
回答问题后核对答案。你可以随意重试多次。
供您自行复习的自动自测。您的答案在浏览器中核对，我们不会查看或评估。这不是考试，也不是资格证书。
您的进度
“已完成”仅表示您将本课标记为已学完，并不是对您理解程度的测试。 学习进度和笔记仅保存在此设备的此浏览器中，而非账户中。