読者です 読者をやめる 読者になる 読者になる

株主優待満喫~いつかは億り人を夢見て♪

優待三昧の生活を満喫しながら、あわてずのんびりコツコツと投資に励む日記です

エクセルで株価取得ソフト なんとか動作した^^

その他

この週末は特に大きな予定もなく時間があったので、いつかはやってみようと思っていたエクセルVBAでの株価取得を頑張ってみました。

f:id:ryuponn:20160508204202p:plain

上記、「株価更新」ボタンを押下して、背景黄色部分の「株価、1株配当、PBR、PER」を自動取得。データの取得元は、お馴染みのヤフーファイナンスで、下記、赤枠で囲った部分になります。

f:id:ryuponn:20160508205804p:plain

この手のソフトについては、ネット上にたくさんフリーソフトとしてアップされておりますので、私も今まではそれらの一つを利用しておりました。私は資産管理をエクセルで行っておりまして、そのフリーソフトで各種指標値を取得し、結果の部分をコピーして自分の管理しているエクセルへ貼り付けておりました。まあ、これでもそんなに手間ではないのですが、どうせならボタン一発で、直接更新できた方が便利かなということで、今回チャレンジ!あとは、エクセルVBAのお勉強も兼ねてます。

以下、出来上がったVBA Codeです。

Sub Click_株価更新()
    Dim http As Object
    Dim sh_control As Worksheet
    Dim st_url As String
    Dim st_code As String
    Dim i_check_row As Integer
    Dim i_max_row As Integer
    Dim i_code_column As Integer
    Dim i_kabuka_column As Integer
    Dim i_haito_column As Integer
    Dim i_pbr_column As Integer
    Dim i_per_column As Integer
    Dim st_search_value As String
    Dim reg_exp As RegExp
    Dim mc As MatchCollection
   
    ' ----------------------------------------
    ' [設定値] ★エクセルシートの内容に合わせてここを変更(A列=1)
    i_code_column = 3               ' 株価コードの列
    i_kabuka_column = 5             ' 現在株価の列
    i_haito_column = 8              ' 1株配当の列
    i_pbr_column = 15               ' PBRの列
    i_per_column = 16               ' PERの列
    i_max_row = 10000               ' 株価のチェックはこの行まで
    ' ----------------------------------------
   
    Set sh_control = ActiveSheet                    ' アクティブシートの設定
    Set reg_exp = CreateObject("VBScript.RegExp")   ' 正規表現オブジェクト生成
    Set http = CreateObject("MSXML2.XMLHTTP")       ' HTTPオブジェクト生成
   
    i_check_row = 1
    Do While i_check_row < i_max_row
        ' コード取得してURL設定
        st_code = sh_control.Cells(i_check_row, i_code_column)
       
        ' 対象外行(コード以外)についてはスキップする
        If Not IsNumeric(st_code) Then GoTo Continue
       
        If Len(st_code) <> 8 Then
            '国内株式のULR設定
            st_url = "http://stocks.finance.yahoo.co.jp/stocks/detail/?code=" & st_code & ".T"
        Else
            '投資信託のULR設定
            st_url = "http://stocks.finance.yahoo.co.jp/stocks/detail/?code=" & st_code
        End If
        
        ' HTMLデータを取得
        http.Open "GET", st_url, False
        http.setRequestHeader "If-Modified-Since", "Thu, 01 Jun 1970 00:00:00 GMT"
        http.Send
       
        If Len(st_code) <> 8 Then ' 国内株式
            ' [株価]
            ' 正規表現で文字列を抽出1
            reg_exp.Pattern = "stoksPrice"">.*</td>"
            Set mc = reg_exp.Execute(http.responseText)
            If mc.Count > 0 Then
                st_search_value = mc.Item(0)
                ' 正規表現で文字列を抽出2
                reg_exp.Pattern = "[0-9]+,?[0-9]+"
                Set mc = reg_exp.Execute(st_search_value)
                If mc.Count > 0 Then
                    ' 抽出した株価をエクセルシートへ反映
                    sh_control.Cells(i_check_row, i_kabuka_column) = CDbl(mc.Item(0))
                End If
            End If
            ' [配当]
            ' 正規表現で文字列を抽出1
            reg_exp.Pattern = "dps_2.*</a>"
            Set mc = reg_exp.Execute(http.responseText)
            If mc.Count > 0 Then
                st_search_value = mc.Item(0)
                ' 正規表現で文字列を抽出2
                reg_exp.Pattern = ">.*</a>"
                Set mc = reg_exp.Execute(st_search_value)
                If mc.Count > 0 Then
                    st_search_value = mc.Item(0)
                    ' 正規表現で文字列を抽出3
                    reg_exp.Pattern = "[0-9]+\.?[0-9]+"
                    Set mc = reg_exp.Execute(st_search_value)
                    If mc.Count > 0 Then
                        ' 抽出した配当をエクセルシートへ反映
                        sh_control.Cells(i_check_row, i_haito_column) = CDbl(mc.Item(0))
                    End If
                End If
            End If
            ' [PBR]
            ' 正規表現で文字列を抽出1
            reg_exp.Pattern = "<strong>.*\n.*PBR"
            Set mc = reg_exp.Execute(http.responseText)
            If mc.Count > 0 Then
                st_search_value = mc.Item(0)
                ' 正規表現で文字列を抽出2
                reg_exp.Pattern = "[0-9]+\.?[0-9]+"
                Set mc = reg_exp.Execute(st_search_value)
                If mc.Count > 0 Then
                    ' 抽出したPBRをエクセルシートへ反映
                    sh_control.Cells(i_check_row, i_pbr_column) = CDbl(mc.Item(0))
                End If
            End If
            ' [PER]
            ' 正規表現で文字列を抽出1
            reg_exp.Pattern = "<strong>.*\n.*PER"
            Set mc = reg_exp.Execute(http.responseText)
            If mc.Count > 0 Then
                st_search_value = mc.Item(0)
                ' 正規表現で文字列を抽出2
                reg_exp.Pattern = "[0-9]+\.?[0-9]+"
                Set mc = reg_exp.Execute(st_search_value)
                If mc.Count > 0 Then
                    ' 抽出したPBRをエクセルシートへ反映
                    sh_control.Cells(i_check_row, i_per_column) = CDbl(mc.Item(0))
                End If
            End If
        Else ' 投資信託
            ' [株価]
            ' 正規表現で文字列を抽出1
            reg_exp.Pattern = "stoksPrice"">.*</td>"
            Set mc = reg_exp.Execute(http.responseText)
            If mc.Count > 0 Then
                st_search_value = mc.Item(0)
                ' 正規表現で文字列を抽出2
                reg_exp.Pattern = "[0-9]+,?[0-9]+"
                Set mc = reg_exp.Execute(st_search_value)
                If mc.Count > 0 Then
                    ' 抽出した株価をエクセルシートへ反映
                    sh_control.Cells(i_check_row, i_kabuka_column) = CDbl(mc.Item(0))
                End If
            End If
        End If
Continue:
        i_check_row = i_check_row + 1
    Loop
End Sub

ヤフーファイナンスから該当銘柄ページのHTMLデータを取得して、正規表現で株価やPBRの文字列を抜き出している感じです。正規表現をあまりよく理解しておらず、かなり強引な方法で行っているので、もっと効率的な方法はあるんでしょうけど、とりあえず動作しているからOKとしようかな。。以下、作成メモです。もし、興味のある方がいらっしゃったらどうぞ♪こんな適当なソースコード、誰も興味ないですかね^^

 ◆株価取得エクセルの作成方法
[環境]
Office Excel2013

[手順]
1.開発タブの表示
・[ファイル] - [オプション] - [リボンのユーザ設定]で、右側の「開発」にチェックを入れる。

2.参照設定(正規表現)を追加する。
・[Alt] + F11キーを押下して、VBAの画面を表示する。
・[ツール] - [参照設定]で、「Microsoft VBScript Regular Expressions 5.5」にチェックを付ける。
・VBAの画面を閉じる。

3.株価更新ボタンの作成
・[開発タブ] - [挿入]でボタンを選択して、シート上にボタンを配置
・[マクロの登録]ウィンドウが表示されるので、任意のマクロ名(サンプルでは、"Click_株価更新")を設定して、[新規作成]を選択。
・VBAの画面が表示されるので、上記のソースコードをコピー。
・VBAの画面を閉じる。

上記で完成です。
エクセルシートのC列に、株価コードを入れて、手順3で追加したボタンを押下すると、
株価、1株配当、PBR、PERを自動で取得します。

[備考]
株価コードや株価などの位置(列)は、上記ソースコードの[設定値]の部分を修正することで変更可能です。