見出し画像

エクセルで日本株の最新株価を自動更新する方法

FIRE中年si｜高配当日本株のんびり投資家

ども、ぶしょうヒゲもサマになってきた40代なかば、FIRE中年siです

エクセルで保有株を管理している人も多いと思います（僕もそうです
エクセルで株を管理すれば、フィルターにソートにとエクセルの超便利機能が使える一方、最大のネックになるのが最新株価への更新だと思います

手動で更新している？そんなあなたに朗報です！

今日は、エクセルVBAを使って、自動で株価取得する方法を解説します
エクセル使っている人ならすぐに使えるようなシンプルなプログラムにしました
（オリジナルのシートへの組み込みも、サンプルからのカスタムも抵抗なくできると思います

2025年1月7日更新
Yahooファイナンスのフォーマットが変更されていたので、動作するように修正しました
動いた／動かなかった等、コメントやスキで意思表示してくれると励みになります


サンプルプログラムのダウンロード

まずはサンプルをダウンロードして動きを確かめてみましょう

ダウンロードした「株価自動取得マクロ.xlsm（最後の拡張子は表示されない場合があります）」ファイルを、デスクトップやドキュメントなどのお好きな場所にコピーします

最初に、ダウンロードしたファイルを右クリックして「プロパティ」からセキュリティを許可します
ウイルスなど悪意のあるプログラムが混入していないことは、作者である僕が保証しますので安心してください

ツールを起動すると、下図のようにセキュリティの警告が表示される場合があります
これは、ダウンロードしたファイルに特殊なプログラムが組み込まれている場合の警告文です。
本ツールではYahooファイナスから株価情報を取得するのにプログラムを使ってますので、「コンテンツの有効化」ボタンを押してプログラムを有効にしてください。

使い方

サンプルプログラムの使い方は簡単！
コード、保有数、取得単価は手動で入力して、株価の列にある「更新ボタン」を押すだけで、最新株価や、PER、PBR、配当を取得してきます

⇩⇩⇩

銘柄・・・株の銘柄名。空セルなら自動で入ります
コード・・・株の証券コード。手動で入れてください
保有数・・・保有株数。手動で入れてください
取得単価・・・株の買値。手動で入力してください
取得額・・・株の購入額。自動で計算されます
株価・・・最新株価。自動で取得します
評価額・・・最新の株の評価額。自動で計算されます
評価損益・・・評価額と、評価額の差。自動で計算されます
保有割合・・・ポートフォリオの評価額に対する銘柄の評価額。自動で計算されます
PER・・・PER。自動で取得します
PBR・・・PBR。自動で取得します
配当・・・一株配当。自動で取得します

※初回更新時にエラーが出ることがあります（出たり出なかったりで、プログラマー泣かせのバグ・・・
直せた方はコメントください・・・サンプルプログラムを更新します

プログラム解説

プログラムは、Yahooファイナンスに接続して株価情報を取得する「株式情報クラス」と、更新ボタンが押された時に起動して、株式情報クラスをキック、取得した株価をセルに入力する「呼び出し部」のプログラムから構成されます

株式情報クラス

株式情報クラスは、証券コードを引数で取得しYahooファイナンスから株価情報を取得します

コードは、分割しながらすべて記載しますが、まとめて使いたいときは、ダウンロードしたサンプルプログラムからエクスポートすると早いです

クラスのプライベート変数など

Option Explicit

' Yahooファイナンスから株式情報を取得するクラス

' 参照設定で「Microsoft HTML Object Library」をチェックする

' オブジェクト
Private httpObj As Object ' HTTP通信用オブジェクト
Private htmlDoc As Object ' HTMLドキュメント

Private regex2 As Object

Private 銘柄 As String
Private 株価 As String
Private PER As Single
Private PBR As Single
Private 配当 As Single

コンストラクタ／デストラクタ

' コンストラクタ
Public Sub Class_Initialize()
    Set httpObj = CreateObject("MSXML2.ServerXMLHTTP")
    Set htmlDoc = New HTMLDocument
    
    ' 正規表現で文字列を取得する
    Set regex2 = CreateObject("VBScript.RegExp")
    regex2.Pattern = "([0-9,\.]+)[^0-9]+([0-9\-]{1,4}[/:][0-9\-]{1,2})"
End Sub


' デストラクタ
Public Sub Class_Terminate()
    Set httpObj = Nothing
    Set htmlDoc = Nothing
    Set regex2 = Nothing
End Sub

オブジェクトの宣言、開放を行ってます
正規表現は、ちょっと入り組んだ文字列から目的の値を取得するために複雑になっています

メイン処理①（Yahooファイナンスへアクセス）

' 株価情報を取得して、データを取得する
Public Sub get株式情報(StackCode As String)
    
    Dim url As String
    url = "https://finance.yahoo.co.jp/quote/" & StackCode & ".T"
    
    
    httpObj.Open "GET", url
    httpObj.send

    ' readyState=4で読み込みが完了
    Do While httpObj.readyState < 4
        DoEvents
    Loop

    Dim statusCode As Integer
    statusCode = httpObj.Status

    Dim PostContents As String
    ' HTTPのステータスコードが200(OK)以外であれば、ステータスコードなどを返す。
    If (statusCode = 200) Then
        PostContents = httpObj.responseText
    Else
        ' エラー処理
        Exit Sub
    End If

証券コードからYahooファイナンスのURLを生成し、接続しています
接続に成功した場合は、取得したレスポンス（HTML文章）をPostContentsに取得します
エラー処理は作ってませんね（笑い

メイン処理②（データ取得）

    ' データ取得開始
    Call htmlDoc.write(PostContents)
    DoEvents 'OSに処理を戻してDOMを完成させる
    
    銘柄 = htmlDoc.getElementsByTagName("h2")(1).innerText
    株価 = htmlDoc.getElementsByClassName("StyledNumber__value__3rXW")(0).innerText
    
    Dim i As Long
    Dim li As Object
    Dim matche As Object
    For i = 0 To htmlDoc.getElementsByTagName("li").Length - 1
        Set li = htmlDoc.getElementsByTagName("li")(i)
        
        If InStr(li.innerText, "1株配当") Then
            Set matche = regex2.Execute(li.innerText)
                        
            If matche.Count > 0 Then 配当 = matche(0).submatches(0)
            
        ElseIf InStr(li.innerText, "PER") Then
            Set matche = regex2.Execute(li.innerText)
                        
            If matche.Count > 0 Then PER = matche(0).submatches(0)
            
        ElseIf InStr(li.innerText, "PBR") Then
            Set matche = regex2.Execute(li.innerText)
                        
            If matche.Count > 0 Then PBR = matche(0).submatches(0)
            
            Exit For
        End If
    Next i

End Sub

ここは掘り下げます

Call htmlDoc.write(PostContents)
DoEvents 'OSに処理を戻してDOMを完成させる

メイン処理①で取得したPostContents（HTML文章）からDOMを作成します
DOMにすることで、文書の操作が楽になります

銘柄 = htmlDoc.getElementsByTagName("h2")(1).innerText
株価 = htmlDoc.getElementsByClassName("StyledNumber__value__3rXW")(0).innerText

作成したDOMから、銘柄と株価を取得します
getElementsByTagName・・・DOMから”タグ名”を指定して、該当する配列を取得します
getElementsByClassName・・・DOMから”クラス名”を指定して、該当する配列を取得します

銘柄

以下は銘柄の表示と、該当するHTML部分です
銘柄は＜h2＞タグで表示されているようなので、getElementsByTagNameで取得しています

ただしh2が複数あるようで、VBAのウォッチウィンドウで確認した所、取得した配列の2番目に銘柄が格納されていました

配列の2番目の要素を示す（１）を指定して銘柄を取得しました

株価

以下は株価の表示と、該当するHTML部分です
株価は「StyledNumber__value__3rXW」クラスで表示されているようなので、getElementsByClassNameで取得しています
幸いにも、配列の最初の要素が該当していたので、最初の要素を指定して価格を取得します

配当／PER／PBR

For i = 0 To htmlDoc.getElementsByTagName("li").Length - 1
　　Set li = htmlDoc.getElementsByTagName("li")(i)

　　If InStr(li.innerText, "1株配当") Then
　　　　Set matche = regex2.Execute(li.innerText)
　　　　If matche.Count > 0 Then 配当 = matche(0).submatches(0)
　　ElseIf InStr(li.innerText, "PER") Then
　　　　Set matche = regex2.Execute(li.innerText)
　　　　If matche.Count > 0 Then PER = matche(0).submatches(0)
　　ElseIf InStr(li.innerText, "PBR") Then
　　　　Set matche = regex2.Execute(li.innerText)
　　　　If matche.Count > 0 Then PBR = matche(0).submatches(0)
　　　　Exit For
　　End If
Next i

指標系の値は、＜li＞タグで連続して表示されているので、li要素をループさせ、各々キーワードで該当箇所を引っ掛けてから、正規表現で文字を抜き出しています

以下は、例としての配当の表示と、該当するHTML部分です

li要素内にはゴチャゴチャ書いてありますが、innerTextをみると、

「1株配当（会社予想）用語　74.00　(2023/12)」
という文字列になるので、正規表現で配当の部分だけを取得しています
どのli要素も同じ構成になっているので、単一の正規表現で対応可能でした

以下が正規表現で対比させた該当部分です

正規表現は、単体のテーマで扱っても奥が深いので、興味があれば自分で調べてくださいね

呼び出し部

呼び出し部は、更新ボタンで起動し、株式情報クラスを起動して値を取得、セルに入力する処理を担当します

Option Explicit

Sub 更新_Click()
    Dim 証券コード As String
    
    Dim 株式情報 As 株式情報クラス
    
    ' 日付を入れる
    Cells(2, 7) = "株価（" & Date & "）"
    
    ' 株価更新ループ
    Dim 行番号 As Long
    For 行番号 = 3 To 100 ' 3行目から100行目まで確認
        証券コード = Cells(行番号, 3)
        
        If 証券コード = "" Then Exit For '空白行で終了
        
        'データクリア
        Cells(行番号, 7) = ""      ' 株価
        Cells(行番号, 12) = ""     ' PER
        Cells(行番号, 13) = ""     ' PBR
        Cells(行番号, 14) = ""     ' 配当
        
        ' 株式情報を読み込む
        Set 株式情報 = New 株式情報クラス
        株式情報.get株式情報 証券コード
        
        
        'データ入力
        If (Cells(行番号, 2) = "") Then Cells(行番号, 2) = 株式情報.get銘柄
        Cells(行番号, 7) = 株式情報.get株価
        Cells(行番号, 12) = 株式情報.getPER
        Cells(行番号, 13) = 株式情報.getPBR
        Cells(行番号, 14) = 株式情報.get配当
                
                
        ' オブジェクトの開放
        Set 株式情報 = Nothing
    Next


End Sub

3行目から100行目までループして、証券コード読み込んで株式情報クラスを起動しています

カスタムするときや、オリジナルのシートに使うときは、行番号、列番号に注意してください

問題

ここまで、株価の自動更新プログラムについて紹介してきましたが、Yahooファイナンスにアクセスして情報を取得する処理は、Yahooファイナンスの表示形式が変更になった瞬間からまったく動かなくなります

紹介してきたやり方を理解できれば、新しい表示形式に対応することもできると思いますが、もうひとつ重大な問題として、

https://support.yahoo-net.jp/PccFinance/s/article/H000011276

ということで、身も蓋もないですが、ここまで紹介してきた手法は、あくまで勉強用ということで使用してください
他のスクレイピングOKなサイトから、同じ方法で情報を取得できるはずです

株管理ツールのご紹介

今回のサンプルプログラムとは別に、siが本気で作った株管理ツールがあります
情報収集はサーバ側で担当しており、Yahooファイナンスのようなスクレイピングを禁止しているようなサイトからではなく、多くのソースから情報を収集しているので、問題なく使用していただけます

また、機能も充実しており、株価の自動収集はもちろん、ポートフォリオ分析など、個人投資家がほしい情報を視覚的にわかりやすく表示しています

10銘柄までは無料で使えるので、エクセルで株を管理している人、エクセルで株を管理したい人は一度ダウンロードして使ってみてください

⇩コチラの記事で詳しく紹介しています

機能の一例として、各指標のランキングでポートフォリオの状況を確認できます

⇩ポートフォリオの概要

使えば良さが分かります！
まずはダウンロードをお願いします

いいなと思ったら応援しよう！