Excel VBA Scraping - les tableaux HTML ne sont pas visibles

Question

J'essaie d'obtenir des données de "https://in.tradingview.com/symbols/NSE-ABB/technicals/" en utilisant excel vba website scraping, bien que j'obtienne une réponse, mais le body.innerHTML n'affiche pas le tableau requis, mais dans chrome si j'inspecte la page, je suis capable de voir le tableau avec le nom.

Quel est le problème avec le code ?

With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .send
        sResponse = StrConv(.responseBody, vbUnicode)
    End With

    sResponse = Mid$(sResponse, InStr(1, sResponse, "<!DOCTYPE "))
    WriteTxtFile sResponse
    With html
        .body.innerHTML = sResponse
        Set tElementC = .getElementsByClassName("table-1i1M26QY- maTable-27Z4Dq6Y- tableWithAction-2OCRQQ8y-")(0).getElementsByTagName("td")

    End With

URL --> https://in.tradingview.com/symbols/NSE-ABB/technicals/ nom de classe auquel accéder = "table-1i1M26QY- maTable-27Z4Dq6Y- tableWithAction-2OCRQQ8y-"

Answer 1

La page web source HTML par le lien fourni https://in.tradingview.com/symbols/NSE-ABB/technicals/ ne contient pas les données nécessaires, il utilise AJAX. Le site web dispose d'une sorte d'API. La réponse est renvoyée au format JSON. Vous devez donc d'abord effectuer un travail de rétro-ingénierie pour découvrir comment fonctionne le site Web. Dans un navigateur, par exemple Chrome, appuyez sur F12 pour ouvrir DevTools, naviguez jusqu'à la page Web, allez sur l'onglet Réseau, définissez le filtre sur XHR, il ressemblera à l'illustration ci-dessous :

Examinez les réponses enregistrées. L'une d'entre elles ayant la plus grande taille contient en fait toutes les données nécessaires :

Pour réaliser ce type de XHR, vous devez également conserver la structure complète de la charge utile et ajouter les en-têtes pertinents :

Dans la section Données du formulaire, il y a beaucoup de titres de champs de citation qui se trouvent dans le tableau, vous pouvez donc choisir ceux dont vous avez besoin. Vous pouvez trouver plus de titres disponibles, cliquez sur le lien Initiateur (première capture d'écran ci-dessus), vous verrez le code JS qui a initié ce XHR. Cliquez sur Pretty print {} en bas pour rendre le code lisible. Tapez n'importe quel titre que vous avez déjà extrait de Form Data dans la boîte de recherche, par exemple Recommend.Other et en trouver d'autres à côté dans le code :

Voici un exemple VBA montrant comment un tel raclage pourrait être effectué. Importation JSON.bas dans le projet VBA pour le traitement JSON.

Option Explicit

Sub Test()

    Dim aQuoteFieldTitles()
    Dim aQuoteFieldData()
    Dim sPayload As String
    Dim sJSONString As String
    Dim vJSON
    Dim sState As String
    Dim i As Long

    ' Put the necessary field titles into array
    aQuoteFieldTitles = Array( _
        "name", "description", "country", "type", "after_tax_margin", "average_volume", "average_volume_30d_calc", "average_volume_60d_calc", "average_volume_90d_calc", "basic_eps_net_income", "beta_1_year", "beta_3_year", "beta_5_year", "current_ratio", "debt_to_assets", "debt_to_equity", "dividends_paid", "dividends_per_share_fq", _
        "dividends_yield", "dps_common_stock_prim_issue_fy", "earnings_per_share_basic_ttm", "earnings_per_share_diluted_ttm", "earnings_per_share_forecast_next_fq", "earnings_per_share_fq", "earnings_release_date", "earnings_release_next_date", "ebitda", "enterprise_value_ebitda_ttm", "enterprise_value_fq", "exchange", "expected_annual_dividends", _
        "gross_margin", "gross_profit", "gross_profit_fq", "industry", "last_annual_eps", "last_annual_revenue", "long_term_capital", "market_cap_basic", "market_cap_calc", "net_debt", "net_income", "number_of_employees", "number_of_shareholders", "operating_margin", _
        "pre_tax_margin", "preferred_dividends", "price_52_week_high", "price_52_week_low", "price_book_ratio", "price_earnings_ttm", "price_revenue_ttm", "price_sales_ratio", "quick_ratio", "return_of_invested_capital_percent_ttm", "return_on_assets", "return_on_equity", "return_on_invested_capital", "revenue_per_employee", "sector", _
        "eps_surprise_fq", "eps_surprise_percent_fq", "total_assets", "total_capital", "total_current_assets", "total_debt", "total_revenue", "total_shares_outstanding_fundamental", "volume", "relative_volume", "pre_change", "post_change", "close", "open", "high", "low", "gap", "price_earnings_to_growth_ttm", "price_sales", "price_book_fq", _
        "price_free_cash_flow_ttm", "float_shares_outstanding", "total_shares_outstanding", "change_from_open", "change_from_open_abs", "Perf.W", "Perf.1M", "Perf.3M", "Perf.6M", "Perf.Y", "Perf.YTD", "Volatility.W", "Volatility.M", "Volatility.D", "RSI", "RSI7", "ADX", "ADX+DI", "ADX-DI", "ATR", "Mom", "High.All", "Low.All", "High.6M", "Low.6M", _
        "High.3M", "Low.3M", "High.1M", "Low.1M", "EMA5", "EMA10", "EMA20", "EMA30", "EMA50", "EMA100", "EMA200", "SMA5", "SMA10", "SMA20", "SMA30", "SMA50", "SMA100", "SMA200", "Stoch.K", "Stoch.D", "MACD.macd", "MACD.signal", "Aroon.Up", "Aroon.Down", "BB.upper", "BB.lower", "goodwill", "debt_to_equity_fq", "CCI20", "DonchCh20.Upper", _
        "DonchCh20.Lower", "HullMA9", "AO", "Pivot.M.Classic.S3", "Pivot.M.Classic.S2", "Pivot.M.Classic.S1", "Pivot.M.Classic.Middle", "Pivot.M.Classic.R1", "Pivot.M.Classic.R2", "Pivot.M.Classic.R3", "Pivot.M.Fibonacci.S3", "Pivot.M.Fibonacci.S2", "Pivot.M.Fibonacci.S1", "Pivot.M.Fibonacci.Middle", "Pivot.M.Fibonacci.R1", _
        "Pivot.M.Fibonacci.R2", "Pivot.M.Fibonacci.R3", "Pivot.M.Camarilla.S3", "Pivot.M.Camarilla.S2", "Pivot.M.Camarilla.S1", "Pivot.M.Camarilla.Middle", "Pivot.M.Camarilla.R1", "Pivot.M.Camarilla.R2", "Pivot.M.Camarilla.R3", "Pivot.M.Woodie.S3", "Pivot.M.Woodie.S2", "Pivot.M.Woodie.S1", "Pivot.M.Woodie.Middle", "Pivot.M.Woodie.R1", _
        "Pivot.M.Woodie.R2", "Pivot.M.Woodie.R3", "Pivot.M.Demark.S1", "Pivot.M.Demark.Middle", "Pivot.M.Demark.R1", "KltChnl.upper", "KltChnl.lower", "P.SAR", "Value.Traded", "MoneyFlow", "ChaikinMoneyFlow", "Recommend.All", "Recommend.MA", "Recommend.Other", "Stoch.RSI.K", "Stoch.RSI.D", "W.R", "ROC", "BBPower", "UO", "Ichimoku.CLine", _
        "Ichimoku.BLine", "Ichimoku.Lead1", "Ichimoku.Lead2", "VWMA", "ADR", "RSI[1]", "Stoch.K[1]", "Stoch.D[1]", "CCI20[1]", "ADX-DI[1]", "AO[1]", "Mom[1]", "Rec.Stoch.RSI", "Rec.WR", "Rec.BBPower", "Rec.UO", "Rec.Ichimoku", "Rec.VWMA", "Rec.HullMA9" _
    )

    ' Field titles exactly as in the table MOVING AVERAGES
    ' aQuoteFieldTitles = Array("EMA5", "SMA5", "EMA10", "SMA10", "EMA20", "SMA20", "EMA30", "SMA30", "EMA50", "SMA50", "EMA100", "SMA100", "EMA200", "SMA200", "Ichimoku.BLine", "VWMA", "HullMA9")

    ' Compose payload
    sPayload = "{""symbols"":{""tickers"":[""NSE:ABB""],""query"":{""types"":[]}},""columns"":" & JSON.Serialize(aQuoteFieldTitles) & "}"
    ' Retrieve JSON response
    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", "https://scanner.tradingview.com/india/scan", True
        .setRequestHeader "content-type", "application/x-www-form-urlencoded"
        .setRequestHeader "user-agent", "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.110 Safari/537.36"
        .setRequestHeader "content-length", Len(sPayload)
        .send (sPayload)
        Do Until .readyState = 4: DoEvents: Loop
        sJSONString = .responseText
    End With
    ' Parse JSON response
    JSON.Parse sJSONString, vJSON, sState
    ' Check response validity
    Select Case True
        Case sState <> "Object"
            MsgBox "Invalid JSON response"
        Case IsNull(vJSON("data"))
            MsgBox vJSON("error")
        Case Else
            ' Output data to worksheet #1
            aQuoteFieldData = vJSON("data")(0)("d")
            With ThisWorkbook.Sheets(1)
                .Cells.Delete
                .Cells.WrapText = False
                For i = 0 To UBound(aQuoteFieldTitles)
                    .Cells(i + 1, 1).Value = aQuoteFieldTitles(i)
                    .Cells(i + 1, 2).Value = aQuoteFieldData(i)
                Next
                .Columns.AutoFit
            End With
            MsgBox "Completed"
    End Select

End Sub

Le résultat pour moi est le suivant :

BTW, l'approche similaire appliquée dans d'autres réponses .

Answer 2

Comme mentionné dans les commentaires, le javascript doit être exécuté sur la page pour mettre à jour le contenu requis. Il ne semble pas y avoir d'API disponible gratuitement. Vous pouvez utiliser un navigateur. Vous devez aller dans VBE > Outils > Références > ajouter une référence à Microsoft Internet Controls.

Option Explicit
Public Sub GetInfo()
    Dim IE As InternetExplorer, ws As Worksheet, hTable As Object, tRow As Object, td As Object, r As Long, c As Long, headers()
    headers = Array("name", "value", "action")
    Set ws = ThisWorkbook.Worksheets("Sheet1"): Set IE = New InternetExplorer
    With IE
        .Visible = True
        .Navigate2 "https://in.tradingview.com/symbols/NSE-ABB/technicals/"

        While .Busy Or .readyState < 4: DoEvents: Wend
        Set hTable = IE.document.querySelector("table + .tableWithAction-2OCRQQ8y-")
        ws.Cells(1, 1).Resize(1, UBound(headers) + 1) = headers
        For Each tRow In hTable.getElementsByTagName("tr")
            r = r + 1: c = 1
            For Each td In tRow.getElementsByTagName("td")
                ws.Cells(r, c).Value = td.innerText
                c = c + 1
            Next td
        Next tRow
        .Quit
    End With
End Sub