如何在Excel VBA中使用getElementsByClassName从网页获取数据 [英] how to get data from webpage using getElementsByClassName in excel vba

问题描述

我正在尝试从 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

您可以通过

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中.打开开发人员工具"窗口( F12 )的网络"选项卡，并在页面加载后检查记录的XHR.您可以在上一个"/响应"选项卡上找到包含相关数据的XHR之一(通过

Short description. Navigate the URL https://in.finance.yahoo.com/quotes/ADANIENT.BO in Chrome. Open Developer Tools window (F12), 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从网页获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！