excel - VBA：提取雅虎财经财务报表的每一行

Question

我正在尝试从 Yahoo Finance 财务报表中获取所有信息。到目前为止，我能够使用以下代码获取一些数据：

 Sub BALANCESHEET()
    Dim http As Object, s As String

    Set http = CreateObject("MSXML2.XMLHTTP")

    With http
        .Open "GET", "https://finance.yahoo.com/quote/" & Sheets("DATA").Range("D2").Value & "/balance-sheet?p=" & Sheets("DATA").Range("D2").Value & """, False"
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send
        s = .responseText
    End With
    
    Dim html As MSHTML.HTMLDocument, html2 As MSHTML.HTMLDocument, re As Object, matches As Object
    
    Set html = New MSHTML.HTMLDocument: Set html2 = New MSHTML.HTMLDocument
    Set re = CreateObject("VBScript.RegExp")
    
    html.body.innerHTML = s
    
    Dim headers(), rows As Object
    
    headers = Array("Breakdown", "TTM")
    Set rows = html.querySelectorAll(".fi-row")
    
    With re
        .Global = True
        .MultiLine = True
        .Pattern = "\d{1,2}/\d{1,2}/\d{4}"
        Set matches = .Execute(s)
    End With
    
    Dim results(), match As Object, r As Long, c As Long, startHeaderCount As Long
    startHeaderCount = UBound(headers)
    ReDim Preserve headers(0 To matches.Count + startHeaderCount)

    c = 1
    For Each match In matches
        headers(startHeaderCount + c) = match
        c = c + 1
    Next
    
    Dim row As Object
    ReDim results(1 To rows.Length, 1 To UBound(headers) + 1)
 
    For r = 0 To rows.Length - 1
        html2.body.innerHTML = rows.Item(r).outerHTML
        Set row = html2.querySelectorAll("[title],[data-test=fin-col]")
        
        For c = 0 To row.Length - 1
            results(r + 1, c + 1) = row.Item(c).innerText
        Next c
    Next
    
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("BALANCE SHEET")
    
    With ws
        .Cells(1, 1).Resize(1, UBound(headers) + 1) = headers
        .Cells(2, 1).Resize(UBound(results, 1), UBound(results, 2)) = results
    End With
End Sub

但是，这让我得到了部分数据，而不是折叠的数据......

在此处输入图像描述

任何想法？？？

干杯

马特

标签： excelvbayahoo-finance