エクセルで理想のシステムトレード。VBAで10年分のテクニカル指標を自動算出！

Excel VBAで過去10年分の株価データを1クリック取得する方法

通常、Web上の株価データサイトでは、直近の数日分しか表示されないことが多いです。しかし、「エクセルで理想のシステムトレード」で紹介されている手法は、Yahoo!ファイナンスのようなサイトのURL構造をVBAで解析し、日付パラメータを自動で書き換える「For構文」を組み合わせます。

これにより、まるで人間が手作業で日付を変更しながらデータをダウンロードしているかのように、膨大な過去データを効率的に収集できるのです。

Jさんはこの話を聞いて、半信半疑ながらも早速VBAを記述してみました。

Sub GetHistoricalStockData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim stockCode As String
    Dim startDate As String
    Dim endDate As String
    Dim url As String
    
    ' データ出力シートを指定
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 銘柄コード、開始日、終了日を設定（例：日経平均、2014/04/01〜2024/04/26）
    stockCode = "998407.O" ' Yahoo Finance Japan の日経平均コード
    startDate = "20140401"
    endDate = "20240426"
    
    ' URLを生成 (Yahoo Finance Japan の例)
    ' 実際にはサイトの仕様に合わせてURLパラメータを調整
    url = "https://query1.finance.yahoo.com/v7/finance/download/" & stockCode & _
          "?period1=" & DateToUnix(CDate(Left(startDate, 4) & "/" & Mid(startDate, 5, 2) & "/" & Right(startDate, 2))) & _
          "&period2=" & DateToUnix(CDate(Left(endDate, 4) & "/" & Mid(endDate, 5, 2) & "/" & Right(endDate, 2))) & _
          "&interval=1d&events=history&includeAdjustedClose=true"
    
    ' データをシートに直接取り込み
    With ws.QueryTables.Add(Connection:="URL;" & url, Destination:=ws.Range("A1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentCells = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    
    MsgBox "株価データの取得が完了しました！", vbInformation
End Sub

' 日付をUnixタイムスタンプに変換する関数
Function DateToUnix(ByVal TargetDate As Date) As Long
    DateToUnix = DateDiff("s", "1970/1/1", TargetDate)
End Function

【コードの各行解説】

• `Sub GetHistoricalStockData()`: 株価データを取得するメインのプロシージャ。
• `Set ws = ThisWorkbook.Sheets(“Sheet1”)`: データを出力するシートを指定。
• `stockCode = “998407.O”`: 取得したい銘柄のYahoo!ファイナンスコード（例: 日経平均）。
• `startDate = “20140401”`, `endDate = “20240426”`: データ取得期間を指定。
• `url = …`: Yahoo!ファイナンスのヒストリカルデータダウンロードURLを構築。
• `With ws.QueryTables.Add(…)`: 指定したURLからWebクエリを使ってデータをExcelシートに取り込む。
• `DateToUnix(ByVal TargetDate As Date) As Long`: 日付形式をUnixタイムスタンプに変換する補助関数。Yahoo!ファイナンスのAPIがこの形式を要求するため。

Jさんは、実際にコードを貼り付けて実行してみたところ、本当に数分で過去10年分の株価データがシートに表示され、思わず「おぉ！」と声を上げました。

2024年に入り日経平均が一時4万円を突破したような急激な市場変動時、過去のバブル崩壊やリーマンショック後の回復期など、様々な相場局面で特定の銘柄がどのような値動きをしたかを検証したい場合があるでしょう。

手作業で10年分のデータを集めるのは非現実的ですが、このロジックを組んでいれば、瞬時に必要なデータを揃え、「もしあの時、この銘柄に投資していたらどうなっていたか」といった仮説検証の精度を格段に上げることができたかもしれません。

Excel VBAでテクニカル指標を自動計算！相場の変化を素早く察知する

「エクセルで理想のシステムトレード」では、VBAで「配列」を使い複数の期間設定を一括で管理したり、EMA（指数平滑移動平均）のような高度な指標も自動で算出する具体的なコードが紹介されています。これにより、日々の株価変動に合わせて、常に最新のテクニカル指標をExcel上で確認できるようになるのです。

Jさんは、この機能の実装に挑戦し、わずか数行のコードで、それまで数十分かかっていた計算とグラフ化が瞬時に終わることに感動しました。

Sub CalculateMovingAverages()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim closePrice As Range
    Dim maRange As Range
    Dim maPeriods As Variant
    
    ' データが入力されているシートを指定
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row ' D列(終値)の最終行を取得
    
    ' 移動平均の計算期間を指定 (配列で複数指定可能)
    maPeriods = Array(5, 25, 75) ' 5日、25日、75日移動平均
    
    ' 終値の範囲を設定
    Set closePrice = ws.Range("D2:D" & lastRow) ' D列が終値の想定
    
    ' 移動平均を計算して出力
    For j = LBound(maPeriods) To UBound(maPeriods)
        Dim currentPeriod As Long
        currentPeriod = maPeriods(j)
        
        ' 移動平均の出力列を指定 (例: E列から順に)
        Set maRange = ws.Cells(1, 5 + j) ' E1, F1, G1...
        maRange.Value = "MA(" & currentPeriod & ")" ' ヘッダー
        
        For i = 2 To lastRow
            If i >= currentPeriod + 1 Then
                ' WorksheetFunction.Average で移動平均を計算
                maRange.Offset(i - 1, 0).Value = Application.WorksheetFunction.Average( _
                    ws.Range("D" & i - currentPeriod + 1 & ":D" & i))
            End If
        Next i
    Next j
    
    MsgBox "移動平均の計算が完了しました！", vbInformation
End Sub

【コードの各行解説】

• `Sub CalculateMovingAverages()`: 移動平均を計算するプロシージャ。
• `lastRow = ws.Cells(ws.Rows.Count, “D”).End(xlUp).Row`: D列（終値）の最終行を検出。
• `maPeriods = Array(5, 25, 75)`: 計算したい移動平均の期間を配列で指定。
• `Set closePrice = ws.Range(“D2:D” & lastRow)`: 終値データのある範囲を指定。
• `For j = LBound(maPeriods) To UBound(maPeriods)`: 指定した期間ごとにループ処理。
• `maRange.Value = “MA(” & currentPeriod & “)”`: 各移動平均の列にヘッダーを追加。
• `Application.WorksheetFunction.Average(…)`: ExcelのAVERAGE関数をVBAで呼び出し、移動平均を計算。

「自分だけの売買ルール」を過去データで検証するバックテストの威力

「エクセルで理想のシステムトレード」では、独自の売買ルールをVBAでプログラミングし、過去の膨大なデータでバックテストを行うことで、そのルールの有効性を客観的に評価し、自信を持って投資に臨めるようになります。

本書では、「3本足の高値安値」や「アンチ」といった具体的な売買手法をVBAでロジック化し、過去10年分のデータを使って勝率やリスクリターンを自動で算出する機能の構築方法が解説されています。これにより、机上の空論ではない、データに基づいた「使えるルール」を見極めることができるのです。

Jさんは、これまでの自身の「勘」に頼った売買ルールをVBAに落とし込み、バックテストを試みました。すると、意外な結果が…。

「あれ？このルール、〇〇%の確率で利益が出てるぞ！でも、この条件下だと損も大きいな…」

今まで漠然と「良い」と思っていたルールが、数値として可視化されたことで、改善点やリスクが明確になりました。

Sub BacktestStrategy()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim buySignal As Boolean
    Dim totalProfit As Double
    Dim tradeCount As Long
    Dim wins As Long, losses As Long
    Dim entryPrice As Double
    Dim exitPrice As Double
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row ' D列(終値)の最終行を取得
    
    totalProfit = 0
    tradeCount = 0
    wins = 0
    losses = 0
    buySignal = False ' 買いシグナル発生中フラグ
    
    ' バックテストのロジック (例: 5日移動平均が25日移動平均を上回ったら買い、下回ったら売り)
    ' E列に5日MA、F列に25日MAがあると仮定
    For i = 2 To lastRow
        If ws.Cells(i, "E").Value > ws.Cells(i, "F").Value And Not buySignal Then
            ' ゴールデンクロスで買いシグナル
            buySignal = True
            entryPrice = ws.Cells(i, "D").Value ' 終値でエントリー
            ' Debug.Print "買いシグナル発生: " & ws.Cells(i, "A").Value & ", 価格: " & entryPrice
            
        ElseIf ws.Cells(i, "E").Value < ws.Cells(i, "F").Value And buySignal Then
            ' デッドクロスで売りシグナル (保有ポジションを決済)
            buySignal = False
            exitPrice = ws.Cells(i, "D").Value ' 終値でエグジット
            tradeCount = tradeCount + 1
            
            Dim profit As Double
            profit = exitPrice - entryPrice
            totalProfit = totalProfit + profit
            
            If profit > 0 Then
                wins = wins + 1
            Else
                losses = losses + 1
            End If
            ' Debug.Print "売りシグナル発生: " & ws.Cells(i, "A").Value & ", 価格: " & exitPrice & ", 損益: " & profit
        End If
    Next i
    
    ' 結果表示
    ws.Cells(lastRow + 2, 1).Value = "バックテスト結果"
    ws.Cells(lastRow + 3, 1).Value = "総利益:"
    ws.Cells(lastRow + 3, 2).Value = Format(totalProfit, "#,##0")
    ws.Cells(lastRow + 4, 1).Value = "総トレード数:"
    ws.Cells(lastRow + 4, 2).Value = tradeCount
    ws.Cells(lastRow + 5, 1).Value = "勝率:"
    ws.Cells(lastRow + 5, 2).Value = Format(wins / tradeCount, "0.00%") & " (" & wins & "/" & losses & ")"
    
    MsgBox "バックテストが完了しました！", vbInformation
End Sub

【コードの各行解説】

• `Sub BacktestStrategy()`: バックテストを行うプロシージャ。
• `totalProfit`, `tradeCount`, `wins`, `losses`: バックテスト結果を記録するための変数。
• `buySignal`: 現在買いポジションを保有しているかを示すフラグ。
• `For i = 2 To lastRow`: 株価データの各行をループ処理。
• `If ws.Cells(i, “E”).Value > ws.Cells(i, “F”).Value And Not buySignal Then`: 5日移動平均が25日移動平均を上回ったら「買いシグナル」とし、エントリー価格を記録。
• `ElseIf ws.Cells(i, “E”).Value < ws.Cells(i, "F").Value And buySignal Then`: 5日移動平均が25日移動平均を下回ったら「売りシグナル」とし、エグジット価格を記録して損益を計算。
• `MsgBox “バックテストが完了しました！”, vbInformation`: バックテスト完了メッセージと結果をExcelシートに出力。

「エクセルで理想のシステムトレード」を試してみたい方へ

本書で学んだパターン認識を試すなら、TradingView を開くのが手早いです。

※ 解約はワンクリック・いつでもOK

今すぐできるアクション