我应该如何抓取这个网页的https://www.bseindia.com/stock-share-price/asian-paints-ltd/asianpaint/500820/,并特别需要表中提到的净资产收益率数字?
我在Excel中使用了以下代码。我不太了解谷歌抓取床单
Sub FetchData()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://www.bseindia.com/stock-share-price/asian-paints-ltd/asianpaint/500820/", Destination:=Range( _
"$A$1"))
.Name = "www"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
我无法正确获取数据。
对此有什么建议/帮助吗?需要ROE数字,其余部分不是必需的。
发布于 2019-06-03 00:45:04
使用页面使用它的API要快得多。您可以使用powerquery来处理json响应,json解析器,或者直接使用split。如果您想要在按下按钮时刷新,请将代码放在标准模块中并链接到按钮。
Option Explicit
Public Sub GetInfo()
Dim s As String, ids(), i As Long
ids = Array(500820, 500312, 500325, 532540)
With CreateObject("MSXML2.XMLHTTP")
For i = LBound(ids) To UBound(ids)
.Open "GET", "https://api.bseindia.com/BseIndiaAPI/api/ComHeader/w?quotetype=EQ&scripcode=" & ids(i) & "&seriesid=", False
.send
s = .responseText
ActiveSheet.Cells(i + 1, 1) = Split(Split(s, """ROE"":""")(1), Chr$(34))(0)
Next
End With
End Sub
发布于 2019-06-02 22:38:50
下面是我发现更容易获得该特定值的方法。一旦for loop
检测到ROE
,它将获取所需的值并退出循环,因为它们都在同一个父节点中。
Sub FetchData()
Dim IE As New InternetExplorer, post As Object
Dim Html As HTMLDocument, elem As Object
With IE
.Visible = False
.navigate "https://www.bseindia.com/stock-share-price/asian-paints-ltd/asianpaint/500820/"
While .Busy Or .readyState < 4: DoEvents: Wend
Set Html = .document
End With
For Each post In Html.getElementsByTagName("td")
If post.innerText = "ROE" Then
Set elem = post.ParentNode.querySelector(".textvalue")
Exit For
End If
Next post
[A1] = elem.innerText
End Sub
要添加的引用:
Microsoft Html Object Library
Microsoft Internet Controls
发布于 2019-06-02 23:00:00
不幸的是,这是不可能的,因为该网站是由JavaScript控制的,而Google Sheets不能理解/导入JS。您可以简单地通过禁用给定链接的JS进行测试,您将看到一个空白页面:
你能得到的就是你所看到的:
=ARRAY_CONSTRAIN(IMPORTDATA("https://www.bseindia.com/stock-share-price/asian-paints-ltd/asianpaint/500820/"), 5000, 15)
https://stackoverflow.com/questions/56415587
复制相似问题