複数ページにわたるWebデータをエクセルに取り込む



2018-06-26_20h24_28
このブログはヤフーの配当利回りランキングを参考にしています。
このデータをエクセルに取り込むために使ったVBAのマクロを記録しておきます。


 エクセルでマクロを動かす方法


2018-06-26_18h48_05
ツール→マクロ→Visual Basic Editorを選択します。

2018-06-26_20h22_50
挿入→標準モジュールを選択します。

2018-06-26_19h12_16
下記のコードをコピペして、実行を選択すると取り込みが始まります。


 コードと解説


このページのコードを参考に連続して全ページを連続して取り込めるようにしました。
コードは下記の通りです。
Sub sample1()
Dim i As Long
i = 1
On Error Resume Next
Do
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://info.finance.yahoo.co.jp/ranking/?kd=8&tm=d&vl=a&mk=1&p=" & i, _
Destination:=Cells(1 + (i - 1) * 52, 1))
.Name = "?kd=1&tm=d&vl=a&mk=1&p=1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
If Err.Number <> 0 Then Exit Do
i = i + 1
Loop Until i = 62
On Error GoTo 0
End Sub


3行目:i = 1 iを変数として使用します。
7行目:"URL;https://info.finance.yahoo.co.jp/ranking/?kd=8&tm=d&vl=a&mk=1&p=" & i, _ データを取り込みたいwebページのアドレスを入れます。

2018-06-26_20h26_43
今回は、「https://info.finance.yahoo.co.jp/ranking/?kd=8&tm=d&vl=a&mk=1&p=1」です。
このデータは全部で61ページあり、URLの最後の部分がp=1、p=2、p=3のように増えて行きます。
ここに変数のiを使います。
8行目:Destination:=Cells(1 + (i - 1) * 52, 1)) 取り込んだデータを書き出すセルを指定します。
2018-06-26_22h26_58エクセルのセルはCellsを使って、左の図のように指定できます。
Cells(1,1)がA1、Cells(1,2)がB1、Cells(2,1)がA2、Cells(2,2)がB2というようになります。
A1 A2という書き方と左右が逆で軽く混乱しますが。

このwebページでは、1ページあたりのデータが50行と見出し行が上下に2行で52行あり、
webページを1ページ取り込むごとに52行のデータがエクセル上に取り込まれます。
その次のページのデータはその次の行に書きたいので2ページ目は53行目、3ページ目は105行目となります。
そのため、変数のiを使って、「1 + (i - 1) * 52」行目のA列を指定します。

31行目:i = i + 1 iに1を足して次のページを指定します。
32行目:Loop Until i = 62 61ページあるので、iが61+1の62になるまで続けます。
(61にすると60+1で61になったときに止まってしまうので、61ページ目が取り込めなくなります。)

URLがp=1、p=2、p=3のように増えて行くページなら上記の7行目コードのURL部分を変えて
ページあたりのデータ数で8行目と全ページ数で32行目を変更すれば連続取り込みができるようになるはずです。