Excelでバックテストする方法を知りたい。
どうやってＶＢＡでプログラムを組めばいいの？
この記事では、上記のような疑問にお答えします。
本記事のポイント
・Excelでバックテストするメリット
・Excel ＶＢＡによるバックテストツールの開発方法
この記事では、Excelによるバックテストの方法を紹介しています。
特に、ＶＢＡによるバックテストツールの開発方法を解説しています。
オリジナルのバックテストツールを作りたい人は必見です。
はじめまして！マンモスです。
投資歴15年です。
以前は負け続けていましたが、システムトレードの考え方を習得したことで、大きく資産を増やすことができました。
※2021年4月に資産3000万円を達成しました。
そんな私が、「Excelでバックテストする方法。【ＶＢＡによるツール開発】」を解説します。
Excelでバックテストする方法
Excelでバックテストする方法は2つあります。
②関数を使う方法
また、証券会社やＦＸ業者、仮想通貨業者などが提供するサービスを使えば、Excelの機能拡張をすることができ、自動売買や各口座と連携したExcelベースのツールなども作成可能になります。
※バックテストの基本は、こちらの記事をご覧ください。
ＶＢＡでツールを開発する方法
ＶＢＡとはExcelに実装されているプログラミング言語です。
ＶＢＡを使うことによって、Excelで様々な機能拡張、処理の自動化ができます。
つまり、Excelをベースとしたシステムトレードツールを作ることができます。
また、プログラミングはアイデア次第では、なんでもできるといっても過言ではありません。
プログラミングスキルさえ習得できれば、高度なツールの自作も可能になります。
※ＶＢＡで作るバックテストツールの実例は後述します。
※ＶＢＡは初心者におすすめのプログラミング言語です。
下記の記事で詳しく解説しています。
関数を使う方法
Excelの関数を使ってバックテストすることもできます。
こちらはＶＢＡと違い、プログラミングスキルは不要です。
関数を使えれば誰でもバックテストができる反面、高度なバックテストにはかなりの手間がかかります。
かなり限定したバックテストであれば問題ありませんが、バックテストに必要なデータが膨大になる場合はＶＢＡを使うほうがおすすめです。
Excelでバックテストするメリット
Excelでバックテストするメリットは3つあります。
②拡張性が高い
③自動売買に発展させることができる
低コストでバックテスト環境を作れる
バックテストツールやシステムトレードツールは高額になるケースがあります。
株などでは、月1万円くらいが相場です・・・・
その点、Excelは一般的なＰＣであればほとんどインストールされています。
※インストールされていなければ、1万5000円程度で買うことになる。
バックテスト環境を自作する、という手間はかかるものの、低コストで運用ができることがメリットです。
仮に月1万円程度のコストが発生した場合、そのコスト分を含めてトレードで勝たなければいけません。
高額を運用する投資家であれば問題ありませんが、少額を運用する場合、大きな利益を狙う必要がでてくるため、必要以上のリスクを取る必要があります。
ですから、低コストというだけで大きなアドバンテージになります。
拡張性が高い
市販のシステムトレードツール、バックテストツールなどは、プロが作っているため完成度は非常に高いです。
また、様々なことができます。
しかし、完成された製品であるがゆえに、拡張性は全くありません。
例えば、自身にとって必要なリスク指標が算出できない、ということもありえます。
市販ツールであれば、妥協して使い続ける必要がありますが、自作の場合であればいくらでも機能拡張ができます。
自動売買に発展させることができる
Excelでバックテストツールを作った場合、バックテストのみならず、各業者のＡＰＩを使うことによって自動売買に発展させることも可能です。
つまり、バックテストした売買ルールをそのまま自動売買に転用できるというわけです。
そうすれば、総合的なシステムトレードツールを低コストで運用できるため、トレードでの損益を大きく改善するのに役立ちます。
※ＡＰＩは下記の記事内で詳しく解説しています。
Excel ＶＢＡによるバックテストツールの開発方法
バックテストツールの開発は、以下の手順で作業します。
②必要なデータを準備する（時系列データ）
③プログラミングする
また、以下の流れで、ＶＢＡによるバックテストツールの作成方法を解説します。
②売買ルールの説明
③時系列データの取得方法
④各プログラムの変数説明
⑤各プログラムのコード解説
※当サイトではＶＢＡの基本を解説しています。
もし、ＶＢＡの基本が分からなければ、下記リンクより基礎を学習できます。
今回作るバックテストツールは、上記のＶＢＡ基礎を元に作っているプログラミング学習教材という意味合いが強いです。
実用性重視というよりは、未経験者がバックテストツール作成の全体像を把握できることを重視しています。
※高度なバックテストをしたい場合、下記の記事で紹介している本を読めば、バックテストについての知識が深まります。
ＶＢＡの全コード
まず始めに、この記事で作るバックテストプログラムの全コードを掲載します。
詳細は各項目で解説します。
※変数名についてはカウンタ変数「i」以外は全て日本語としています。
日本語にした理由は、
「変数が多く、初心者向けに変数とそれ以外のコードの違いを明確にする」ためです。
通常、変数名は半角英語が推奨されますが、
今回は分かり易さ重視にしています。
自身でプログラミングする際は半角英語を使いましょう！
1 Option Explicit
2 Sub バックテスト()
3 Dim i As Integer
4 Dim MA期間1 As Integer
5 Dim MA期間2 As Integer
6 Dim 最終行 As Integer
7 Dim 買いシグナル As Integer
8 Dim 売りシグナル As Integer
9 Dim フラグ As Boolean
10 Dim 買値 As Double
11 Dim 売値 As Double
12 Dim 勝ち数 As Integer
13 Dim 負け数 As Integer
14 Dim 損益 As Double
15 MA期間1 = Range(“N1”)
16 MA期間2 = Range(“N2”)
17 最終行 = Worksheets(“データ”).Cells(Rows.Count, 1).End(xlUp).Row
18 フラグ = False
19 買いシグナル = 0
20 売りシグナル = 0
21 買値 = 0
22 売値 = 0
23 勝ち数 = 0
24 負け数 = 0
25 損益 = 0
26 For i = MA期間1 + 1 To 最終行
27 Cells(i, 7) = WorksheetFunction.Average(Range(Cells(i – MA期間1 + 1,5),Cells(i, 5)))
28 Next
29 For i = MA期間2 + 1 To 最終行
30 Cells(i, 8) = WorksheetFunction.Average(Range(Cells(i – MA期間2 + 1,5),Cells(i, 5)))
31 Next
32 For i = MA期間2 + 1 To 最終行
33 If フラグ = False And Cells(i, 7) > Cells(i, 8) Then
34 買いシグナル = 買いシグナル + 1
35 フラグ = True
36 Cells(i, 9) = 買いシグナル
37 買値 = Cells(i, 5) * 100
38 End If
39 If フラグ = True And Cells(i, 7) < Cells(i, 8) Then
40 売りシグナル = 売りシグナル + 1
41 フラグ = False
42 Cells(i, 10) = 売りシグナル
43 売値 = Cells(i, 5) * 100
44 Cells(i, 11) = 売値 – 買値
45 買値 = 0
46 売値 = 0
47 End If
48 If Cells(i, 11) > 0 Then
49 勝ち数 = 勝ち数 + 1
50 ElseIf Cells(i, 11) <= 0 And Cells(i, 11) <> “” Then
51 負け数 = 負け数 + 1
52 End If
53 損益 = 損益 + Cells(i, 11)
54 Next
55 Range(“N3”) = 買いシグナル
56 Range(“N4”) = 勝ち数
57 Range(“N5”) = 負け数
58 Range(“N6”) = 勝ち数 / 買いシグナル
59 Range(“N7”) = 損益
60 End Sub
事前準備
完成型は以下のようになります。
Excelのシートを準備し、G列～Ｋ列1行目の見出しと赤字部分は事前に入力しておきましょう。
バックテストする売買ルールの説明
バックテストするために、まずは売買ルールを決めます。
今回はあくまでプログラミング学習をメインにしているため、以下のようなシンプルで分かり易い売買ルールにしています。
具体的な売買条件
買いルール（ゴールデンクロス)
→移動平均線(5日)が移動平均線(25日)を上回ったら、その日の終値で買う。
売りルール（デッドクロス)
→移動平均線(5日)が移動平均線(25日)を下回ったら、その日の終値で売る。
売買単位→最低単元で取引きする。（株を想定している）
実際に売買ルールを決める際は、以下のことを明確かつ曖昧さがないように決める必要があります。
・エントリータイミング（買い場）
・イグジットタイミング（売り場）
・マネーマネジメント（いくら買うかなど）
・リスクマネジメント（損切りラインなど）
時系列データの準備
時系列データを準備します。
CSVデータをネットよりダウンロードし、図のように「日付、始値、高値、安値、終値」のデータをCSVファイルよりコピペしてエクセルに貼り付けましょう。
※セルＡ１を起点に貼り付けましょう。
※CSVファイルのダウンロードは下記記事で解説しています。
もしCSVファイルがメモ帳などのテキストファイル形式になっている場合、下記の手順でExcel形式にできます。
①対象ファイルを右クリックする。
②プロパティを開き全般タブを表示する。
③「変更」をクリックし、プログラムをExcelにして「OK」を押します。
移動平均線の算出
Excel側の準備が終わりましたので、今度はいよいよプログラミングになります。
まず始めにモジュールを準備し、プロシージャを作成します。
2 Sub バックテスト()
60 End Sub
次にやるべきことは移動平均線の算出です。
移動平均線算出に使用するコード
今回は移動平均線2本分を算出します。
以下のようなコードで移動平均を算出しています。（全コードより抜粋）
3 Dim i As Integer
4 Dim MA期間1 As Integer
5 Dim MA期間2 As Integer
15 MA期間1 = Range(“N1”)
16 MA期間2 = Range(“N2”)
17 最終行 = Worksheets(“データ”).Cells(Rows.Count, 1).End(xlUp).Row
26 For i = MA期間1 + 1 To 最終行
27 Cells(i, 7) = WorksheetFunction.Average(Range(Cells(i – MA期間1 + 1,5),Cells(i, 5)))
28 Next
29 For i = MA期間2 + 1 To 最終行
30 Cells(i, 8) = WorksheetFunction.Average(Range(Cells(i – MA期間2 + 1,5),Cells(i, 5)))
31 Next
移動平均線に使用する変数
「i」→ループ用のカウンタ変数（全プログラムを通して共通）
「MA期間1」、「MA期間2」→移動平均線の1本目、２本目の算出期間を表す変数
「最終行」→CSVよりコピペしたデータの最終行を表す変数（全プログラムを通して共通）
MA期間1 = Range(“N1”)→セルN1より移動平均線（1本目）の計算期間を取得します。
MA期間2 = Range(“N2”)→セルN2より移動平均線（2本目）の計算期間を取得します。
上記のコードは「MA期間1 = 5」と直接コード上で算出期間を指定してもよいのですが、任意の期間を簡単に設定できるようにセルよりデータを取得するようにしています。
※移動平均線はそれぞれ、5日、25日で計算させます。
最終行 = Worksheets(“データ”).Cells(Rows.Count, 1).End(xlUp).Row
→このコードはワークシート（データ）A列の一番下の行より、上に向かってデータが入力された最後の行数を取得することができます。
この最終行を取得するコードですが、VBAにおける必須知識です。
このまま暗記しましょう。
このコードの詳細な解説として
Worksheets(“データ”)
→ワークシートを表すオブジェクト
※今回はシート名をデータとしてますが、実際に使用するシート名を入力します。
Cells(Rows.Count, 1)
→セルを表すオブジェクト
()の中の値がセルの座標を表す。
Cells(行、列)
※Rows.Countとは最大の行数を表します。
End(Direction)
→対象範囲の末端のRange オブジェクトを返します。
※上方向、下方向、右方向、左方向より取得可能です。
.Row
→行を表すプロパティ。
この.Rowがないと「最終行」の変数にオブジェクトを代入することになります。
オブジェクトを直接変数に代入するのと、オブジェクトのプロパティ（値）を代入するのは全く違う意味合いになるので注意しましょう。
移動平均線を作成するプログラム
下記が移動平均線を作成するプログラムです。
26 For i = MA期間1 + 1 To 最終行
27 Cells(i, 7) = WorksheetFunction.Average(Range(Cells(i – MA期間1 + 1,5),Cells(i, 5)))
28 Next
解説
For i = MA期間1 + 1 To 最終行
→MA期間1に「+1」している理由はセル計算期間の修正です。
移動平均線（5日）を計算する場合、終値5日分が必要になりますが、1行目には日付、始値、高値、安値、終値などの見出しが挿入されているため、その分を+1して修正しています。
5日分で計算する場合は、6行目が一番最初の行となります。
最終行は時系列データが入力された最終行になります。
※今回のプログラムではセルN1に5を入力しており、 MA期間1は5となります。
Cells(i, 7) = WorksheetFunction.Average(Range(Cells(i – MA期間1 + 1, 5), Cells(i, 5)))
→移動平均の算出先をＧ列にし、5日間の平均を算出しています。
このコードの詳細な解説として
WorksheetFunction
→Excelの関数を使用するためのコードです。
Average
→Excel関数のAverageを使用し、「平均」を算出できます。
Averageのあとの（）に範囲を指定することでその平均を計算させています。
範囲の指定はRange(Cells(1,1),Cells(5,1))のようにRangeの()内にCells（）を配置することで範囲指定することができます。
※セル範囲の指定は様々な方法があります。上記の方法は変数の使用と相性がいいです。
Range(Cells(i – MA期間1 + 1, 5), Cells(i, 5))
→このコードで範囲指定しています。
仮に「i」の値が6であれば、Cells(i – MA期間1 + 1, 5)はCells(6-5+1,5)となりセルE2となります。
Cells(i,5)はセルE6になります。
つまりRange(セルE2, セルE6)となり、この範囲の5日分のデータで平均を算出します。
また、この+1も「For i = MA期間1 + 1 To 最終行」の+1同様に行数調整するものです。
上記で説明したfor文を移動平均線の1本目、2本目それぞれに用意しています。
※移動平均線（5日）はＧ列、移動平均線（２5日）はH列に算出されます。
売買シグナルの発生と損益計算
次にやるべきことは売買シグナルの発生と損益の算出です。
売買シグナルの発生と損益計算に使用するコード
7 Dim 買いシグナル As Integer
8 Dim 売りシグナル As Integer
9 Dim フラグ As Boolean
10 Dim 買値 As Double
11 Dim 売値 As Double
12 Dim 勝ち数 As Integer
13 Dim 負け数 As Integer
14 Dim 損益 As Double
18 フラグ = False
19 買いシグナル = 0
20 売りシグナル = 0
21 買値 = 0
22 売値 = 0
23 勝ち数 = 0
24 負け数 = 0
25 損益 = 0
32 For i = MA期間2 + 1 To 最終行
33 If フラグ = False And Cells(i, 7) > Cells(i, 8) Then
34 買いシグナル = 買いシグナル + 1
35 フラグ = True
36 Cells(i, 9) = 買いシグナル
37 買値 = Cells(i, 5) * 100
38 End If
39 If フラグ = True And Cells(i, 7) < Cells(i, 8) Then
40 売りシグナル = 売りシグナル + 1
41 フラグ = False
42 Cells(i, 10) = 売りシグナル
43 売値 = Cells(i, 5) * 100
44 Cells(i, 11) = 売値 – 買値
45 買値 = 0
46 売値 = 0
47 End If
48 If Cells(i, 11) > 0 Then
49 勝ち数 = 勝ち数 + 1
50 ElseIf Cells(i, 11) <= 0 And Cells(i, 11) <> “” Then
51 負け数 = 負け数 + 1
52 End If
53 損益 = 損益 + Cells(i, 11)
54 Next
売買シグナルの発生と損益計算に使用する変数
「フラグ」 →買っている場合とそうでない場合を識別するための変数です。
フラグ=Trueなら買っている状態、フラグ=Falseなら買っていない状態を表します。
「買いシグナル」 →買い場を表す変数です。買い場が発生するたびに+1しカウントする役目もあります。
「売りシグナル」 →売り場を表す変数です。売り場が発生するたびに+1しカウントする役目もあります。
「買値」→買いの価格を表す変数です。
「売値」 →売りの価格を表す変数です。
「勝ち数」→勝ちの回数を表す変数です。
「損益」 →損益の合計を表す変数です。
初期値の設定として、フラグのみ買っていない状態を表すFalseをセットします。
その他の変数は全て0をセットします。
買いシグナルを発生させるプログラム
ループ内の最初のIf文が買いシグナルを作成するプログラムです。
※ループを「For i = MA期間2 + 1 To 最終行」としている理由
→ゴールデンクロス、デッドクロスには2本の移動平均線が必要になり、2本目のデータがまだ算出されていない期間をループさせても意味がないためです。
そのため、ループ開始は26行目からとなります。
33 If フラグ = False And Cells(i, 7) > Cells(i, 8) Then
34 買いシグナル = 買いシグナル + 1
35 フラグ = True
36 Cells(i, 9) = 買いシグナル
37 買値 = Cells(i, 5) * 100
38 End If
解説
If フラグ = False And Cells(i, 7) > Cells(i, 8) Then
→このコードで買いシグナルを発生させています。
日本語にすると、
「もしフラグが買っていない状態（False)かつ、移動平均線（1本目)が移動平均線（2本目)より高いなら」
となります。
If文内のコードは条件を満たした場合、「買いシグナル」は＋１され「フラグ」はTrueになります。
ここで「フラグ」をTrueの買っている状態にすることで、売りシグナルが出るまで複数の買いシグナルが発生するのを防ぎます。
また、カウントした買いシグナルをI列のセルに入力しています。
これは後で見たときにどこで何回目の買いが入ったかを分かりすくするためです。
最後に「買値」の変数に当日の終値の価格をセットしています。
「×100」している理由は最低単元の100株分を終値に掛けるためです。
売りシグナルを発生させるプログラム
ループ内の2番目のIf文が売りシグナルを作成するプログラムです。
39 If フラグ = True And Cells(i, 7) < Cells(i, 8) Then
40 売りシグナル = 売りシグナル + 1
41 フラグ = False
42 Cells(i, 10) = 売りシグナル
43 売値 = Cells(i, 5) * 100
44 Cells(i, 11) = 売値 – 買値
45 買値 = 0
46 売値 = 0
47 End If
解説
買いとは真逆のプログラムになります。
売りの場合にのみ追記しているコードは以下のようになります。
Cells(i, 11) = 売値 – 買値
→損益を計算しＫ列に入力します。
買値 = 0
売値 = 0
→買値と売値を0にリセットします。
勝ち負けを集計するプログラム
最後のIf文で勝ち負けを集計しています。
48 If Cells(i, 11) > 0 Then
49 勝ち数 = 勝ち数 + 1
50 ElseIf Cells(i, 11) <= 0 And Cells(i, 11) <> “” Then
51 負け数 = 負け数 + 1
52 End If
53 損益 = 損益 + Cells(i, 11)
解説
「売りシグナルを発生させるプログラム」でK列に入力した損益をもとに、損益がプラスなら「勝ち数」を+1し、0かマイナスであれば「負け数」を+1しています。
最後に「損益」の変数にK列の損益をプラスしています。
結果集計
一番最後に結果を集計しています。
55 Range(“N3”) = 買いシグナル
56 Range(“N4”) = 勝ち数
57 Range(“N5”) = 負け数
58 Range(“N6”) = 勝ち数 / 買いシグナル
59 Range(“N7”) = 損益
上記のコードで「売買回数」、「勝ち数」、「負け数」、「勝率」、「損益合計」を算出しています。
まとめ
「Excelでバックテストする方法。【ＶＢＡによるツール開発】」のまとめです。
→「ＶＢＡでツールを開発する方法」と「関数を使う方法」がある。
・Excelでバックテストするメリット
→低コストでバックテスト環境を作れる。
→拡張性が高い。
→自動売買に発展させることができる。
VBAによるプログラミングは慣れるまでは結構大変です。
しかし、一度プログラムを作ってしまえば、後は自由自在にカスタマイズできます。
また、一旦作ったプログラムをコピペして転用したり、汎用性を重視して作ることで、最終的には売買ルール部分のプログラミングだけすればいいという状況も作りだせます。
そのため、苦労するのは最初だけであり、あとはバックテストに集中できる環境になります。