Microsoft Excel VBA - Excel VBAで株価を取得する方法
◆概要
Yahoo!ファイナスをはじめ、証券会社などのWEBページで株価を検索することができます。しかし、これをエクセルなどのアプリケーションで利用するには都合が悪いようです。XMLで記述されておらずHTMLで記述されていますから、特定の値を取得するのが難しいのです。また、WebAPIが提供されていないサイトに対してHTMLから直接情報を取り出す技術として、スクレイピングがあります。これには大きく分けて2つの方法があります。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列に表示させたい銘柄のコードを入力しておきます。1件しか登録していないとオーバーフローしますので、2件以上登録してください。
- 以下のマクロを標準モジュールに張り付けます。
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"