iT邦幫忙

0

vba抓取台股加權指數天數問題(using xmlhttp)

想要用vba抓台股加權指數的歷史資料,但最多只能抓到102行的table資料,但看網頁內容超過102行...想請問要如何抓超過120行的資料?
以下是我的code
------------------------code------------------------------
Option Explicit
Sub xmlhttp_get()
Dim i As Integer, j As Integer
Dim objXML As Object, objSear As Object, objClick As Object, DOM As Object, table As Object
Dim start As Date, finish As Date
Dim URL As String

start = Timer
URL = "https://finance.yahoo.com/quote/%5ETWII/history?period1=1548460800&period2=1571184000&interval=1d&filter=history&frequency=1d"

With Cells
.ClearContents
.ClearFormats

End With
Set objXML = CreateObject("microsoft.xmlhttp")
Set DOM = CreateObject("htmlfile")

With objXML
.Open "get", URL, False
.send
If .Status = 200 Then
DOM.body.innerHTML = .responseText
Set table = DOM.getElementsByTagName("table")(2)

End If

End With
MsgBox table.Rows.Length
With Sheets(2)
For i = 0 To table.Rows.Length - 1
For j = 0 To table.Rows(i).Cells.Length - 1
Cells(i + 1, j + 1).Value = table.Rows(i).Cells(j).innerText
Next
Nexta
End With

Set objXML = Nothing
Set DOM = Nothing
Set table = Nothing

finish = Timer
' MsgBox Format((finish - start), "0.00") & " s"
End Sub

marlin12 iT邦新手 1 級 ‧ 2020-01-29 18:11:54 檢舉
看似是lazy loading的問題

1 個回答

1
echochio
iT邦高手 1 級 ‧ 2020-01-29 16:27:08

VBA 不熟 ... 但是看你給的網頁

https://finance.yahoo.com/quote/%5ETWII/history?period1=1548460800&period2=1571184000&interval=1d&filter=history&frequency=1d

看裡面有一個 Download Data
https://ithelp.ithome.com.tw/upload/images/20200129/20110611brzad8sjBp.png

https://ithelp.ithome.com.tw/upload/images/20200129/201106113sc3dmY20j.png

是不是直接抓比較快 ....

dragonH iT邦超人 6 級 ‧ 2020-01-29 17:21:02 檢舉

有 verify cookie

雖然沒試過

不過應該不是太麻煩解決...吧XD/images/emoticon/emoticon01.gif

我要發表回答

立即登入回答