may the VBA be with you

Excel VBAとか業務自動化とか

VBAでこんなことできます_その3 指定した銘柄の時系列株価データを取得するマクロ

どうもinageです。
VBAでどんなことできるかちょちょっと見せる用サンプル第3弾です。

「インターネット上のデータを自動で取得できるんです!」
と言って見せると
「え、そんなことできるんだ」と驚いてくれる場合と
「まあ、そりゃできるでしょ」顔をされる場合があります。

まあ、なんでもそうなんですけどね・・・。

f:id:vba-belle-equipe:20190909112033g:plain
web情報取得マクロ

どんなマクロ

4桁の証券コードを指定してボタンを押すと、時系列の株価データを表形式で取得します。

使い方

1)証券コード(4桁の数字)を入力する
2)株価取得ボタンを押す



以下、同じようにどっかから表形式のデータを持ってきたい場合の
参考までに、流れを書きたいと思います。

ただ動かしたい場合は「ファイルのダウンロード」まで飛んでください。

VBAでのIE制御について

こちらのサイトが超詳しく、勉強になりますので是非ともご覧ください。
www.vba-ie.net

ネット上のデータを取得するマクロができるまで

  • データを取得したいページをなんとなく探す
  • パラメータが必要か調べる
  • Webページの構造を調べる
  • VBAのコードを書いてみる

みたいな流れになります。

データを取得したいページ

f:id:vba-belle-equipe:20190909093408p:plain
Yahoo! ファイナンスの銘柄別時系列ページ

今回はこの、Yahoo! ファイナンスの時系列株価情報ページの表を
そのままExcelに持ってくることを考えるとします。

パラメータ?

アドレスバーを見ると「https://stocks.finance.yahoo.co.jp/stocks/history/?code=6460.T」となっています。

ここで「?code=6460.T」というのがパラメータの部分で、

「code」というパラメータに値「6460.T」を設定してね

ということです。

試しに他の銘柄を指定すると、「6460.T」の部分が変わることがわかります。

「.T」の部分はつけなくても良いみたいなので

https://stocks.finance.yahoo.co.jp/stocks/history/?code=銘柄コード

という形でアドレスを指定すれば良さそう、ということがわかります。

パラメータをつけたアドレス生成のコード(抜粋)
Dim codeStr As String
codeStr = Range("B2")
objIE.navigate "https://stocks.finance.yahoo.co.jp/stocks/history/?code=" & codeStr

この部分ですね。(B2セルに4桁のコードが入力される前提)

Webページの構造を調べる

ブラウザでホームページを見ている時に、おもむろにF12キーを押すと、
「開発者ツール」というものが開きます。
(ブラウザによって名前とか見た目は違います)

それをいい感じで使うと、ターゲットとなる表が
どのように構成されているかわかります。(説明放棄)

f:id:vba-belle-equipe:20190909095921p:plain
開発者ツールでWebページの構造を調べているところ


この場合、チェックするのは

・対象となる部分が<table>タグで出来ている
・見出しの行は<th>タグで、7つ(7列分)ある
・データ部分は<td>タグで、1行につき7つ(7列分)ある

というところです。

その情報を元に、どんなタグのデータを取得して、どこで折り返すか
を指定している部分が以下です。

    Const NumOfColumns As Long = 7    '一行の列数
    Const StartCol As Long = 5
    Const StartRow As Long = 4
    
    '対象テーブルの指定
    Set objTable = objIE.document.getElementsByTagName("table")(1)
    Set objTHs = objTable.getElementsByTagName("th")
    crRow = StartRow
    crCol = StartCol - 1
    
    '項目行の転記
    For Each obj In objTHs
        crCol = crCol + 1
        Cells(crRow, crCol) = obj.outerText
    Next
    
    Set objTDs = objTable.getElementsByTagName("td")
    cnt = 0
    crRow = StartRow + 1
    crCol = StartCol - 1
    
    'データ部分の転記
    For Each obj In objTDs
        cnt = cnt + 1
        crCol = crCol + 1
        Cells(crRow, crCol) = obj.outerText
        '既定の列数に達したら次の行に移る
        If cnt Mod NumOfColumns = 0 Then
            crCol = StartCol - 1
            crRow = crRow + 1
        End If
    Next

テーブルの指定

「objIE.document.getElementsByTagName("table")」は
tableタグのオブジェクトを配列として取得します。

今回はそれに(1)をつけることで、ソースコードに現れるtableのうち、
2番目のものを指定しています。1番目なら("table").(0) ですね。

f:id:vba-belle-equipe:20190909110723p:plain
なんで("table")(1)なの

画像のピンク枠が、1個目のテーブルです。

対象となるテーブルが何個目なのかは、まあ、htmlのソースを検索すれば
ある程度わかります。

仮に間違っても世界が終わるわけではないので、落ち着いて数字を変えながら
テストしていきましょう。

ファイルのダウンロード

web情報取得01.xlsm - Google ドライブ

上記リンク先からダウンロードしてください。

できなかったらゴメンナサイ。

諸注意

Windowsで動作します
・デモ用なので、エラー処理とかちゃんとやってません
・自由に使っていただいて構いませんが、自己責任でお願いします
・日光に当てたり、水を与えたり、深夜12時以降に食べ物をあげたりしないでください