株や投資信託の日次データは使いにくい
株や投資信託について記事を書く際に日次の価格データしか手に入らない場合があります。そういった時にエクセルを使って簡単に月次データに変換する方法を紹介します。
日次データを月次データに変換する方法
実際にS&P500指数の１０年間の配当込トータルリターンの日次データを使って変換してみます。
日次データを用意
データはS&P ダウ・ジョーンズ・インデックス - S&P 500より１０年分２５３８日文をダウンロードしました。単純な指数データであればダウンロードできるサイトはいくつかありましたが、配当込のトータルリターンとなると私はこのサイトしか見つけられませんでした。
A列が年月日、B列がトータルリターン指数となっています。
月末の価格にフラグを立てる
次に月末の価格にフラグを立てます。C２のセルに次の関数を入力し下方にオートフィルドラッグします。
=IF(A2="","",IF((MONTH(A2)<>MONTH(A3)),ROW(),""))
↑こんな感じです。
そうすると下の画像のように毎月の最終取引日の価格のC列にフラグが立ちます。フラグはエクセルの行番号が入る設定になっています。
２００７年の９月は最終取引日が２８日ですが、ちゃんと２８日にフラグ「６５」が立っています。
フラグが立ったデータのみを抽出
次にフラグが立っている月末データのみを抽出します。 セルD２に次の関数を入力します。要となるのはINDEX関数とSMALL関数となります。
=IF(COUNT($C:$C)<ROW(A1),"",INDEX(A:A,SMALL($C:$C,ROW(A1))))
E列にフィルドラッグでコピーし、下方向にもオートフィルドラッグすると完成です。
今回の関数をうまく使えば、月初だけ、年末だけ、特定の月末だけといった抽出も可能となります。
使用した関数を詳しく解説
今回使った関数を詳しく解説します。不要な方は読み飛ばしてください。
フラグを立てる関数
=IF(A2="","",IF((MONTH(A2)<>MONTH(A3)),ROW(),""))
「=IF(A2="",""」は日付の部分が空白""ならば、空白""をC2に返す関数です。
日付が空白でなければ、次の関数を実行します。
「IF((MONTH(A2)<>MONTH(A3))」はA２とA３の日付から月の値を比較しています。不等号を並べた「<>」はA2とA3の月の値が異なるという意味です。
「ROW()」はA2とA3の月の値が異なった場合、つまり月末の値である場合にその行番号を返す関数です。
フラグが立ったデータを抽出する関数
=IF(COUNT($C:$C)<ROW(A1),"",INDEX(A:A,SMALL($C:$C,ROW(A1))))
「SMALL($C:$C,ROW(A1))」はC列に立てたフラグの中から、ROW（N）番目に小さい順位の数字を返します。セルD２ならROW(A1)は１になるので、１番小さいフラグの数字「２」が返ってきます。
「INDEX(A:A,SMALL($C:$C,ROW(A1))」はA列の上から「SMALL($C:$C,ROW(A1))」番目の数字を引っ張ってきます。セルD２にはA列の上から２番目、つまり「2007/6/29」が 入ります。
「=IF(COUNT($C:$C)<ROW(A1),""」はC列に立てたフラグを個数を数えます。それが関数の入ったセルより小さくなると空白を返します。直近の月末で計算を終わらせるための関数となります。
あわせて読みたい関連記事
日次・月次・年次データがあれば、投資先のリターンとリスクは簡単にエクセルで計算できます。その方法を解説しています。
