使用VBA从雅虎金融检索数据(资产负债表而非股票)

Question

我试了几天用VBA指令(如".getElementsByTagName"(从雅虎金融获取资产负债表数据，但它不起作用。。。什么都没发生，我被卡住了。。。

有什么想法吗？参见下面的代码：

Sub Get_Data()
Dim url As String
Dim http As Object
Dim TR_col As Object, Tr As Object
Dim TD_col As Object, Td As Object
Dim row As Long, col As Long
Dim html As Object
Dim tbl As Object
'
On Error Resume Next
Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0") 'CreateObject("MSXML2.ServerXMLHTTP.6.0")
url = "https//finance.yahoo.com/quote/AAPL/balance-sheet?p=AAPL"
http.Open "POST", url, False
http.setRequestHeader "Content-Type", "text/xml"
http.Send
MsgBox http.responseText
Set html = CreateObject("htmlfile")
html.body.innerHTML = http.responseText
Set tbl = html.getElementById("Pos(r)")
row = 1
col = 1
Set TR_col = html.getElementsByTagName("TR")
For Each Tr In TR_col
Set TD_col = Tr.getElementsByTagName("TD")
For Each Td In TD_col
Cells(row, col) = Td.innerText
col = col + 1
Next
col = 1
row = row + 1
Next
End Sub 

Answer 1

我想这就行了。我不得不在URL中添加"："，将"POST"更改为"GET"，并设置2个请求标头，第一个是cookie，我认为它将在一年后过期，这是通过运行以下程序发现的：

Cells(1, 1) = http.responseText
Dim responseHeader As String
responseHeader = http.getAllResponseHeaders
Cells(2, 1) = responseHeader

并读取setcookie行。然后，我简单地将其硬编码到请求标头中。运行代码如下：

Sub Get_Data()
Dim url As String
Dim http As Object
Dim TR_col As Object, Tr As Object
Dim TD_col As Object, Td As Object
Dim row As Long, col As Long
Dim html As Object
Dim tbl As Object
'On Error Resume Next
Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0") 
'CreateObject("MSXML2.ServerXMLHTTP.6.0")
url = "https://uk.finance.yahoo.com/quote/AAPL/balance-sheet?p=aapl"
http.Open "GET", url, False
http.setRequestHeader "Cookie", "B=22guonpesgnqg&b=3&s=5p"
http.setRequestHeader "Content-Type", "text/xml"
http.Send
Cells(1, 1) = http.responseText
'Dim responseHeader As String
'responseHeader = http.getAllResponseHeaders
'Cells(2, 1) = responseHeader
Set html = CreateObject("htmlfile")
html.body.innerHTML = http.responseText
Set tbl = html.getElementById("Pos(r)")
row = 1
col = 1
Set TR_col = html.getElementsByTagName("TR")
For Each Tr In TR_col
Set TD_col = Tr.getElementsByTagName("TD")
For Each Td In TD_col
Cells(row, col) = Td.innerText
col = col + 1
Next
col = 1
row = row + 1
Next
End Sub

希望这就是你想要的，返回的数据看起来和我的浏览器显示的类似。