问题描述
我正在尝试从 https://in.finance.yahoo中获取数据. excel vba中的com/quotes/ADANIENT.BO 使用以下代码，但似乎不起作用.
I am trying to get data from https://in.finance.yahoo.com/quotes/ADANIENT.BO in excel vba using following code but it doesn't seem to work.
Private Sub mysub()
'Use References> Microsoft Internet Controls and Microsoft HTML Object Library
Dim IE As InternetExplorer, doc As HTMLDocument, quote As String
Dim URL As String
Set IE = CreateObject("internetExplorer.application")
URL = "https://in.finance.yahoo.com/quotes/ADANIENT.BO"
IE.navigate (URL)
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Set doc = IE.document
quote = doc.getElementById("JB3wv").getElementsByClassName("-fsw9 _16zJc")(0).getElementsByClassName("_3Bucv")(0).innerText
'quote = doc.getElementById("JB3wv").getElementsByTagName("table")(0).getElementsByTagName("tr")(0).getElementsByTagName("td")(1).getElementsByClassName("_3Bucv").innerText
Debug.Print quote
IE.Application.Quit
End Sub
您可以转到URL https://in.finance.yahoo.com/报价/ADANIENT.BO ，并检查检查元素的最新价格
you can goto the URL https://in.finance.yahoo.com/quotes/ADANIENT.BO and check inspect element for Last price
<div class="JB3wv"><table class="-fsw9 _16zJc" data-test="contentTable"> <tbody><tr data-index="0" data-key="ADANIENT.BO" data-test-key="ADANIENT.BO" class=""><td class="_2VvFs"><span><label class="_120DQ _2z7ql"><input name="rowToggle" value="on" data-rapid_p="14" data-v9y="1" type="checkbox"><i></i></label><a class="_61PYt " title="ADANIENT.BO" href="/quote/ADANIENT.BO" data-rapid_p="15" data-v9y="1">ADANIENT.BO</a></span></td><td style="font-weight: 700;"><span class="_3Bucv" style="font-weight: 700;">121.60</span></td><td style="font-weight: 700;"><span class="_3Bucv _2ZN-S" style="font-weight: 700;">+1.50</span></td><td style="font-weight: 700;"><span class="_3Bucv _2ZN-S" style="font-weight: 700;">+1.25%</span></td><td style="text-align: left;">INR</td><td><span>3:56 PM IST</span></td><td style="font-weight: 700;"><span class="_3Bucv" style="font-weight: 700;">1.98m</span></td><td>-</td><td>1.45m</td><td style="text-align: left;"><canvas style="width: 140px; height: 23px;" width="140" height="23"></canvas></td><td style="text-align: left;"><canvas style="width: 140px; height: 23px;" width="140" height="23"></canvas></td><td style="text-align: left;"><canvas style="width: 70px; height: 25px;" width="70" height="25"></canvas></td><td style="font-weight: 700;"><span class="_3Bucv" style="font-weight: 700;">0</span></td></tr></tbody></table></div>
推荐答案
最近在finance.yahoo.com上进行更改之后，正确的URL就是
Seems after recent changes on finance.yahoo.com the correct URL is
https://finance.yahoo.com/quote/ADANIENT.BO
要通过IE自动化从网页DOM检索所需的值，可以使用以下代码:
To retrieve the value you need from the webpage DOM via IE automation you can use the following code:
Option Explicit
Sub GetLastPriceIE()
Dim sURL As String
Dim sHeader As String
Dim sQuote As String
sURL = "https://in.finance.yahoo.com/quote/ADANIENT.BO"
' Open IE
With CreateObject("InternetExplorer.Application")
' Navigate URL
.Visible = True
.Navigate sURL
' Wait IE
Do While .readyState < 3 Or .Busy
DoEvents
Loop
' Wait document
Do While .document.readyState <> "complete"
DoEvents
Loop
' Wait target element
Do While IsNull(.document.getElementById("quote-header-info"))
DoEvents
Loop
' Retrieve quote header info inner text
sHeader = .document.getElementById("quote-header-info").innerText
.Quit
End With
' Create RegEx
With CreateObject("VBScript.RegExp")
.Global = True
.MultiLine = True
.IgnoreCase = True
' Set pattern to match values like "121.55+1.15 (+0.96 %)"
.Pattern = "^\s*(\d+\.\d+)\s*[+-]\d+\.\d+\s*\(\s*[\+-]\d+\.\d+\s*%\s*\)\s*$"
With .Execute(sHeader)
If .Count = 1 Then
sQuote = .Item(0).SubMatches(0)
Else
sQuote = "N/A"
End If
End With
End With
Debug.Print sQuote
End Sub
您可以通过 https://query1.finance.yahoo.com/v7/finance/quote?lang=zh-CN&fields=regularMarketPrice&symbols=ADANIENT.BO ，在这里是示例代码:
You can get the last price via API by the URL like https://query1.finance.yahoo.com/v7/finance/quote?lang=en-US&fields=regularMarketPrice&symbols=ADANIENT.BO, here is the example code:
Option Explicit
Sub GetLastPricesXHR()
Dim aSymbols
Dim aPrices
Dim i As Long
' Put symbols into array
aSymbols = Array("ADANIENT.BO", "NTPC.BO", "BHEL.BO")
' Retrieve prices
aPrices = ParseLastPricesXHR(aSymbols)
' Output
For i = 0 To UBound(aSymbols)
Debug.Print aSymbols(i), aPrices(i)
Next
End Sub
Function ParseLastPricesXHR(aSymbols)
Dim sResp As String
Dim aChunks
Dim i As Long
Dim sChunk As String
Dim aPrices
Dim sPrice As String
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", "https://query1.finance.yahoo.com/v7/finance/quote?lang=en-US&fields=regularMarketPrice&symbols=" & Join(aSymbols, "%2C"), False
.Send
sResp = .ResponseText
End With
aChunks = Split(sResp, """regularMarketPrice"":", UBound(aSymbols) + 2)
If UBound(aChunks) <> UBound(aSymbols) + 1 Then
MsgBox "Wrong response"
End
End If
ReDim aPrices(UBound(aSymbols))
For i = 0 To UBound(aSymbols)
sChunk = aChunks(i + 1)
sPrice = Split(sChunk, ",", 2)(0)
aPrices(i) = sPrice
Next
ParseLastPricesXHR = aPrices
End Function
简短说明.浏览URL https://in.finance.yahoo.com/quotes/ADANIENT.BO 在Chrome中.打开开发人员工具"窗口()的网络"选项卡，并在页面加载后检查记录的XHR.您可以在上一个"/响应"选项卡上找到包含相关数据的XHR之一(通过 https://in.finance.yahoo.com/quotes/ADANIENT.BO/view/v1 ):
Short description. Navigate the URL https://in.finance.yahoo.com/quotes/ADANIENT.BO in Chrome. Open Developer Tools window (), Network tab, and examine logged XHRs after the page loaded. You can find one of XHRs containing the relevant data on Previev / Response tabs (searched by the value 121.6 from the page https://in.finance.yahoo.com/quotes/ADANIENT.BO/view/v1):
看看标题"标签:您可以在常规"参数中找到URL，并在查询字符串参数"中找到详细信息:
Take a look at Headers tab: you can find here the URL in General parameters, and detailed Query String Parameters:
某些参数似乎是可选的，因此我在上面的代码中省略了它们.尽管响应实际上是JSON，但使用Split进行解析对于一个值而言既简单又高效.
Some of parameters seems to be optional, so I omitted them in the above code. Notwithstanding the response actually is JSON, parse using Split is simple and efficient for one value.
要通过API和JSON解析从Yahoo Finance检索更多数据-请通过解析HTML内容来检查此答案-此答案.
For retrieving more data from Yahoo Finance via API and JSON parsing - check this answer, via parsing HTML content - this answer.
这篇关于如何在Excel VBA中使用getElementsByClassName从网页获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！