Microsoft Excel VBA - Excel VBAで株価を取得する方法

◆概要

　Yahoo!ファイナスをはじめ、証券会社などのWEBページで株価を検索することができます。しかし、これをエクセルなどのアプリケーションで利用するには都合が悪いようです。XMLで記述されておらずHTMLで記述されていますから、特定の値を取得するのが難しいのです。また、WebAPIが提供されていないサイトに対してHTMLから直接情報を取り出す技術として、スクレイピングがあります。これには大きく分けて２つの方法があります。HTMLをテキストとみなし、正規表現で処理する方法と、HTMLをXHTMLに変換し、そのDOMに対してXPathで処理する方法です。このサイトで公開している「郵太」では利用しましたが、正規表現はなかなか難しく敷居が高いと思います。

◆銘柄を指定してHTMLを取得する

　ここでは、銘柄を指定してHTMLを取得し、VBAのInstr関数を利用して株価や前日比を取得します。株価のHTMLは、x Laboratryで提供されている株価のRSSを利用してさせていただきました。この場をお借りしてお礼申し上げます。

コードは以下のようになります。

Function GetStock(strStock As String) As String
' Summary:引数で指定された銘柄コードの株価を取得する。
' Written by White Tiger
' Date:2008/09/26
' Argument:strStock as string
' return value:Getatml as string
' cgi:http://www.xlabo.net/index.php?%B3%F4%B2%C1%A4%CERSS
' Get Value Web:Yahoo!ファイナンス

    Dim strURL As String    ' URL
    Dim oHttp As Object     ' オブジェクト
    Dim datHtml As String   ' 取得データ
    Dim strStart As String  ' 検索文字列の開始位置
    Dim strEnd As String    ' 検索文字列の終了位置
    Dim intStart, intEnd, intStrLength As Integer
    Dim strGet As String
    
    ' URLを指定
    strURL = "http://www.xlabo.net/cgi-bin/rss_stock.cgi?" + strStock
    ' オブジェクト生成し変数oHttpへ代入
    Set oHttp = CreateObject("Microsoft.XMLHTTP")
    ' openメソッドで、GET、False(同期通信)を設定
    oHttp.Open "GET", strURL, False
    ' サーバーから oHttpへ送信
    oHttp.send
    ' 受信データを変数datHtmlへ入れる
    datHtml = oHttp.responseText
    
    Set oHttp = Nothing
    
    ' 株価を取得する
    strStart = InStr(datHtml, "<stock:last_trade>")
    strEnd = InStr(datHtml, "</stock:last_trade>")
    
    ' strStart及びstrEndを整数型へ変換
    intStart = CInt(strStart)
    intEnd = CInt(strEnd)
    ' 取得する文字列の長さ
    intStrLength = intEnd - intStart - Len("<stock:last_trade>")
    
    ' 開始文字を18文字ずらす
    intStart = intStart + 18
    
    ' 文字列抜き出し
    strGet = Mid(datHtml, intStart, intStrLength)
    
    '関数の戻値を設定
    GetHTML = strGet

End Function

さて、ここでテストをしてみましょう。以下のコードを貼り付け、F5キーを押下してください。トヨタ自動車の今日の株価が表示されるはずです。

Sub test()
    Dim strKabuka As String
    strKabuka = GetStock("7203")
    MsgBox strKabuka
End Sub

◆株価情報を取得するアプリケーション

取得したHTMLをみると、会社名や前日比、出来高などが取得できそうです。これらを使って株式情報をエクセルのワークシートに取得するアプリケーションを作りましょう。

ワークシートの一行目に銘柄コード、市場、名称、取引日時、株価、前日比、騰落率、出来高という項目を入力してください。
A列に表示させたい銘柄のコードを入力しておきます。１件しか登録していないとオーバーフローしますので、２件以上登録してください。
以下のマクロを標準モジュールに張り付けます。

Public datHtml            As String   ' 取得データ
Public pub_strCompany     As String   ' 会社名
Public pub_strMrketPlace  As String   ' 上場市場
Public pub_strTradeTime   As String   ' 取引日時
Public pub_strStockPrice  As String   ' 株価
Public pub_strStockChange As String   ' 前日比
Public pub_strCangePercen As String   ' 騰落率
Public pub_strStockVolume As String   ' 出来高

Public strStart     As String  ' 検索文字列の開始位置
Public strEnd       As String  ' 検索文字列の終了位置
Public intStart, intEnd, intStrLength As  Integer

Sub GetStackInfo()
    Dim i As Integer
    Dim strStackSymbol As String
    
    On Error GoTo Err_getStakInfo
    
    Application.ScreenUpdating = False
    
    ' セルA2を起点にA列の最後まで繰り返す
    For i = 2 To Cells(2, 1).End(xlDown).Row
        ' public変数を初期化
        Call SetArgNothing
        strStackSymbol = Cells(i, 1).Value
        ' publc変数にセットする
        If GetHTML(strStackSymbol) = True Then
            Call GetCompanyName
            Call GetMarketPlace
            Call GetTradeTime
            Call GetStockPrice
            Call GetStockChange
            Call GetChangePercent
            Call GetVolume
        End If
        ' public変数を該当のセルに代入
        Cells(i, 2) = pub_strMrketPlace
        Cells(i, 3) = pub_strCompany
        Cells(i, 4) = pub_strTradeTime
        Cells(i, 5) = pub_strStockPrice
        Cells(i, 6) = pub_strStockChange
        Cells(i, 7) = pub_strCangePercen
        Cells(i, 8) = pub_strStockVolume
    Next i
    Application.ScreenUpdating = True
    
    MsgBox "株価を取得しました!" & vbCrLf & _
        "東証以外のものは別途確認する必要があります。", _
        vbOKOnly + vbInformation, "株価取得 From Yahoo!ファイナンス"

    Exit Sub

Err_getStakInfo:
    MsgBox Err.Number & Err.Description

End Sub
Function SetArgNothing()
' public変数を初期化する
    datHtml = ""
    pub_strCompany = ""
    pub_strMrketPlace = ""
    pub_strStockPrice = ""
    pub_strStockChange = ""
    pub_strCangePercen = ""
    pub_strStockVolume = ""
End Function
Function GetHTML(strStock As String) As Boolean
' Summary:引数で指定された銘柄コードのdatHTMLを取得する。
' Written by White Tiger
' Date:2008/09/27
' Argument:strStock as string
' Return:取得に成功ならTrue,失敗ならFalse
' cgi:http://www.xlabo.net/index.php?%B3%F4%B2%C1%A4%CERSS
' Get Value Web:Yahoo!ファイナンス

    Dim strURL As String    ' URL
    Dim oHttp As Object     ' オブジェクト
    
    On Error GoTo Err_GetHTML
    ' URLを指定
    strURL = "http://www.xlabo.net/cgi-bin/rss_stock.cgi?" + strStock
    ' オブジェクト生成し変数oHttpへ代入
    Set oHttp = CreateObject("Microsoft.XMLHTTP")
    ' openメソッドで、GET、False(同期通信)を設定
    oHttp.Open "GET", strURL, False
    ' サーバーから oHttpへ送信
    oHttp.send
    ' 受信データを変数datHtmlへ入れる
    datHtml = oHttp.responseText
    Set oHttp = Nothing
    GetHTML = True
    Exit Function

Err_GetHTML:
    GetHTML = False
    MsgBox Err.Number & Err.Description
End Function

Function GetMarketPlace()
' Summary:引数で指定された銘柄コードの上場市場名を取得し、
' public変数pub_strMrketPlaceに代入する。
' Written by White Tiger
' Date:2008/09/27
' Argument:Nothing
    
    ' 検索文字列の開始位置、終了位置を取得する
    strStart = InStr(datHtml, "<stock:market_place>")
    strEnd = InStr(datHtml, "</stock:market_place>")
    
    ' strStart及びstrEndを整数型へ変換
    intStart = CInt(strStart)
    intEnd = CInt(strEnd)
    ' 取得する文字列の長さ
    intStrLength = intEnd - intStart - Len("<stock:market_place>")
    
    ' 開始文字位置
    intStart = intStart + Len("<stock:market_place>")
    ' 文字列抜き出し
    pub_strMrketPlace = Mid(datHtml, intStart, intStrLength)

End Function

Function GetCompanyName()
' Summary:GetHTMLで取得したdatHtmlから会社名を取得し、
' public変数pub_strCompanyに代入する。
' Written by White Tiger
' Date:2008/09/27
' Argument:Nothing

    ' 検索文字列の開始位置、終了位置を取得する
    strStart = InStr(datHtml, "<stock:company_name>")
    strEnd = InStr(datHtml, "</stock:company_name>")
    
    ' strStart及びstrEndを整数型へ変換
    intStart = CInt(strStart)
    intEnd = CInt(strEnd)
    ' 取得する文字列の長さ
    intStrLength = intEnd - intStart - Len("<stock:company_name>")

    ' 開始文字位置
    intStart = intStart + Len("<stock:company_name>")
    
    ' 文字列抜き出しpublic変数pub_strCompanyに代入する
    pub_strCompany = Mid(datHtml, intStart, intStrLength)
    
End Function

Function GetTradeTime()
' Summary:GetHTMLで取得したdatHtmlから銘柄コードの取引日と時間を取得し、
' public変数pub_strTradeTimeに代入する
' Written by White Tiger
' Date:2008/09/27
' Argument:Nothing
    
    ' 検索文字列の開始位置、終了位置を取得する
    strStart = InStr(datHtml, "<stock:trade_time>")
    strEnd = InStr(datHtml, "</stock:trade_time>")

    ' strStart及びstrEndを整数型へ変換
    intStart = CInt(strStart)
    intEnd = CInt(strEnd)
    ' 取得する文字列の長さ
    intStrLength = intEnd - intStart - Len("<stock:trade_time>")

    ' 開始文字位置
    intStart = intStart + Len("<stock:trade_time>")
    
    ' 文字列抜き出しpublic変数pub_strTradeTimeに代入する
    pub_strTradeTime = Mid(datHtml, intStart, intStrLength)

End Function

Function GetStockPrice()
' Summary:GetHTMLで取得したdatHtmlから銘柄コードの株価を取得し、
' public変数pub_strStockPriceに代入する
' Written by White Tiger
' Date:2008/09/27
' Argument:Nothing
    
    ' 検索文字列の開始位置、終了位置を取得する
    strStart = InStr(datHtml, "<stock:last_trade>")
    strEnd = InStr(datHtml, "</stock:last_trade>")
    
    ' strStart及びstrEndを整数型へ変換
    intStart = CInt(strStart)
    intEnd = CInt(strEnd)
    ' 取得する文字列の長さ
    intStrLength = intEnd - intStart - Len("<stock:last_trade>")
    
    ' 開始文字を18文字ずらす
    intStart = intStart + 18
    
    ' 文字列抜き出しpublic変数pub_strStockPriceに代入する
    pub_strStockPrice = Mid(datHtml, intStart, intStrLength)
    '千円単位を示す","を除く
    pub_strStockPrice = Replace(pub_strStockPrice, ",", "")


End Function

Function GetStockChange()
' Summary:GetHTMLで取得したdatHtmlから前日比を取得し、
' public変数pub_strStockChangeに代入する
' Written by White Tiger
' Date:2008/09/27
' Argument:Nothing

    ' 検索文字列の開始位置、終了位置を取得する
    strStart = InStr(datHtml, "<stock:change>")
    strEnd = InStr(datHtml, "</stock:change>")

    ' strStart及びstrEndを整数型へ変換
    intStart = CInt(strStart)
    intEnd = CInt(strEnd)
    ' 取得する文字列の長さ
    intStrLength = intEnd - intStart - Len("<stock:change>")

    ' 開始文字を14文字ずらす
    intStart = intStart + 14
    
    ' 文字列抜き出し
    pub_strStockChange = Mid(datHtml, intStart, intStrLength)
    ' 千円単位を示す","を除く
    pub_strStockChange = Replace(pub_strStockChange, ",", "")

End Function

Function GetChangePercent()
' Summary:GetHTMLで取得したdatHtmlから騰落率を取得し、
' public変数pub_strCangePercentに代入する
' Written by White Tiger
' Date:2008/09/27
' Argument:Nothing

    ' 検索文字列の開始位置、終了位置を取得する
    strStart = InStr(datHtml, "<stock:change_percent>")
    strEnd = InStr(datHtml, "</stock:change_percent>")

    ' strStart及びstrEndを整数型へ変換
    intStart = CInt(strStart)
    intEnd = CInt(strEnd)
    ' 取得する文字列の長さ
    intStrLength = intEnd - intStart - Len("<stock:change_percent>")

    ' 開始文字位置
    intStart = intStart + Len("<stock:change_percent>")
    
    ' 文字列抜き出しpublic変数pub_strCangePercentに代入する
    pub_strCangePercen = Mid(datHtml, intStart, intStrLength)
    
End Function

Function GetVolume()
' Summary:GetHTMLで取得したdatHtmlから出来高を取得し、
' public変数pub_strStockVolumeに代入する
' Written by White Tiger
' Date:2008/09/27
' Argument:Nothing

    ' 検索文字列の開始位置、終了位置を取得する
    strStart = InStr(datHtml, "<stock:volume>")
    strEnd = InStr(datHtml, "</stock:volume>")

    ' strStart及びstrEndを整数型へ変換
    intStart = CInt(strStart)
    intEnd = CInt(strEnd)
    ' 取得する文字列の長さ
    intStrLength = intEnd - intStart - Len("<stock:volume>")

    ' 開始文字位置
    intStart = intStart + Len("<stock:volume>")
    
    ' 文字列抜き出しpublic変数pub_strStockVolumeに代入する
    pub_strStockVolume = Mid(datHtml, intStart, intStrLength)
    ' 千円単位を示す","を除く
    pub_strStockVolume = Replace(pub_strStockVolume, ",", "")

End Function

最後に、ワークシートにボタンを張り付けGetStackInfoマクロを登録します。

ボタンをクリックすると、Yahoo!ファイナンスの無料情報から現在の株価を取得し、Excelワークシート上に表示されます。通信環境にもよりますが、ほぼ瞬時に取得できます。

注:厳密にはYahoo!ファイナンスの無料情報が30分遅れで表示される関係で、リアルタイムに株価を取得することはできません。

◆銘柄コードを指定してWebブラウザでYahoo!ファイナンスを表示するには

　前項までで一通り、株価を取得するアプリケーションはできました。

　個別にyahoo!ファイナンスなどで確認したいときがあると思います。銘柄コードを指定してIEなどのWebブラウザでYahoo!ファイナンスを表示するには、次のようにします。この例では、銘柄コード7203を指定してます。

    Dim ie As Object

    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True ' When brank page show,deleet this line.
    ' 表示
    ie.Navigate _
      "http://quote.yahoo.co.jp/q?s=7203&d=v1&k=c3&h=on&z=m&esearch=1T"

ちなみに、ロイターで株価を表示するには、銘柄コードに".T"(ドットT)をつけて次のようにします。なお、大阪証券取引所の株価を表示するときはTの代わりにOSを指定します。

    Dim ie As Object

    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True ' When brank page show,deleet this line.
    ' 表示
    ie.Navigate "http://jp.reuters.com/investing/quotes/quote?symbol=7203.T"

参考：ExcelでWebページを表示する方法

 ▼ページトップへ

White Tiger

Microsoft Excel VBA - Excel VBAで株価を取得する方法

Link

design: TempNate