Excelでテクニカル計算

テクニカル指標を使うにあたって最も大事なことはそのテクニカル指標が有用であるかということです。
テクニカル指標を投資判断の材料に使うなら、その指標がその銘柄にあっているかどうかテストすることはとても重要なことです。市販のソフトでもテクニカル指標の有効性をテストできるものがありますが、内部の処理方法までわかるソフトは少ないです。Excelでも関数を使えば市販のソフトと遜色ないバックテストができますし、自分でテストの内容を構築するのでどのような処理が行われているか疑う必要がありません。ここではバックテストについて解説します。

テクニカル指標の有効性を確かめるためにはいろいろな条件の相場(上げ相場、下げ相場、保合相場)があるとよいので、元となる4本値のデータはかなり長い期間のものを用意したほうがよいです。
ここでは2000/1/4〜2010/7/31までの日経平均のデータを使用します。


まず、売買戦略ですが一番簡単な単純移動平均上抜けで買建、下抜けで決済というシステムでバックテストの構築を試みてみます。
買建判断に使う単純移動平均と決済判断に使う単純移動平均は別の移動平均を使うということにして、2本の移動平均を使うことにします。

			条件:	終値が移動平均1を上抜けた次の日の始値で買建
				終値が移動平均2を下抜けた次の日の始値で決済

とします。次の日の始値を使っている理由は、移動平均は当日終値が決まらないと確定しないからです。

				
			設計:	移動平均を2本使うので2列必要
				上抜け、下抜けを判断するのでそれぞれ1列ずつ、計2列必要
				買建の価格を記録する場所に1列必要
				決済の価格を記録する場所に1列必要
				損益を計算するのに1列必要
				累積損益を計算するのに1列必要

と考えていくと最低8列ほど使うことになります。これ以外にも月別損益の計算、ロスカットの判定などを考えていくとさらに列が必要になりますが、まずはシステムを構築しなければ何も始まらないので上記の8列を作っていきます。

では作りやすいところから作っていきましょう。

上の画像のように配置してみます。

			F列:買建判定に使う移動平均
			G列:上抜け判定
			H列:買建価格の保持
			I列:決済判定に使う移動平均
			J列:下抜け判定
			K列:決済価格の保持
			L列:損益計算
			M列:累積損益

F列、I列は単純移動平均を計算するだけなので、単純移動平均の式をセルに入力します。F5セル、I6セルは移動平均の日数を指定するセルとします。計算式がわからない人は単純移動平均のページに行って調べてください。

G列はF列と終値を使って上抜けの判定をします。上抜けしたら1、そうでない場合は0が代入されるようにします。 終値が移動平均を上抜けるということは、前日が終値＜移動平均で当日が終値＞移動平均ということになります。 今回の場合、上抜けの次の日の始値で買建するという条件ですから、判定日は買建日の前日が終値＞移動平均、前々日が終値＜移動平均ということになります。G12セルに上記の条件で式を作ると
となります。前日が終値＞移動平均はE11>F11、前々日が終値＜移動平均はE10<F10で、この両方の条件を満たさなければならないのでAND(E11>F11,E10<F10)となります。これで上抜け判定ができるようになりました。
ただ、このままだと前々日や前日の移動平均のセルが空白だった場合に0が代入されてしまうので、それを防ぐために前々日の移動平均が空白だった場合には空白を代入するという判定を付け加えて とします。
J列の下抜け判定もG列の上抜け判断と同じ形で式ができます。下抜け判定の場合、条件が買建日の前日が終値＜移動平均、前々日が終値＞移動平均となり、条件に使う移動平均の列がI列になるので式は となります。

次は買建価格を保持するH列を作ります。
ここは少々複雑です。
上抜け判定のG列で1が出たらその行の始値を代入する。
下抜け判定のJ列で1が出たら空白を代入する。
というものが基本動作です。
G列が1の場合はもう一つ条件が加わって、すでに買建ていた場合は判定を無視するというものが入ります
上記を式にすると
となります。
前半部分
は買建判定で1が出た時の処理です。
の部分が、すでに買建てしているかどうかの判定部分です。買建済みの場合はH11に値が入っており、買建していない場合は空白になっているはずなのでH11が空白かどうかで買建済みかどうかの判断をします。
空白なら始値(B12)を代入、空白でない場合は前の行の値(H11)を代入ということです。
後半部分
は買建判定が0の時の処理です。
は前日の判定で、前日が空白なら空白を入れろという意味です。
は決済判定で1が出た時の処理で決済判定が出た場合は空白を、そうでない場合は前の行の値(H11)を代入ということです。
これで買建価格を保持できるようになりました。

決済価格を保持するK列を作ります。
判定はJ列で行っているのでJ列で1が代入された行で始値が代入されるようにします。J列が0の場合は空白が代入されるようにします。
K12セルを例に取れば
となります。これで決済価格の保持ができるようになりました。

買建価格と決済価格が保持できるようになったのでL列の損益計算部分を作ります。
損益は当日の決済価格が保持されており、前日の買建価格が保持されている場合に計算するようにします。
式は
となります。
当日の決済価格が保持されているということは、セルの値が数字ということなのでそれを判定するために ISNUMBER関数を使います。この関数はセルの値が数字のとき真を返し、そうでないとき偽を返します。
はK12セルとH11セルの両方が数字かどうかを判定します。
両方数字の場合は決済が発生しK12-H11が代入されます。
決済が発生しない場合はセルを空白にしたいので""とします。

M列の累積損益は前日の累積損益とL列の損益を合算すればよいので
とします。
F12セル〜M12セルをコピーして、F8行目から4本値のデータの最後の行までペーストすれば最低限のバックテストができるようになります。

これで、バックテストの部分はできましたが、変数を動かした時の損益の変化が分かりにくいので損益の変化の可視化を考えます。
まず、累積損益がすぐに分かるように累積損益を集計するセルを作ります。 K2セルに
と式を入力します。これはL列の合計を計算するという意味です。
これで累積損益が計算できるようになりました。

実際に移動平均の数値を動かしてみると分かりますが、累積損益の数値だけでは損益がどのように積み上がっているかわからないので、累積損益のグラフをつくってみようと思います。
一番簡単なのは累積損益が計算されているM列のデータがある部分を選択し、グラフウィザードで折れ線グラフを作れば累積損益の推移が分かります。 グラフを作ってみるとこんな感じになります。日経平均の終値も同グラフに書いてみれば、日経平均の推移と累積損益の推移が比較できるのでテクニカル指標が有効な局面が一目瞭然になります。
このグラフの累積損益が右肩上がりになる数値の組み合わせを探すことがバックテストの目的です。
これでバックテストの準備が整いました。

今回作ったファイルをダウンロードできるようにしたのでご自由に活用してください。
Excelファイルがダウンロードできない場合はリンクを右クリックして「対象をファイルに保存」を選択して保存すればダウンロードできます。

バックテストの実行と改良に進む

今回使用した関数

---

---

津島式システムトレード FXで自動的に儲かる仕組み 税込 2160円	システムトレードの達人 入会金＋初回月額 税込 21168円	DVD 為替の戦略セミナー「システムトレードの基礎」 税込 4104円	DVD システムトレード、アイデアと実践 税込 5184円
株システムトレードへの招待状 日経225先物データで18年間連勝法などCD-R付 税込 30240円	DVD 短期システムトレード 税込 4104円	DVD FXシステムトレードのための統計学 勝ち続けるシステムを客観的に導き出す方法 税込 4104円	DVD 225先物とTOPIX先物の実践取引 初心者のためのシステムトレード入門 税込 5184円

---