Excel 蒙地卡羅模擬：從你的交易明細估出回撤範圍

目錄

1. 如何在 Excel 執行蒙地卡羅模擬？
2. 為什麼需要交易明細，而不是權益曲線？
3. 如何用 RAND 與 SORTBY 設定重新排列？
4. 如何把打亂重複數百次？
5. 如何解讀結果：中位數還是壞情況的回撤？
6. 該打亂交易，還是以重置抽樣來重抽？
7. 用試算表做有哪些限制？
8. 什麼時候該從試算表改用研究軟體？
9. 常見問題
10. 參考文獻

如何在 Excel 執行蒙地卡羅模擬？

要在 Excel 執行蒙地卡羅模擬，先把回測中每筆交易的結果貼進同一欄。用 RAND 替每筆交易產生一個隨機數，用 SORTBY 重新排列交易，再計算最大回撤。接著用資料表重複幾百次。這些回撤的分布，顯示壞的排列順序可能跌多深。

蒙地卡羅模擬是一種重複進行隨機實驗許多次，再研究結果分布的方法。你可以把交易想成一副牌。回測以某個順序發出這些牌。同樣的牌換一種發法，可能一開始就連續五筆虧損，也可能把虧損分散開來。把這副牌洗上數百次，就能看出糟糕的連續虧損可能多長、多深。

回撤是從目前為止的權益最高點，到當前權益的跌幅。最大回撤是整個測試中最大的一次跌幅。免費的回撤計算器可以算出單一權益序列的最大回撤與恢復情況。蒙地卡羅則用同樣的交易，給你數百個合理的最大回撤。

為什麼需要交易明細，而不是權益曲線？

你需要每筆交易的結果，因為只有單筆交易才能重新排序。權益曲線是累計總額，每個點都已包含之前所有的交易，把這些點打亂只會得到沒有意義的結果。

輸入資料請這樣準備：

1. 每筆已平倉的交易一列，順序與回測的成交順序相同。
2. 以金額表示的淨損益，已扣除手續費與滑價。使用毛損益會讓所有模擬的回撤都偏小。如何設定合理的成本，請見回測中的交易成本。
3. 固定的部位大小。如果回測每次都只交易一口，金額結果可以直接打亂。如果部位大小有變動，請先把每筆交易換算成一口的結果。
4. 以數值貼上，不要連結到其他檔案，這樣重新計算才會快。

如果你只有每日權益，可以取相鄰兩天的差額，改為打亂每日損益。這樣做可行，但打亂的是日，而不是交易。

如何用 RAND 與 SORTBY 設定重新排列？

一次打亂在同一張工作表上需要六欄，按 F9 就會產生新的一次。SORTBY 可用於 Microsoft 365 版 Excel 與 Excel 2021 之後的版本。以下步驟假設 200 筆交易放在 A2 到 A201。請依你的清單調整列數。

步驟	儲存格	輸入	作用
1	A2:A201	你的交易結果	輸入清單，每列一筆交易。
2	B2，向下填滿到 B201	=RAND()	替每筆交易產生 0 到 1 之間的隨機數。每次重新計算都會改變。
3	D2	=SORTBY(A2:A201,B2:B201)	依隨機數的順序列出交易。結果會溢出到 D201。這就是一組打亂後的序列。
4	E2，再把 E3 向下填滿	E2: =D2 E3: =E2+D3	打亂後序列的累計權益，從零開始。
5	F2，再把 F3 向下填滿	F2: =MAX(0,E2) F3: =MAX(F2,E3)	目前為止的權益最高點。從零開始，也就是起始權益。
6	G2，向下填滿	=F2-E2	每筆交易時的回撤：目前為止的權益最高點減去當前權益。
7	J2	=MAX(G2:G201)	這一次打亂的最大回撤。

步驟 6 是唯一需要弄懂的公式：回撤 = 目前為止的權益最高點 − 當前權益。「目前為止的權益最高點」是 F 欄，「當前權益」是 E 欄。兩者都以金額表示。

步驟 2 與 3 有個捷徑：在 D2 輸入 =SORTBY(A2:A201,RANDARRAY(200))。RANDARRAY 一次產生 200 個隨機數，所以不需要 B 欄。

如何把打亂重複數百次？

Excel 的資料表會對工作表每列重新計算一次並儲存每次結果，因此能替你記錄數百次打亂。每次重新計算都會抽出新的隨機數，所以每一列都是新的最大回撤。

1. 在 L3 到 L502 輸入執行編號 1 到 500。它們只是標籤。
2. 在 M2 輸入 =J2，也就是最大回撤的儲存格。
3. 選取 L2:M502。
4. 前往「資料」、「模擬分析」、「資料表」。
5. 「列變數儲存格」留空。「欄變數儲存格」選同一張工作表上任何空白儲存格，例如 K1。按「確定」。
6. 複製 M3:M502，以數值貼到另一欄。工作表一有變動，資料表就會重新計算，貼上的副本則能保留固定的一組結果。

如果活頁簿變慢，請把「公式」、「計算選項」設為「自動，但資料表除外」。這樣資料表只會在你按 F9 時更新。

500 次足以看出單一策略回撤分布的形狀。次數越多，壞情況的數字越穩定。想讓數字更穩定，可以多加幾列；計算速度依 CPU 效能而定。

如何解讀結果：中位數還是壞情況的回撤？

規劃帳戶時，應以壞情況的回撤為準，而不是中位數。中位數代表一般的序列，壞情況則是你必須撐得過去、不能因此停掉策略的序列。

數字	對貼上結果使用的公式	它告訴你什麼
回撤中位數	=MEDIAN(範圍)	一半的打亂回撤較小，一半較大。
第 95 百分位	=PERCENTILE.INC(範圍,0.95)	20 次打亂中只有 1 次更糟。常用的壞情況數字。
最差一次	=MAX(範圍)	找到的最深回撤。次數越多它越大，所以只能當作粗略參考。
超過你的上限的比例	=COUNTIF(範圍,">"&上限)/500	回撤超過你會停止交易的金額的頻率。

也請把原始回測的回撤與中位數比較。如果原始回撤遠低於中位數，代表回測的順序比大多數打亂的結果更友善。你實單交易的第一年，沒有理由一定這麼友善。

該打亂交易，還是以重置抽樣來重抽？

想研究手上這些交易的路徑，就用打亂。如果也希望最終獲利會變動，就用重置抽樣。打亂會讓每筆交易恰好用一次。重置抽樣則隨機抽取交易，有些交易會出現兩次，有些則一次都沒有。統計學家稱之為自助法（bootstrap），技術性交易規則的研究也會使用（Sullivan, Timmermann and White, 1999）。

要在工作表中做重置抽樣，請把步驟 3 換成在 D2 輸入 =INDEX($A$2:$A$201,RANDBETWEEN(1,ROWS($A$2:$A$201)))，並向下填滿到 D201。ROWS 會算出清單中的交易筆數，所以 RANDBETWEEN 會從第一筆到最後一筆之間隨機挑一個列號。在資料表中再加一個輸出欄，指向 E201，也就是最終權益。然後數一數有多少次最終結果低於零。

重置抽樣也能讓你對齊規劃期間。最大回撤會隨交易筆數增加而變大。如果你一年約做 100 筆交易，想看明年的風險，就把 INDEX 公式向下填 100 列，而不是 200 列。

用試算表做有哪些限制？

試算表模擬很適合用在一個已完成的策略，但這個方法有三個限制，需要預先考量。

1. 速度。每次執行都會重新計算整張工作表。幾百次執行搭配幾百筆交易沒有問題。但如果要對許多候選策略都這樣做，或每個策略都跑上幾千次，就會變慢，慢到什麼程度依 CPU 效能而定。這就像要用手一輛一輛檢查大型停車場裡的每部車：檢查一排還行，檢查全部就不實際了。
2. 交易的相依性。打亂時，每筆交易都被當成與前一筆無關。真實的交易常常會群聚：虧損會集中在盤整或波動很大的月份。打亂會把這些群聚拆散，所以壞情況的回撤可能算得偏小。分塊打亂則能把群聚保留在一起。把每筆交易的出場月份放在 C 欄，用 UNIQUE 列出各月份，替每個月份指定一個 RAND 值，再為每筆交易查出該值。接著用 SORTBY，以該月份的隨機值為第一排序鍵，原始交易順序為第二排序鍵。
3. 重複使用同樣的交易。每次執行都只用到你回測中的交易。模擬無法呈現歷史中不曾出現過的市場狀況。如果這個策略是從許多變體中挑出最好的，這些交易就已帶有選擇偏誤，每次打亂都會繼承這個偏誤（Bailey, Borwein, López de Prado and Zhu, 2014）。蒙地卡羅衡量的是路徑的風險，並不檢驗優勢是否存在。

針對第三個限制，要用策略沒看過的資料做檢驗。推進分析會檢查你建立與調整策略的方式，在樣本外是否站得住腳。回測過度最佳化研究則說明為什麼許多回測中最好的一個通常是被高估的。

什麼時候該從試算表改用研究軟體？

當你要檢查的策略不只寥寥幾個，或希望每次都使用相同的設定時，用研究軟體會更輕鬆。試算表要求你為每個策略重複一次設定，而且結果還取決於你有沒有記得改對儲存格。

情況	試算表	具標準策略檢驗的研究軟體
一兩個已完成的策略	很合適，每一步都看得到。	也可以。
搜尋得到的數十個候選策略	又慢又重複。	對每個策略以相同設定執行。
分塊或對齊期間的重置抽樣	可以做，但需要額外的輔助欄。	視軟體而定。
搭配樣本外檢查	另外進行。	推進分析等樣本外檢查，對每個策略以相同設定執行。

在 AlphaPrime（Valynox）中，每個策略的交易與回撤會顯示在績效報表裡，這正是蒙地卡羅執行時的起點資料。蒙地卡羅無法告訴你優勢在沒看過的資料上是否成立。要回答這個問題，AlphaPrime 的策略檢驗提供基本、標準或完整檢驗，包含推進分析，並可自訂門檻，對每個策略都用同樣的方式執行。

如果你使用 AlphaPrime，這代表：先讓策略檢驗依樣本外表現篩選候選策略，再對少數入選者用試算表做蒙地卡羅，依它們壞情況的回撤來規劃帳戶規模。

常見問題

不用特殊軟體，可以免費做蒙地卡羅模擬嗎？

可以。只要有 RAND、SORTBY 和資料表的試算表，就足以打亂交易明細並記錄每次的回撤。SORTBY 需要 Microsoft 365 版 Excel 或 Excel 2021 之後的版本。較舊的版本可以手動依一欄 RAND 值排序，一次做一輪。

為什麼每次打亂的最終獲利都一樣？

打亂會讓每筆交易恰好用一次，而總和與順序無關。只有路徑改變，所以只有回撤改變。如果你希望最終獲利也會變動，請改用重置抽樣來重抽交易。

交易策略的蒙地卡羅需要跑多少次？

幾百次就足以呈現單一策略回撤分布的形狀。次數越多，第 95 百分位越穩定。單次最差的結果會隨次數增加而持續變大，所以規劃時應以百分位數為準，而不是最大值。

蒙地卡羅的結果好，就證明策略有效嗎？

不能。蒙地卡羅重複使用回測本身的交易，所以它衡量的是這些交易的順序可能壞到什麼程度。它無法顯示優勢是真的還是過度最佳化的結果。推進分析等樣本外檢驗才能回答這個問題。

參考文獻

• Sullivan, R., Timmermann, A., & White, H. (1999). Data-Snooping, Technical Trading Rule Performance, and the Bootstrap. The Journal of Finance, 54(5), 1647–1691.
• Bailey, D. H., Borwein, J., López de Prado, M., & Zhu, Q. J. (2014). Pseudo-Mathematics and Financial Charlatanism: The Effects of Backtest Overfitting on Out-of-Sample Performance. Notices of the American Mathematical Society, 61(5), 458–471.

AlphaPrime 在這裡能幫上什麼

AlphaPrime 的策略檢驗提供基本、標準或完整檢驗，包含推進分析，並可自訂門檻。績效報表會顯示每個策略的權益、回撤與單筆交易。

看方案與價格