Excel〜テクニカル

前日比＆騰落率

・ Excelの注意点。2進法による計算誤差に注意。

■IF(論理式,真の場合,偽の場合)
論理式の条件が満たされる場合は真の内容を選び満たされない場合は偽の内容を選ぶ。

　特に難しいことはないですが小数点以下の計算が生じる騰落率などは注意が必要です。 F行に前日比。G行に騰落率を表示される場合。
F3セルに【=IF(E4="","",E3-E4)】、G3に【=IF(E4="","",(F3*100)/(E4*100))】と入力し以降のセルにもコピーします。（図2-1
ここでの注意点は小数点以下の数字は整数に直してから計算することです。

　なぜ整数にして計算するのか？
二進法であるエクセルでは小数点以下の計算をすると誤差を生じることがあります。
例えば、105.40-105.39を計算すると本来の答えは0.01ですがエクセルでは0.0100000000000051になります。そのため、小数点以下の数字を計算する場合は一度整数にしてから計算するようにします。 通常は誤差の範囲ですがシステムトレードでは考慮しておいたほうが無難です。
　　・参考　フリー百科事典『ウィキペディア（Wikipedia）』　 → Microsoft Excelの問題点

(図2-1
Excelに戻る


移動平均線

・ 任意の日数が設定できる単純移動平均線と指数平滑移動平均線を求める数式とグラフの基本設定。

■OFFSET(基準,行数,列数,高さ,幅)
基準としたセルから指定した行数、列数だけ移動したセルを参照する。セル範囲を指定する場合は行の高さ、列の幅を指定する。
■AVERAGE(数値1.数値2,…)
指定した数値や範囲内の数値の平均を計算します。空白セルや文字列は無視して計算します。

　任意の単純移動平均線（SMA） Simple Moving Averageを求めます。
移動平均線は、全体のトレンドを捕らえる場合に使われます。短期線と長期線の関係で売買タイミングを計ります。順張り系の指標となります。
　　・参考　新生銀行テクニカル分析講座　 → 『移動平均線』

　単純平均線は一定期間の株価の終値を単純平均したものです。エクセルで求める場合AVERAGE関数を使いますが入力した値によって結果を変えたい場合はOFFSET関数を合わせて使います。この関数を使うことで最適化が容易になります。
H3のセルに【=AVERAGE(OFFSET($E3,0,0,H$2,1))】と入力。

　・OFFSET
基準…ここでは10/29が基準となる終値平均なのでE6のセルとなります。後々のために列のみ絶対参照にします。
行数…6行が基準となるので0。
列数…E列が基準となるので0。
高さ…下方向への高さ。ここではH5セルの値によって変化するようにしてます。
幅…対象範囲は1列(終値)だけでよいので1。

　ただしこのままではデータの日数が求める平均日数に足らない場合でも値が表示されます。 これを修正するため以下のように数式を追加します。（図2-2
　　【=IF(OFFSET($E3,H$2-1,0,1,1)="","",AVERAGE(OFFSET($E3,0,0,H$2,1)))】
これで設定日数に達しない場合は空白となります。最後に下にオートフィルして完了です。

(図2-2

　次に任意の指数平滑移動平均（EMA） Exponential Moving Averageを求めてみます。EMAは直近の株価に注目した平均線です。他の移動平均線に見られない特徴として「平均線が上向きであること」と「株価が平均線の上にあること」は同時にしか起こりません。
計算式(n日)
　1日目の計算　(c1+c2+c3+c4+c5+……+cn)÷n
　2日目以降の計算　(前日の指数平滑移動平均)+α×(当日終値-前日の指数平滑移動平均)
　※　cn＝n-1日目前の価格。c1＝当日価格。　※　α(平滑定数)＝2÷(n+1)

　求める数式は下のようになります。
　　【=IF(OFFSET($E3,I$2-1,0,1,1)="","",IF(OFFSET($E3,I$2,0,1,1)="", AVERAGE(OFFSET($E3,0,0,I$2,1)),I4+2/(I$2+1)*($E3-I4)))】

(図2-3

　ここで移動平均線のグラフを作成してみます。
【挿入】→【グラフ】を選択。グラフの種類は、ロウソクと出来高のグラフもありますが後々指標を追加することを考えてここでは折れ線を選択します。 データ範囲は複雑になるので【系列】で順番に追加していきます。ここでは終値、5日移動平均線、25日移動平均線とします。

(図2-4

　このままでは見難いのでいくつか調整します。基本的に好みの問題ですが､､､ まず、グラフ全体を選択して全体の文字の大きさを調整します。そして各項目を選択し右クリックするとそれぞれ設定が選択できます。

　【グラフエリアの書式設定】
→【パターン】輪郭なし
　【プロットエリアの書式設定】
→【パターン】輪郭なし、領域なし
　【軸の書式設定】
→【パターン】目盛の種類を交差、補助目盛の種類を内向き。 【目盛】Y/数値軸目盛を最小値5000、目盛間隔1000、補助目盛間隔200。【表示形式】小数点を表示しないように変更します。
　【データ系列の書式設定】
→【パターン】線の色や太さを見やすいように調整。
　【凡例の書式設定】
→【位置】を上に設定。

　設定を変更すると（図2-5の状態になります。

(図2-5
Excelに戻る


ボリンジャー・バンド

・ 任意の日数が設定できるボリンジャー・バンドを求める数式とグラフ表示１

■OFFSET(基準,行数,列数,高さ,幅)
基準としたセルから指定した行数、列数だけ移動したセルを参照する。セル範囲を指定する場合は行の高さ、列の幅を指定する。
■STDEV(数値1.数値2,…)
指定した数値や範囲内のデータを母集団の標本であると見なして標準偏差を計算します。空白セルや文字列は無視して計算します。
指定する数値が母集団全体である場合は、STDEVP関数を使って計算します。

　ボリンジャーバンドは統計上の売られすぎ買われすぎを判断する逆張り指標として使用されます。 基本的な性質として次のようなものがあります。
　　「-1σ」〜「+1σ」の間に値が存在する確率は68.3％
　　「-2σ」〜「+2σ」の間に値が存在する確率は95.4％
　　「-3σ」〜「+3σ」の間に値が存在する確率は99.7％
　　・参考　新生銀行テクニカル分析講座　→ 『ボリンジャー・バンド』

　標準偏差(σ)はSTDEV関数で計算できますがボリンジャー・バンドで使う標準偏差は若干異なります。20日間の標準偏差を求める場合、20日間の価格にその日の20日間移動平均値を加えて21日間で計算します。
J3セルに【=IF(OFFSET($E3,J$2-1,0,1,1)="","",STDEV(OFFSET($E3,0,0,J$2,1),H3))】と入力。

　■STDEV(数値1.数値2)
　数値1…設定期間(終値)に基づく範囲。
　数値2…設定期間に基づく移動平均値。

　移動平均の時と同様にOFFSETを使い設定期間未満の値を表示しないようにします。

　ボリンジャー・バンドでよく使われる±2σの値は、移動平均の値±2×σで求められます。 セルK3とL3に数式を入力しオートフィルすれば完了です。（図2-6

(図2-6

　前回の移動平均線で求めたグラフにボリンジャー・バンドの±2σをくわえてみます。 グラフ全体を選択して右クリック→元データ→【系列】追加(A)を選択します。
次に移動平均線がゴールデンクロスした箇所を橙色、デットクロスした箇所を青色で表示されるようにしてみます。N列の数式がゴールデンクロス、O列の数式がデットクロスの系列を示し、 クロスした日は終値が表示されそれ以外はエラーが出るような数式になっています。 通常グラフでは空白の場合、0として表示されますが値にエラーを返すとグラフには表示されなくなります。エラーを返す関数はNA()となります。この系列をグラフにくわえ【データ系列の書式設定】→【パターン】線を非表示にし点を見やすい形状にします。
これでボリンジャー・バンドが追加され移動平均線がクロスした箇所が表示されました。 クロスした箇所が表示されるので有効なテクニカルか視覚的に判断しやすくなると思います。（図2-7

(図2-7
Excelに戻る


MACD

・ 任意の日数が設定できるＭＡＣＤを求める数式とグラフ表示２

■OFFSET(基準,行数,列数,高さ,幅)
基準としたセルから指定した行数、列数だけ移動したセルを参照する。セル範囲を指定する場合は行の高さ、列の幅を指定する。

　MACD(Movng Average Convergence Divergence)移動平均収束拡散法は2本の指数平滑平均を使います。売買サインが明確で信頼性も比較的高いので使いやすい指標です。特に先行指標として有効です。
指数平滑平均は移動平均線のところで求めた数式を使います。
MACDは【短期指数平滑平均−中期指数平滑平均】となります。 よく使われる期間は12日と26日です。シグナルはMACDの9日単純平均がよく使われます。（図2-8

(図2-8

　MACDはMACDがシグナルを下から上抜いたところを買いサイン、上から下抜いたところを売りサインと判断します。
また、MACDの売買サインには上記のほかにいくつかあります。
　　１．MACDが0ラインを上抜いたところで買いサイン、下抜いたところで売りサイン。いわゆるEMAのゴールデンクロスとデッドクロスになります。
　　２．以前の反転ポイントがその後の反転の目安にある。
　　３．MACDが相場の流れと反対方向への動きをしているときは、それまでのトレンド転換を示唆。
　　４．MACDの掲載するトレンドを上下に抜けると、それまでの流れの変化を示唆。
　　・参考　新生銀行テクニカル分析講座　 → 『MACD』

　前回のグラフに今度はMACDを追加してみます。 系列を追加するところは同じですが、同軸上に表示すると見難くなるので、グラフを選択しデータ系列の書式設定【軸】→使用する軸を第２軸に設定します。 もしグラフが見えていない場合はY/数値軸目盛をいったん自動に設定してグラフが表示されてから設定します。
　今度は平均線のクロスの替わりにMACDとシグナルのクロスが表示されるようにしています。 また、NA()の替わりに-10000000000等の極小値を表示するようにしてます。これによりラインが引かれたように見えトレンドの変化がわかりやすくなります（図2-9

(図2-9

Excelに戻る