年足チャートをエクセルで作る

何年か前に、めちゃんこテクニカル分析に詳しい方に、年足のチャートを作って見てみるといいと言われ、それ以来、毎年、年初にエクセルでPIVOTを入れた年足チャートを作っていやす。ローソク1本が1年分の値動きになるチャートなんて、一般には存在していないので、やはり自前で作るしかないってわけです。(^^;

例年ですと、僕はUSD/JPYとEUR/USDの２つの年足チャートを作るんですが、今回は、せっかくなので、それにプラスして、日経225と、昨年から何かと話題になっている上海総合指数の年足チャートを作ってみやした。いちおう、この記事の一番下から全てダウンロードできるようにしてあるので、欲しい方はどうぞ！！(^^)

もとの四本値のデータは、USD/JPYとEUR/USDに関しては、FXDDのMT4の月足データを使い、日経225と上海総合指数は、それぞれ以下の米国YAHOOのページからダウンロードしました。

日経225
http://finance.yahoo.com/q/hp?s=%5EN225+Historical+Prices

上海総合指数
http://finance.yahoo.com/q/hp?s=000001.SS+Historical+Prices

ということで、エクセルでの年足チャートの作り方を書いてみたいと思います。
いちおう、下のやつがUSD/JPYの完成図です。

エクセルの使い方としては、特に難しい部分はないんですけど、ポイントとしては、月足データを年足データに加工する部分と、普段はあまり使わない、エクセルに搭載されているグラフの中で「株価」を使用することくらいでしょうか・・・。

まず、ダウンロードした月足の四本値データを1枚目のシートに貼り付け、日付、始値、高値、安値、終値のみ残し、シート名を「四本値」に変更します。図はKingsoftですけど、エクセルでも全く同じなので、気にしないで下さい。(^^)

次に、2枚目のシートに1枚目のシートに貼り付けた月足データを使って、年足データを作っていきます。こちらもシート名を「年足データ」に変更します。

ここで、まず肝心なのが、下の図のように、始値、高値、安値、終値の順番に並べる事です。ここでは、Open、High、Low、Closeと書いてますが、始値、高値、安値、終値でも構いません。とにかく、この名前とこの順番が、株価グラフを作る時に肝心なポイントです。

そして、下の図の黄色く着色してある1行目には、そのまま、1991年のデータが入ってます。
始値は1枚目のシートの月足データ1991年1月の始値。高値には、1991年の高値の12ヶ月間の中の最高値。安値には同じく12ヶ月間の中の最安値。そして終値には1991年の12月の終値が入ります。

それぞれのセルの式は以下のようになっています。

始値　=四本値!B2
高値　=MAX(四本値!C2:C13)
安値　=MIN(四本値!D2:D13)
終値　=四本値!E13

高値と安値に、指定範囲の最大値と最小値をかえすMAX関数とMIN関数を使ってるだけで、ここまでは簡単ですよね！！(^^)

年足データの2016年分のところには、まだ1月なので、月足データをそのまま生の数字で貼り付けてあります。

そして、1992年から2015年までの四本値データに関しては関数処理を行います。今回で言えば、目玉部分です。とは言っても、そんなに難しいもんやないですけど・・・。(^^;

基本的な考え方は、1行目の1991年と一緒なんですけど、ただ、膨大な1枚目のシートの月足データの中から、拾い出すのが大変なのと、間違いも起こりやすいので、関数で処理するってだけの話です。

図の1992年の始値つまりB3セルには、以下の関数が入れてあります。

=OFFSET(四本値!$B$2,(ROW()-2)*12,0)

一見ややこしく見えるかもしれませんが、使ってる関数はOFFSET関数とROW関数だけです。OFFSET関数は基準セルから指定しただけずらした所から、範囲指定が出来る関数です。書式は以下のような感じです。

OFFSET(基準セル、行数、列数、高さ、幅)

今回の場合ですと、始値の基準セルを1991年1月の始値つまり、「四本値!B2」セルにしています。あとで、オートフィルで下にコピーする都合上、「四本値!$B$2」と絶対参照にしてあります。

そして次の1992年の始値は12ヶ月先の1月、1993年の始値はさらに12ヶ月先、つまり24ヶ月先の1月と、12ずつ下にずれていきます。ということで、OFFSET関数の2番目のずらす行数を「(ROW()-2)*12」と書いてみました。

ROW()は単純にセルの行番号をかえす関数です。年足データの1992年の始値のセルはB3ですから、3がかえってきます。ここでは、四本値!B2から下に12ずらしたいだけなので、2を引いて12をかけています。こう書いておけば、1993年の始値のセルはB4ですから24が指定され、1995年では36と、以下12ずつ、ずれて指定されていくことになります。その後ろの列数は、今回はずらす必要がないので、0としてあります。

終値も全く同じ構造で、基準セルが変わるだけで、以下のようになります。

=OFFSET(四本値!$E$13,(ROW()-2)*12,0)

あとは、始値も終値も、オートフィルで、2015年まで引っ張って完成です。

次に高値ですが、1992年の高値C3セルには、以下のように関数が入っています。

=MAX(OFFSET(四本値!$C$2,(ROW()-2)*12,0,12,1))

さらに、ややこしく見えますが、先程のOFFSET関数で範囲指定をして、それをMAX関数でかこって最大値を拾っているだけです。そして、OFFSET関数の部分も、先程の始値と基準セルが変わってるだけで、12ずつずらすのは一緒です。その後ろに、「12,1」というパラメータが新たに入っていますが、これは、ずらしたセルも含め12行1列を範囲指定するって意味です。

安値も全く一緒で、MAX関数がMIN関数に変わってOFFSET関数の基準セルが変わるだけで、1992年の安値セルD2は以下のようになります。

=MIN(OFFSET(四本値!$D$2,(ROW()-2)*12,0,12,1))

あとは、高値も安値も2015年までオートフィルで引っ張って、年足データの完成です。(^^)

3枚目のPIVOTのシートは、単純なPIVOTの計算式が入ってるだけなので、ダウンロードしてご覧ください。(^^;

そして、先程の年足データの横には、ずらっと数字が並んでいますが、こちらは、チャートにPIVOTのラインを入れるために作ってるだけで、PIVOTのシートの値を年数分いれてあるだけです。(^^;

これで、チャートを作る準備が出来たので、いよいよチャートを作ります。
まず、下の図のように、年足データの四本値だけを範囲指定して、エクセルのグラフの挿入でグラフの種類を株価にして次へをクリックし、後は通常のグラフ作成と一緒です。グラフが出来たら、X軸やY軸、チャートの見栄え等々を整えて出来上がりです。(^^)

また、今年の途中や来年にはデータを足していく事になるわけですが、それによって、年足データの範囲が増えますし、来年になれば、PIVOTも変更することになります。その際、グラフの上で右クリックし、元のデータを開き、以下の図のように、系列タブで、四本値、PIVOT関連、「X項目軸ラベルに使用のデータ範囲」を変えてもらえれば、毎年、データを足して使っていける事になりやす。(^^)