MS Excelを用いた売買検証法

MS Excelを用いた売買検証法
日本テクニカルアナリスト協会正会員谷口和弘

第1回：『関数を用いたテクニカル指標計算』

今後数回にわたって表計算ソフトであるMS Excelを用いた売買検証法について、紹介していきたいと思います。

１．R1C1形式を使う
これは関数を使っての検証には直接関係ないのですが、今後、紹介する予定の"マクロ"（ビジュアルベーシック）を用いての検証の際に、表示される行と列の順序がマクロの場合と一致し、迷うことが少なくなると思いますので、R1C1形式の使用をお勧めします。

《手順》
メニューの"ツール" → "オプション" → "全般"をクリック → 「R1C1参照形式を使用する（C）」をチェック → "OKボタン"をクリック（完了）

このとき、左端の縦に並んでいる番号が"行番号"、上の横に並んでいる番号が"列番号"になります。行番号３、列番号７のセルは"R3C7"と表示されます。
さらに、関数計算を使うときに出てくるのですが、"R[-2]C[-1]"という表示方法もあります。これは選択しているセルを基準にした場合、"２つ上・１つ左"のセルを表しています。また、"R[2]C[1]"の場合は、"２つ下・１つ右"のセルを表します。

Excelのヘルプにもありますが、R1C1形式の説明を付け加えておきます。
"R1C1"は絶対参照を表しています。どういう事かと言いますと、現在指定されているセルが何処であれ、"R1C1"は"1行・1列"のセルのことです。これに対して相対参照があります。これは"R[1]C[1]"のようにカッコ付きで表示されます。これは、現在指定しているセルを"RC (R[0]C[0])"とした時に何処のセルか（計算に用いる時などの場合）を表しています。カギ括弧内の数値が正なら行列番号をそれぞれ下・右方向へ、負なら上・左方向へそれぞれ数値分だけ移動させることになります。
さらに、計算式の入力中にマウスや方向キーを使ってセルを参照する場合（これについは後で述べますが）、入力の途中で、絶対参照にしたいセルをマウスで指定した後"F4キー"を押せばそのセルのみが絶対参照として入力されます。

２．よく使用する関数
データの検証を行う上で必要と思われる関数をいくつか紹介します。

関数の基本的な使い方は、まず"＝（イクオール）"を入力し、さらに関数、データの範囲等を入力することで計算できます。
式や使い方などの詳細は"Excelのヘルプ"で見ることができますので、ここでは割愛させて頂きます。

計算などに用いるもの
平均値・・・=AVERAGE( )
最大値・・・=MAX( )
最小値・・・=MIN( )
合計・・・=SUM( )
絶対値・・・=ABS( )
データの個数・・・=COUNT( )
標準偏差・・・=STDEV( )

その他
条件設定1・・=if(条件A,真,偽) →もし条件Aならば真を、違うなら偽を表示する

条件設定2・・・=if(and(a,b,c,…),真,偽) →もしaかつbかつcかつ…ならば真を、違うなら偽を表示する
セルの指定・・・=offset(基準セル,行数,列数) → 基準セルから指定された行数と列数だけシフトさせたセルを参照する

３．テクニカル指標の作成
今回は、以下の指標の作成方法を紹介します。テクニカル指標関係でよく使う関数などを使っていますので、色々な指標の計算に応用して頂きたいと思います。
a)移動平均線
b)チャネルバンド
c) RSI
d)標準偏差バンド

各計算式を入力する前の準備

今回は１枚のシートに１つの指標を作っていきます。シートが４枚必要なのですが、足りない場合はメニューの"挿入" → "ワークシート"で追加して下さい。
シートに名前を付けていきます。まず、シートの下端（"sheet1"などと表示されている部分）を"ダブルクリック"もしくは"右クリック → 名前の変更"で文字を入力できる状態にして下さい。その後、各指標の名前に変更します。

さて、これから解説を進めていきますが、データの位置を統一する為に、各データを以下のように配列して下さい。
1列・・・日付、2列・・・限月、3列・・・始値、4列・・・高値、5列・・・安値
6列・・・終値、1番最初のデータ・・・4行目

以上は一例ですので、今後は各自使いやすいと思われる配列に変更して下さい。
さてこれから実際に入力を開始するのですが、こまめに保存（上書き保存）されることをお勧めします。パソコンはこちら側の都合などに関係なく突然動かなくなったりします。その時、保存していなければ否応なく再び同じ入力をしなければいけません。これを防ぐためにもこまめな保存が必要です。

a) 移動平均線
今回は14日間と21日間の単純移動平均線を作ります。

１．まずR1C7に"移動平均線（a）"、R1C8に移動平均線（b）"と入力して下さい。

２．その一つ下のセル（R2C7とR2C8）に計算させたい移動平均の期間を入力して下さい。
今回は14、21日間にしますので、"14"、"21"と入力します。このセルは色を変えておくと分かり易くなります。

３． R17C7に計算式を入力します。
式は"=AVERAGE(OFFSET(RC[-1],R2C7*-1+1,0):RC[-1])"です。（式の入力は小文字で大丈夫です。入力後自動的に大文字に変換されます。）

入力後、Enterを押すと完了です。（数値が表示されているはずです。）
ここで入力した計算式の確認します。入力が完了したR17C7をダブルクリックすると6列に青い枠が現れます。この枠の中に計算させたいデータが入っていれば問題ないと思われます。（手計算で確認すると完璧でしょう。）

4. 3.と同様にR24C8にも計算式を入力します。
式は"=AVERAGE(OFFSET(RC[-2],R2C8*-1+1,0):RC[-2])"です。

5. 入力が完了したR17C7を選択し、セルの右下で黒十字をダブルクリックして終値のデータの最後までコピーします。同様にR24C8もコピーして下さい。7、8列の最後までデータがあれば完了です。

さてグラフの作成方法ですが、日付、終値、移動平均線（a）、（b）を24行目のデータから最後のデータまでを選択して黒く反転させます。この時、日付と終値、移動平均線（a）、（b）のデータが離れていますので"Ctrlキー（コントロールキー）"を利用します。まず、日付を選択します。そしてキーボードの"Ctrlボタン"を押したまま残りの3つのデータを日付と同じ行数まで選択します。
その後、メニューの"挿入" → "グラフ" で"グラフウィザード"を表示させます。グラフの種類で"折れ線グラフ"を選んだ後、好みのグラフに仕上げて下さい。これで終値と2本の移動平均線のグラフができます。
以上で移動平均線は完了です。

ここでOFFSET関数を使った理由について説明します。AVERAGE関数のみで範囲を指定して計算することもできますが、その方法だと、例えば7日、42日移動平均線を調べる場合に、毎回計算式を書き換えなければいけませんが、OFFSET関数を使っておくと、セルR2C7に入力してある期間の数値"14"を7や42にするだけで計算することができます。これは今後、最適化などを行うために必要ですので、ぜひ使えるようになって頂きたいと思います。
ただし注意してもらいたい点が一つあります。それは計算させるデータの範囲を確認することです。例えば期間を"14"から"15"に変更したとしますと、データ範囲は空白のセル（R3C7）まで含んでしまい正確な計算が出来なかったり、エラー値（#REF!など）が表示されることになります。これを避ける為にも、「現在何処のデータ範囲で計算しているのか」ということを常に把握する癖を付けて下さい。

b）チャネルバンド
今回は「20日間の最高値・最安値」でチャネルバンドを作ります。チャネルバンドは前日までの20日間の最高値・最安値を当日のレンジ（値幅）として表示させます。

1. セルR1C7、R1C8に"チャネルバンド"、R2C7に"High"、R2C8に"Low"と入力します。

2. R3C7、R3C8に計算期間の"20"を入力します。（ここも色を変えておくと便利です。）

3. 計算式の入力です。R24C7に"=MAX(OFFSET(RC[-3],R3C7*-1+1,0):RC[-3])"そして、R24C8に"=MIN(OFFSET(RC[-3],R3C8*-1+1,0):RC[-3])"と書き込んで下さい。入力が終了しましたら、青い枠で確認することを忘れないで下さい。もしデータの範囲が間違っているなら訂正して下さい。このとき、計算式を書き換えることでも良いのですが、簡単なミス（範囲指定において、安値が高値になっているなど）の場合は「青い枠を移動」させることで対処できます。これを憶えますと訂正だけでなく、計算データの範囲などを手早く変更することができるので大変便利です。

4. 計算式を書き込んだ2つのセルを下方向にコピーして完了です。

「青い枠を移動」させるとは？
計算式を入力したセルをダブルクリックすることで、データとして用いているセルが青などの色で囲まれます。この枠にマウスポインタを合わせると、マウスポインタが白十字から白矢印に変わります。この状態でセルをクリックし、そのまま移動させたい場所まで動かせば、計算式のデータが書き換えられています。このとき、セルの枠の色と計算式のデータの色が一致していますので、確認しながら移動させて下さい。

便利な関数計算式の入力方法
上記の入力方法では"RC[-3]"や"R3C7"などを手入力されていたのではないかと思います。以下に手入力ではなくマウスや方向キーを利用した入力の説明をします。

（例） R1C1とR2C1の足し算
1. 新しいシートを挿入して下さい。そして、R1C1に"10"、R2C1に"20"と手入力します。

2. R2C2に"＝"を入力した後、マウスか方向キーでR1C1（"10"）を指定します。そうすると、そのセルが点線で囲まれた状態になります。さらに"＋（プラス）"を押すと点線が消え、数式バーに"=RC[-1]+"と表示されます。

3. その後同様に、R2C1（"20"）を指定してセルが点線で囲まれた状態になっていることを確認して、Enter を押すと完了です。（数式バーに"=RC[-1]+R[-1]C[-1]"と表示されます。）

この方法を利用することで「自分が何処と何処を計算させたいのか」「何処を範囲にしたいのか」などを目で確認しながら入力できます。
もう少し早くに説明すれば良かったのかもしれませんが、今後の"マクロ"の為には、一つ一つ手入力することも必要なことだと思いましたので、あえて説明しませんでした。
これで今後の入力が少し楽になるのではないでしょうか。

c）RSI
RSIはJ.Welles Wilder,Jr.氏が開発したオシレータ系指標で、今回は14日間と42日間の2本の「RSI」を作ります。ですが、OFFSET関数を使用しますので、完成後でも自由に期間を変更できます。

1. R1C7、R1C8、R1C9、R1C10にそれぞれ"前日比プラス"、"前日比マイナス"、"RSI(a)"、 "RSI(b)"と入力します。
2."前日比プラス"のセルには、当日の終値が前日の終値よりも高かったならば、その値を、そうでないなら0を表示させます。"前日比マイナス"はその逆です。R5C7に"=IF(RC[-1]-R[-1]C[-1]>0,RC[-1]-R[-1]C[-1],0)"、R5C8に"=IF(R[-1]C[-2]-RC[-2]>0,R[-1]C[-2]-RC[-2],0)と入力して、下方向にコピーをして下さい。

3. R2C9、R2C10に"14"、"42"と入力しセルの色を変えておきます。計算式は少し長いですが、それぞれ次の通りです。

R18C9="100-(100/(1+AVERAGE(OFFSET(RC[-2],R2C9*-1+1,0):RC[-2])/AVERAGE(OFFSET(RC[-1],R2C9*-1+1,0):RC[-1])))"

R46C1="100-(100/(1+AVERAGE(OFFSET(RC[-3],R2C10*-1+1,0):RC[-3])/AVERAGE(OFFSET(RC[-2],R2C10*-1+1,0):RC[-2])))"

この式を下方向にコピーすれば完了です。

d）標準偏差バンド
これは、John Bollinger氏が考案したので「Bollinger Bands」とも呼ばれています。標準偏差バンドとは、「Ｎ日間の移動平均線」に「Ｎ日間の標準偏差をｋ倍したもの」を加算・減算して求めます。今回は一般的な"Ｎを20日"、"ｋを2倍"で作ります。

1. R1C7、R1C8、R1C9、R1C10にそれぞれ"移動平均線"、"標準偏差"、"マイナス２σ"、"プラス２σ"と書き込みます。

2. 移動平均線は上記で説明した方法と同様に入力して下さい。標準偏差についてですが、移動平均線の"AVERAGE"を"STDEV"に書き換えるだけで計算できます。

R23C8＝"=STDEV(OFFSET(RC[-2],R2C8*-1+1,0):RC[-2])"

3. マイナス２σ、プラス２σの計算ですが、これは先程も述べましたが「移動平均線に標準偏差の2倍したものを加算・減算する」ことでできますので、計算式も大変簡単なものになっています。

R23C9＝"=RC[-2]-RC[-1]*2"
R23C10＝"=RC[-3]+RC[-2]*2"

これを下方向へコピーすると完了です。

今回はここまでです。次回は「損益計算」、「データテーブルを用いた最適化」、「売買ルールの評価数値（平均損益、ドローダウンなど）」について解説する予定です。