株価のような１つの時系列データを週毎の系列データに変換するVBAコード

株価や投資信託の価格推移をいろんなHPで入手することができます。

ふと、それらの株価をエクセルで折れ線グラフにプロットさせる時に、週毎に色を変えて表示させたいなと思ったことがありました。

イメージとしては↓

これを↓みたいに

（出典：eMAXIS_先進国投資型ファンドの価格推移）

週毎に価格数値が入った列をスライドさせることで系列を変更（新たな系列を作成する）できるようになるわけですが、数年分の株価データを手動でポチポチと操作すれば日が暮れてしまいます。

【週ごとに系列を新規作成する例】

↓こういう風にスライドさせる

そこで今回はタイムスタンプから曜日のインデックスを取得→VBAでデータを整理することで週毎に系列を新規作成する手順を共有します。データの量によってはかなり手順が省けるようになると思います。

１．タイムスタンプから曜日インデックスを取得する

なお今回はeMAXISのファンドの価格情報を使用しています。↓

ｅＭＡＸＩＳ Ｓｌｉｍ 先進国株式インデックス | ｅＭＡＸＩＳ

エクセルシートに移すと以下のようになります。

ここで、Excelのセル内にある日付を曜日名に変換するには２通りの方法があります。

　a.　=TEXT(セル名, "aaaa")
　b. 　=WEEKDAY(セル名,[種類])

a.の方法では日本語対応だとそれぞれ「月曜日、火曜日...」という値がかえってきますが、
b.を使うと曜日の判定が月曜日の場合＝1、火曜日の場合＝2...といったように数値で返ってきます。

後ほどこの曜日判定を使ってＶＢＡで処理をしなければならないので、今回は後者を使います。

これを最後の列までコピペします↓

２．VBAのロジックを考える

ここで、VBAコードのロジックを考えます。

今回は「週が新しくなるごとに新しい行へ価格情報をスライドさせる」が命題になるので、まず週が新しくなった事を知る方法が必要になります。

先ほどExcelシート内でWeekday関数を使って曜日を数字に変換したので、それを使用します。週が新しくなると数字が若くなるので、VBA的にこれを表すと、（セルの位置等は↑画像を参考）

Dim i As Long
i = 1

For i = 1 to データの数

    If Cells(i + 2, 3).Value < Cells(i + 1, 3).Value Then
        '週が変わった！'
        'データをさらに1行横へスライドさせる処理を行う'
    Else
        '週は変わってない'
        '一日前と同じ行の位置までスライドさせる処理を行う'
    End If
    
Next

となります。

このロジックにのっとって最終的なコードを以下に記します。

Sub week_slider()

Dim i As Long
Dim Colnum As Integer
Dim Pricecol As Integer

i = 1
Colnum = 3 'WeekdayIndexがある行を指定'
Pricecol = 4　'次に価格情報があるべき行を指定。週が変わるごとに＋１します'


For i = 6 To 865　'データが865個あったので'

   If Cells(i + 1, Colnum).Value < Cells(i, Colnum).Value Then '週が変わったとき'
       Pricecol = Pricecol + 1
       Cells(i + 1, Colnum + 1).Cut Destination:=Cells(i + 1, Pricecol)
   Else
       Cells(i + 1, Colnum + 1).Cut Destination:=Cells(i + 1, Pricecol)
   End If

Next
End Sub

これを実行すると↓のようになります。（２～３年分くらいデータがあったので何度もフリーズしてしまいました笑）

このシートを使えば、↓のように週ごとに色分けした価格推移を示せるようになります。

３．使い道

この手順を使えば、株価はもちろん先物価格を限月ごとに色分けした価格推移グラフも作成できるようになります。