東証株 - 今日の株価を取得するエクセルマクロ(Excel VBA) | エクセルマクロ(VBA)実践蔵(じっせんぐら)

トップ項目一覧

エクセルで東証株価を取得するマクロ

はじめに

Google Spread Sheetで取得することも可能ですが、あえてエクセルマクロ(Excel VBA)で今日の株価を取得するマクロを作ってみました。

Google Financeのサイトへアクセスして、今日の株価(最新の値段)を取得します。

2023/6/6現在、Googleのrobots.txtより、スクレイピングの許可がでていることは確認しています。禁止されるように変更になった場合は、記事を取り下げます。

また、Google Finance側でサイトリニューアル等があると取得できなくなる可能性がありますので、その点はご了承ください。

IEは、環境構築の必要がなかったのですが、サポート終了のため、Edgeを使用しています。EdgeをVBAから操作するにあたり、「Seleniumのインストール」、「EdgeのWebDriver」、「.NET Framework 3.5」が必要になります。

この３つをインストール後に、Visual Basic Editorにて、参照設定「Selenium Type Library」が必要です。具体的な操作はこちら。

補足になりますが、インストールすることが難しい環境の場合は、グーグルスプレッドシートで株価を自動取得する環境を構築し、そのURLへエクセルの「データ取得機能（Webから）」を使って、データを取得する方法もあります。GASで株価を取得するには、「getPrice」や「getStockPrice」で調べてみてください。ただ、セルから関数を呼び出す方法はあまりおすすめできません。セルから呼び出す関数は、どこかのセルが変更されただけで、すべてのセル内で再計算処理が発生するため、無駄が多く、スクレイピング対象のサーバに負担をかけてしまいます。そのため、テーブル項目数分でループして、株価を取得し、セルに格納するGASコードを組む方法がおすすめです。そして、作成した関数に実行トリガを設定しておけば、自動でシート内の情報を更新してくれます。

あえて、エクセルでやりたいという方は、どうぞ進んでください。

エクセル表側の見た目

「株価取得」で赤枠内を更新します。銘柄名は、空欄の場合のみ更新します。マクロが参照するのは、B列の銘柄一覧になるため、あらかじめ、入力してください。

E列F列は、例として載せていますが、削除してもマクロへの影響はありません。E列以降に列を追加するのは可能です。取得単価や取得単価と今日の株価の比較列を作成しても良いと思います。

また、ネットワークを経由して情報を取得することから、処理時間がかかるため、進捗率を下記の位置に表示させるようにしています。(ネットワーク環境に依存しますが、8件の取得で20s程度かかりました。)

また、持ち株と監視株でシートをわける場合もあるかと思いますので、シートコピーに対応しています。具体的には、フォームコントロールのボタンではなく、ActiveXのボタンを使用することで、シート別に独立して操作可能です。

エクセルマクロの詳細について

シートオブジェクトに記載するコード

シートに依存する情報は、シートオブジェクトに記載します。

下記は、表の固定情報になります。表内の構造を変更したときに、修正箇所が少なくなるように記述しています。

 
'==================================== 
' 固定位置の設定 
'==================================== 
' ---------------------------- 
'  取得日セル Range型 
' ---------------------------- 
Function 取得日セル() As Range 
     
    Set 取得日セル = Range("D1") 
     
End Function 
 
' ---------------------------- 
'  項目開始行数 
' ---------------------------- 
Function 開始行数() As Long 
     
    開始行数 = 3 
 
End Function 
 
' ---------------------------- 
'  銘柄Code先頭セル Range型 
' ---------------------------- 
Function CodeTopRng() As Range 
     
    Set CodeTopRng = Range("B" & 開始行数) 
     
End Function 
 
' ---------------------------- 
'  表末尾の行位置(行数)を取得 
' ---------------------------- 
Function 最終行数() As Long 
 
    'テーブル末尾（銘柄Codeが空欄）まで 
 
    If CodeTopRng.Offset(1, 0).Value <> "" Then 
        '2つ以上銘柄がある場合 
        最終行数 = CodeTopRng.End(xlDown).Row 
    Else 
        '先頭が最終行 
        最終行数 = CodeTopRng.Row 
    End If 
     
End Function 
 
' ---------------------------- 
'  銘柄Code列のセルすべて 
' ---------------------------- 
Function Code列Rngs() As Range 
     
    '開始行～最終行までのB列情報を返す 
    Set Code列Rngs = Range("B" & 開始行数 & ":B" & 最終行数) 
     
End Function 
 
' ---------------------------- 
'  銘柄名列のセルすべて 
' ---------------------------- 
Function 銘柄名列Rngs() As Range 
     
    '開始行～最終行までのC列情報を返す 
    Set 銘柄名列Rngs = Range("C" & 開始行数 & ":C" & 最終行数) 
     
End Function 
 
' ---------------------------- 
'  株価列のセルすべて 
' ---------------------------- 
Function 株価列Rngs() As Range 
 
    '開始行～最終行までのD列情報を返す 
    Set 株価列Rngs = Range("D" & 開始行数 & ":D" & 最終行数) 
     
End Function 
 

よく呼び出すものを関数化させて、メインの処理でのコード量が短くなるようにしています。step実行で追いにくいため、ステップオーバーのShift+F8がおすすめです。

ActiveXボタン「株価取得」クリック時のメソッド

今回は、シートコピーで複製対応ができるようにActiveXボタンを使用します。

ActiveXボタンのオブジェクト名を「株価取得」に変更することで、下記のメソッドが呼び出されます。ActiveXボタンは、オブジェクト名と連動して動作するため、環境にあわせてください。

上図プロパティ内のCaptionが、ボタンに表示される文章です。

ひきつづき、シートオブジェクトに記述します。

 
' -------------------------------- 
'シートの「株価取得」ボタンで呼び出される 
' -------------------------------- 
Private Sub 株価取得_Click() 
 
    '自作クラスJPNKabuの生成(New)を付ける 
    Dim cIE As New JPNKabu 
    '参照セル 
    Dim aCell As Range 
    '取得数（進捗表示用） 
    Dim getStockCnt As Long 
     
    '取得日をセット 
    取得日セル.Value = Now 
     
    '取得数の初期化 
    getStockCnt = 0 
     
    '進捗率を表示 
    Application.StatusBar = "ただいま実行中：" & getStockCnt & "/" & Code列Rngs.Rows.Count 
     
    For Each aCell In Code列Rngs 
     
        'JPNKabuクラスに銘柄code情報を渡す 
        Call cIE.SetCode(aCell.Value) 
               
        '銘柄セルが空欄か確認   
        If Cells(aCell.Row, 銘柄名列Rngs.Column).Value = "" Then 
             
            '空欄なら設定する 
            Cells(aCell.Row, 銘柄名列Rngs.Column).Value = cIE.get銘柄 
 
        End If 
         
        '株価の列に株価を設定 
        Cells(aCell.Row, 株価列Rngs.Column).Value = cIE.get株価 
         
         
        '取得カウンタ 
        getStockCnt = getStockCnt + 1 
         
        '進捗率を表示 
        Application.StatusBar = "ただいま実行中：" & getStockCnt & "/" & Code列Rngs.Rows.Count 
         
    Next 
     
    '終了を表示 
    Application.StatusBar = "完了!" 
     
    Set cIE = Nothing 
     
End Sub 

東証株価取得コードの解説

Edgeを操作して、データを取得する部分はJPNKabuクラス内に記述しています。

このSub関数で行っていることは、JPNKabuクラスを使用して、銘柄CodeをSetCodeメソッドでクラスへ情報を渡して、銘柄名を格納するセルが空欄なら、get銘柄メソッドによって銘柄名を取得してセルに格納。その後、株価を取得し、セルに格納して、進捗状況を表示させています。

また、テーブルの配置が変わった場合に、コードのいろいろなところを修正しないといけなくなるとたいへんなので、テーブルの配置に依存するコードは関数化しています。そのため、このコードだけをみると見づらいとは思いますが、下記に読み替えてください。

「取得日セル」は、Range("D1")、「Code列Rngs」は、Range("B3:B10")、「銘柄名列Rngs」は、Range("C3:C10")になります。

「取得日セル」には、実行時の日付と時間情報を格納しています。

Cells(行数,列数)メソッドを使用しています。行数は、Forループで動く、セル(aCell)の行数です。

新規クラスモジュール　JPNKabuを作成

VBA Project - エクスプローラ内で右クリックして、挿入を選び、クラスモジュールを作成します。

クラスのオブジェクト名は、プロパティウィンドウからJPNKabuに変更します。このオブジェクト名が株価取得_Click内に記述した「Dim cIE As New JPNKabu」とつながっています。

JPNKabuクラスのSetCodeメソッド

JPNKabuクラス内に下記を記述していきます。

 
'参照設定「Selenium Type Library」 
 
Private myEdge As New EdgeDriver 
 
Function SetCode(銘柄code As String) 
    
    Dim URLStart As String 
    Dim URLEnd As String 
     
    '画面遷移を抑止 
    myEdge.SetCapability "ms:edgeOptions", "{""args"": [""headless""] }" 
     
    'Edge操作開始 
    myEdge.Start 
     
    '引数の銘柄codeも使って、株価取得URLを作成 
    URLStart = "https://www.google.com/finance/quote/" 
    URLEnd = ":TYO?hl=ja" 
     
    'URL設定 
    myEdge.Get (URLStart & 銘柄code & URLEnd) 
 
End Function 
 

JPNKabuクラスのSetCodeメソッドの解説

引数で渡された、銘柄Codeを使用して、URLを作成して、ネットワークへアクセスする準備をしています。JPNKabuクラス内でEdgeDriverクラス型のmyEdge変数を共有しています。

JPNKabuクラスのget銘柄メソッド

 
Function get銘柄() As String 
     
    Dim element As WebElement 
    Dim str As String 
     
    Set element = myEdge.FindElementByCss(".zzDege", , False) 
         
    str = element.Text() 
     
    get銘柄 = str 
 
End Function 

JPNKabuクラスのget銘柄メソッドの解説

"zzDege"は、Google Financeのページで銘柄が記述されているClass名です。

実際にウエブページを開いて右クリックしてソースの表示を行い、"zzDege"を検索してみると様子が見れます。

コードに(.)記号が先頭についているのは、EdgeDriverの仕様です。

JPNKabuクラスのget株価メソッド

 
Function get株価() As Double 
     
    Dim element As WebElement 
    Dim str As String 
    
    Set element = myEdge.FindElementByCss(".YMlKec.fxKbKc", , False) 
    str = element.Text() 
    str = Replace(str, "￥", "", , , vbTextCompare) 
    str = Replace(str, ",", "", , , vbTextCompare) 
     
    If IsNumeric(str) Then 
        get株価 = CDbl(str) 
    Else 
        get株価 = 0 
    End If 
     
End Function 
 

JPNKabuクラスのget株価メソッドの解説

銘柄同様、"YMlKec fxKbKc"は、Google Financeのページで株価が記述されているClass名です。

コードに(.)記号が先頭とスペース位置についているのは、EdgeDriverの仕様です。

株価を数値(小数の数字)として取得するために、\マークの削除や,カンマの削除をしています。

取得失敗時は、get株価メソッドは、0を返す仕様としています。

ここまでで、エクセルマクロを実行した当日の株価を取得することができるようになります。

このエクセルマクロをベースに、データベースへ情報を追加しているようなコードも書いていこうと思っていますが、このページはここまでにします。

• 関連記事
• 持ち株の損益グラフを作成するマクロ

エクセルマクロ有効ブックのダウンロード

IEは、環境構築の必要がなかったのですが、サポート終了のため、Edgeを使用しています。EdgeをVBAから操作するにあたり、「Seleniumのインストール」、「EdgeのWebDriver」、「.NET Framework 3.5」が必要になります。

この３つをインストール後に、Visual Basic Editorのメニューバー「ツール」から参照設定を選択し、参照設定「Selenium Type Library」にチェックをつける必要があります。参考：参照設定方法

その他の、環境構築の参考URLです。外部サイトになります。

補足だけ記述すると、webDriverのVersion選択時に使用中のEdge Versionが「バージョン 114.0.1823.41」なのに対して、完全一致のwebDriverが見当たらず、「114.0.1823.18」を選んでインストールしましたが、動作はできました。

ダウンロードしたエクセルファイルを起動し、コンテンツの有効化を行う。

マクロの有効化手順については、こちら「マクロの初期設定と基本」も参考にしてください。

今日の株価を取得するエクセルマクロファイル(xlsm)のダウンロード