IT系おじさんのチラシの裏
2018年10月～
当サイトの記事にはアフィリエイト広告のリンクが含まれる場合があります

投資のバックテストはExcelでも簡単にできる（VBAマクロの知識も不要）

お金の話
0

と3回に渡って複利効果や長期運用のメリット、短期売買の危うさについて書いてきました。

今回は前回の最後に書いた「テクニカル的に売られすぎラインでエントリーして、買われすぎラインでエグジットする、を繰り返したらどうなるの？」というのを実際に過去データを使ってシミュレーションする方法について話したいと思います。

要するにバックテストですね。

株価指標のCSVダウンロードはYahoo Financeが便利

前回も少し触れましたが、NYダウ、S&P500、NQ100、日本225等、過去の株価データを取得するならYahoo Financeが便利です。

Yahoo Financeで株価指数の[Historical Data]を選ぶと下記のようなページが開きます。
Yahoo Financeで株価CSVをダウンロード

そのページにある[Time Period]で、例えば[1/1/2018]～[12/31/2018]と入力し、[Apply]をクリックすると2018年の株価一覧が表示されます。そのまま[Download Data]をクリックすることでCSVファイルもDLできちゃう。便利！

ダウンロードしたCSVから休業日を取り除く

昔DLしたときには休業日は空だったような気がするのですが、今DLしてみたら、休業日にはnullが入っています。

このままだと邪魔なので、nullが入っている行は消しちゃいましょう。

手順は以下のとおり。

  1. ExcelでCSVを開く
  2. メニューの[データ]から[フィルター]をクリック
  3. 1行目にプルダウン「▼」が表示されるので、[Open]～[Close]のどれでも良いから「▼」をクリック
  4. すると、下記のような画面が開くので、検索欄に「null」と入力して[OK]をクリック Excelのフィルター
  5. 下記のようにnullの行だけ表示されるため、2行目以降を選択して行削除 Excelでフィルターした後
  6. 最後にリボンメニューの[フィルター]をクリックして元の表示に戻す

文字で書くとややこしそうですが、「null行だけ表示して削除する」という至極単純なことをしているだけです。

移動平均の求め方

今回は単純に移動平均乖離率で買いと売りを繰り返すシミュレーションをしたいと思いますので、まずは移動平均を計算します。

移動平均ってものすごく単純なわりに、いや単純だからこそか、世の中でもっとも使われているテクニカル指標だと思います。ほらゴールデンクロスとかデッドクロスとか投資の分野に興味のない人ですら聞いたことくらいあったりしますし。

あらためて説明するまでもないかもですが、5日移動平均なら、「過去5日間の終値の平均」、20日移動平均なら「過去20日間の終値の平均」です。もっと言うなら、過去20日間の終値をぜんぶ足して20で割るだけで移動平均を求めることができます。単純でしょ。

ありがたいことに、ExcelにはAVERAGEという「指定したセルの平均値を算出する」関数が存在するので、さっきのCSVを開いたままなら、=AVERAGE(E2:E21)と入力するだけで移動平均が算出できます。

こんな感じ。
Excelで移動平均を計算する

あとは最後の行まで選択して貼り付けるだけで2018年分の20日移動平均の算出は完了です。
Excelで移動平均を計算する

びっくりするほど簡単でしょ。

自分でExcelを使ってこういうデータを作ることで、テクニカル分析って大層な名前が付いているけど実は単純だし終値しか見ていないということが理解できるのは大きなメリットだと思います。RSIやストキャスティクスについては今回の記事では扱いませんが、ググればすぐに計算式が出てくるし、すごく簡単だってのもわかりますよ。

あと、これはワリとどうでも良いかもですが、2018年の営業日数は246日なんだなーとわかることも個人的にはメリットです。自分のトレード記録を付けているので、あとで読みなおして反省するときに1日あたりの平均利益とか、ちゃんと計算するために営業日数って大事なんですよね。まぁそれだけですけど。

移動平均乖離率の求め方

イドウヘイキンカイリリツ、なんて硬そうで難しそうな名前が付いてますけど、その日の終値が移動平均と比べてどのくらい離れているのかを割合で求めるだけのことです。要するに「（終値－移動平均）÷移動平均」。

下の例では移動平均がH列、終値がE列なので「=(E21-H21)/H21」と入力しています。 Excelで移動平均乖離率

そのままだと-0.00945644とか表示されて見づらいため、[セルの書式設定]で[パーセンテージ]を選び、小数点以下の桁数は2桁とかに設定しておくと見やすいでしょう。そうしたら、移動平均のときと同じように、乖離率も最後の行まで選択してコピペするだけ。 Excelで移動平均乖離率

これだけでもう移動平均と移動平均乖離率は準備できちゃいました。

買い条件と売り条件の判断

ここでは仮に下記のような売買条件としましょうか。

  • 移動平均よりも2％下落したら買い
  • 移動平均よりも2％上昇したら売り

こんなときExcelではIF関数を使います。

乖離率の横あたりに「買条件」という列を追加して、「=IF(I21<-0.02, 1, "")」と入力してみましょうか。I21、つまり乖離率を計算したセルですね、これが-0.02以下だったら、1を表示する、そうじゃなければ空にする、という意味です。

「売条件」の列ではその逆に「=IF(I21>0.02, 1, "")」とでも入力すれば、乖離率が2％を超えた日だけ1と表示するようにできますね。こんな感じ。

Excelで売買条件の判定

どうでしょう。既にシミュレーション出来ている感じがしませんか。

たったこれだけでも移動平均乖離率が-2％を下回る日が連続して5日とかあるんだなぁ、とか、そこから+2％に転じるまでに最大で18回は買い足さないといけないなぁ、とか色々参考になるはずです。これだけでも資金を20分割してエントリーする理由付けになりますよね。

まとめ

今回のシミュレーションで使ったのはAVERAGE関数とIF関数だけです。それだけでも簡単なバックテストができるということがわかって頂けたんじゃないかなと思います。

より本格的に

  • 移動平均の日数を自由に変更したい
  • 買いと売りの乖離率をそれぞれ個別に設定したい
  • 最大で必要な資金を算出したい
  • そのときの損益合計と損益率を知りたい
とか、いろいろ工夫したくなると思いますが、それらも買条件が1だったら○○みたいなIF関数を組み合わせることで簡単………かどうかは人によるとして、少なくともプログラミングの知識がなくとも実現可能です。

こんなふうに。

Excelで日経平均のバックテスト

実際にはご自身で計算したほうが理解が深まるので良いのですが、面倒くさがりさん、あるいはとりあえず先に結果を見たい方のためにExcelシートも用意しておきました。

https://blog.ver001.com/uploads/20190112_nikkei225_backtest.xlsx

移動平均日数、買）乖離率、売）乖離率の3つのパラメーターを変更することで、必要資金や利益率が変わるので、いろいろ試してみると面白いかも知れません。

ちなみに、以前の記事で書いたとおり、自分用のバックテストは恐怖指数(VIX)やストキャス／RSI等の複数の指標を参考に売買するルールですし、損切りのシミュレーションもするように組んでいるため、ちょっと記事で紹介できる分量ではございません。

なので簡略化した（それでも十分役立つと信じている）Excelの計算式だけでバックテストをする方法の紹介でしたが、いやはや、これだけでもだいぶ長い記事になっちゃいましたね。

ここまでおつきあい頂きありがとうございました m(_ _)m

関連記事

コメント

新しいコメントを投稿する

[新規投稿]
 
TOP