システムトレードのためのエクセル講座
第一回
ヒゲ逆張りシステム
今回は早速、売買検証をやってみたいと思います。30分くらいでできると思いますので頑張りましょう。
ロジック 一定以上の髭が出現したら逆張り方向でエントリー、一定の期間保有したら決済。
簡易コード表
1, G10に =IF(AND(F10>C10,D10-F10>0.4),1,"") と打ち込む。
2, H10に IF(AND(F10＜C10,F10-E10＞0.4),1,"")と打ち込む。
3, I10に =IF(G10=1,C11-C12,0)と記述。
4, J10に =IF(H10=1,C12-C11,0)と記述。
5, K10に =I10+J10+K9と打ち込む。
6,G10を選択し、枠の右下にカーソルを合わせる。
7,黒の十字カーソルになっている状態でダブルクリック。
8,以下H10I10J10K10にも6,7,の手順を行う。
ステップ1 データ取得
売買検証をするためにはまず時系列データを取得する必要があります。
時系列データの取得方法は次回の講義に説明いたしますので、今回はこちらを右クリックで名前をつけてリンク先を保存を選びDLしてください。
ポンド円の1時間足の2008年4月７日2009年1月30日までの時系列データが入っています。
本来はエクセルファイルをアップロードしたかったのですが、FC2ブログではファイルの形式上アップロードできませんでした。
ですのでこのデータをエクセルにコピーして頂く必要があります。
まずDLしたテキストファイルを開いていただいたら
①Ctrl+Aを押す。 ファイルの内容が全て選択されます。
②Ctrl+Cを押してコピー指定をする。
③自身のエクセルを開きA9を指定してCtrl＋Vで貼り付ける。
Excel1.png
以上でデータの取得は終了になります。
ステップ２ エントリー時のサインを出す。
エントリー条件は以下の二つです。
①ローソク足が陽線で一定幅以上の上ヒゲがでる。
②ローソク足が陰線で一定幅以上の下ヒゲがでる。
逆に言うと
・ローソク足が陰線で一定幅以上の上ヒゲがでた時
・ローソク足が陽線で一定幅以上の下ヒゲがでた時
では逆張りでなくなってしまうのでエントリーしません。
まずは①ローソク足が陽線で一定幅以上の上ヒゲがでる。から見ていきましょう。
エントリーサイン記述方法
1, G10を選択
2,=IF(AND(F10>C10,D10-F10>0.4),1,"")と打ち込む。
３,G10を選択し、枠の右下にカーソルを合わせる。白の十字カーソルから黒の十字カーソル変化する
場所です。下の図をご覧ください。
4,黒の十字カーソルになっている状態でダブルクリック。
Excel2.png
解説
2,=IF(AND(F10>C10,D10-F10>0.4),1,"")
関数を説明した後で取り上げます。
4,黒の十時カーソルになっている状態でダブルクリック。
これを行うことで左隣にあるセルに対応した数だけ、相対的にコピーを実行してくれます。
相対的というのは、下の図を見て頂きたいのですが、例えばG11を見て頂くと、F11は青に、E１１は紫に、C11は緑に囲われていますね。
その色で囲われている情報は、値としてエクセルに記憶されているのではなく、相対的なセルの位置として記憶されるわけです。
F11>C11というのは、F11の方がC11よりも大きいというよりも、
現在選択されているセル（G11)から一つ左の値（F11)の方が選択されているセル（G11)の4つ左の値（C11)よりも大きい
というようにエクセルは処理していると言えます。
このお陰で、コピーしていくとG１１と同じ処理をどの場所でも行ってくれるという非常に便利なことができるのです。EXcel3.png
____________________________________________________________________________________________
関数解説
今回使用したエクセル関数 IF AND について解説したいと思います。
IF
記述例 =IF(論理式,[真の場合],[偽の場合]）
IFの後に括弧でくくり、その後コンマを2回使うことで式を三つにわけます。その三つの式は以下のようになります。
１,論理式。 条件式の役割を果たす。
2,[真の場合] 論理式の条件が満たされていたら実行する式
3,[偽の場合] 論理式の条件が満たされていない時に実行する式。
メモ:IFの論理式内でのみ使用する関数に AND OR NOT の三つがあります。
具体例
A＝論理式 B＝真の場合 C=偽の場合
=IF(A,B,C)
もし論理式Aに当てはまればBの式を実行し、当てはまらなければCの式を実行します。
AND
記述例 =AND(論理式1,[論理式2],[論理式3],・・・）
ANDの後に括弧でくくり、その中でコンマで式を区切っていくことによってIF関数の論理式をいくつでも増やすことができます。IFの論理式の中でのみ使用し、ANDの括弧内に入っている全て論理式を満たしたときのみ[真の場合]の式を実行し、一つでも満たしていなければ[偽の場合]の式を実行します。
具体例
A＝論理式 A'=論理式２ B＝真の場合 C=偽の場合
=IF(AND(A,A')B,C)
もし論理式Aと論理式A'両方を満たせばBの式を実行し、そうでなければCの式を実行します。
____________________________________________________________________________________________
以上のことをおさえて頂いたところでもう一度2,=IF(AND(F10>C10,D10-F10>0.4),1,"")をみてみましょう。
=IF(AND(F10＞C10,D10-F10＞0.4),1,"")
=IF（AND( A , A' ),B ,C)
メモ:Aは論理式 A'は論理式２ Bは真の場合 Cは偽の場合
A F10＞C10 終値のほうが始値よりも大きい→ローソク足は陽線
A' D10-F10＞0.4 高値と終値の差が0.4円以上→上ヒゲの長さは0.4円以上。
B 1 エントリーサインは１とする
C "" 空欄を入力します。
ローソク足が陽線かつ高値と終値の差が0.4円以上ならエントリーサイン１を入力し、そうでないなら空欄を入力します。
メモ:""は文字を入力する際に使用する鍵括弧みたいなものです。"エントリーサインはでませんでし
た"とすることもできます。 今回は何も""の中にいれていないので空欄が入力されることになり
ます。
次に②ローソク足が陰線で一定幅以上の下ヒゲがでる。エントリーサインを記述しましょう。
エントリーサイン記述方法
1, H10を選択
2,IF(AND(F10＜C10,F10-E10＞0.4),1,"")と打ち込む。
３,G10を選択し、枠の右下にカーソルを合わせる。
4,黒の十字カーソルになっている状態でダブルクリック。
解説
先ほどと変わった場所はF10＜C10と、F10-E1の2ヶ所です。
=IF(AND(F10＜C10,F10-E10＞0.4),1,"")
=IF（AND( A , A' ),B ,C)
A F10＞C10 終値のほうが始値よりも小さい→ローソク足は陰線。
A' F10-E10＞0.4 終値と安値の差が0.4円以上→下ヒゲの長さは0.4円以上。
B 1 エントリーサインは１とする。
C "" 空欄を入力します。
ステップ３ イグジッドのサインを出す
ロジック: エントリーサインがでた次の始値でエントリーし、エントリーした次の始値で決済。
ケース１:エントリー条件①の時のイグジッド（売り手仕舞い）
ケース2:エントリー条件②の時のイグジッド（買い手仕舞い）
ケース１からみていきましょう。
イグジッドサイン記述方法
1, I10を選択
2,=IF(G10=1,C11-C12,0)と記述。
3,I10を選択し、枠の右下にカーソルを合わせる。
4,黒の十字カーソルになっている状態でダブルクリック。
解説
=IF(G10=1,C11-C12,0)
=IF( A ,B ,C)
メモ:Aは論理式 Bは真の場合 Cは偽の場合
A G10=1 ローソク足が陽線で一定幅以上の上ヒゲがでた時のエントリーサインを確認
B C11-C12 次の始値（C11）でエントリーし、またその次（1時間後）の始値（C12）で決済。逆張り
なので売りからはいります。ですのでエントリー（C11)-一時間後の始値（C12）にな
ります。
C ０ エントリーサインが出ていないのなら損益は発生しないので０を入力。
次にケース２です。
イグジッドサイン記述方法
1, J10を選択
2,=IF(H10=1,C12-C11,0)と記述。
3,I10を選択し、枠の右下にカーソルを合わせる。
4,黒の十字カーソルになっている状態でダブルクリック。
解説
=IF(H10=1,C12-C11,0)
=IF( A ,B ,C)
メモ:Aは論理式 Bは真の場合 Cは偽の場合
A H10=1 ローソク足が陰線で一定幅以上の下ヒゲがでた時のエントリーサインを確認。
B C12-C11 次の始値（C1１）でエントリーし、またその次の始値（C12）で決済。今度は買いから
入るので、一時間後の価格が上の時に利益が入るわけです。ですのでC12-C11に
なります。
C ０ エントリーサインが出ていないのなら損益は発生しないので０を入力。
ステップ４ 損益を計算
お疲れ様でした。ここでついに、苦労が報われるときがきました！損益を計算しましょう。
損益計算記述方法
1, K10を選択。
2,=I10+J10+K9と打ち込む。
３,K10を選択し、枠の右下にカーソルを合わせる。
4,黒の十字カーソルになっている状態でダブルクリック。
解説
=I10+J10+K9
I10 エントリー条件①の損益
J10 エントリー条件②の損益
K９ 累計損益を保持
ステップ５ グラフ作成。
Kの列はそのまま損益曲線になります。いよいよグラフにして結果を見てみましょう。
1,K10を選択。
２,Cｔｒｌ+SHIFT+↓を押す。
3,挿入タブのグラフ、折れ線を選択（左上のもので良いと思います）
4,作成したグラフを右クリック。切り取りを選択
5,Sheet３を選択
6,好きな場所で右クリック貼り付けを選択。
EXcel7.png
結果はなんと、綺麗な右肩下がり！
ステップ６ 調整
綺麗な右肩下がり→反対で売買すれば綺麗な右肩上がりになる。
ということで、逆張りではなくて、順張りでやってみましょう。反対売買にすればいいだけなので以下の手順になります。
1, I10を選択
2,=IF(G10=1,C11-C12,0)→=IF(G10=1,C12-C11,0)に変更。
3,I10を選択し、枠の右下にカーソルを合わせる。
4,黒の十字カーソルになっている状態でダブルクリック。
5, J11を選択
6,=IF(H10=1,C12-C11,0)→=IF(H10=1,C11-C12,0)と記述。
7,I10を選択し、枠の右下にカーソルを合わせる。
8,黒の十字カーソルになっている状態でダブルクリック。
9,K10を選択し、枠の右下にカーソルを合わせる。
10,,黒の十字カーソルになっている状態でダブルクリック。
グラフを見てください。きれいな右肩上がりの直線になりましたね。
EXcel8.png
結論。ヒゲが出た時の逆張りは危険。一般的な常識とは反対の順張りの方が利益が出る。
一般論はあてにならない。自分で検証してみることが大事。
ーーーーーーーー
システムトレードのためのエクセル講座
第２回
価格データの取得と多市場間テスト
データの取得方法
今回は価格データの取得を書こうと思ったのですが、ODLさんのサイトで丁寧に書かれているところがありましたのでこちらをごらんください。
1. ODL MetaTrader 4の基本
∟メタトレーダーのインストール方法
でメタトレーダーをインストールして頂いて、
6. ツールメニュー
∟ヒストリーセンター
を参考になさって時系列データを取得してください。
メタトレーダーを使えば1分足、5分足、15分足、30分足、1時間足、4時間足、日足、週足、月足の時系列データがそれぞれDLできるので大変便利です。
時系列データを一気にDLして一つのフォルダにまとめておくと便利です。
多市場間テスト
前回作成したポンド円15分足ヒゲ逆張りシステムを調整した
ポンド円15分足ヒゲ順張りシステムが他の市場ではどのように作用するか検証してみましょう。
今回は他市場として、ユーロ円とドル円を見ていきたいと思います。
もしユーロ円、ドル円でも良い成績がでれば、このロジックは堅固なシステムであると言えます。
それでは早速見ていきましょう。
前回作ったエクセルシートをお開きください。
よろしいでしょうか。
メモ:ポンド円ではヒゲとみなす終値と高値若しくは安値との乖離幅を0.4円としましたが、ユーロ円、ドル円ではボラティリティが違う為ユーロ円は0.3円をヒゲとみなす乖離幅に、ドル円では0.2円をヒゲとみなす乖離幅に設定します。
ユーロ円
1.ヒストリーセンターでEURJPYの1時間足の時系列データを取得する。
2.ご自分の好きな期間コピーする。
3.前回作ったエクセルシートのC10を選択。
セル指定
4.2のコピーを貼り付ける。
5.G10の =IF(AND(F10>C10,D10-F10>0.4),1,"")0.4を0.3に変更。
6.変更したらG10の右下をダブルクリックして、相対的コピーを下まで実行。
以上のようにすると以下のようなグラフになります（ここのデータは2008年4月7日～2009年1月30日までの1時間足5000本のデータです。）
ユーロ円
前半が気になりますが一応右肩上がりと考えてよさそうです。
1.ヒストリーセンターでUSDJPYの1時間足の時系列データを取得する。
2.ご自分の好きな期間コピーする。
3.前回作ったエクセルシートのC10を選択。
4.2のコピーを貼り付ける。
5.G10の =IF(AND(F10>C10,D10-F10>0.4),1,"")0.4を0.2に変更。
6.変更したらG10の右下をダブルクリックして、相対的コピーを下まで実行。
以上のようにすると以下のようなグラフになります（ここのデータは2008年4月7日～2009年1月30日までの1時間足5000本のデータです。）
何とも言えない形ですが最終的に利益は得られているようです。
結論
ヒゲ順張りシステムはなかなか機能するようです。
ーーーーーーーー
システムトレーダーのためのエクセル講座
第３回
モデルの評価
前回はパラメーターの変更と最適化をとりあげるとかいたのですが、その前にモデルの評価を記述することの方が先だと気が付きましたので、今回はモデルの評価をとりあげます。パラメーターの変更と最適化は次回にとりあげます。失礼いたしました。
今回モデルの評価で取り上げるのは以下の８つです。隣には評価を算出するのに必要な関数を添えました。
最大損益 MAX
平均勝ちPips AVERAGEIF
平均負けPips AVERAGEIF
取引回数
勝ち回数 COUNTIF
負け回数 COUNTIF
PF ABS
勝率
累計損益
まずは今回初めて出てきた関数から説明し、
その後上から順に記述する方法を取り上げていきます。
____________________________________________________________________________________________
簡易コード表
1. L11に =IF(K10＞L10,K10,L10) と記述
2. 最終行までコピー（コピーの仕方は第１回のエクセル講座をご覧ください）
3. M11に =L11-K11 と記述
4. 最終行までコピー
5. A1に最大損失と記述
6, A2に平均勝ちPipsと記述
7, A3に平均負けPipsと記述
8, A4に取引回数と記述
9, A5に勝ち回数と記述
10, A6に負け回数と記述
11, A7に勝率と記述
12, A８にPFと記述
13, A9に累積損益と記述
14. B1に=MAX(M10:M5001)と記述
15, B2に =AVERAGEIF(I11:J5001,"＞0")と記述
16, B3に =AVERAGEIF(I11:J5001,"＜0")と記述
17, B5に =COUNTIF(I11:J5001,"＞0")と記述
18, B6に =COUNTIF(I11:J5001,"＜0")と記述
19, B4に =B5+B6と記述
20, B7に =B5/B4と記述
21, B８に =（B2*B5)/ABS(B3*B6)と記述
22, B9に =B2*B5+B3*B6と記述
____________________________________________________________________________________________
関数解説
今回取り上げるのはMAX,AVERAGEIF,COUNTIF,ABSです。説明することはあまりないのですぐに終わります。
MAX
指定した範囲の最大値を表示します。
記述式: =MAX(範囲）
具体例:=MAX(A3:B19)
A3からB19までのセルで最も大きい値を表示します。
メモ:範囲を指定する際には : を使用します。=MAX(始点 : 終点）といった感じです。 =MAX(A3,B19)で
したらA3とB19のうちどちらが大きいかということにしかなりませんので注意してください。
AVERAGEIF
指定した条件に当てはまる範囲の平均値を表示します。
記述式: =AVERAGEIF(範囲,"条件")
具体例: =AVERAGEIF(A3:B19,"＞0")
A3からB19までのセルの中で、数値が０以上であるものの平均を算出します。範囲を指定したらコンマで区切ると条件に移行します。 条件はダブルクオーテーションマーク(")で囲む必要があることに注意してください。
COUNTIF
指定した範囲の中で条件に当てはまるセルの個数を表示します。
記述式: =COUNTIF(範囲,"条件")
具体例: =COUNTIF(A3:B19,"＞0")
A3からB19までのセルの中で、数値が０以上であるものの個数を数えます。範囲を指定したらコンマで区切ると条件に移行します。 条件はダブルクオーテーションマーク(")で囲む必要があることに注意してください。
ABS
ABSに続く括弧内の数値、もしくは式の解の絶対値を表示します。
記述式: =ABS（数値)
具体例: =ABS(E10-F10)
E10=202.35 F10=202.65 の場合 ABS（E10-F10）=0.3
E10=202.35 F10=202.05 の場合 ABS（E10-F10）=0.3
______________________________________________________________________________________
関数解説は以上です。それではモデル評価の記述方法にまいりましょう。
最大損失
概要
モデルを評価する上で大変重要になってくるのがこの最大損失です。
最大損失とは一時的に最大資産から落ち込んだ所の損失額で最も大きいものを表します。
簡単に言うとそのシステムを最悪の時期に使用した時に生じる損失はいくらかということです。
僕はそんなに資産が無いので最大ドローダウンよりも最大損失で計算しています。わかりやすいので。
システムを使用する際に、最大損益の３倍の資産は欲しいところです（理想ですが）。
システムを使用する際に、自分の資産が最大損益が発生しても２０%程の損失でしか無い程のロット数で賭けるのが資金管理的にはちょうど良いのではないかと思います。
記述式
1.L11に =IF(K10＞L10,K10,L10) と記述
2.最終行までコピー（コピーの仕方は第１回のエクセル講座をご覧ください）
3.M11に =L11-K11 と記述
４.最終行までコピー
５.A1に最大損失と記述
6.B1に=MAX(M10:M5001)と記述
解説
1,最大損失を求めるためにはピークを求めなければなりません。累計損益（K１０）とピーク（L10）を比べ、累計損益が今までのピークを上回れば累計損益の値を表示し、上回らなければピークをそのまま表示します。
３,上記で求めたピークから累計損益を引いたものがピークからの損失額になります。
6,３で求めた損失額の最大値を求めます。
平均勝ちPips
概要
勝ちトレードの時は平均で何Pipsとっているのかを算出します。システムを使っている時の目安になると思います。
記述式
1,A2に平均勝ちPipsと記述
2,B2に =AVERAGEIF(I11:J5001,"＞0")と記述
解説
個別損益（I11:J5001)のなかで０以上の値のもの（＝勝ちトレード）を抜き出しその平均値を算出します。
平均負けPips
概要
負けトレードの時は平均で何Pips負けているのかを算出します。システムを使っている時の目安になると思います。
記述式
1,A3に平均負けPipsと記述
2,B3に =AVERAGEIF(I11:J5001,"＜0")と記述
解説
個別損益（I11:J5001)のなかで０以下の値のもの（＝負けトレード）を抜き出しその平均値を算出します。
勝ち回数
概要
取引回数、勝率をだすための準備だと思ってください。売買回数よりもさきにこちらから解説します。
記述式
1,A5に勝ち回数と記述
2,B5に =COUNTIF(I11:J5001,"＞0")と記述
解説
個別損益（I11:J5001)のなかで０以上の値のもの（＝勝ちトレード）を抜き出しその個数を数えます。
負け回数
概要
勝ち回数と同じく取引回数、勝率をだすための準備です。
記述式
1,A6に負け回数と記述
2,B6に =COUNTIF(I11:J5001,"＜0")と記述
解説
個別損益（I11:J5001)のなかで０以下の値のもの（＝負けトレード）を抜き出しその個数を数えます。
取引回数
概要
検証で行われたトレード回数です。この値が少なすぎるとシステムの信頼性が疑われます。
自分のロジックはそこそこのエントリー機会があり、売買検証の結果は信頼に足るものなのかを判断するのに必要な材料になります。
記述式
1,A4に取引回数と記述
2,B4に =B5+B6と記述
解説
勝ち回数＋負け回数＝取引回数ですね。
勝率
概要
システムを使っている時の目安です。勝率がわかっていれば平均どれくらいの頻度で勝ち負けがあるかが気休め程度に把握できます。（もちろん勝率７０%でも負けが何回も続くことはありますのであくまでも気休めです。）
記述式
1,A7に勝率と記述
2,B7に =B5/B4と記述
解説
総トレード回数を勝ちトレード回数で割ることで勝率がでます。
PF
概要
PFとは、プロフィットファクターで、総利益が総損失の何倍かを示すデータのことです。
期待値のようなものになります。１以下ならばそのシステムは使えない（やればやるだけ損失を出す）
ものであることがわかり、２以上ならば相当良いシステムということができます。
記述式
1,A8にPFと記述
2,B8に =（B2*B5)/ABS(B3*B6)と記述
解説
勝ちトレード数×勝ちトレード回数÷負けトレード平均PipS×負けトレード回数
＝（ 総利益 ）÷（ 総損失 ）
となり、PFになります。損失は－がついてしまいますのでABSを使って絶対値で計算します。
累計損益
概要
そのシステムで取引を続けた結果いくらの損益になったかを表すものです。
ここではスプレッドを入れていないので、累計損益-(スプレッド×売買回数)が累計損益になります。
記述式
1,A9に累積損益と記述
2,B9に =B2*B5+B3*B6 と記述
解説
勝ちトレード数×勝ちトレード回数+負けトレード平均PipS×負けトレード回数
＝（ 総利益 ）+（ 総損失 ）
となり、累計損益となります。
大変お疲れ様でした。もし僕と同じデータをお使いになられた方はおそらく↓のようになると思います。
（書式をいじって色等はかえています）
ｻ鄙・ﾚ ﾁ#58600;､ 2
結果としてはどうでしょうか。まぁ、悪くはないシステムですね。目立たないですが。
最大損失が10なのでもし常に１万通貨で取引をしていると仮定したら１0万円が最大損失になります。最悪のタイミングで取引をはじめたら１０万円の資金でやっていたら破産するということになります。
スプレッドが0.3がとして考えると実際の累計損益は32.65-568×0.03=15.63
つまり1563Pips抜いていたということになります。
一万通貨で取引し続けたら１５万６千３百円の利益です。
2008年４月７日から2008年1月30日までで1563pipsなのでまぁ、すくなくても僕が裁量で取引するよりかは格段に良好なパフォーマンスといえると思います。
僕裁量システムはPFが0.02とかだと思います。データを集めて計算してみると二度と裁量はやりたくないと思えて良いかもしれません。
PFは1.22と低いですが売買回数が568回と多いのでそこはまぁ目をつぶっても良いのではないかと思います。
今回のモデルの評価はこんな感じです。
ーーーーーーーー
皆さんはエクセルをつかって最適化を行えることはご存知でしたか？
実は、可能なんです。今回はそのことに触れていきます。
これを知るとシステム構築にすごい幅が出ると思いますので、ご存じでなかった方は是非覚えておいてください。
それでは講義はじめましょう。前回のエクセルシートをお開きください。
システムトレードのためのエクセル講座
第４回
パラメーターの変更
簡易コード表
１, C1にヒゲ乖離幅と打ち込む
２, D1に0.4と記述
３,C1に時間と記述
４,D2に１と記述
５, G10を=IF(AND(F10＞C10,D10-F10>D$1),1,"")に変更。
６, G10セルを選択し、右下にカーソルを合わせ黒十時になるのを確認し、ダブルクリックしてコピー
７, H10を=IF(AND(F10＜C10,F10-E10>D$1),1,"")に変更。
８, H10セルを選択し、右下にカーソルを合わせ黒十時になるのを確認し、ダブルクリックしてコピー
９,I10に=IF(G10=1,OFFSET(C11,D$2,0)-C11,0) と修正
１０, I10セルを選択し、右下にカーソルを合わせ黒十時になるのを確認し、ダブルクリックしてコピー
１１,J10に=IF(H10=1,C11-OFFSET(C11,D$2,0),0)と修正
１２, J10セルを選択し、右下にカーソルを合わせ黒十時になるのを確認し、ダブルクリックしてコピー
パラメーターの変更
ヒゲの長さを変える。
前々回の講義で、ドル円やユーロ円で多市場間テストをした時に、通貨ごとにひげの乖離幅を変えていました。 その時の手順は以下のようなものでした。
1,セルをいじってパラメーター（ヒゲの乖離幅）を変更し
2,他のセルにも適用させるように下まで全コピーを実行する
これだと少し不便ですね。
そこで今回は絶対参照を使ってその不便さを解消します。
まずはコードを次のように修正してください。
１, C1にヒゲ乖離幅と打ち込む
２, D1に0.4と記述
3, G10を=IF(AND(F10＞C10,D10-F10>D$1),1,"")に変更。
4, G10セルを選択し、右下にカーソルを合わせ黒十時になるのを確認したらダブルクリックしてコピー
5, H10を=IF(AND(F10＜C10,F10-E10>D$1),1,"")に変更。
6, H10セルを選択し、右下にカーソルを合わせ黒十時になるのを確認したらダブルクリックしてコピー
解説
3, G10を=IF(AND(F10＞C10,D10-F10>D$1),1,"")に変更。
ここででてくる＄ですが、これはセルを絶対参照させたい時に使う記号です。
具体的に説明しますので、コピーされたセルを見ていってください。
G10 =IF(AND(F10>C10,D10-F10>D$1),1,"")
G11 =IF(AND(F11>C11,D11-F11>D$1),1,"")
G12 =IF(AND(F12>C12,D12-F12>D$1),1,"")
G13 =IF(AND(F13>C13,D13-F13>D$1),1,"")
＄をつけた後の数字１が自動的に2,3,4のように変わっていないことに注目してください。
第一回目の講義で、コピーをする時に、エクセルは相対的にコピーを実行するとお話したことを覚えていますでしょうか。
その結果F10、F11、F12、F13というようにそれぞれのセルに対応した数値に変更され、記述した式が全てのセルで同じように計算させることができるのでしたね。
しかし、セルを相対的にコピーしたくない時があります。
そのような時に使うのが＄です。
＄は絶対参照を指定する時に使う記号です。これを使うことで、その次の数値や記号を、相対的ではなく、固定してコピーできるようになります。
それを抑えた上でもう一回コードを確認しましょう。
=IF(AND(F10>C10,D10-F10>D$1),1,"")
D10-F10>D$1
ってなんでしょうか。
これは高値と安値の差がD$1以上だったら、という意味になります。
D$1を見てください。先ほど0.4を入れておきました。D$1=0.4 なので
D10-F10>0.4
になります。結局前の式と変わらないわけですね。
しかし今回違うのは、高値と安値の差について、すべてのセルの式がD1を参照にしているという点です。
あとは同じなので、解説は以上で終わります。
実験
さてそれでは早速D１をいじってみましょう。
とその前に、もともとヒゲの幅0.4円の時の損益曲線と分析データーをみてみましょう。
0.4表4Graph.png
次にD１を0.２にしてみてください。↓になります
0.2表０．２Graph
だいぶ成績が落ちてしまいましたね。これでは手数料負けしてしまいます。
次は思い切ってD１を１にしてみてください。以下のようになります。
1表1Graph.png
PFが２を超えました！！
それでも売買回数が51回と少ないので信頼度は引くそうです。
もし自動で更新されないようでしたら数式タブの計算方法の設定で、再計算が自動になっているかどうかを確認してください。
さて今回は、最適化のステップにいくために、もう一つパラメーターを作りたいと思います。
手仕舞い時間の変更
今までの方法だと、ヒゲが出たら次の始値でエントリーし、一時間後（エントリーの次のローソク足の始値）にイグジッドすることになります。
それがもし2時間後、3時間後にイグジッドしたら成績はどのようにかわるか興味はありませんか？
・・余り興味はないかもしれませんが、最適化のステップのために、コードを記述していきましょう！
1,C1に時間と記述
2,D2に１と記述
3,I10に=IF(G10=1,OFFSET(C11,D$2,0)-C11,0) と修正
4, I10セルを選択し、右下にカーソルを合わせ黒十時になるのを確認したらダブルクリックしてコピー
5,J10に=IF(H10=1,C11-OFFSET(C11,D$2,0),0)と修正
6, J10セルを選択し、右下にカーソルを合わせ黒十時になるのを確認したらダブルクリックしてコピー
まずは今回初めて出てきたOFFSET関数から解説します。
OFFSET
指定したセルから指定した行、列に移動させたセルの値を入力します。
結構大事な関数なのでしっかり押さえておきましょう。
記述式: =OFFSET（参照,行数,列数）
具体例:=OFFSET(K10,4,3)
図を見てください。K10から下に4行左に3列移動させたセルの値が入力されていますね。
OFFSEｔ
それではコードを解説します。
3,I10に=IF(G10=1,OFFSET(C11,D$2,0)-C11,0) と修正
もともと、C12-C11だったものが、OFFSET(C11,D$2,0)-C11に変わりました。
C11からD$2で指定された数列下にいって、左に0行動かしたセルの値から、C11を引くことになります。
列は移動しませんので、D$2で指定した数値だけ下にいく＝決済までの時間が増えるということになります。
4,も原理は同じですので解説は省きます。
実験
さてそれではD2をいじってみましょう。
2日後に決済したらどうなるかという遊び心で４８に変更してみてください。（乖離幅は0.4にしてあります。）
48日48agraf.png
累計損益が154円を突破しています！要は15400Pipsです！
といっても、赤い丸のところから運用を開始していたとしたら最大損失額である１２４円程負けていることになりますので、怖くてとても運用を任せられません。
今回作ったパラメーターであるヒゲの長さと、決済時間の最適かつ、信頼のできる組み合わせをみつけるにはどうすればよいでしょうか。
ーーーーーーーーー
お待たせしました。今回はエクセルを使った最適化について述べていきたいと思います。
これを覚えると売買検証が大変効率よく行えるようになりますのでぜひ覚えておいてください。
それでは始めます。どうぞお楽しみください。
システムトレードのためのエクセル講座
第5回
最適化
まず前回までのエクセルファイルをひらいてください。
そしたら新しいシートを選択してください。左下にあるタブのSheet３で良いと思います。
手順の方の解説は今回は最小限にとどめます。
Sheet３が最適化シート、Sheet２にグラフ、Sheet1には売買検証のデータが入っていると仮定します。
それでは始めましょう。
まず完成図からみてください。
Sheet３の完成図
1-14.png
このようになります。イメージをつかんで頂いた上でコードを記述していきましょう。
Sheet３の記述式
1, A1にヒゲ乖離幅と入力
2, A2に時間と入力
3, A3にPFと入力
4, B1に１を入力
5, B2に１を入力
6, B3に=Sheet1!B8を入力
7, C3に0.1を入力
8, D3に0.2を入力
9, C3とD3をまとめて選択し、左下にカーソルを合わせ黒十時が出ることを確認する
10, 黒十時が出てる状態でドラッグしQ3まで引っ張る。
11, B4に1を入力
12, B5に2を入力
13, B4とB5をまとめて選択し、左下にカーソルを合わせ黒十時が出ることを確認する
14, 黒十時が出てる状態でドラッグしB50まで引っ張る。
解説
今回は赤い番号の手順だけ解説していきます。
6, B3に=Sheet1!B8を入力
今回は、最適化するための判断材料としてPFをつかうことにしました。
Sheet1のB8をみてください。PFの値がでるセルが選択されていますね。
直接=Sheet1!B8と入力するよりも、
1, Sheet3のB3を選択する
2, =と入力
3, Sheet1のタブをクリックする
4, B8をクリックする
5, エンターキーを押す
という方が感覚的にわかりやすくて良いかもしれません。
10, 黒十時が出てる状態でドラッグしQ3まで引っ張る。
この列は何かというと、ヒゲ乖離幅の最適化するときに調べたい値です。１時間足で1.5以上のヒゲがでることは殆ど無いのでQ3までにしました。
もう少し細かく調べたい方は0.05刻みで調べてもよいかもしれません。
0.05刻みで調べたい方は7,8の手順で0.1 0.2 となっているところを0.05 0.1に変えて頂くとできるようになります。
14, 黒十時が出てる状態でドラッグしB50まで引っ張る。
とりあえず47までにしておきました。特に理由はありません。
個人的には47本後ものことになると良い結果が出たとしてもそれは完全に運でしかないのではないかとおもいます。
ただ、ヒゲ順張りブレイクアウトとして使える可能性があるかもしれないということで、もしブレイクアウトならばどれくらいの長さを置いて手仕舞うのが都合良いのかが知りたいため、多めに本数をとってみました。
つぎに、Sheet１を少しいじります。Sheet1を開いてください。
Sheet1.png
Sheet１の記述式
1,D1を=Sheet3!B1と修正。
2,D2を=Sheet3!B2と修正。
解説
上のやり方は繰り返しになりますが、
1, Sheet1のD1を選択する
2, バックスペースをおしてセルの中身をクリアする。
3, =と入力
4, Sheet3のタブをクリックする
5, B1をクリックする
6, エンターキーを押す
という方が感覚的にわかりやすくて良いかもしれません。
もちろんD2も同じように行えます。
これで下準備は整いました。 お疲れ様でした。それでは最適化の最終ステップにまいりましょう。
Sheet3に戻ってください。
最適化
1,C4を選択して表示-window枠の固定-window枠の固定を選択してください。
windowwaku.png
2,数式タブにある再計算方法をデータテーブル以外自動を選択してください。
saikeisan.png
2,B3:Q50の範囲を選択します。
3,データ-WhatIF分析-データテーブルを選択します。
WHaif.png
4,行の代入セルに$B$１を記述（直接B1をクリックすると$B$1と記述されます)
5,列の代入セルに$B$2を記述（直接B２をクリックすると$B$２と記述されます)
6,数式タブにある再計算実行をおしてください。
jikkou.png
7,計算が終わるまでしばらく待ちます。（5分くらい？）
8,完成です。
ｻ鄙・ﾚ ﾁ#58600;､ 49
解説
これでヒゲの長さ0.1～1.5円と時間１～４７時間の組み合わせすべてのPFがでました。
PFの組み合わせが表のようになっているのがご覧いただけるのではないでしょうか。
この中から最も成績が良いと思われるものを選び出しましょう。
データーの範囲を選択するとExcelの右下に最大値や最小値がでますのでそちらを参考にするのも良いと思います。
もしPFでなくて累計損益の最適化をおこないたいのならば、Sheet3のB3セルに=Sheet1!B9を入れ込めば、累計損益の最適化を行うことができます。
ヒゲ順張りシステムは使えるか？
さてヒゲ順張りは使えるのでしょうか？それを分析するための僕なりの選定判断基準を説明します。
それは
PFが1以上であるものの割合が65％をこえているかどうか。
です。
いくらPFが４とかのモデルがあっても、パラメーターをいじくるとほとんどは1以下になってしまうモデルはオーバーフィッティング（過剰最適化）である可能性が高く、モデルの見直しをする必要があります。
それを調べるためにこのような表をつくってみました。
1ijou.png
D1D2D3の式だけ説明します。
D1：=COUNTIF(C4:Q50,">1") PFが1以上である個数
D2：=COUNTIF(C4:Q50,"<1") PFが1以下である個数
D3：=D1/(D1+D2)*100 PFが1以上である個数のパーセンテージ
今回は90％以上がPFが1以上なのでオーバーフィッティングの心配は無いと言えるでしょう。
どうもお疲れ様でした！本来は、パラメーターの組み合わせの選択方法まで踏み込みたかったのですが、意外と文が長くなってしまったので、今回はここまでにします。
iPadから送信
