如何使用getElementsByClassName在Excel中获取网页中的数据vba

Question

我想在excel vba中使用下面的代码从https://in.finance.yahoo.com/quotes/ADANIENT.BO中获取数据，但它似乎不起作用。

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 

，你可以转到的网址https://in.finance.yahoo.com/quotes/ADANIENT.BO和检查检查元素最新价格

<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> 

Answer 1

上finance.yahoo.com最近更改之后似乎正确的URL是

https://finance.yahoo.com/quote/ADANIENT.BO

要通过IE自动化从网页DOM中检索所需的值，可以使用以下代码：

Option Explicit 

Sub Get_Last_Price_via_IE() 

    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 

您可以通过URL获得通过API的最后价格像https://query1.finance.yahoo.com/v7/finance/quote?lang=en-US&fields=regularMarketPrice&symbols=ADANIENT.BO，这里是示例代码：

Sub Get_Last_Price_via_XHR() 

    With CreateObject("MSXML2.XMLHTTP") 
     .Open "GET", "https://query1.finance.yahoo.com/v7/finance/quote?lang=en-US&fields=regularMarketPrice&symbols=ADANIENT.BO", False 
     .Send 
     s = .ResponseText 
    End With 
    q = Split(Split(s, """regularMarketPrice"":", 2)(1), ",", 2)(0) 
    Debug.Print q 

End Sub 

简短说明。在Chrome中导航网址https://in.finance.yahoo.com/quotes/ADANIENT.BO。打开开发工具窗口（F12），网络选项卡，并在加载页面后检查记录的XHR。你可以找到包含在Previev /响应选项卡上的相关数据XHR时的一个（从页https://in.finance.yahoo.com/quotes/ADANIENT.BO/view/v1由值121.6搜索）：

看看标题选项卡：您可以在这里找到的URL常规参数，并详细的查询字符串参数：

一些参数似乎是可选的，所以我在上面的代码省略它们。尽管响应实际上是JSON，但使用Split进行解析对于一个值来说简单而高效。

用于通过API和JSON解析从Yahoo Finance检索更多数据 - 通过解析HTML内容检查this answer - this answer。