Excel VBAでEAのモンテカルロシミュレーションを行いPF(プロフィットファクター)を分析する

　この記事ではEA(自動売買システム)のバックテストデータを使って簡易的なモンテカルロシミュレーションをするツールをExcel VBAで作成していきます。

#モンテカルロシミュレーターとは

1. バックテストのトレード損益データを用意する
2. そこからランダムにトレードを抽出する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1882169/018d72e9-679b-da91-dce1-d1ce2afeade5.png)

　モンテカルロシミュレーションによって得たランダム損益データは__「こんなバックテスト結果もあり得たよね」__という仮想バックテストデータとなります。
　さらにランダム損益データを複数個作成し、ランダム損益データ群を用意します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1882169/56e3ebe3-d591-a4a8-ac8a-09057aacfdbf.png)

 各ランダム損益データはそれぞれプロフィットファクターや最大ドローダウンなどのEAを評価する指標を算出することができます。多くの仮想バックテストデータは「そのシステムの成績がどれくらいばらつくのか」などといった情報を調べることができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1882169/a3d4a8d8-74a9-6466-daf4-82079c3e11be.png)

#VBAでランダム損益データを抽出する
　まずExcelに分析するEAのバックテストの損益データを貼り付け、すぐ隣にランダム損益を入れるマクロを作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1882169/102d2722-3e8f-b6c5-e0da-7428f1a3bf07.png)

``` .vb
Sub RandomSampling()
'変数の宣言
Dim x As Integer
Dim y As Integer

'元データの数をカウント
x = WorksheetFunction.CountA(Range("a2:a1048576"))

For i = 1 To 100
    y = Int(Rnd * 1000000) Mod x 'この時点ではxは0～[元データの数-1]の乱数
    y = y + 1                    'これでyは1～[元データの数]の乱数になる
    
    '2列目からなので+1する
    Cells(i + 1, 2) = Cells(y + 1, 1).Value

Next i

End Sub

```
今回の例では100個の損益をランダムに抽出するように作りました

これを実行すると
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1882169/e9c17d0f-ee33-2ce8-ce23-c5e95432d781.png)
ランダム損益を抽出することができます。

#モンテカルロシミュレーションでPFの傾向を調べる
　ランダム損益を抽出するマクロが作れたらPF(プロフィットファクター)の傾向を調べるモンテカルロシミュレーションのマクロを作っていきます。

* __ランダム損益の抽出(1回目) → PFを算出(1個目)__
* __ランダム損益の抽出(2回目) → PFを算出(2個目)__
* __ランダム損益の抽出(3回目) → PFを算出(3個目)…__

というようにPFを何個も生成します。

さっきのシートにPFを算出する欄を作っておきます
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1882169/2d7d0e9e-8ac4-3997-d664-66c827d86dcf.png)

```.vb
Sub MonteCarlo()

'100回シミュレーションする
Const no_PF As Integer = 100
'トレード損益を100個ランダムに抽出する
Const no_Sa As Integer = 100
 
Dim RndRange As Range
Set RndRange = Range(Cells(2, 2), Cells(no_Sa - 1, 2))

For i = 1 To no_PF
    
    RandomSampling no_Sa
    
    '勝トレード金額の合計と負トレード金額の合計
    go_win = WorksheetFunction.SumIf(RndRange, ">0")
    go_lose = WorksheetFunction.SumIf(RndRange, "<0")
    
    'PFの算出
    Cells(i + 1, 3) = go_win / go_lose * (-1)
    
Next i

End Sub


Sub RandomSampling(ByVal nos As Integer)
'変数の宣言
Dim x As Integer
Dim y As Integer

'元データの数をカウント
x = WorksheetFunction.CountA(Range("a2:a1048576"))

For i = 1 To nos
    y = Int(Rnd * 1000000) Mod x 'この時点ではxは0～[元データの数-1]の乱数
    y = y + 1                    'これでyは1～[元データの数]の乱数になる
    
    '2列目からなので+1する
    Cells(i + 1, 2) = Cells(y + 1, 1).Value

Next i

End Sub
```
これを実行すると
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1882169/ebfbde0d-9403-2fa8-45e4-95b8b7bfcd0d.png)
このように複数回のシミュレーションによって、たくさんのPFを算出することができます。

さらにこのような項目を追加してみましょう
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1882169/2b1db9d7-5ce1-36d7-5a4f-2a153e076545.png)

```Excelの数式.
[元データのPF]       = -SUMIF(A2:A1048576,">0")/SUMIF(A2:A1048576,"<0")

[モンテカルロ平均PF] = AVERAGE(C2:C1048576)

[PFの95％下ぶれ値]   =F3-NORMSINV(0.95)*STDEV.P(C2:C1048576)
```

　これはマクロではなく普通のExcelの数式で計算しています。シミュレーション時の平均値や下ぶれしたときの値を算出しています。

　シミュレーションの結果、全体の95％は[PFの95％下ぶれ値]より高い数値だったということを示しています。__もしこのEAを運用するのであれば[PFの95％下ぶれ値]くらい悪い結果になることも想定することが必要__だと言えると思います。また、[PFの95％下ぶれ値]よりも悪い数値が実運用時に出たときは「EAの運用を停止する」などの判断の目安になるかもしれません。


　今回はモンテカルロシミュレーションでPFの傾向を調べるツールを作ってみましたが、計算式を追加すればPF以外(例えば最大ドローダウンなど)の評価指標も同様に調べることができます。