9月末といえば～♪♪
んんっ？このWebサイト、1ページ10件までしか見れないのか？
じゃあ、スクレイピングでExcelシートに書き出そうか
Webサイトのデータ（検索結果）をExcelシートに書き出すマクロを紹介します。複数ページある場合は、リンクをクリックして最終ページまでクロールします。
題材として、イチロー選手と天海祐希さんでおなじみ「SMBC日興証券」のWebサイトを使います。
※SMBC日興証券の取引アカウントをお持ちでなくてもマクロは使えます！
スクレイピングのイメージ動画です（9月は件数が多いため、8月データ）
画面左側がWebサイトです。画面のチラつきは画面遷移（次ページへ移動）です。
Webサイトをクロール（次ページ遷移）しながらExcelシートに書き出すマクロの使い方
（事前準備）VBEの参照設定
VBAでIEを操作するためには下記2つのライブラリを参照します。
- Microsoft HTML Object Library
- Microsoft Internet Controls
VBE（エディタ）で「メニュー」→「ツール」→「参照設定」を選択します。
マクロを標準モジュールに貼り付け
下記のコードを標準モジュールに貼り付けます。コードの解説は後の項で紹介します！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
Sub main() Application.ScreenUpdating = False Dim objIE As InternetExplorer Set objIE = New InternetExplorer Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("list") Dim shell As Object Set shell = CreateObject("Shell.Application") 'シェルオブジェクト生成 Dim win As Object For Each win In shell.Windows '起動中のウィンドウを順番にチェック If win.Name = "Internet Explorer" Then '起動してるIEを取得 Set objIE = win Exit For End If Next Call WriteShareholderBenefitsData(objIE) MsgBox "終了しました" End Sub Sub waitIE(objIE) Do While objIE.Busy = True Or objIE.readyState < READYSTATE_COMPLETE '読み込み待ち DoEvents Loop End Sub '株主優待の検索結果をシートに書き出す Sub WriteShareholderBenefitsData(objIE As InternetExplorer) Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("list") '前回書き込みデータのクリア Range(ws.Cells(2, 1), ws.Cells(Rows.Count, 5)).ClearContents Dim r As Long, c As Long '書き込み先のセルの行列番号 Dim cnt As Long Dim isLastPageFlag As Boolean isLastPageFlag = False '検索結果の最終ページに達するまで処理を繰り返す Do While isLastPageFlag = False '検索結果のHTMLを読み込む Dim htmlDoc As HTMLDocument Set htmlDoc = objIE.document Dim yuutaiTbl As IHTMLElement Set yuutaiTbl = htmlDoc.getElementById("item01") 'id名=item01 Dim tdTags As IHTMLElementCollection Set tdTags = yuutaiTbl.getElementsByTagName("td") Dim tdTag As IHTMLElement For Each tdTag In tdTags r = cnt \ 5 + 2 '書き込み先の行番号 c = cnt Mod 5 + 1 '書き込み先の列番号 ws.Cells(r, c).Value = tdTag.innerText cnt = cnt + 1 Next tdTag ' class名は同一ページ内で重複可能なので、何番目かを指定する ' ここでは1番目のnextを取得したいので、(0)を指定する Dim nextPageLink As IHTMLElement '「次の10件」のリンク Set nextPageLink = htmlDoc.getElementsByClassName("next")(0) 'class名=next If nextPageLink Is Nothing = False Then 'もし次のページがあるなら nextPageLink.getElementsByTagName("a")(0).Click 'aタグをクリック Call waitIE(objIE) '画面遷移を待機する Else '検索結果の最終ページならフラグを立てる isLastPageFlag = True End If Set htmlDoc = Nothing 'このページのHTML参照をいったん破棄 Loop End Sub |
Internet Explorerを起動して日興証券のページを開く
このマクロは「起動してるInternet Explorerを取得する」仕組みなので、事前に対象のページにアクセスしておく必要があります。
※ブラウザはInternet Explorerを使用します。
Internet ExplorerでSMBC日興証券の「マーケット情報 > 株主優待の検索」にアクセスし、検索条件を指定して検索結果を表示しておきます。
ここでは、権利確定時期=9月 で検索してみます（他の条件でもOKです）
※起動してるウィンドウを順番にチェックして、最初に見つかったInternet Explorerを処理対象として取得する仕組みなので、Internet Explorerの起動は1つのみとしてください。
マクロを実行する
IEを起動して、対象の検索結果画面を表示させた状態で、マクロのmainプロシージャを実行してください。
423件が、約75秒でExcelシートに書き出せました！
オートメーションエラーが発生した場合
下記のエラーが発生した場合は、IEを再起動してみてください。
スクレイピングコードの解説
起動済みのIEを取得する
起動してるウィンドウを順番にチェックして、最初に見つかったInternet Explorerを処理対象として取得します。
シェルオブジェクトのウィンドウは、エクスプローラーやインターネットのブラウザを取得できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Dim shell As Object Set shell = CreateObject("Shell.Application") 'シェルオブジェクト生成 Dim win As Object For Each win In shell.Windows '起動中のウィンドウを順番にチェック If win.Name = "Internet Explorer" Then '起動してるIEを取得 Set objIE = win Exit For End If Next |
検索結果の最終ページまで処理を繰り返す
このスクレイピングでは、検索結果のページ数、処理を繰り返します。そのため、「最終ページであるか否か」の判定処理が必要です。
変数isLastPageFlagを用意し、Falseの間（=最終ページでない）処理を繰り返します。最終ページか否かの判定は後ほど紹介します。
|
1 2 3 4 5 6 7 8 9 10 11 |
Dim isLastPageFlag As Boolean isLastPageFlag = False '検索結果の最終ページに達するまで処理を繰り返す Do While isLastPageFlag = False '処理 Loop |
銘柄の検索結果の範囲を取得する
検索結果のHTMLを確認してみます。HTMLの確認はChromeを使いましょう！
検索結果の部分は、id名=item01であることが確認できます。
|
1 |
<div class="yuutaiBox04" id="item01">検索結果の中身</div> |
HTMLオブジェクトのgetElementByIdメソッドの引数に”item01″を指定して、変数yuutaiTblに格納します。
|
1 2 3 4 5 |
Dim htmlDoc As HTMLDocument Set htmlDoc = objIE.document Dim yuutaiTbl As IHTMLElement Set yuutaiTbl = htmlDoc.getElementById("item01") 'id名=item01 |
検索結果テーブルのtdタグをすべて取得する
銘柄のテーブルはシンプルな作りになっています。1銘柄5項目なので、5つのtdタグが存在します。
テーブルの1行（1銘柄）のHTMLです。tdタグで挟まれている中身を取得できればよさそうですね。
|
1 2 3 4 5 6 7 |
<tr> <td class="center">1446</td> <td><a href="javascript:jv_Yuutai_Detail(144600)">キャンディル</a></td> <td class="center">9月末日</td> <td class="right pr30">200</td> <td>金券</td> </tr> |
getElementsByTagNameメソッドの引数にtdタグを指定して、変数tdTagsに格納します。tdタグは複数あるので、tdTagsはコレクションになります。
|
1 2 |
Dim tdTags As IHTMLElementCollection Set tdTags = yuutaiTbl.getElementsByTagName("td") |
ポイントは、変数yuutaiTblからtdタグを取得していることです。
yuutaiTblを指定せずに、ページ全体のすべてのtdタグにしてしまうと、銘柄の検索結果と関係のないtdタグまで取得してしまうのです。
tdタグを1つずつシートに書き出す
コレクションtdTagsから、tdタグを1つずつ取り出して変数tdTagに格納します。変数名は複数形と単数形にしておくとわかりやすいです。
- tdTags（複数形・コレクション）
- tdTag（単数形）
tdタグで囲まれた中身がinnerTextです。
|
1 |
<td class="xxx">ここがinnerTextだよ</td> |
tdTagのinnerTextをExcelシートのセルに書き込みます。
|
1 2 3 4 5 6 7 8 9 10 11 |
Dim tdTag As IHTMLElement For Each tdTag In tdTags r = cnt \ 5 + 2 '書き込み先の行番号 c = cnt Mod 5 + 1 '書き込み先の列番号 ws.Cells(r, c).Value = tdTag.innerText cnt = cnt + 1 Next tdTag |
次のページに遷移する
1ページ10件の書き込みが終わったら、「次の10件」のリンクをクリックして次のページに進みます。
リンクのHTMLを確認しましょう。
|
1 |
<span class="next"><a href="javascript:jv_PageNext1()">次の10件</a></span> |
class名は”next”です。
HTMLのidとclassの違いに注意しましょう！
- id = 同一ページ内で重複不可
- class = 同一ページ内で重複OK
HTMLオブジェクトのgetElementsByClassNameメソッドの引数に”next”を指定して、変数nextPageLinkに格納します。
getElementsByClassNameメソッドの返り値はコレクションなので、何番目の要素であるか、番号を指定します。ここでは1番目なので(0)を指定します。
|
1 2 |
Dim nextPageLink As IHTMLElement '「次の10件」のリンク Set nextPageLink = htmlDoc.getElementsByClassName("next")(0) 'class名=next |
（参考）class名がページ全体の何番目であるか確認する方法
HTMLのclass名は、同一ページに複数指定することが可能です。目的のclassが全体の何番目であるか、chromeの検証機能で検索しましょう。
Ctrl+Fで検索ダイアログが登場します。class名を検索します。（検索結果では2 of 3となっていますが、1番目のnextは無関係なので、ここでは目的のリンクが1番目 = 0 となっています）
最終ページであるか否かの判定処理
変数nextPageLink（オブジェクト）の中身をチェックし、もしオブジェクトが空でなければ、つまり次のページがあるなら、クリックします。
検索結果の最終ページにはリンクがありません。
クリックするのは、spanタグの中にあるaタグです。
|
1 |
<span class="next"><a href="javascript:jv_PageNext1()">次の10件</a></span> |
nextPageLinkオブジェクトの、getElementsByTagNameメソッドの引数に”a”タグを指定して、Clickメソッドを実行します。
（spanタグの中にaタグは1つのみなので、要素番号は1番目 の (0) を指定）
|
1 2 3 4 5 6 7 8 9 10 |
If nextPageLink Is Nothing = False Then 'もし次のページがあるなら nextPageLink.getElementsByTagName("a")(0).Click 'aタグをクリック Call waitIE(objIE) '画面遷移を待機する Else '検索結果の最終ページならフラグを立てる isLastPageFlag = True End If |
次ページ遷移のリンクがない場合はnextPageLinkが空になります。
nextPageLinkが空の場合、つまり最終ページの場合は、「最終ページであるか否か」の判定に使用する変数isLastPageFlagをTrueにします。
変数isLastPageFlagがTrueになると、ループ処理を抜けます。
まとめ：VBAでIE操作してスクレイピング
日興証券の株主優待銘柄の一覧を題材にして、ページをクロールしながらExcelシートにデータを書き出す方法を紹介しました。
スクレイピングとは、WebサイトのHTMLを収集して、目的のデータを抽出することです。
Webサイトのデータ収集で、コピー貼付けで苦戦している人は、この「スクレイピング」という技術を覚えるとラクですよ。
「権利付き最終日」は「権利確定日」の2営業日前なので、「権利確定日＝9月末日」の銘柄は9月26日（木）中に買っておけばOKですね。
