Excelでのフォワード予測グラフ作成 その１

こんにちは、Forestです。

先日EA分析方法の記事を公開したところ大変ご好評をいただき、ありがたい限りです。記事についていくつかお問合せをいただきましたので、その中から今回は「フォワード予測グラフをExcelで作成する方法」について説明します。この記事は前半戦として、「時間情報」を使わずに「損益だけ」を再現したフォワード推移予測を行います。

Excelは表計算や限られた範囲での集計は得意なのですが、今回のような複数の手続きが必要な処理は苦手です。どうしてもVBAが必要になってきますのでその点はご注意ください。
※RやPython、MATLABなどを使える方はそちらで実現したほうが楽ということはお伝えしておきます。

フォワード推移予測の手順

バックテストからフォワード予測を作成するにあたり、いきなり時間情報を含めた予測を作成するのは難易度が高いです。まずは時間情報を含めない、損益だけを再現したフォワード推移予測を作成するところから始めることにしましょう。これはつまり、グラフの横軸を「時間」ではなく「取引回数」で表示する推移予測という意味です。この作成を通してブートストラップ法の考え方に慣れたあと、「時間情報」を考慮したフォワード推移予測を作成する方法に言及していきます。

このように、損益だけを再現する、時間情報を含めて再現する、というステップを踏むことは、わかりやすくなる点以外にも時間情報がフォワード推移予測にどう影響するかを確認できるという利点もあります。実際、損益だけを見れば順調だが時間を含めると全然ダメ（取引回数が少なすぎる）というEAも存在します。ぜひ、ご自身で様々なEAを分析して確かめてみてください。

損益だけを再現したフォワード推移予測

時間情報を考慮しない場合、トレードごとの損益さえあればフォワード予測を作成できます。具体的な手順は以下で行います。

  1. バックテストレポートから損益を抽出
  2. 損益を１ロットあたりの金額に変換
  3. 損益をランダムに１つ抽出
  4. 3.を$N$回行い、累積して損益系列化
  5. 4.を$M$回繰り返し、$M$本の損益系列を作成
  6. 5.で作成した$M$本の損益系列の中央値（$M/2$個目の位置の値）と$95\%$信頼区間（上から$2.5\%$, $97.5\%$の位置の値）を計算
  7. 中央値と信頼区間、実際のフォワード推移をグラフに表示

実際のフォワード推移予測を行う部分は3～5の部分ですが、一通りの手順について説明しておきます。

1. バックテストレポートから損益を抜き出す

まずバックテストレポートに載っている損益の情報をExcelに取り込む必要があります。どんな方法で取り込んでも良いのですが、調べてみるとExcelは直接HTMLファイル（拡張子htm, html）を開いてデータを取り込めるようです。今回は直接HTMLファイルを開いて取り込んでしまいましょう。

開きたいHTMLファイルをExcelにドラッグ＆ドロップする、Excelの開くコマンドからHTMLを選択する、など、方法は問わないのでHTMLファイルをExcelで開いてください。
下図のようにExcelで見慣れたバックテストレポートが開きます。各情報はセルに入っているため、Excel上で簡単に扱えます。

取り込んだバックテストの取引テーブルにはエントリー、注文修正、決済などの行がありますが、損益は決済の行だけに存在します。損益だけが欲しいので、Excelのフィルター機能を使ってフィルタリングしましょう。フィルターをかけたい表の列名の行にカーソルを移動してから、「並べ替えとフィルター」→「フィルター」を選びます。
※Excelのバージョンによってフィルターのボタン配置が違うかもしれません。

正常にフィルターが機能していれば表の列名の部分に下矢印が表示されます。これをクリックし、チェックボックスが並んだ部分を一番下までスクロール、「空白セル」のチェックを外してください。OKをクリックすると、損益が空白の行が非表示になります。

フィルタリング後の表

あとは損益列を全選択して別シートや別ブックに貼り付ければ損益だけを抽出できます。場合によっては表の損益列と残高列が結合されており、損益列だけを選択できない場合があります。その時はセルの結合を解除してしてください。

2. 損益を１ロットあたりの金額に変換する

この部分は必須の項目ではありません。しかし、バックテストとフォワードでロットが異なることは往々にしてあります。その場合はバックテストの損益が１ロットあたりになっていると、ロットを揃えるためにはフォワードのロットをかけるだけで良いので手間が少なくてすみます。また、EA分析の各種統計値を比較する際にもEA間でロットが揃っていると簡単に比較できます。

やり方は簡単です。損益をロットで割れば１ロットあたりの損益になります。ロットの異なる複数のポジションを保つ場合は、最小ロットのポジションが１ロットになるようにしておくと良いと思います（最大ロットでも構いません）。

サンプルのバックテストは$0.1$ロット基準なので損益を$0.1$で割りました。以降はこの１ロットあたりの損益を使っていきます。

Excel関数だけでフォワード予測を行う

3.以降はVBAが必要となり難しくなっていきます。まずはExcel関数だけで可能な範囲でフォワード予測を作成して「ブートストラップ法」で予測を作成するイメージを作っておきます。VBAでもやることは変わりません。ここでは2.で用意した損益を使って$30$トレードの損益系列を$20$本作って、中央値と信頼区間を出してみます。Excel関数だけで以下のようなExcelを作成するのを目標とします。

1,2を含めて実装したExcelファイルを添付しておくので参考にしてください。

Excel関数だけで作るフォワード推移予測ダウンロード

再配布したい場合は事前にTwitterのDMかお問合せフォームから連絡をください。無断での再配布は禁止します。

Excel関数版はお試しで本命はVBA版という理由を説明しておきます。
Excel関数だけで作った場合は、抽出する損益数を増やしたいときや系列数を増やしたいときに手動でセルのデータをコピーしたり減らしたりしなければならず、運用に手間がかかります。それでも良い、というのならExcel関数だけでフォワード予測を行えば良いのですが、抽出する損益数や系列数をパラメータで変えたい場合はVBAを使っての実装が必要になります。

3. 損益をランダムに1つ取り出す

フォワード予測を生成するうえで最も重要な部分は「バックテストからランダムに１つ損益を取り出す」ことです。これさえできれば、あとは同じことを何回も繰り返すだけなのでそれほど難しくありません。

Excelでランダムに損益を取り出すには「乱数」を使います。乱数は、指定した値の範囲でランダムに生成される値のことを指します。「何番目の損益を取ってくるか」を乱数を使って決めれば、バックテストの損益の中からランダムに損益を取得できます。

そのためには生成する乱数の範囲を決めるためにトレード総数が必要ですから、まずはトレード総数を求めましょう。トレードごとの損益がA2から各行に入っているとします（A1はタイトル）。データ（数値）の数は COUNT関数 で求められますから、A列の数値の数を調べてトレード総数とします。タイトル行も範囲に含まれますが、数値ではないためカウントからは省かれるので問題ありません。

COUNT($A:$A)

次に乱数を使って損益全体のうちの１つを示す値を取得します。
RANDBETWEEN関数*1を使うと範囲を指定した乱数が生成できます。RANDBETWEEN(最小値, 最大値) とすると、最小値以上、最大値以下（最大値含む）の値が１つ生成されます。RANDBETWEEN(1, トレード総数) とすれば、全体のうちの１つの損益を示す値となります。（トレード総数が2005なら1～2005）上の図のようにB2にトレード総数が入っているのならば以下のようにすれば良いです。

RANDBETWEEN(1, $B$2)

最後に生成した乱数を使ってトレードを一つ取得します。INDEX関数を使うと、指定した範囲の中の任意のセルの値を取得できます。例えば、INDEX(A2:A10, 2)ならば、A2～A10のうちの２番目、つまりA3のセルの値を取得します。
損益はA列の２行目以降に入っているので以下のようにすればランダムに１つ損益を取得できます。

INDEX($A:$A, RANDBETWEEN(1, $B$2)+1)

*1 Excelの乱数は線形合同法で生成されており下位ビットが$0,1$を順番に取る(RANDBETWEEN(1,2)とすると1と2が交互に出る)という致命的な弱点がありますが、今回は気にせず使用します。気になる方はご自身でMersenne Twisterやxorshiftなどの優秀な疑似乱数アルゴリズムのDLLを取得し、VBAから利用できるようにしてください。

4. 損益を30個取り出して１つの系列にする

INDEX関数とRANDBETWEEN関数を使って１つの損益を取り出すことを３０回行って、３０回分の疑似トレード結果を生成します。これを損益セットと呼ぶことにしましょう。これは単に上記のINDEX(～～)を３０行にコピーすれば良いです。

３０回分の損益から損益セットが生成できたら、損益セットを累積して損益系列へ変換します。上記の画像の場合K22～K51に損益セットが入っています。SUM関数を使い、SUM(K\$22:K22)、SUM(K\$22:K23)としていけば累積を計算できます。
下の画像の例ではK55～K84に累積した損益系列を入れています。K55に SUM(K\$22:K22) と入れて、K84までコピー＆ペーストすれば累積できます。

5. 損益系列を２０系列作る

先ほど作成した損益セットと損益系列を２０系列分作成します。先ほどは行方向にコピーしていきましたが、今度は列方向にコピーします。

損益系列も同様にコピーすれば問題ありません。

これで擬似トレードから生成した損益系列が２０系列作成できました。次に表示上のセンターラインと上下限ラインに使う中央値と信頼区間を求めていきます。

6. 中央値と信頼区間を求める

作成した２０本の損益系列の各トレードにおける中央値と信頼区間を求めます。「各トレード」の意味は「同じトレード数の各系列の値」という意味です。例えば、系列１～２０の１トレード目、系列１～２０の２トレード目のことを指します。先ほど作成した損益系列のExcelでは、同じ行の値がこれにあたります。

中央値や信頼区間は PERCENTILE関数 を使えば取得できます。PERCENTILE関数は、範囲と割合を指定すると、範囲内のセルの数値から指定した割合の位置の値を抽出してくれます。
例えば以下のように PERCENTILE(K55:AD55, 0.5) とすれば、各系列の１トレード目までの損益の$50\%$の値（＝中央値）を算出できます。同様に、$95\%$信頼区間の下側は$2.5\%=0.025$、上側は$97.5\%=0.975$を指定すれば算出できます。

PERCENTILE(K55:AD55, 0.025)
PERCENTILE(K55:AD55, 0.5)
PERCENTILE(K55:AD55, 0.975)

この計算を損益系列の各行に対して行えば、損益だけを考慮したフォワード推移予測が計算できます。

7. フォワード予測のグラフを作成する

添付の参考用Excelでは、損益系列を１つ余分に作成して疑似フォワードとして扱いグラフに描くことにします。トレード数を表す列を１つ作成しておき、グラフは横軸・縦軸の値を指定できる散布図で描くと融通がききます。このグラフの横軸はトレード数、縦軸は累積損益です。あとは好きなようにレイアウトを整えれば完成です。

ここまで、Excel関数だけでフォワード推移予測を作成してきました。損益だけを再現し、系列数や再現トレード数を固定とするのならばこの方法でも良いのですが、「時間情報」を考慮したりパラメータで系列数やトレード数を変えたい場合はExcel関数だけでは実現が困難です。もしかしたらできるかもしれませんが、私はやり方を知りません。そういう理由でここからはVBAでフォワード推移予測を作成していきます。

VBAを使ったフォワード推移予測作成

VBAで作成すると言っても、やることはExcel関数で実施した内容と変わりません。Excel関数でやったことをVBAに移植するだけです。つまり以下をVBAで書いていきます。事前にバックテストレポートから損益を抽出し、１ロットあたりの金額に変換してあるものとします。

  1. パラメータを読み込む
  2. 損益をランダムに1つ取り出す
  3. 損益を$N$個取り出して累積し１つの系列にする
  4. 損益系列を$M$系列作る
  5. 中央値と信頼区間を求める
  6. 求めた中央値と信頼区間をExcelシートに書き込む
  7. グラフに表示する

任意に値を変更したい生成取引数、系列数、信頼区間幅、フォワードロットをパラメータとして読み込むために 1. が、VBA上で計算した数値をExcelシートに移すために 6. が追加されています。といっても難しいことはありません。このあと順に説明していきます。なお、VBAの入門サイトはいくらでもあるので、このサイトの説明では変数宣言、データ型、関数、Range・CellsオブジェクトなどのVBAの最低限の知識がある前提で話を進める点はご了承ください。
すべてを理解する必要はありませんが、最低限改造ができる程度に要点を押さえておくと良いと思います。

最終的には下の図のようなExcelを作成することを目標とします。正直レイアウトはキレイとは言えないので、各々で使いやすくチューニングしてください。

以下に作成済みのExcelを添付しておきます。これを参考にしながら説明を読むとより理解しやすいと思います。

損益のみを考慮したフォワード推移予測（VBA）ダウンロード

再配布したい場合は事前にTwitterのDMかお問合せフォームから連絡をください。無断での再配布は禁止します。個人使用の範疇でのカスタマイズはご自由に行っていただいて構いません。

1. パラメータを読み込む

まずはパラメータとして設定する値を読み込んでいきます。パラメータは生成する取引数、系列数、信頼区間の幅、フォワードのロット数の４つで、それぞれE3～H3に設定される前提とします。また、後々バックテストの総取引数が必要になるので、これもB2から読み込みます。

生成する取引数は、フォワード推移予測として何トレード生成するかを決めるパラメータです。基本的には、実際のフォワードと同じ取引数として現在のフォワードの状態を見るか、それより多い数を指定して将来の予測推移を見ると良いと思います。

系列数は、フォワード予測の正確さを決めるパラメータです。生成する系列数が多ければフォワード予測もより正確になりますが、その分計算に時間がかかります。このサイトでのフォワード推移予測では2000系列生成して中央値と信頼区間を求めていますが、味見程度なら500系列くらいで十分です。

信頼区間の幅は、フォワード予測の上下限の幅を決めるパラメータです。信頼区間の幅の意味は「その幅の中に期待値が含まれる確率」ですから、$95\%=0.95$とした場合はざっくりと実フォワード推移が範囲から外れる確率が$5\%$と見ておけば良いです。

フォワードのロット数は予測した損益をフォワードに合わせるためのパラメータです。バックテストの損益は１ロットに正規化したので、調整しないと損益が大きすぎたり小さすぎたりします。

なお、作成するVBAの関数名は「modForwardForecastingWithProfits」としておきます。Alt+F11を押してVBAエディタを開いておいてください。

以下はパラメータ読み込みに関連するコードです。
最初のsheet変数は、ActiveSheetへアクセスする際に短い名前でアクセスするための変数です。コード内の３行目でsheet変数にThisWorkbook.ActiveSheetを設定しています。

バックテストの総取引数はLong型のnumOfBacktestTrades変数として読み込みます。EAによっては取引回数がVBAのInteger型の最大値32767を越えますから、Long型でないといけません。総取引数はB2セルにあるのでsheet.Cells(2, 2)で読み込みます。

生成するトレード数、系列数、信頼区間、フォワードロット数はそれぞれnumOfGeneratingTrades、numOfSeries、confidence、forwardLotsとして読み込みます。型は順にLong, Long, Double, Doubleです。

    ' Activesheetを短い変数に入れておく
    Dim sheet As Worksheet
    Set sheet = ThisWorkbook.ActiveSheet

    ' バックテストのトレード数を読み込む
    Dim numOfBacktestTrades As Long
    numOfBacktestTrades = sheet.Cells(2, 2) ' B2固定

    ' パラメータとして、生成するトレード数、系列数、フォワードロットを読み込む
    Dim numOfGeneratingTrades As Long
    Dim numOfSeries As Long
    Dim confidence As Double
    Dim forwardLots As Double
    numOfGeneratingTrades = sheet.Cells(3, 5) ' E3固定
    numOfSeries = sheet.Cells(3, 6) ' F3固定
    confidence = sheet.Cells(3, 7) ' G3固定
    forwardLots = sheet.Cells(3, 8) ' H3固定

以下に変数名とパラメータの関係をまとめます。

パラメータ変数名データ型
バックテストの総取引数numOfBacktestTradesLong
生成する取引数numOfGeneratingTradesLong
生成する系列数numOfSeriesLong
信頼区間の幅confidenceDouble
フォワードロット数forwardLotsDouble

2. 損益をランダムに1つ取り出す

次にバックテストの損益からランダムに１つだけ取り出す関数を作ります。Excel関数で実装した際は以下のようにINDEX関数とRANDBETWEEN関数を使って実現しました。

INDEX($A:$A, RANDBETWEEN(1, $B$2)+1)

このときはA列全体から選択しましたが、VBAでは先にA2からトレード数分の範囲を抽出して、その後でランダムに選ぶ形を取ります。

以下のようにgetBacktestProfit関数を定義します。
この関数は引数でバックテスト損益を受け取り、１つだけ損益を返すように実装した関数です。引数で受け取るprofits変数はバックテスト損益の範囲を示すRangeオブジェクトです。例えばバックテストのトレード数が2005回なら、この範囲はA2～A2006になります。
ランダム抽出には、Excel関数のときと同様にRANDBETWEEN関数を利用します。Rangeオブジェクトは.countとすると範囲内のセル数を取得できます。今回はセル数=トレード数なので、そのまま乱数の上限値に用います。

' バックテスト損益からランダムに１つトレードを取得する関数
' Parameters
'   :profits: バックテスト損益を表すRangeオブジェクト
' Return
'   (Double): 抽出した損益
Function getBacktestProfit(profits As Range) As Double
    ' お手軽にWorksheetFunctionのRANDBETWEENを使う
    getBacktestProfit = profits(WorksheetFunction.RandBetween(1, profits.count))
End Function

なお、上記関数で想定しているRangeオブジェクトは以下のように取得したものです。総取引回数numOfBacktestTradesを利用して、A2～A(総取引回数+1) を取得しています。numOfBacktestTrades=2005ならば A2～A2006となります。

    ' バックテストの損益の範囲を取得する
    Dim rangeBacktestProfits As Range
    Set rangeBacktestProfits = sheet.Range(Cells(2, 1), Cells(numOfBacktestTrades + 1, 1)) ' A2～バックテストの数

3. 損益を$N$個取り出して１つの系列にする

Excel関数で作成した際は行のコピーとSUM関数を使った累積で済ませましたが、VBAでは関数として作成します。以下のようにmakeProfitSeries関数を定義します。

' 指定した数の損益を生成して損益系列を作成する関数
' Parameters
'   :profits (Range): バックテスト損益を表すRangeオブジェクト
'   :numOfGeneratingTrades (Long): 生成するトレード数
' Return
'   (Double(numOfGeneratingTrades+1)): 抽出した損益の配列：要素数=numOfGeneratingTrades+1で、i=0は損益0
Function makeProfitSeries(profits As Range, numOfGeneratingTrades As Long) As Double()
    ' 可変長配列で確保しておき、あとから生成トレード数に設定
    Dim profitSeries() As Double
    ReDim profitSeries(numOfGeneratingTrades)
    ' バックテスト損益から抽出して累積を計算
    Dim i As Long
    profitSeries(0) = 0
    For i = 1 To numOfGeneratingTrades
        profitSeries(i) = getBacktestProfit(profits) + profitSeries(i - 1)
    Next i
    makeProfitSeries = profitSeries
End Function

第１引数のprofitsは先ほどのgetBacktestProfit関数と全く同じでバックテスト損益の範囲を示すRangeオブジェクトです。
第２引数のnumOfGeneratingTradesには生成するトレード数を指定します。ここにはパラメータで読み込んだ生成トレード数を渡すことを想定しています。

この関数の要所は以下の損益抽出と累積を行う部分です。
今回は最初の損益はゼロになるように profitSeries(0)=0 としてします。その後、先ほど定義したgetBacktestProfit関数でnumOfGeneratingTrades回のトレードを生成しつつ、１つ前の損益 profitSeries(i – 1) と足すことで累積損益に変換しています。

    ' バックテスト損益から抽出して累積を計算
    Dim i As Long
    profitSeries(0) = 0
    For i = 1 To numOfGeneratingTrades
        profitSeries(i) = getBacktestProfit(profits) + profitSeries(i - 1)
    Next i

この関数によってnumOfGeneratingTrades回の損益が累積された損益系列が生成できます。初回のゼロが含まれるため、配列長はnumOfGeneratingTrades+1である点に注意してください。

4. 損益系列を$M$系列作る

Excel関数で作成した際は列をコピーすることで済ませましたが、VBAでは3.の損益系列生成を$M$回繰り返すことで実現します。

以下のようにmakeMultiProfitSeries関数を定義します。
第１、第２引数はmakeProfitSeries関数と同じですが、第３引数に生成する系列数を表すnumOfSeriesが追加されています。

' 抽出トレード数と系列数を指定してリサンプリングデータを作成する関数
' Parameters
'   :profits (Range): バックテスト損益を表すRangeオブジェクト
'   :numOfGeneratingTrades (Long): 生成するトレード数
'   :numOfSeries (Long): 生成する系列数
' Return
'   (Double(numOfGeneratingTrades+1,numOfSeries)): 抽出した損益の配列。各系列(0, i)の値は0
Function makeMultiProfitSeries(profits As Range, numOfGeneratingTrades As Long, numOfSeries As Long) As Double()
    ' トレード数 x 系列数 の2次元配列を定義
    Dim multiProfitSeries() As Double
    ReDim multiProfitSeries(numOfGeneratingTrades, numOfSeries - 1)

    ' 1系列の配列を定義
    Dim profitSeries() As Double
    ReDim profitSeries(numOfGeneratingTrades)

    Dim iseries, itrade As Long
    For iseries = 0 To numOfSeries - 1
        ' 1系列分の損益系列を生成
        profitSeries = makeProfitSeries(profits, numOfGeneratingTrades)
        ' 2次元配列へコピー
        For itrade = 0 To numOfGeneratingTrades
            multiProfitSeries(itrade, iseries) = profitSeries(itrade)
        Next itrade
    Next iseries

    makeMultiProfitSeries = multiProfitSeries
End Function

VBAは配列の定義や配列間の値のコピーが面倒なので複雑に見えますが、やっていることは単純でmakeProfitSeries関数を生成する系列の数だけ繰り返し呼び出して、結果を２次元配列に集約しているだけです。もっとスマートな書き方があるのかもしれませんが、VBAをよく知らないので愚直に書いています。

この関数の戻り値として、numOfGeneratingTrades+1行・numOfSeries列のDouble型の配列が得られます。この配列は、Excel関数で作成した行がトレードごとの累積損益、列が損益系列の表に対応します。

5. 中央値と信頼区間を求める

Excel関数版では、中央値と信頼区間は損益系列の表から行ごとにPERCENTILE関数で求めました。VBAでも同様にワークシート関数のPERCENTILE関数を使って行ごとに計算します。

以下のようにcalcMedianAndConfidenceInterval関数を定義します。この関数は、makeMultiProfitSeries関数で作成した$N$トレードx$M$系列の２次元配列を受け取り、中央値と指定した信頼区間の上下限を計算する関数です。第１引数のseriesでmakeMultiProfitSeries関数にて作成した２次元配列を受け取ります。第２・第３引数はmakeMultiProfitSeries関数と同じです。第４引数はパラメータで読み込んだ信頼区間の幅を受け取る想定です。

' 生成した系列から中央値と信頼区間を求める関数
' Parameters
'   :series (Dobule(numOfGeneratingTrades+1,numOfSeries)): 生成した損益系列
'   :numOfGeneratingTrades (Long): 生成したトレード数
'   :numOfSeries (Long): 生成した系列数
'   :confidence (Double): 信頼区間の幅（0～1で指定）
' Return
'   (Double(numOfGeneratingTrades+1,3)): 信頼区間下限(0)、中央値(1)、信頼区間上限(2)
Function calcMedianAndConfidenceInterval(series() As Double, numOfGeneratingTrades As Long, numOfSeries As Long, confidence As Double) As Double()
    ' トレード数 x 信頼区間下限・中央値・信頼区間上限 の2次元配列を定義
    Dim medianAndConfidence() As Double
    ReDim medianAndConfidence(numOfGeneratingTrades, 2)
    ' 配列の(0, :)にはゼロを入れておく
    medianAndConfidence(0, 0) = 0#
    medianAndConfidence(0, 1) = 0#
    medianAndConfidence(0, 2) = 0#

    ' 計算のための一時保存用配列を定義
    Dim tmp() As Double
    ReDim tmp(numOfSeries - 1)

    Dim iseries, itrade As Long
    For itrade = 1 To numOfGeneratingTrades
        ' 生成した損益系列のトレードNoごとに、中央値と信頼区間を計算
        For iseries = 0 To numOfSeries - 1
            tmp(iseries) = series(itrade, iseries)
        Next iseries
        ' 信頼区間下限
        medianAndConfidence(itrade, 0) = WorksheetFunction.Percentile(tmp, (1 - confidence) / 2)
        ' 中央値
        medianAndConfidence(itrade, 1) = WorksheetFunction.Percentile(tmp, 0.5)
        ' 信頼区間上限
        medianAndConfidence(itrade, 2) = WorksheetFunction.Percentile(tmp, 1 - (1 - confidence) / 2)
    Next itrade

    calcMedianAndConfidenceInterval = medianAndConfidence
End Function

この関数の要所は以下の部分です。この部分で損益系列を横断して行（トレード）ごとに中央値と信頼区間を計算しています。Excel関数版で行ったことと同じことをVBAで実施しています。

    For itrade = 1 To numOfGeneratingTrades
        ' 生成した損益系列のトレードNoごとに、中央値と信頼区間を計算
        For iseries = 0 To numOfSeries - 1
            tmp(iseries) = series(itrade, iseries)
        Next iseries
        ' 信頼区間下限
        medianAndConfidence(itrade, 0) = WorksheetFunction.Percentile(tmp, (1 - confidence) / 2)
        ' 中央値
        medianAndConfidence(itrade, 1) = WorksheetFunction.Percentile(tmp, 0.5)
        ' 信頼区間上限
        medianAndConfidence(itrade, 2) = WorksheetFunction.Percentile(tmp, 1 - (1 - confidence) / 2)
    Next itrade

この関数の戻り値はnumOfGeneratingTrades+1行３列の２次元配列です。１列目は信頼区間下限、２列目は中央値、３列目は信頼区間上限に対応します。以下の並びの想定です。

6. 求めた中央値と信頼区間をExcelシートに書き込む

ここまでで、VBAによるフォワード推移予測に必要な計算は終わりました。あとはグラフに表示するために計算結果をExcelシートに出力すれば終わりです。出力の前に、実フォワードとロット数を揃えるために損益を調整しておく必要があります。

まず、ロット数調整のために以下のようなadjustProfitsToForwardLots関数を定義します。
第１引数にcalcMedianAndConfidenceInterval関数で計算した中央値と信頼区間の配列、第４引数に調整先のロット数を指定します。第４引数にはパラメータで読み込んだフォワードロット数を指定する想定です。calcMedianAndConfidenceInterval関数で計算した結果をこの関数に渡せばフォワードのロット数に即した損益に調整できます。

' フォワードロットに合うように損益を調整する関数
' Parameters
'   :series (Dobule(numOfGeneratingTrades+1,3)): 中央値と信頼区間
'   :numOfGeneratingTrades (Long): 生成したトレード数
'   :numOfSeries (Long): 生成した系列数
'   :forwardLots (Double): フォワードロット
' Return
'   (Double(numOfGeneratingTrades+1,3)): ロット調整済みの損益系列、信頼区間下限(0)、中央値(1)、信頼区間上限(2)
Function adjustProfitsToForwardLots(medianAndConfidence() As Double, numOfGeneratingTrades As Long, numOfSeries As Long, forwardLots As Double) As Double()
    ' ロット調整結果を保存する2次元配列を定義
    Dim adjustedProfits() As Double
    ReDim adjustedProfits(numOfGeneratingTrades, 2)

    Dim iseries, itrade As Long
    For itrade = 0 To numOfGeneratingTrades
        For iseries = 0 To 2
            adjustedProfits(itrade, iseries) = forwardLots * medianAndConfidence(itrade, iseries)
        Next iseries
    Next itrade

    adjustProfitsToForwardLots = adjustedProfits
End Function

次に、シートに中央値と信頼区間を出力していきます。

まずは以下のように生成結果を出力する範囲を設定しておきます。ここでは最大行数を5万行として、D29～G50028の範囲に結果を書き込むことにしました。行を広く指定する理由の一つは、グラフに表示する際に参照する範囲を明確にするためです。別の理由として、取引数を変えて計算し直した際に前の計算結果が残らないように範囲全体を消去するためというものもあります。
今回はD29～G50028の範囲をresultRangeという名前のRangeオブジェクトとして定義しました。

    ' 生成結果を挿入する範囲を指定する
    Dim minCol, maxCol As Long
    minCol = 4  ' D列：トレードNo
    maxCol = 7  ' G列：信頼区間上側
    Dim minRow, maxRow As Long
    minRow = 29 ' 開始行=29
    maxRow = 50000  ' 最大行数=50000

    Dim resultRange As Range    ' 挿入先の範囲
    Set resultRange = sheet.Range(Cells(minRow, minCol), Cells(maxRow, maxCol))

範囲が定義できたので、以下に定義したinsertMedianAndConfidenceToCells関数で中央値と信頼区間をシートに出力します。この関数の第１引数はcalcMedianAndConfidenceInterval関数で計算した中央値と信頼区間の配列、第２引数は先ほどの出力範囲を指定するresultRangeオブジェクト、第３引数は生成したトレード数です。

' 中央値と信頼区間をセルに挿入する関数
' Parameters
'   :series (Dobule(numOfGeneratingTrades+1,3)): 中央値と信頼区間
'   :allRange (Range): 挿入先の範囲
'   :numOfGeneratingTrades (Long): 生成したトレード数
Sub insertMedianAndConfidenceToCells(medianAndConfidence() As Double, allRange As Range, numOfGeneratingTrades As Long)
    ' 範囲全体のセルの内容を削除
    allRange.Clear
    ' 範囲全体のうち、挿入する範囲だけを背景色指定、数値指定、小数点以下桁数指定
    Dim insertRange As Range
    Set insertRange = allRange.Range(Cells(1, 1), Cells(numOfGeneratingTrades + 1, 4)) ' トレード数+1なのは最初のゼロを含むため
    ' 背景色指定：#E2EFDA
    insertRange.Interior.Color = &HE2EFDA
    ' 数値指定、小数点以下桁数指定：トレードNo列は0, 中央値・信頼区間は0.00_ ;[赤]-0.00
    insertRange.Range(Cells(1, 2), Cells(numOfGeneratingTrades + 1, 4)).NumberFormatLocal = "0.00_ ;[赤]-0.00"
    insertRange.Range(Cells(1, 1), Cells(numOfGeneratingTrades + 1, 1)).NumberFormatLocal = "0_;"
    ' 罫線を引く
    insertRange.Borders.LineStyle = xlContinuous
    ' 中央値、信頼区間を挿入
    insertRange.Range(Cells(1, 2), Cells(numOfGeneratingTrades + 1, 4)).Value = medianAndConfidence
    ' 連番を挿入
    Dim i As Long
    For i = 0 To numOfGeneratingTrades
        insertRange.Cells(i + 1, 1) = i
    Next i
End Sub

この関数で行っていることは以下の４点です。

  • 出力先の全範囲を消去する
  • 生成したトレード数の行だけ、書式と背景色、罫線を設定する
  • 中央値と信頼区間を出力する
  • グラフ表示用にトレード番号（０～トレード数）を出力する

この関数を実行すれば、中央値と信頼区間をExcelシートへ出力できます。

ここまでに作成した関数群を組み合わせ、以下の順序で実行すると中央値と信頼区間をシートに出力できます。細かい部分は添付のExcelのVBAを確認してください。

    ' 指定したパラメータで損益系列を生成する
    ' トレード数 x 系列数 の2次元配列を定義
    Dim multiProfitSeries() As Double
    ReDim multiProfitSeries(numOfGeneratingTrades, numOfSeries - 1)
    multiProfitSeries = makeMultiProfitSeries(rangeBacktestProfits, numOfGeneratingTrades, numOfSeries)

    ' 中央値と信頼区間を計算する
    ' トレード数 x 信頼区間下限・中央値・信頼区間上限 の2次元配列を定義
    Dim medianAndConfidence() As Double
    ReDim medianAndConfidence(numOfGeneratingTrades, 2)
    medianAndConfidence = calcMedianAndConfidenceInterval(multiProfitSeries, numOfGeneratingTrades, numOfSeries, confidence)

    ' フォワードロットに合うように損益を調整する
    medianAndConfidence = adjustProfitsToForwardLots(medianAndConfidence, numOfGeneratingTrades, numOfSeries, forwardLots)

    ' 結果をセルに挿入する
    Call insertMedianAndConfidenceToCells(medianAndConfidence, resultRange, numOfGeneratingTrades)

7. グラフに表示する

ここまでで、以下のようにExcelシートに各トレード番号における中央値と信頼区間上下限が出力できました。実フォワードはご自身で入力してもらう必要がありますが、一通りグラフ化する準備ができています。

VBA内のパラメータで規定したとおり、出力の最大行数は5万行で範囲はD29～G50028です。D列はトレード番号なので、D列のD29～D50028とE列のE29～E50028で信頼区間下限、D列とF列で中央値、D列とG列で信頼区間上限を散布図上に表示していきます。フォワードも簡易的にD列と組合せてH29～H50028をプロットします。

insertMedianAndConfidenceToCells関数で一度D29～G50028の範囲を全削除してトレード数分だけデータを入れているので、データのない範囲は空欄になっておりグラフ上では無視されます。つまり、生成するトレード数を変えてもグラフが勝手に表示するトレード数を増やしてくれます。

最後に、フォワード推移予測マクロの実行ボタンを作りましょう。ボタンを用意しておけば、毎回メニューからマクロを実行しなくて良くなります。今回は簡単にフォームコントロールボタンで作成します。リボンの「開発」→「挿入」→「フォームコントロール」→「ボタン」を選択します。「開発」リボンが無い方は、オプションの「リボンのユーザ設定」から表示してください。
選択できたらボタンを好きな大きさで作ります。そして右クリック→テキストの編集でボタンの表示名を好きな名前に変えましょう。ここでは「フォワード予測作成」としました。

そして、再度右クリック→「マクロの登録」を選択し、今回作成したmodForwardForecastingWithProfits関数をボタンに登録します。これで、ボタンをクリックするとフォワー推移予測が実行されるようになりました。

ここまでで、損益を考慮したフォワード推移予測ができるようになりました。

時間情報を考慮する場合は、makeProfitSeries関数でトレードを生成する際に、ある日時を超えるまでトレードを生成し続けたうえで日毎に損益を集計する方針で改造していくことになります。

さいごに

この記事では、Excelを使って損益だけを考慮したフォワード推移予測を作成する方法を説明してきました。参考のExcelファイルも添付しましたので、フォワード推移予測を動かすことができたのではないかと思います。

一足飛びに説明をしてきましたので、もしかしたらわかりにくい部分があるかもしれません。疑問ありましたらコメントやTwitterで質問をいただければと思います。

私は普段ほとんどExcelを使わないので、Excel関数の使い方やVBAの書き方が冗長な部分があるかもしれません。特に、VBAの高速化のお作法などは全然わからないので、系列数を増やすとVBA実行に時間がかかってしまいます。もっと良いやり方をご存じの方がいらっしゃいましたら教えていただけるとありがたいです。

次の記事では「時間情報」を考慮してフォワード推移予測を作成する方法を説明する予定です。現在執筆中なのでもうしばらくお待ち下さい。書きあがり次第私のTwitterアカウントで通知致します。

Forest