期毎集計における日付関数の活用

昨日のコラムでご紹介しました、資産カーブのローソク足チャート表示ですが、それを実現するためには、日付関数の使い方がポイントとなります。
基本的には、シリアル値を分解するYEAR関数、MONTH関数と、年月日からシリアル値を求めるDATE関数を用いれば十分です。

ご存知の方も多いと思いますが、YEARはシリアル値から年を求める関数、MONTHは月を求める関数で、対象を()で括るだけです。
例えば、YEAR(39742)とすると、2008が求まります。

一方、DATE関数は、DATE(年,月,日)で指定します。例えば、2008年10月21日は、DATE(2008,10,21)で求まり、そのシリアル値は39742となります。
これを書式設定すると、元の2008年10月21日などと表示されます。

今、株価や資産累計の日次データから月次や四半期、年次のデータを集計する場合、日にちを追っていったのでは埒が明きません。
これらを求めるためには、日付は不要なわけですから、日付をYEAR関数とMONTH関数で括って、西暦年と月を求めれば事足ります。

そうすると、行方向に西暦年と月がずらーっと並ぶことになります。例えば、月の替わり目でフラグ"1"を立てるようにすれば、どこで月が替わったか容易に判断できます。
これは、当月と前月の差を採っていけば、容易に実現できます。12月から1月に替わる時は、"-11"になってしまいますが、ちょっとした工夫で対処できます。

あとは、そのフラグを基準として、その月における始値や高値、安値、終値を求めていけばいいわけです。
また、フラグを立てる場所を四半期の初めに設定すれば四半期毎集計が、年の初めに設定すれば年毎集計が可能となります。

さて、そのようにして期毎の集計が可能になったわけですが、では具体的に、その期における始値や高値などを求めるには、どうしたらいいでしょうか。
期の初めの行と終わりの行を調べて、その行間における最大値を求める、などとしたら、気が遠くなってしまいます。

詳しくは述べませんが、漸化式の考え方を応用すれば、期の最終日において始値や高値、安値、終値が自動的に求まります。
これはある意味、常套手段ですね。トレーディングシステムにおいても、売買毎の成績を集計したり、ドローダウンを求めたりする際に利用します。

このようにして、期末において4本値データが出揃ったわけですが、これらの値が飛び飛びになっていることに気付くかと思います。
値が飛び飛びでは、エクセルに備わった株価チャート機能が使えません。さて、どうしたものでしょう。

ちょっと面倒ですが、別シートにおいて再集計してやる必要があります。基本的には、期を代表する日付、例えば期末の月初の日付（この理由は後でお話します）を最初の列に設定し、MATCH関数を用いて対応するデータを参照します。

なお、そのためには、参照元のシートにおける対応データのある行に、上記同様、期末の月初の日付を設定しておく必要があります。
月初の日付を設定するには、DATE関数を使用します。すでに西暦年と月は求められているわけですから、DATE(西暦年,月,1)とすることで、期末の月初の日付が求まります。

これで、期毎データを隙間なく集計することが出来るかと思います。そうしましたら、挿入メニューからグラフを選択し、株価を選んで、後はウィザードに従います。
なお、データ系列の書式設定におけるオプションタブで、棒の間隔を"0"に設定すると、チャートが見やすくなります。

なお、上記において月初の日付を使用したのは、グラフを作成した時にX軸を月表示（年毎の場合は年表示）にするためです。月初以外の日付を用いた場合、正しく表示されなかったり、日付まで表示されてしまったりする場合があります。

なお、期末の日付を設定する場合、例えば月毎だけでなく、四半期毎や年毎も自動的に設定できたら便利です。
例えば、月毎、四半期毎、年毎、といった場合毎に、日付の表示を変えれば良いわけですが、結構大変な作業となります。マクロを使った方が多分楽だと思います。

一応、マクロを使わないで実現するためのヒントとしましては、DATE関数を活用する、ということになります。
DATE関数の月の引数部分は、1～12に限らず、それより大きな数字でも扱えます。例えば、13としますと、西暦年が1年繰り上がって、月は1月になります。

以上、簡単ですが、エクセルを用いて任意の期毎にデータを集計する方法について、ご説明いたしました。
もちろん、これらは基本のみなので、実際に使い勝手の良いシステムにするためには、さらにもう一工夫が必要になります。

タグ：株 システムトレード トレーディングシステム エクセル 日付関数 期毎集計 ローソク足