09
--
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
--
>>
<<
--
LATEST ENTRY
CATEGORY
ARCHIVE
PROFILE
SEARCH
RECENT COMMENT
  • エクセルファイルのパスワードを忘れたら・・・
    里奈 (09/09)
  • 【QUICKFIX】 FX自動売買への道 27 【接続時の仕様】
    mrsg (07/29)
  • 【QUICKFIX】 FX自動売買への道 11 【デモ環境への接続】
    大工の源さん (05/01)
  • 【QUICKFIX】 FX自動売買への道 19 【レート情報の取得(MarketDataRequest)】
    まこ (04/11)
  • 【QUICKFIX】 FX自動売買への道 19 【レート情報の取得(MarketDataRequest)】
    ぼん (04/11)
  • Excel の パスワード解除 (VBS版)
    おさる (01/05)
  • Excel の パスワード解除 (VBS版)
    おさる (01/05)
  • エクセルファイルのパスワードを忘れたら・・・
    ヤナト (07/07)
  • ffmpeg+libx264+libfaac の static ビルド
    ひげお (03/17)
  • TSの2重音声問題解決
    K (10/14)
MOBILE
qrcode
OTHERS
<< 【開発入門】Webサーバ、MySQLサーバ、開発環境の構築 【かんたんデバッグ手順付き】 | top | 【全テーブル】Oracle -> CSV -> Excel【抽出】 >>
【情報】Excel で スクレイピング 【ぶっこ抜き】
EXCEL マクロで、Web スクレイピングです

ちょっと地震情報が欲しくなったので、Yahoo!さんの地震情報をExcelに移すために作りました



欲しいのは地震の一覧ページ
 http://typhoon.yahoo.co.jp/weather/jp/earthquake/list/


このページ、日本各地の地震情報が一覧で出てきます。
なんと2004年までさかのぼって地震情報が確認できる素晴らしいページ

でも、ちょっと集計したりなんだりするときに、Webページだとめんどくさいんですよね。

というわけで、一覧を Excel に流し込んでしまいましょう



まず、ページの仕様を確認。
・1ページに100件ずつ
・ページの下部には必ず [ 次へ ] のリンクが存在
・一番最後のページには [ 次へ ] のリンクは存在しない
・地震の詳細情報は別ページへのリンク


ついでにソースを覗いてみると
・地震の情報は TABLE タグで作られている
・TABLEのタイトル行は  <tr bgcolor="#eeeeee" valign=middle>
・TABLEの情報行は   <tr bgcolor="#ffffff" valign=middle>
となっているので、bgcolor が ffffff の行だけを取り込むことにします。


なんとなく抜き取りたい情報の概要がわかったので、EXCEL マクロの作成にとりかかります

まずはExcel を開いて、マクロ作成の準備をします。

[Excel のオプション]の[基本設定]の[Excel の使用に関する基本オプション] にある
・チェックボックス [開発]タブをリボンに表示する
にチェックをつけておきます。

ついでに
[Excel のオプション]の[セキュリティセンター]の[セキュリティセンターの設定]ボタンを押して
[セキュリティセンター]の[マクロの設定]の[マクロの設定]にある
・ラジオボタン すべてのマクロを有効にする
を選択しておきます。


この設定をしておけば、リボンに[開発]が現れるので[コードの表示]を押します
すると、コードのウィンドウが表示されるはず

マクロはこのコードのウィンドウで編集します。



■参照設定

マクロからYahoo!のページを取得するために必要な参照設定をしておきます。
コードのウィンドウで[ツール]の[参照設定]を開き、以下の行を探してチェックをつけておきます
・Microsoft HTML Object library
・Microsoft Internet Controls


■標準モジュール
実際にマクロソースを書き込むモジュールを追加します。
コードのウィンドウで[挿入]の[標準モジュール]を選択します。
左側のプロジェクトツリーに標準モジュール Module1 が追加されること


ここまでやったらとりあえずファイルを保存
拡張子を xlsx ではなく、マクロ付きの xlsm で保存しときます。



■マクロの全般設定

コードのウィンドウで標準モジュールの module1 を選択して開きます。
全般設定部分(Gelneral) の(Declarations)に、以下コードを書き込みます

Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private oIE As InternetExplorer



■Yahoo!ページ呼び出し部分

続いて以下を書き込みます

Sub Navigate()
    Dim url
    url = "http://typhoon.yahoo.co.jp/weather/jp/earthquake/list/"
    
    '// IEの準備
    If oIE Is Nothing Then Set oIE = New InternetExplorer
    
    '// IEを表示
    If oIE.Visible <> True Then oIE.Visible = True
    
    '// ページ読み込み
    oIE.Navigate2 (url)
    
    '// 読み込みが完了するまで待つ
    While oIE.readyState <> READYSTATE_COMPLETE Or oIE.Busy = True
        DoEvents
        Sleep 100
    Wend
    
    '// 読み込み完了後の安定化待ち
    Sleep 200
    
    '// 後処理
    oIE.Quit
    Set oIE = Nothing
End Sub


ここまでかけたら、Sub Navigate() の部分にカーソルを置いて、 F8 ボタンを押してステップ実行をしてみます。

F8 ボタンを押す毎に1行ずつ実行しますので、1行ずつ、ちゃんと動いているかを確認してください。


無事にYahoo!ページが開いて、閉じたら成功です。
エクセルからIEを呼び出して、任意のページを開くことに成功しています。





■リンク 「次へ>」の取得

上記までが成功してたら、ソースを以下のように書き込みます(一部修正してます)
上記までが成功していなかったら、この段階でエラーなどを直しておいてください。

Yahoo!ページ下部にある [次へ>] のリンク先を自動的に取り出せるようにします。

Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private oIE As InternetExplorer


Sub Navigate()

    Dim url
    url = "http://typhoon.yahoo.co.jp/weather/jp/earthquake/list/"
    
    '// IEの準備
    If oIE Is Nothing Then Set oIE = New InternetExplorer
    
    '// IEを表示
    If oIE.Visible <> True Then oIE.Visible = True
    
    '// ページ読み込み
    oIE.Navigate2 (url)
    
    '// 読み込みが完了するまで待つ
    While oIE.readyState <> READYSTATE_COMPLETE Or oIE.Busy = True
        DoEvents
        Sleep 100
    Wend
    
    '// 読み込み完了後の安定化待ち
    Sleep 200
    
    '// 次ページ部分の取得
    MsgBox (GetNextPage(oIE.document))
    
    '// 後処理
    oIE.Quit
    Set oIE = Nothing
End Sub


Function GetNextPage(oDoc As HTMLDocument) As String

    Dim a As HTMLElementCollection
    
    '// INIT
    GetNextPage = ""
    
    '// <div id="pageNextback"> 内の <A> タグをループする
    For Each a In oDoc.getElementById("pageNextback").Children(0).getElementsByTagName("A")
        If a.innerText = "次へ>" Then
            GetNextPage = a.href
            Exit For
        End If
    Next a
End Function


ここまでかけたら再度、Sub Navigate() の部分にカーソルを置いて、 F8 ボタンを押してステップ実行をしてみます。

F8 ボタンを押す毎に1行ずつ実行しますので、1行ずつ、ちゃんと動いているかを確認してください。

無事にYahoo!ページが開いて、次へ のリンク文字が表示できたら成功です。




■次ページの自動読み込み

取り出した [次へ>]リンクを自動的に読み取って、次々に[次へ>]のページを開くようにします。

以下を行うまでに、必ず上記までが成功していること
エラーの切り分けが簡単になります。

Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private oIE As InternetExplorer


Sub main()

    Call GetPage("http://typhoon.yahoo.co.jp/weather/jp/earthquake/list/")
    If Not oIE Is Nothing Then oIE.Quit
    Set oIE = Nothing
End Sub


Sub GetPage(url)

        
    '// ページ移動
    Call Navigate(url)
    
    '// 次へ のリンクURLを取得
    url = GetNextPage(oIE.document)
    
    '// 次へ があれば、次へページを開く
    If Len(Trim(url)) <> 0 Then Call GetPage(url)
    
End Sub


Sub Navigate(url)

    
    '// IEの準備
    If oIE Is Nothing Then Set oIE = New InternetExplorer
    
    '// IEを表示
    If oIE.Visible <> True Then oIE.Visible = True
    
    '// ページ読み込み
    oIE.Navigate2 (url)
    
    '// 読み込みが完了するまで待つ
    While oIE.readyState <> READYSTATE_COMPLETE Or oIE.Busy = True
        DoEvents
        Sleep 100
    Wend
    
    '// 読み込み完了後の安定化待ち
    Sleep 200
    
End Sub



Function GetNextPage(oDoc As HTMLDocument) As String

    Dim a As HTMLElementCollection
    
    '// INIT
    GetNextPage = ""
    
    '// <div id="pageNextback"> 内の <A> タグをループする
    For Each a In oDoc.getElementById("pageNextback").Children(0).getElementsByTagName("A")
        If a.innerText = "次へ>" Then
            GetNextPage = a.href
            Exit For
        End If
    Next a
End Function


同じようにページを読み込み続ける処理が必要だったので、
GetPage() という再帰処理を追加しました。

ついでに最初に GetPage() を呼び出す main も追加。
Navigate に入れておいた IEの終了処理は main に移動しとります。


今度は、Sub main() の部分にカーソルを置いて、 F8 ボタンを押してステップ実行をしてみます。
F8 ボタンを押す毎に1行ずつ実行しますので、1行ずつ、ちゃんと動いているかを確認してください。

途中で止めないと、180ページ以上どんどん移動していくので、ある程度の確認ができたら
[実行]−[リセット]で終了しときます。




■地震情報の取り込み

いよいよ本番
地震情報の取り込みを行います。

上記までで、地震情報ページをすべて表示させる確認ができているので
各ページから地震情報を抜き出すところを追加します。

Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private oIE As InternetExplorer


Sub main()


    '// 地震情報の読み込み開始
    Call GetPage(2, "http://typhoon.yahoo.co.jp/weather/jp/earthquake/list/")
    
    '// 終了処理(IEを消す)
    If Not oIE Is Nothing Then oIE.Quit
    Set oIE = Nothing

End Sub


Sub GetPage(row, url)

        
    '// ページ移動
    Call Navigate(url)
    
    '// 地震情報取得
    Call getEarthQuakeList(row, oIE.document)
    
    '// 次へ のリンクURLを取得
    url = GetNextPage(oIE.document)
    
    '// 次へ があれば、次へページを開く
    If Len(Trim(url)) <> 0 Then Call GetPage(row, url)
    
End Sub


Sub Navigate(url)

    
    '// IEの準備
    If oIE Is Nothing Then Set oIE = New InternetExplorer
    
    '// IEを表示
    If oIE.Visible <> True Then oIE.Visible = True
    
    '// ページ読み込み
    oIE.Navigate2 (url)
    
    '// 読み込みが完了するまで待つ
    While oIE.readyState <> READYSTATE_COMPLETE Or oIE.Busy = True
        DoEvents
        Sleep 100
    Wend
    
    '// 読み込み完了後の安定化待ち
    Sleep 200
    
End Sub



Function GetNextPage(oDoc As HTMLDocument) As String

    Dim a As HTMLElementCollection
    
    '// INIT
    GetNextPage = ""
    
    '// <div id="pageNextback"> 内の <A> タグをループする
    For Each a In oDoc.getElementById("pageNextback").Children(0).getElementsByTagName("A")
        
        '// 次へ> の文字列が見つかったら、そのリンクを取得する
        If a.innerText = "次へ>" Then
            GetNextPage = a.href
            Exit For
        End If
    Next a
End Function



Sub getEarthQuakeList(ByRef row, oDoc As HTMLDocument)

    
    Dim a As HTMLElementCollection
    Dim b As HTMLElementCollection
    Dim flg As Boolean
    Dim wk, i
    
    '// 地震リストテーブルを検索
    flg = False
    For Each a In oDoc.getElementsByTagName("TABLE")
        DoEvents
        
        '// TABLE タグの クラス名をチェック
        wk = Split(a.className)
        For i = 0 To UBound(wk)
            DoEvents
            
            '// クラス名に yjw_table が見つかったら、地震リスト
            If UCase(wk(i)) = UCase("yjw_table") Then flg = True
        Next i
        If flg <> False Then Exit For
    Next a
    
    '// 地震リストテーブルが見つからなかったら処理しない
    If flg = False Then Exit Sub

    '// 地震リストテーブルからエクセルに転記
    For Each b In a.Children(0).getElementsByTagName("TR")
        DoEvents
        
        '// データ行のみ対象
        If b.getAttribute("BGCOLOR") = "#ffffff" Then
        
            '// エクセルに転記する
            ActiveSheet.Range("A" & row).Select
            ActiveSheet.Range("A" & row).Value2 = row - 1                                     'No
            ActiveSheet.Range("B" & row).Value2 = oIE.document.url                             '取得ページ
            ActiveSheet.Range("C" & row).Value2 = b.getElementsByTagName("TD")(0).Children(0).href '詳細情報ページ
            ActiveSheet.Range("D" & row).Value2 = b.getElementsByTagName("TD")(0).innerText    '発生時刻
            ActiveSheet.Range("E" & row).Value2 = b.getElementsByTagName("TD")(1).innerText    '発表時刻
            ActiveSheet.Range("F" & row).Value2 = b.getElementsByTagName("TD")(2).innerText    '震源地
            ActiveSheet.Range("G" & row).Value2 = b.getElementsByTagName("TD")(3).innerText    'マグニチュード
            ActiveSheet.Range("H" & row).Value2 = b.getElementsByTagName("TD")(4).innerText    '最大震度
            
            '// カウントアップ
            row = row + 1
        End If
        
    Next b
End Sub



地震情報を抜き出す getEarthQuakeList() を追加しています。
エクセルに転記する際の 行 の情報も取りまわせるようにちょっと各所に変更が入っています。

HTML を DOM オブジェクトとして取り扱って、任意の場所の任意の情報のみを取得することができました。

スクレイピング は便利ですが、人間が見るのとは段違いのスピードでWEBサーバに要求を投げることができてしまうため、
必ず処理中に「待ち」の部分を作ってWEBサーバに負荷をかけすぎないようにすることが重要です。
人様に迷惑をかけるのはやめましょう!!


以下、執筆時点でのYahoo!ページのHTMLソース(抜粋)を張っておきます。
Yahoo!さんの仕様が変わったら、上記マクロも併せて修正してください。


<!---履歴--->
<div class="yjw_main_md">
  <div class="yjw_title_h2"><h2>履歴一覧</h2></div>

  <div class="etqk_traffic yjSt">
    <p class="lft">18739件中2601〜2700件を表示しています。</p>
    <p class="rgt">
      [ <a href="?sort=1&key=1&b=2501">前の100件</a> | <a href="?sort=1&key=1&b=2701">次の100件</a> ]
    </p>
  </div>
 
  <table border=1 cellpadding=2 cellspacing=0 width=100% class="yjw_table yjSt boderset">
    <tr bgcolor="#eeeeee" valign=middle>
      <td width=31% align=center>
        <a href="/weather/jp/earthquake/list/?sort=0&key=1">
          <img src="http://i.yimg.jp/images/search/down.gif" class="mr2p" alt="降順">
        </a>
        <a href="/weather/jp/earthquake/list/?sort=0&key=1"><strong>発生時刻</strong></a>
      </td>
      <td width=27% align=center>
        <a href="/weather/jp/earthquake/list/?sort=1&key=2"><strong>情報発表時刻</strong></a>
      </td>
      <td width=22% align=center>
        <a href="/weather/jp/earthquake/list/?sort=1&key=3"><strong>震源地</strong></a>
      </td>
      <td width=10% align=center>
        <a href="/weather/jp/earthquake/list/?sort=1&key=4"><strong>マグニ<br>チュード</strong></a>
      </td>
      <td width=10% align=center>
        <a href="/weather/jp/earthquake/list/?sort=1&key=5"><strong>最大震度</strong></a>
      </td>
    </tr>
 
    <tr bgcolor="#ffffff" valign=middle>
      <td><a href="/weather/jp/earthquake/20111128233300.html">2011年11月28日 23時33分 ごろ</a></td>
      <td>2011年11月28日 23時37分</td>
      <td align=center>茨城県沖</td>
      <td align=center>4.4</td>
      <td align=center>3</td>
    </tr>
    <tr bgcolor="#ffffff" valign=middle>
      <td><a href="/weather/jp/earthquake/20111128173900.html">2011年11月28日 17時39分 ごろ</a></td>
      <td>2011年11月28日 17時43分</td>
      <td align=center>長野県南部</td>
      <td align=center>2.5</td>
      <td align=center>1</td>
    </tr>
    
    ・
    ・
    ・
    
  </table>

</div>
<!---/履歴--->

<!-- ページ送り -->
<div id="pageNextback">
  <p>
    <span class="m">
    <a href="?sort=1&key=1&b=2501" class="frame"><span class="arr">&lt;</span><span class="underLline">前へ</span></a>
    </span>
    <a href="?sort=1&key=1&b=1" title="1" class="frame">1</a><span class="om">...</span>
    <a href="?sort=1&key=1&b=2101" title="22" class="frame">22</a>
    <a href="?sort=1&key=1&b=2201" title="23" class="frame">23</a>
    <a href="?sort=1&key=1&b=2301" title="24" class="frame">24</a>
    <a href="?sort=1&key=1&b=2401" title="25" class="frame">25</a>
    <a href="?sort=1&key=1&b=2501" title="26" class="frame">26</a>
    <strong>27</strong>
    <a href="?sort=1&key=1&b=2701" title="28" class="frame">28</a>
    <a href="?sort=1&key=1&b=2801" title="29" class="frame">29</a>
    <a href="?sort=1&key=1&b=2901" title="30" class="frame">30</a>
    <a href="?sort=1&key=1&b=3001" title="31" class="frame">31</a>
    <span class="om">...</span>
    <a href="?sort=1&key=1&b=18701" title="188" class="frame">188</a>
    <span class="m">
    <a href="?sort=1&key=1&b=2701" class="frame"><span class="underLline">次へ</span><span class="arr">&gt;</span></a>
    </span>
  </p>
</div>
<!-- /ページ送り -->


まこ | 開発 | 22:55 | comments(0) | trackbacks(0) |
スポンサーサイト
スポンサードリンク | - | 22:55 | - | - |
Comment









Trackback
URL: