オプティマルfを計算する – Excel編

私は臆病だけど欲張りなので、青い線を描く資産カーブで運用したい！！

このグラフの損益カーブは、全て同じトレード明細をもとに、複数の資金管理方法のシミュレート結果で作成されています。

損益シミュレーションでは、１年半の複利運用で、10万円が最大500万円強になりました。

これが、オプティマルfの真価。

Excelを使用して、売買システムを複利運用する際に、最終的な資産を最大化する掛け率である、最適固定比率（以後、オプティマルf)の算出が簡単にできるようになる記事。

上記グラフでは、青の線が最終資産が最大となっていて、ジャストこの掛け率を算出します。

比較の為、グラフには一般的な2%リスク運用や、バルサラの破産確率が0.5%となる掛け率によるシミュレーション結果も掲載していますが、オプティマルfに全く及びもしない資産カーブとなります。

オプティマルfの算出過程は、高度で複雑な計算をしているわけではなく、結構シンプルでわかりやすいです。

単利のトレード成績に対して、複利計算式を用いて、総当たりで掛け率を少しずつ変えていき、各掛け率の最終資産残高（倍率)を算出します。その中で、最大の資産を示すものがオプティマルfとなります。

目的達成に必要な前提条件：

複利運用について、少しかじっていただけると（おすすめは、ラルフビンスさんの書籍）
Microsoft Excelが必須　(マクロを使用します。なお、コーディングスキルや、Excelの操作知識が無くても作業できるように、本記事を構成したつもりです）
オプティマルfを算出するにあたり、算出対象システムの単利（できればpips)損益情報が必要

オプティマルfなら、ラルフビンスさんのこの書籍から。革新的だと思います。

投資家のためのマネーマネジメント

Excelは、パッケージ版を入手するか、権利更新型の使用形態で。

本記事掲載のマクロは、Excel2000以降のどのバージョンのExcelでも稼働するはずです。。。

–

オプティマルf算出の要点

オプティマルｆを算出するトレード成績について、計算上はトレード順の依存関係はありませんので、トレード明細のソートは不要です
損益のバラツキによる掛け率に対する影響は、オプティマルfを算出する際のTWRの計算結果に反映されます。よって、オプティマルfは、トレード結果の分散に対する耐性が少なからずあると思われます（確認記事）
オプティマルfでは、リスクパラメータは最大損失のみです（私の間違った解釈かもしれません）。よって、一般的に問題とされるドローダウンの深さは問題になりません。１トレードで、最大どれだけ損をするか、その１点について問題としています。

※将来の運用成績を何ら保証するものではありません。また、オプティマルfによる運用は、一般的に言われているリスクとは比較にならない大きなリスクをとる運用となりますので、徹底したリスク管理が要求されます。

それでは、手順を追ってExcelでオプティマルfを算出していきましょう。

手順：１

適当な初期状態のExcelブックを用意します。

ファイル名や、シート数は適当で構いません。

手順：2

オプティマルfを算出したいトレード明細を、Excelブックにちまちまと入力します。

一番左のシートで作業しましょう。

2列目の２行目以降が、オプティマルfの算出対象となります。

(1行目や、１列目は適当で構いません。が、何かしらの入力が必要です）

ここは、単利トレード成績で、尚且つ（個人的な意見ですが）トレードロット調整が可能な単位で記録すべきと思います。

例えば、複数ポジションをとる戦略では、そのトレード単位毎に成績を集計することで、トレード実態をより反映したオプティマルfが算出できるのではないかと思います。

手順：３

マクロをExcelブックに記録しましょう。

マクロの記録方法は、使用するExcelのバージョンによって相違すると思いますが、一般的にはメニューの”ツール”->”マクロ”のような感じでマクロ記録まで遷移できると思います。

とにかく、マクロエディタまで辿りついて下さい。

適当なマクロ名を入力し、作成ボタンを押下。

とにかく、マクロエディタまで遷移しましょう。

マクロエディタまで来ましたか？

手順：4

オプティマルf算出マクロを実装しましょう。

手順３のマクロエディタの内容を、次に掲載のVBコードで完全に上書きします。

作業結果は、このようになっている必要があります。

コメント行からソースコードが始まり、End Function的な一行でコード終端となります。

※このコード全体を、（行番号を除き）そのままマクロエディタに貼り付けます

※※当記事をご覧になった皆さんの良心を信じます。。。

※※※怪しい通信処理等が無く、セキュアなコードとなっていることを、少しでもご確認いただければと思います

'Copyright 2015, Life with FX
'Link - http://lifewithfx.jp
 
Sub CalcOptimalF()
 
    Dim FVal As Double
    Dim FStepVal As Double
    Dim FLastVal As Double
 
    Dim HPRVal As Double
    Dim TWRVal As Double
    Dim MinPips As Double
    Dim OptimalF As Double
    Dim MaxTWR As Double
    Dim GeoMeaVal As Double
 
    Dim LineIndicator As Long
    Dim LastLineNumber As Long
 
    Dim EARangeStarts As Long
    Dim EARangeEnds As Long
    Dim IndiRangeProcess As Long
    Dim NumOfTrades As Long
 
    Dim RecsSheetName As String
    Dim TwrSheetName  As String
    Dim OptimalFSheetName As String
 
    Dim RecPipsColInd As Integer
    Dim TwrSheetRowInd As Integer
    Dim TwrSheetColInd As Integer
 
    Dim WinTradeCnt As Long
    Dim LossTradeCnt As Long
    Dim WinRate As Double
    Dim AvgWinInPips As Double
    Dim AvgLossInPips As Double
    Dim TotalWinInPips As Double
    Dim TotalLossInPips As Double
    Dim PayoffRatio As Double
    Dim WinTradeThresholdInPips As Double
 
    '初期設定
    RecsSheetName = Worksheets(1).Name
 
    If Worksheets.Count < 3 Then
        Worksheets.Add After:=Worksheets(RecsSheetName), Count:=(3 - Worksheets.Count)
    End If
 
    TwrSheetName = Worksheets(2).Name
    OptimalFSheetName = Worksheets(3).Name
    RecPipsColInd = 2
 
    '結果の格納シートの初期化
    Worksheets(TwrSheetName).Cells.Clear
    Worksheets(TwrSheetName).Cells(1, 1) = "f"
    Worksheets(OptimalFSheetName).Cells.Clear
    Worksheets(OptimalFSheetName).Cells(1, 1) = "トレード数"
    Worksheets(OptimalFSheetName).Cells(1, 2) = "想定最大損失(pips)"
    Worksheets(OptimalFSheetName).Cells(1, 3) = "最大最終資産倍率（倍)"
    Worksheets(OptimalFSheetName).Cells(1, 4) = "オプティマルf"
    Worksheets(OptimalFSheetName).Cells(1, 5) = "幾何平均"
    Worksheets(OptimalFSheetName).Cells(1, 6) = "勝ちトレード数"
    Worksheets(OptimalFSheetName).Cells(1, 7) = "負けトレード数"
    Worksheets(OptimalFSheetName).Cells(1, 8) = "勝率(%)"
    Worksheets(OptimalFSheetName).Cells(1, 9) = "平均勝ちトレード(pips)"
    Worksheets(OptimalFSheetName).Cells(1, 10) = "平均負けトレード(pips)"
    Worksheets(OptimalFSheetName).Cells(1, 11) = "ペイオフレシオ"
 
    '初期状態の準備
    TwrSheetColInd = 1
    LineIndicator = 2
    EARangeStarts = 2
    LastLineNumber = Worksheets(RecsSheetName).Range("B1").End(xlDown).Row
 
    MaxTWR = -1
    OptimalF = 0
    MinPips = 100000
    FVal = 0.001
    FStepVal = 0.001
    FLastVal = 0.999
 
    WinTradeThresholdInPips = 0.001
    WinTradeCnt = 0
    LossTradeCnt = 0
    WinRate = 0
    AvgWinInPips = 0
    AvgLossInPips = 0
    TotalWinInPips = 0
    TotalLossInPips = 0
    PayoffRatio = 0
 
    MsgBox "fの初期値::" & FVal & "    fの変化値::" & FStepVal & "    fの最大値::" & FLastVal & "  にて計算処理を開始"
 
    '処理本体
    For LineIndicator = 2 To LastLineNumber Step 1
 
        '最大損失を押さえる
        If (MinPips > Worksheets(RecsSheetName).Cells(LineIndicator, RecPipsColInd)) Then
            MinPips = Worksheets(RecsSheetName).Cells(LineIndicator, RecPipsColInd)
        End If
 
        '統計情報
        If Worksheets(RecsSheetName).Cells(LineIndicator, RecPipsColInd) > WinTradeThresholdInPips Then
            WinTradeCnt = WinTradeCnt + 1
            TotalWinInPips = TotalWinInPips + Worksheets(RecsSheetName).Cells(LineIndicator, RecPipsColInd)
        Else
            LossTradeCnt = LossTradeCnt + 1
            TotalLossInPips = TotalLossInPips + Worksheets(RecsSheetName).Cells(LineIndicator, RecPipsColInd)
        End If
 
    Next LineIndicator
 
    'Max TWRを総当たりで計算し、OptimalFを導出する
    EARangeEnds = LastLineNumber
    NumOfTrades = LastLineNumber - 1
    TwrSheetColInd = TwrSheetColInd + 1
    TwrSheetRowInd = 1
 
    'シートの見出しを設定する
    Worksheets(TwrSheetName).Cells(TwrSheetRowInd, TwrSheetColInd) = "TWRs"
    TwrSheetRowInd = TwrSheetRowInd + 1
 
    '掛け率を変化させTWRの算出を行う
    For FVal = 0.001 To FLastVal Step FStepVal
 
        TWRVal = 1
 
        For IndiRangeProcess = EARangeStarts To EARangeEnds Step 1
            HPRVal = CalcHPR(Worksheets(RecsSheetName).Cells(IndiRangeProcess, RecPipsColInd), MinPips, FVal)
            TWRVal = TWRVal * HPRVal
        Next IndiRangeProcess
 
        '1つのf値について、TWRの算出が完了した (データの記録とオプティマルfを保存する)
        Worksheets(TwrSheetName).Cells(TwrSheetRowInd, 1) = FVal
        Worksheets(TwrSheetName).Cells(TwrSheetRowInd, TwrSheetColInd) = TWRVal
        TwrSheetRowInd = TwrSheetRowInd + 1
 
        If MaxTWR < TWRVal Then
            MaxTWR = TWRVal
            OptimalF = FVal
        End If
 
    Next FVal
 
    '統計情報の整備
    If WinTradeCnt <> 0 Then
        AvgWinInPips = TotalWinInPips / WinTradeCnt
    Else
        AvgWinInPips = 0
    End If
 
    If LossTradeCnt <> 0 Then
        AvgLossInPips = TotalLossInPips / LossTradeCnt
        ProfitFactor = TotalWinInPips / TotalLossInPips * -1
    Else
        AvgLossInPips = 0
        ProfitFactor = 999
    End If
 
    WinRate = WinTradeCnt / (WinTradeCnt + LossTradeCnt) * 100
 
    If AvgLossInPips <> 0 Then
        PayoffRatio = AvgWinInPips / AvgLossInPips
    Else
        PayoffRatio = 999
    End If
 
    '0除算対策
    If OptimalF < 0.01 Then
        OptimalF = 0.001
    End If
 
    '幾何平均等の計算
    GeoMeaVal = Exp((1 / NumOfTrades) * Log(MaxTWR))
 
    '算出済みのオプティマルｆを記録する
    Worksheets(TwrSheetName).Cells(TwrSheetRowInd, 1) = "MaxTWR"
    Worksheets(TwrSheetName).Cells(TwrSheetRowInd, TwrSheetColInd) = MaxTWR
    Worksheets(TwrSheetName).Cells(TwrSheetRowInd + 1, 1) = "オプティマルf"
    Worksheets(TwrSheetName).Cells(TwrSheetRowInd + 1, TwrSheetColInd) = OptimalF
    Worksheets(OptimalFSheetName).Cells(TwrSheetColInd, 1) = NumOfTrades
    Worksheets(OptimalFSheetName).Cells(TwrSheetColInd, 2) = Round(MinPips, 1)
    Worksheets(OptimalFSheetName).Cells(TwrSheetColInd, 3) = Round(MaxTWR, 3)
    Worksheets(OptimalFSheetName).Cells(TwrSheetColInd, 4) = Round(OptimalF, 5)
    Worksheets(OptimalFSheetName).Cells(TwrSheetColInd, 5) = Round(GeoMeaVal, 4)
    Worksheets(OptimalFSheetName).Cells(TwrSheetColInd, 6) = WinTradeCnt
    Worksheets(OptimalFSheetName).Cells(TwrSheetColInd, 7) = LossTradeCnt
    Worksheets(OptimalFSheetName).Cells(TwrSheetColInd, 8) = Round(WinRate, 2)
    Worksheets(OptimalFSheetName).Cells(TwrSheetColInd, 9) = Round(AvgWinInPips, 1)
    Worksheets(OptimalFSheetName).Cells(TwrSheetColInd, 10) = Round(AvgLossInPips, 1)
    Worksheets(OptimalFSheetName).Cells(TwrSheetColInd, 11) = Abs(Round(PayoffRatio, 3))
 
    MsgBox "Task Completed!!"
    Worksheets(OptimalFSheetName).Activate
 
End Sub
 
Function CalcHPR(ByVal pips As Double, ByVal MinPips As Double, ByVal f As Double) As Double
    CalcHPR = 1 + (f * (-1 * pips / MinPips))
End Function

※私が普段使用しているプログラムの簡易版となります。無償の範囲ではこれが限界なのではないかと思います。

※複数EAの一括処理や、トレード間の依存関係を調査して、リスク調整を行う機能はありません。ただシンプルにオプティマルfを計算します

手順：５

マクロの記載まで完了したら、作業中のExcelブックをマクロ実行可能形式で保存しましょう。

マクロはいろいろ言われるところがありますので、セキュリティが厳しいようです。

私が使用するバージョンのExcelでは、”マクロ有効ブック”形式にて保存しました。

これで、準備完了です。

手順：６

マクロを実行して、オプティマルfを算出しましょう。

ここまでの手順が正しく実施できていれば、先ほどのマクロ一覧ウィンドウに、”CalcOptimalF”マクロが表示されます。

CalcOptimalFを実行しましょう。

マクロ稼働後、すぐにこのようなダイアログが表示されます。

オプティマルfの算出単位と計算範囲に関する情報です。

OKボタン押下後に実処理が開始されます。

間も無くマクロ完了とともに、このようなダイアログが表示されます。

数万行のトレード明細を処理させても、処理時間は５分程度です。

本記事掲載のサンプルでは、瞬殺で完了します。

最終ステップ：オプティマルfの確認

オプティマルf計算後、計算結果記録シートに自動的に遷移します。

詳細は、ピクチャをご確認下さい。

２番目のシートには、各掛け率で算出した最終資産倍率が記録されています。

１列目と２列目で、グラフを作ると、資産倍率カーブが見えてきます。

このカーブの頂点となる掛け率が、オプティマルfです。

結び：：：

オプティマルfによる掛け率は、例えば優秀なEAの場合に、90%超となることもあります。トレード資産総額に対して、この最大の掛け率で運用することは、絶対に止めるべきと思います。不意の値飛びや、スリッページで想定最大損失以上の損失を計上した場合に再起不能どころか、口座残高がマイナスになることすらあり大変危険です。

また、例えばEAのバックテスト結果は、過去のある範囲で最適化されていることが多く、実運用ではバックテストほどの成績が発揮でき無い場合が多いです。

よって、オプティマルfでは潜在的にオーバーリスクとなる可能性が高く、最終資産を最大化できません。

以上から、つまるところ、オプティマルf値から幾分かパワーを抑えた運用にすると、最終結果も精神的にも良い運用結果になるような気がしています。

以下、ぼやき

例えば、５万円をドブに捨てたことにして、その予算の範囲内でオプティマルfを用いて運用してみるのも夢があっていいのかもしれないと思います。（今の私の運用方針）
カーブフィッティングや、トレード手法が無くパラメータを組み合わせただけの自動売買プログラムは、オプティマルfで運用するとすぐに破綻域まで追い込まれます。これはこれで、キッパリと運用を諦められるので良いのかも。
オプティマルfを算出するときに、ついでに算出していた幾何平均値は、売買手法を相対的に比較できる指標となります。平均成長率である幾何平均と、トレード頻度に関する情報があれば、統計上は各売買手法の完全な相対性能評価ができてしまいます。

私の狭い知識の範囲では、少ない元手を爆発的に、且つ統計的に限界のスピードで増やす最良の方法が、唯一オプティマルfであると信じています。

何せ、多くのトレードサンプルに対して、総当たりで試算した結果で得られた掛け率ですし。

一度きりの人生、どうせなら夢を見たい。