用excel从雅​​虎金融中提取金融基础 [英] Extract financial fundamental from Yahoo finance with excel

问题描述

雅虎改变了财务(基本面)报告的结构.过去(上个月)在页面 您可以看到它显示为一个表格，但是如果您查看 HTML 代码的内部，则没有关于表格的正常标记.我确定起始表"有这个标签 <div class="D(tbrg)" 但我不知道如何继续提取数据.资产负债表和现金流量选择中也有相同的表格".

提前致谢

解决方案

试试这个.

Sub Yahoo_BS()Dim xmlHttp 作为对象Dim TR_col 作为对象，Tr 作为对象Dim TD_col 作为对象，Td 作为对象Dim row As Long, col As Long设置 xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")myURL = "https://finance.yahoo.com/quote/SBUX/balance-sheet?p=SBUX"xmlHttp.Open "GET", myURL, FalsexmlHttp.setRequestHeader "Content-Type", "text/xml"xmlHttp.send将 html 调暗为对象设置 html = CreateObject("htmlfile")html.body.innerHTML = xmlHttp.responseTextDim tbl 作为对象设置 tbl = html.getElementById("Pos(r)")行 = 1列 = 1设置 TR_col = html.getElementsByTagName("TR")对于 TR_col 中的每个 Tr设置 TD_col = Tr.getElementsByTagName("TD")对于 TD_col 中的每个 Td单元格(行，列)= Td.innerText列 = 列 + 1下一个列 = 1行 = 行 + 1下一个结束子

请记住，这只是一种方法.我相信有几种方法可以达到同样的目的.另外，考虑循环遍历股票代码数组.您可以为每个代码添加新表，并为您查询的每个代码导入相关数据.

如果此解决方案对您有帮助，请点击勾选以表示答案有用.

Yahoo changed his structure for the financials (fundamental) report. In the past (last month) at the page https://finance.yahoo.com/quote/AAPL/financials?p=AAPL&guccounter=1 I could download the information regarding the financial Income Statement or Balance Sheet or Cash Flow in VBA Excel as a table. Now they have updated the "new" version that appears as a table but I cannot extract it con VBA Excel. There someone that can help me? If you need I can post the code in VBA regarding the old version, that can download all the tables in the HTML page.

To better explain I need the information in the yellow box. You can see that it appears as a table, but if you look inside in the HTML code there is not the normal tag regarding tables. I identify that the starting "table" has this tag <div class="D(tbrg)" but I don't know how to proceed to extract data. The same "table" is in the Balance Sheet and Cash Flow selection.

Thanks in advance

解决方案

Try this.

Sub Yahoo_BS()

Dim xmlHttp As Object
Dim TR_col As Object, Tr As Object
Dim TD_col As Object, Td As Object
Dim row As Long, col As Long

    Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    myURL = "https://finance.yahoo.com/quote/SBUX/balance-sheet?p=SBUX"
    xmlHttp.Open "GET", myURL, False
    xmlHttp.setRequestHeader "Content-Type", "text/xml"
    xmlHttp.send

    Dim html As Object
    Set html = CreateObject("htmlfile")
    html.body.innerHTML = xmlHttp.responseText

    Dim tbl As Object
    Set tbl = html.getElementById("Pos(r)")


    row = 1
    col = 1

    Set TR_col = html.getElementsByTagName("TR")
    For Each Tr In TR_col
        Set TD_col = Tr.getElementsByTagName("TD")
        For Each Td In TD_col
            Cells(row, col) = Td.innerText
            col = col + 1
        Next
        col = 1
        row = row + 1
    Next

End Sub

Keep in mind that this is just one way to do it. I'm sure there are several means to the same end. Also, think about looping through an array of ticker symbols. You can add new sheets for each ticker and import the relevant data for each symbol that you query for.

Please click the check to say the answer is useful if this solution helps you.

这篇关于用excel从雅​​虎金融中提取金融基础的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！