Excelでのフォワード予測グラフ作成　その２：時間情報を考慮したフォワード予測

=VLOOKUP($C3, $F$3:$G$2007, 2, FALSE)

=$B3-$A3

=$A4-$A3

    ' パラメータとして、生成開始日、生成する日数、系列数、信頼区間幅、フォワードロットを読み込む
    Dim startDate As Date
    Dim numOfGeneratingDates As Long
    Dim numOfSeries As Long
    Dim confidence As Double
    Dim forwardLots As Double
    startDate = sheet.Cells(3, 7)   ' G3
    numOfGeneratingDates = sheet.Cells(3, 8) ' H3
    numOfSeries = sheet.Cells(3, 9) ' I3
    confidence = sheet.Cells(3, 10) ' J3
    forwardLots = sheet.Cells(3, 11) ' K3

    ' バックテストの範囲を取得する：１トレード目は使わないので２トレード目以降
    Dim rangeBacktestTrades As Range
    Set rangeBacktestTrades = sheet.Range(Cells(3, 1), Cells(numOfBacktestTrades + 1, 3)) ' A3～バックテストの数

' バックテストからランダムに１つトレードを取得する関数
' Parameters
'   :trades: バックテスト損益を表すRangeオブジェクト
' Return
'   (Double(3)): 抽出したHoldPeriod, TradeInterval, 損益を含む1行3列のDouble型配列
Function getBacktestRange(trades As Range) As Double()
    ' お手軽にWorksheetFunctionのRANDBETWEENを使う
    Dim r As Long: r = WorksheetFunction.RandBetween(1, trades.Rows.count)
    Dim row(3) As Double
    row(1) = trades.Cells(r, 1)
    row(2) = trades.Cells(r, 2)
    row(3) = trades.Cells(r, 3)
    getBacktestRange = row
End Function

' 指定日数分のトレードを生成して損益系列を作成する関数
' Parameters
'   :trades (Range): バックテストのHoldPeriod, TradeInterval, 損益を含むRangeオブジェクト
'   :numOfGeneratingDates (Long): 生成する日数
' Return
'   (Double(numOfGeneratingDates+1)): 抽出した損益の配列：要素数=numOfGeneratingDates+1で、i=0は損益0
Function makeProfitSeries(trades As Range, numOfGeneratingDates As Long) As Double()
    ' 日数+1日分の配列を確保してゼロに初期化
    Dim i As Long
    Dim dailyProfit() As Double     ' 日毎の損益
    Dim profitSeries() As Double    ' 累積損益
    ReDim profitSeries(numOfGeneratingDates)
    ReDim dailyProfit(numOfGeneratingDates)
    For i = 0 To numOfGeneratingDates
        profitSeries(i) = 0.0
        dailyProfit(i) = 0.0
    Next i
    ' TradeIntervalの累積がnumOfGeneratingDatesを越えるまで生成し続ける
    ' あわせて決済時刻にごとに損益を集計していく
    Dim closeTime As Double
    Dim tradeRange() As Double
    ReDim tradeRange(3)
    Dim cumsumTradeInterval As Double: cumsumTradeInterval = 0.0
    Do While cumsumTradeInterval < numOfGeneratingDates
        ' バックテストから１行取得
        tradeRange = getBacktestRange(trades)
        ' TradeIntervalを加算
        cumsumTradeInterval = cumsumTradeInterval + tradeRange(2)
        ' OpenTimeが生成日数を超えた時点で終了
        If cumsumTradeInterval >= numOfGeneratingDates Then
            Exit Do
        End If
        ' 決済時間を計算：累積TradeInterval + HoldPeriod
        closeTime = cumsumTradeInterval + tradeRange(1)
        ' 決済時間が指定時間を越えていない場合だけ損益に加算
        ' この時点で終了させないのは、当日中に新たなトレード発生する可能性があるため
        If closeTime < numOfGeneratingDates Then
            ' 決済時間に該当する配列に損益を加える
            ' 0<=closeTime<1のときは開始0日目なので開始日：profitSeries(1)に加える
            ' i<=closeTime<i+1のときは開始i日目なのでprofitSeries(i+1)に加える
            dailyProfit(Int(closeTime) + 1) = dailyProfit(Int(closeTime) + 1) + tradeRange(3)
        End If
    Loop
    ' 日毎の損益を累積損益に変換
    For i = 1 To numOfGeneratingDates
        profitSeries(i) = profitSeries(i-1) + dailyProfit(i)
    Next i
    ' 累積損益を返す
    makeProfitSeries = profitSeries
End Function

    ' TradeIntervalの累積がnumOfGeneratingDatesを越えるまで生成し続ける
    ' あわせて決済時刻にごとに損益を集計していく
    Dim closeTime As Double
    Dim tradeRange() As Double
    ReDim tradeRange(3)
    Dim cumsumTradeInterval As Double: cumsumTradeInterval = 0.0
    Do While cumsumTradeInterval < numOfGeneratingDates
        ' バックテストから１行取得
        tradeRange = getBacktestRange(trades)
        ' TradeIntervalを加算
        cumsumTradeInterval = cumsumTradeInterval + tradeRange(2)
        ' OpenTimeが生成日数を超えた時点で終了
        If cumsumTradeInterval >= numOfGeneratingDates Then
            Exit Do
        End If
        ' 決済時間を計算：累積TradeInterval + HoldPeriod
        closeTime = cumsumTradeInterval + tradeRange(1)
        ' 決済時間が指定時間を越えていない場合だけ損益に加算
        ' この時点で終了させないのは、当日中に新たなトレード発生する可能性があるため
        If closeTime < numOfGeneratingDates Then
            ' 決済時間に該当する配列に損益を加える
            ' 0<=closeTime<1のときは開始0日目なので開始日：profitSeries(1)に加える
            ' i<=closeTime<i+1のときは開始i日目なのでprofitSeries(i+1)に加える
            dailyProfit(Int(closeTime) + 1) = dailyProfit(Int(closeTime) + 1) + tradeRange(3)
        End If
    Loop

Do While cumsumTradeInterval < numOfGeneratingDates

        ' バックテストから１行取得
        tradeRange = getBacktestRange(trades)
        ' TradeIntervalを加算
        cumsumTradeInterval = cumsumTradeInterval + tradeRange(2)
        ' OpenTimeが生成日数を超えた時点で終了
        If cumsumTradeInterval >= numOfGeneratingDates Then
            Exit Do
        End If

        ' 決済時間を計算：累積TradeInterval + HoldPeriod
        closeTime = cumsumTradeInterval + tradeRange(1)
        ' 決済時間が指定時間を越えていない場合だけ損益に加算
        ' この時点で終了させないのは、当日中に新たなトレード発生する可能性があるため
        If closeTime < numOfGeneratingDates Then
            ' 決済時間に該当する配列に損益を加える
            ' 0<=closeTime<1のときは開始0日目なので開始日：profitSeries(1)に加える
            ' i<=closeTime<i+1のときは開始i日目なのでprofitSeries(i+1)に加える
            dailyProfit(Int(closeTime) + 1) = dailyProfit(Int(closeTime) + 1) + tradeRange(3)
        End If

    ' 日毎の損益を累積損益に変換
    For i = 1 To numOfGeneratingDates
        profitSeries(i) = profitSeries(i-1) + dailyProfit(i)
    Next i
    ' 累積損益を返す
    makeProfitSeries = profitSeries

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

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

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

    makeMultiProfitSeries = multiProfitSeries
End Function

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

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

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

    calcMedianAndConfidenceInterval = medianAndConfidence
End Function

' 中央値と信頼区間をセルに挿入する関数
' Parameters
'   :series (Dobule(numOfGeneratingDates+1,3)): 中央値と信頼区間
'   :allRange (Range): 挿入先の範囲
'   :numOfGeneratingDates (Long): 生成した日数
'   :startDate (Date): 生成開始日
Sub insertMedianAndConfidenceToCells(medianAndConfidence() As Double, allRange As Range, numOfGeneratingDates As Long, startDate As Date)
    ' 範囲全体のセルの内容を削除
    allRange.Clear
    ' 範囲全体のうち、挿入する範囲だけを背景色指定、数値指定、小数点以下桁数指定
    Dim insertRange As Range
    Set insertRange = allRange.Range(Cells(1, 1), Cells(numOfGeneratingDates + 1, 4)) ' トレード数+1なのは最初のゼロを含むため
    ' 背景色指定：#E2EFDA
    insertRange.Interior.Color = &HE2EFDA
    ' 書式指定：日付はYYYY/MM/DD, 中央値・信頼区間は0.00_ ;[赤]-0.00
    insertRange.Range(Cells(1, 2), Cells(numOfGeneratingDates + 1, 4)).NumberFormatLocal = "0.00_ ;[赤]-0.00"
    insertRange.Range(Cells(1, 1), Cells(numOfGeneratingDates + 1, 1)).NumberFormatLocal = "yyyy/mm/dd"
    ' 罫線を引く
    insertRange.Borders.LineStyle = xlContinuous
    ' 中央値、信頼区間を挿入
    insertRange.Range(Cells(1, 2), Cells(numOfGeneratingDates + 1, 4)).Value = medianAndConfidence
    ' startDateから時刻を消して00:00にする。時刻は小数点以下の数で表現されるのでIntを取れば00:00になる
    startDate = Int(startDate)
    ' 日付を挿入：トレードごとの損益をプロットする場合も考えて、時刻は23:59:59に設定
    Dim i As Long
    For i = 0 To numOfGeneratingDates
        insertRange.Cells(i + 1, 1) = startDate - 1 + i + (1.0 - 1.0 / 86400.0)
    Next i
End Sub

    ' startDateから時刻を消して00:00にする。時刻は小数点以下の数で表現されるのでIntを取れば00:00になる
    startDate = Int(startDate)
    ' 日付を挿入：トレードごとの損益をプロットする場合も考えて、時刻は23:59:59に設定
    Dim i As Long
    For i = 0 To numOfGeneratingDates
        insertRange.Cells(i + 1, 1) = startDate - 1 + i + (1.0 - 1.0 / 86400.0)
    Next i

    ''' フォワードの集計
    ' フォワードのトレード数を読み込む
    Dim numOfForwardTrades As Long
    numOfForwardTrades = sheet.Cells(2, 17) ' Q2固定

    ' フォワード損益の範囲を読み込む
    Dim rangeForwardTrades As Range
    Set rangeForwardTrades = sheet.Range(Cells(2, 15), Cells(numOfForwardTrades + 1, 16)) ' O2～フォワードトレード数

' フォワード損益を日毎に集計する関数
' Parameters
'   :trades (Range): フォワードのCloseTimeと損益を含むRangeオブジェクト
' Return
'   (Double()): 日毎の累積損益の配列：要素数=フォワード日数
Function aggregateForwardProfit(trades As Range) As Double()
    ' フォワードの初決済と最終決済の日付を取得
    Dim firstDate, lastDate As Date
    firstDate = Application.WorksheetFunction.Min(trades.Columns(1))
    lastDate = Application.WorksheetFunction.Max(trades.Columns(1))

    ' トレード日数を計算：最終日も日数に含むので1を足す
    Dim numOfTradeDates As Long
    numOfTradeDates = Int(lastDate) - Int(firstDate) + 1

    ' 集計用の配列を定義
    Dim dailyProfit() As Double     ' 日毎の損益
    Dim profitSeries() As Double    ' 累積損益
    ReDim dailyProfit(numOfTradeDates)
    ReDim profitSeries(numOfTradeDates)
    Dim i As Long
    For i = 0 To numOfTradeDates
        profitSeries(i) = 0.0
        dailyProfit(i) = 0.0
    Next i

    ' CloseTimeをもとに日毎の損益を集計
    Dim idx As Long
    For i = 1 To trades.Rows.count
        idx = Int(trades.Cells(i, 1)) - Int(firstDate) + 1
        dailyProfit(idx) = dailyProfit(idx) + trades.Cells(i, 2)
    Next i

    ' 日毎の損益を累積損益に変換
    For i = 1 To numOfTradeDates
        profitSeries(i) = profitSeries(i-1) + dailyProfit(i)
    Next i
    ' 累積損益を返す
    aggregateForwardProfit = profitSeries
End Function

    ' フォワードの初決済と最終決済の日付を取得
    Dim firstDate, lastDate As Date
    firstDate = Application.WorksheetFunction.Min(trades.Columns(1))
    lastDate = Application.WorksheetFunction.Max(trades.Columns(1))

    ' トレード日数を計算：最終日も日数に含むので1を足す
    Dim numOfTradeDates As Long
    numOfTradeDates = Int(lastDate) - Int(firstDate) + 1

    ' 集計用の配列を定義
    Dim dailyProfit() As Double     ' 日毎の損益
    Dim profitSeries() As Double    ' 累積損益
    ReDim dailyProfit(numOfTradeDates)
    ReDim profitSeries(numOfTradeDates)

    ' CloseTimeをもとに日毎の損益を集計
    Dim idx As Long
    For i = 1 To trades.Rows.count
        idx = Int(trades.Cells(i, 1)) - Int(firstDate) + 1
        dailyProfit(idx) = dailyProfit(idx) + trades.Cells(i, 2)
    Next i

    ' 日毎の損益を累積損益に変換
    For i = 1 To numOfTradeDates
        profitSeries(i) = profitSeries(i-1) + dailyProfit(i)
    Next i

    ' フォワードをセルに挿入
    ' フォワード挿入範囲を定義：J29～K50028
    Dim insertForwardRange As Range
    Set insertForwardRange = sheet.Range(Cells(minRow, 10), Cells(maxRow, 11))
    Call insertForwardProfit(dailyForwardProfit, insertForwardRange, Application.WorksheetFunction.Min(rangeForwardTrades.Columns(1)))

' フォワード集計結果をセルに挿入する関数
' Parameters
'   :series (Dobule(フォワード日数,2)): フォワード
'   :allRange (Range): 挿入先の範囲
'   :startDate (Date): フォワード開始日
Sub insertForwardProfit(forwardProfit() As Double, allRange As Range, startDate As Date)
    ' 範囲全体のセルの内容を削除
    allRange.Clear
    ' 範囲全体のうち、挿入する範囲だけを背景色指定、数値指定、小数点以下桁数指定
    Dim insertRange As Range
    Set insertRange = allRange.Range(Cells(1, 1), Cells(UBound(forwardProfit) + 1, 2)) ' トレード数+1なのは最初のゼロを含むため
    ' 背景色指定：#E2EFDA
    insertRange.Interior.Color = &HFCE4D6
    ' 書式指定：日付はYYYY/MM/DD, 中央値・信頼区間は0.00_ ;[赤]-0.00
    insertRange.Range(Cells(1, 2), Cells(UBound(forwardProfit) + 1, 2)).NumberFormatLocal = "0.00_ ;[赤]-0.00"
    insertRange.Range(Cells(1, 1), Cells(UBound(forwardProfit) + 1, 1)).NumberFormatLocal = "yyyy/mm/dd"
    ' 罫線を引く
    insertRange.Borders.LineStyle = xlContinuous
    ' フォワード損益を挿入
    insertRange.Range(Cells(1, 2), Cells(UBound(forwardProfit) + 1, 2)).Value = forwardProfit
    ' startDateから時刻を消して00:00にする。時刻は小数点以下の数で表現されるのでIntを取れば00:00になる
    startDate = Int(startDate)
    ' 最初の行は損益ゼロを入れる
    insertRange.Cells(1, 1) = startDate - 1 + (1.0 - 1.0 / 86400.0)
    insertRange.Cells(1, 2) = 0.0
    ' 日付を挿入：トレードごとの損益をプロットする場合も考えて、時刻は23:59:59に設定
    Dim i As Long
    For i = 2 To UBound(forwardProfit) + 1
        insertRange.Cells(i, 1) = startDate + (i - 2) + (1.0 - 1.0 / 86400.0)
        insertRange.Cells(i, 2) = forwardProfit(i - 1)
    Next i
End Sub