米国Yahoo Financeから米国ETFの時系列データをPythonにより取得する一方法の覚書

- 土曜日, 5月 01, 2021

今回はいつもの本家VT積立状況の報告記事ではありません。報告記事に用いるグラフ作成に関する記事です。前置きが長いですが、米国Yahoo Financeから米国ETFなどの時系列データを取得する際に気になった点の覚書です。

前置き

私事ですが、2021年1月から積立状況の月末報告記事に載せるグラフを、下図のように変更しました。

見ての通りですが、2020年12月までは月末時点のデータのみを用いてExcelでグラフを作成。一方、2021年1月以降は日毎のデータを用いてPythonでグラフを作成しています。

以前から月末の値のみの棒グラフに不満がありました。なぜなら、VT価格や為替の日々の値動きに応じた資産の変動を正確に表現できないからです。このため日毎のグラフへの切替を検討していました。そして、今年の1月に切替えてみたのです。

最初は日毎のグラフを今まで通りExcelで作成してみました。VBAを用いれば、米国のYahoo FinanceのサイトからVT価格と円/米国ドル価格の時系列データを簡単に取得可能です。WinHttp.WinHttpRequestなどを用いて時系列データのCSVファイルをウェブ上から取得すればよいのです。後は取得データを加工してグラフを描くだけ。特に難しい点はありません（文末の参考3にコード記載）。

余談ですが、Power Queryを利用すると更にデータ取得、結合が簡単にできました。しかし、残念ながら、基本機能のみで接続先アドレスの一部を実行日に応じて自動変更する方法が、私にはわかりませんでした。ただ、接続先のアドレスが固定の場合ならPower Queryが最も楽だと思います。

上述の通り、Excelで実現できたのですが、新鮮味がなく退屈でした。そんな時、ExcelではなくPythonでグラフを描いてみようという考えが頭に浮かびました。本当に不意に思い浮かんだのです。

過去記事で何度か触れましたが、私の勤務先では数ヵ月前からPythonの学習が推奨され始めました。と言っても、IT系企業でもなく、業務に必須ではありません。ただ、学習する強い動機付けがないながらも、暇な時に気まぐれで独習していました。

そんな折に生じた、ブログ用のグラフ作成をExcelからPythonに変更するという試みが、私には何となく楽しそうに感じたのかもしれません。そんなわけで、KindleでPythonの参考書を購入。足りない知識はネット情報を頼りに取組んでみた、というわけです。

未だ私のPythonに対する理解は初学者レベル止まりです。ですが、最低限意図したことは実現できました。現在ExcelファイルはVTの積立に関する取引や分配金などの生データを保存するためだけの存在になりつつあります。そして、投資に関するデータ一式を保存したExcelファイルを元に、Pythonでデータ統合や、時系列データ生成、グラフ作成などができるようになりました。取引の度にSBI証券からの取引報告書の情報をExcelに追記しておき、データ加工とグラフ作成はPythonで実施するという感じです。これについては別途記事にするかもしれません。

と、前置きはこんな感じです。

覚書

ここからが本題の覚書です。

なお動作環境はWindows10上、Anaconda 4.9.2で実行しています。

Pythonのコードは後述の通りです。Yahoo FinanceからVTの価格と円/米国ドル価格の時系列データを取得。各データを日付が一致するようにマージ。VTの価格と円/米国ドル価格から円換算のVTの価格を計算。そして、最後にグラフを作成するという内容です。

コピー＆ペーストすれば、円換算のVT価格のグラフがPNG画像として、時系列データがExcelファイルとして保存されます（文末の参考1と参考2に掲載）。冗長ですが1行1操作となるようブログ用に修正し、各行にコメントを記載しました。その他、もしもPythonやライブラリの機能に関する疑問が生じた場合は、必要に応じて検索願います。

なお、掲載したコードではVTの価格を取得していますが、Yahoo Financeにはその他の米国ETFの時系列データも充実しています。データ取得に用いるアドレスを必要に応じて変更すれば、VT以外のデータも取得可能と思います。所望の米国ETFの全期間の時系列データをブラウザから手動でダウンロードし、その時のCSVファイルのアドレスを確認してみてください。米国ETFの銘柄に対応する部分と、時系列データの最初の日の情報に対応する部分を確認できるはずです。

さて、ここからが本題です。今回の作業で私が気になった点を覚書として残します。覚書は以下の3つです。

私の自宅のAuひかり回線ではurllib.requestの失敗確率が高い
時系列データのアドレスにUNIXタイムスタンプが必要
時系列データの値の誤差

詳細はコードの後に記載しています。

# 米国ヤフーファイナンスからVTとJPY=Xのヒストリカルデータを取得
# 円換算のVT価格の推移のグラフを作成しデータを保存

# ライブラリ等インポート
import pandas as pd
import urllib.request
import datetime
import matplotlib.pyplot as plt

# グラフのフォント設定
from matplotlib import rcParams
rcParams['font.family'] = 'sans-serif'  
rcParams['font.sans-serif'] = ['Yu Gothic']


# 関数定義：ヤフーファイナンスからデータ取得
def get_yahoo_data(data_url):
    
    # データ取得成功/失敗フラグを真に設定
    flag = True
    
    # データ取得成功までループ
    while flag:

        # ヤフーファイナンスのヒストリカルデータを取得しデータフレーム化
        # except以下はエラー処理
        try:
            with urllib.request.urlopen(data_url) as response:
                data = pd.read_csv(response)
                flag = False
        except urllib.error.URLError as e:
            print('urllib.error.URLError: {}'.format(data_url))
            print(e)
        except Exception as e:
            print('Exception: {}'.format(data_url))
            print(e)
    
    # 日付データdatetime型変換
    data['Date'] = pd.to_datetime(data['Date'], format='%Y-%m-%d')

    # 日付列、終値列のみ抽出
    data = data[['Date', 'Close']]

    return data


# 関数定義：VTのヒストリカルデータを取得
def get_vt_data():

    # 本日UNIXタイムスタンプ取得
    UTS_NOW = str(int(datetime.datetime.now().timestamp()))

    # ヤフーファイナンスのVTのヒストリカルデータのアドレス
    vt_url = 'https://query1.finance.yahoo.com/v7/finance/download/VT?period1=1214438400&period2=' \
        + UTS_NOW \
            + '&interval=1d&events=history&includeAdjustedClose=true'  
    
    # ヤフーファイナンスからデータ取得
    data = get_yahoo_data(vt_url)

    # 列名変更
    data = data.rename(columns={'Date': '年月日', 'Close': 'VT終値（USD/口）'})
    
    return data 


# 関数定義：JPY=Xのヒストリカルデータを取得
def get_jpyx_data():

    # 本日UNIXタイムスタンプ取得
    UTS_NOW = str(int(datetime.datetime.now().timestamp()))

    # ヤフーファイナンスのJPY=Xのヒストリカルデータのアドレス
    jpyx_url = 'https://query1.finance.yahoo.com/v7/finance/download/JPY=X?period1=846633600&period2=' \
        + UTS_NOW \
            + '&interval=1d&events=history&includeAdjustedClose=true'
    
    # ヤフーファイナンスからデータ取得
    data = get_yahoo_data(jpyx_url)

    # 列名変更
    data = data.rename(columns={'Date': '年月日', 'Close': 'JPY=X終値（円/USD）'})

    return data 


# 関数定義：VTとJPY=Xのヒストリカルデータをマージ
def merge_vt_jpyx_data(vt_data, jpyx_data):

    # データを年月日でマージ
    data = pd.merge(vt_data, jpyx_data, on="年月日", how="left")
    
    # 欠損値行削除（vt_dataのデータなし、またはjpyx_dataのデータなし）
    data = data.dropna()

    # インデックス振直
    data = data.reset_index(drop=True)

    # VT終値(円/口)列を計算し追加
    data["VT終値（円/口）"] = data["VT終値（USD/口）"] * data["JPY=X終値（円/USD）"]
    
    return data


# 関数定義：グラフを画像として保存
def save_graph(data):

    # グラフの作成 16:9サイズ ※default: [6.4, 4.8]
    fig = plt.figure(figsize=[4.8*16/9, 4.8])
    ax = fig.add_subplot()

    # グラフをプロット
    ax.plot(data['年月日'], data['VT終値（円/口）'])
    
    # グリッド表示
    ax.grid()

    # タイトル設定
    ax.set_title('VT価格の時系列データ')
    
    # 横軸ラベル設定
    ax.set_xlabel('年月日')

    # 縦軸ラベル設定
    ax.set_ylabel('VT価格（円/口）')

    # 縦軸範囲設定
    ax.set_ylim(0,12000)

    # グラフの画像ファイル名
    graph_filename = 'VT価格_' + datetime.datetime.now().strftime('%Y%m%d_%H%M%S') + '.png'

    # グラフ画像の保存
    fig.savefig(graph_filename, dpi= 360, facecolor="white")
    
    return


# メイン処理の実行------------------------------

# VTのヒストリカルデータを取得
vt_historical_data = get_vt_data()

# JPY=Xのヒストリカルデータを取得
jpyx_historical_data = get_jpyx_data()

# VTとJPY=Xのヒストリカルデータをマージ
vt_jpyx_historical_data = merge_vt_jpyx_data(vt_historical_data, jpyx_historical_data)

# グラフを画像として保存
save_graph(vt_jpyx_historical_data)

# データをエクセルに出力
vt_jpyx_historical_data.to_excel('vt_jpyx_historical_data.xlsx')

# 終了

覚書1：私の自宅のAuひかり回線ではurllib.requestの失敗確率が高い

上のコードではget_yahoo_data(data_url)という関数でYahoo Financeから時系列データを取得します。この関数の引数は時系列データのCSVファイルのアドレスです。与えた引数のアドレスからurllib.requestでCSVファイルを取得し、pandasのデータフレームに変換します。urllib.requestは成功するとは限りませんので、try～exceptを用いてエラー処理しています。そして、成功するまでwhileループで再トライを繰り返します。ここまでは一般的な話です。

その上で、未だ原因不明の現象を確認しています。私の自宅のAuひかり回線では、上のurllib.requestの成功率が非常に低いという現象です。スマホのテザリング（某格安SIMのドコモ回線）、FujiWiFi（ソフトバンク回線）、JR東日本やビジネスホテルのフリーWiFiなどでは1回目で成功します。ところが自宅のAuひかり回線では成功するまで10回近くurllib.requestの失敗を繰り返します。

原因特定できず悩んでいます。ですが、私の力不足で進展がありません。

覚書2：時系列データのアドレスにUNIXタイムスタンプが必要

Yahoo Financeから時系列データを取得するために、時系列データのCSVファイルのアドレスが必要です。そして、そのCSVファイルのアドレスの一部に、取得データの日付範囲を指定するため最初と最後の日に対応した情報が必要です。この情報は日付のUNIXタイムスタンプを数値化したものです。

上のコードでは、VT価格、円/米国ドル価格ともに全ての過去データを取得しています。データの最初の日は固定です。VT価格は2008年6月26日（の9:00）に対応した『1214438400』、円/米国ドル価格は1996年10月30日（の9:00）に対応した『846633600』です。アドレスの文字列の『period1=』以下ですね。一方、データの最後の日はプログラム実行日になります。このため実行日のUNIXタイムスタンプを数値化した文字列を『UTS_NOW』として取得し、アドレスに組み込んでいます。こちらは『period2=』以下ですね。

覚書3：時系列データの値の誤差

Yahoo Financeから取得した時系列データは、ホームページの掲載値と誤差があり、小数点第六位まで続きます。例えば、ホームページ上の終値が101.31USD/口のはずが、時系列データでは101.309998USD/口となったりします。Yahoo Finance内の浮動小数点数の扱いや表示精度のルールは不明ですが、注意が必要です。誤差は十分小さく、個人投資家の保有数量程度では計算上の問題は起きないとは思いますが。

覚書4：時系列データに含まれる欠損値

Yahoo Financeから取得した時系列データには欠損値が含まれる場合があります。日付が存在しても価格などの値がNULLとなっている場合があるので注意が必要です。

参考1：作成されたグラフの画像

参考2：保存されたExcelファイル

参考3：最初にExcelで作成したVBA

こちらは最初にExcelで作成したVBAのコードです。データ取得とデータマージの部分のみ掲載します。最初の下書き状態のままで、全然整理されていません。本当にご参考です。長い行もそのままで見難いですが、ご容赦願います。

この次に二つの時系列データをセルに出力せず、配列なまマージさせる改善をするつもりでしたが、この時点でPythonの方に手を付けてしまったのでやっていません。

Option Explicit


' 関数定義：今のUNIXタイムスタンプを取得
Private Function get_unix_timestamp_now() As String
    get_unix_timestamp_now = CStr(DateDiff("s", "1970/1/1 9:00", Now))
End Function


' サブルーチン定義：新シート作成
Private Sub add_new_worksheet(ByVal worksheet_name As String)

    ' 名前重複有無の確認
    Dim flag As Boolean: flag = True
    Dim i As Worksheet
    For Each i In Worksheets
        If i.Name = worksheet_name Then flag = False
    Next

    ' 名前重複が無ければ新シート作成
    If flag Then
        Worksheets.Add Before:=Worksheets(1)
        Worksheets(1).Name = worksheet_name
    End If
    
End Sub


' サブルーチン定義：ヤフーファイナンスからデータ取得
Private Function get_yahoo_data(ByVal url As String) As String

    ' WinHttpRequest成功コード
    Const HTTP_STATUS_OK = 200
    
    ' WinHttpRequestオブジェクト
    Dim req As Object
    Set req = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    ' urlに接続
    With req
        .Open "GET", url, False
        .Send
        If .Status <> HTTP_STATUS_OK Then
            MsgBox "接続失敗"
            Exit Function
        End If
    End With
    
    ' ADODB.Streamオブジェクト ※文字コードケアのため
    Dim strm As Object
    Set strm = CreateObject("ADODB.Stream")
    
    ' 接続先のテキストを取得
    Dim data As String
    With strm
        .Open
        .Position = 0
        .Type = 1
        .Write req.ResponseBody
        .Position = 0
        .Type = 2
        .Charset = "_autodetect"
        data = .ReadText
        .Close
    End With
    
    ' オブジェクト解放
    Set strm = Nothing
    Set req = Nothing
    
    get_yahoo_data = data

End Function

' サブルーチン定義：VTのヒストリカルデータを取得
Private Sub get_vt_data()

    ' 新シート作成
    Dim worksheet_name As String: worksheet_name = "vt"
    Call add_new_worksheet(worksheet_name)

    ' 本日UNIXタイムスタンプ取得
    Dim UTS_NOW As String: UTS_NOW = get_unix_timestamp_now()

    ' ヤフーファイナンスのVTのヒストリカルデータのアドレス
    Dim vt_url As String: vt_url = "https://query1.finance.yahoo.com/v7/finance/download/VT?period1=1214438400&period2=" _
        & UTS_NOW & "&interval=1d&events=history&includeAdjustedClose=true"

    ' ヤフーファイナンスからデータ取得
    Dim data_all As String: data_all = get_yahoo_data(vt_url)
    
    ' CSVファイルの文字列データを行毎に分割
    Dim data_line() As String: data_line = Split(data_all, vbLf)
    
    ' 行毎に分割した文字列データ
    Dim data() As String

    ' 行に対するループ処理
    Dim i As Integer, j As Integer
    For i = LBound(data_line) To UBound(data_line) Step 1
    
        ' 行毎に分割した文字列データを更にコンマ区切りで分割
        data = Split(data_line(i), ",")
        
        ' コンマ分割データに対するループ処理、日付と終値のみシートに出力
        For j = LBound(data) To UBound(data) Step 1
            With Worksheets(worksheet_name)
                If i = 0 Then
                    ' 列タイトル
                    .Cells(1 + i, 1).Value = "年月日"
                    .Cells(1 + i, 2).Value = "VT終値（USD/口）"
                ElseIf i <> 0 And j = 0 Then
                    ' 年月日
                    .Cells(1 + i, 1).Value = Left(data(j), 4) & "/" & Mid(data(j), 6, 2) & "/" & Right(data(j), 2)
                ElseIf i <> 0 And j = 4 Then
                    ' 終値（j=4）のみ出力
                    .Cells(1 + i, 2).Value = data(j)
                End If
            End With
        Next j
    Next i

End Sub


' サブルーチン定義：JPY=Xのヒストリカルデータを取得
Private Sub get_jpyx_data()

    ' 新シート作成
    Dim worksheet_name As String: worksheet_name = "jpyx"
    Call add_new_worksheet(worksheet_name)

    ' 本日UNIXタイムスタンプ取得
    Dim UTS_NOW As String: UTS_NOW = get_unix_timestamp_now()

    ' ヤフーファイナンスのVTのヒストリカルデータのアドレス
    Dim vt_url As String: vt_url = "https://query1.finance.yahoo.com/v7/finance/download/JPY=X?period1=846633600&period2=" _
        & UTS_NOW & "&interval=1d&events=history&includeAdjustedClose=true"

    ' ヤフーファイナンスからデータ取得
    Dim data_all As String: data_all = get_yahoo_data(vt_url)
    
    ' CSVファイルの文字列データを行毎に分割
    Dim data_line() As String: data_line = Split(data_all, vbLf)
    
    ' 行毎に分割した文字列データ
    Dim data() As String
        
    ' 行に対するループ処理
    Dim i As Integer, j As Integer
    For i = LBound(data_line) To UBound(data_line) Step 1
    
        '行毎に分割した文字列データを更にコンマ区切りで分割
        data = Split(data_line(i), ",")
        
        ' コンマ分割データに対するループ処理、日付と終値のみシートに出力
        For j = LBound(data) To UBound(data) Step 1
            With Worksheets(worksheet_name)
                If i = 0 Then
                    ' 列タイトル
                    .Cells(1 + i, 1).Value = "年月日"
                    .Cells(1 + i, 2).Value = "JPY=X終値（円/USD）"
                ElseIf i <> 0 And j = 0 Then
                    ' 年月日
                    .Cells(1 + i, 1).Value = Left(data(j), 4) & "/" & Mid(data(j), 6, 2) & "/" & Right(data(j), 2)
                ElseIf i <> 0 And j = 4 Then
                    ' 終値（j=4）のみ出力
                    .Cells(1 + i, 2).Value = data(j)
                End If
            End With
        Next j
    Next i

End Sub


' サブルーチン定義：VTとJPY=Xのヒストリカルデータをマージ
Private Sub merge_vt_jpyx_data()

    ' シート名
    Dim worksheet_name_vt As String: worksheet_name_vt = "vt"
    Dim worksheet_name_jpyx As String: worksheet_name_jpyx = "jpyx"
    
    ' 新シート作成
    Dim worksheet_name As String: worksheet_name = "vt_jpyx"
    Call add_new_worksheet(worksheet_name)

    ' VTとJPY=Xのヒストリカルデータをマージ
    
    ' 列タイトル
    With Worksheets(worksheet_name)
        .Cells(1, 1).Value = "年月日"
        .Cells(1, 2).Value = "VT終値（USD/口）"
        .Cells(1, 3).Value = "JPY=X終値（円/USD）"
        .Cells(1, 4).Value = "VT終値（円/口）"
    End With
    
    ' データをマージ
    Dim i As Integer: i = 2
    Dim i_vt As Integer: i_vt = 2
    Dim i_jpyx As Integer: i_jpyx = 2
    Do While Worksheets(worksheet_name_vt).Cells(i_vt, 1).Value <> 0
        Do While Worksheets(worksheet_name_jpyx).Cells(i_jpyx, 1).Value <> 0
            If Worksheets(worksheet_name_vt).Cells(i_vt, 1).Value = Worksheets(worksheet_name_jpyx).Cells(i_jpyx, 1).Value Then
                If VarType(Worksheets(worksheet_name_vt).Cells(i_vt, 2).Value) = vbDouble _
                  And VarType(Worksheets(worksheet_name_jpyx).Cells(i_jpyx, 2).Value) = vbDouble Then
                    With Worksheets(worksheet_name)
                        .Cells(i, 1).Value = Worksheets(worksheet_name_vt).Cells(i_vt, 1).Value
                        .Cells(i, 2).Value = Worksheets(worksheet_name_vt).Cells(i_vt, 2).Value
                        .Cells(i, 3).Value = Worksheets(worksheet_name_jpyx).Cells(i_jpyx, 2).Value
                        .Cells(i, 4).Value = .Cells(i, 2).Value * .Cells(i, 3).Value
                    End With
                    i = i + 1
                End If
                Exit Do
            End If
            i_jpyx = i_jpyx + 1
        Loop
        i_vt = i_vt + 1
    Loop

End Sub


' メイン処理
Sub main()
    Call get_vt_data
    Call get_jpyx_data
    Call merge_vt_jpyx_data
End Sub

このブログを検索

さといもちゃんのブログ