如何用Beautiful Soup正确提取雅虎财经资产负债表数据并生成DataFrame

问题背景 #

要抓取雅虎财经中MSFT（微软）的资产负债表，已通过Selenium实现点击「Expand All」按钮展开所有数据，但用Beautiful Soup提取数据时无法得到可用的表格格式：

• 第一种提取方式返回空索引列表，无法将数据整理为DataFrame
• 第二种提取方式在「Cash Equivalents」行失效，原因是该行2020、2019年无数据，导致数值数量与列数不匹配

当前代码如下：

# for scraping the balance sheet from Yahoo Finance
import pandas as pd
import requests
from datetime import datetime
from bs4 import BeautifulSoup
# importing selenium to click on the "Expand All" button before scraping the financial statements
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.common.by import By
from selenium.webdriver.support import expected_conditions as EC


def get_balance_sheet_from_yfinance(ticker):
    url = f"https://finance.yahoo.com/quote/{ticker}/balance-sheet?p={ticker}"

    options = Options()
    options.add_argument("start-maximized")
    driver = webdriver.Chrome(chrome_options=options)
    driver.get(url)
    WebDriverWait(driver, 3600).until(EC.element_to_be_clickable((
        By.XPATH, "//section[@data-test='qsp-financial']//span[text()='Expand All']"))).click()

    #content whole page in html format
    soup = BeautifulSoup(driver.page_source, 'html.parser')

    # get the column headers (i.e. 'Breakdown' row)
    div = soup.find_all('div', attrs={'class': 'D(tbhg)'})
    if len(div) < 1:
        print("Fail to retrieve table column header")
        exit(0)

    # get the list of columns from the column headers
    col = []
    for h in div[0].find_all('span'):
        text = h.get_text()
        if text != "Breakdown":
            col.append(datetime.strptime(text, "%m/%d/%Y"))

    df = pd.DataFrame(columns=col)


    # the following code returns an empty list for index (why?)
    # and values in a list that need actually be in a DataFrame
    idx = []
    for div in soup.find_all('div', attrs={'data-test': 'fin-row'}):
        for h in div.find_all('title'):
            text = h.get_text()
            idx.append(text)

    val = []
    for div in soup.find_all('div', attrs={'data-test': 'fin-col'}):
        for h in div.find_all('span'):
            num = int(h.get_text().replace(",", "")) * 1000
            val.append(num)

    # if the above part is commented out and this block is used instead
    # the following code manages to work well until the row "Cash Equivalents" 
    # that is because there are no entries for years 2020 and 2019 on this row
    """ for div in soup.find_all('div', attrs={'data-test': 'fin-row'}):
        i = 0
        idx = ""
        val = []
        for h in div.find_all('span'):
            if i % 5 == 0:
                idx = h.get_text()
            else:
                num = int(h.get_text().replace(",", "")) * 1000
                val.append(num)
            i += 1
        row = pd.DataFrame([val], columns=col, index=[idx])
        df = pd.concat([df, row], axis=0) """
    
    return idx, val


get_balance_sheet_from_yfinance("MSFT")

解决方案 #

原方法的问题在于：

1. 第一种方法错误地通过title标签提取Breakdown文本，实际该文本在span标签中
2. 第二种方法假设每行固定有5个span元素（1个Breakdown+4年数据），但缺失数据的行span数量不足，导致数值与列无法对应

修改后的代码逻辑：

• 先确定年份列的数量，确保每行数值数量与列数匹配，缺失数据用pd.NA填充
• 遍历每一行，提取Breakdown文本作为索引，再提取对应年份的数值，处理空值和格式转换
• 逐行构建DataFrame并合并

修改后的完整代码：

import pandas as pd
from datetime import datetime
from bs4 import BeautifulSoup
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.common.by import By
from selenium.webdriver.support import expected_conditions as EC


def get_balance_sheet_from_yfinance(ticker):
    url = f"https://finance.yahoo.com/quote/{ticker}/balance-sheet?p={ticker}"

    options = Options()
    options.add_argument("start-maximized")
    driver = webdriver.Chrome(options=options)
    driver.get(url)
    # 等待并点击Expand All按钮
    WebDriverWait(driver, 3600).until(EC.element_to_be_clickable((
        By.XPATH, "//section[@data-test='qsp-financial']//span[text()='Expand All']"))).click()

    # 解析页面HTML
    soup = BeautifulSoup(driver.page_source, 'html.parser')
    driver.quit()  # 关闭浏览器，避免残留进程

    # 获取表头（年份列）
    header_div = soup.find('div', attrs={'class': 'D(tbhg)'})
    if not header_div:
        print("无法获取表格表头")
        return pd.DataFrame()
    
    col_dates = []
    for span in header_div.find_all('span'):
        text = span.get_text(strip=True)
        if text != "Breakdown":
            try:
                col_dates.append(datetime.strptime(text, "%m/%d/%Y"))
            except ValueError:
                continue  # 跳过非日期格式的表头
    
    num_cols = len(col_dates)
    df = pd.DataFrame(columns=col_dates)

    # 遍历每一行数据
    for row_div in soup.find_all('div', attrs={'data-test': 'fin-row'}):
        spans = row_div.find_all('span')
        if not spans:
            continue
        
        # 获取Breakdown文本作为索引
        breakdown = spans[0].get_text(strip=True)
        # 提取该行的数值
        values = []
        for span in spans[1:]:
            text = span.get_text(strip=True)
            if not text:
                values.append(pd.NA)
            else:
                try:
                    # 处理数值格式，去掉逗号并乘以1000
                    num = int(text.replace(",", "")) * 1000
                    values.append(num)
                except ValueError:
                    values.append(pd.NA)
        
        # 确保数值数量与列数一致，不足的补NA
        while len(values) < num_cols:
            values.append(pd.NA)
        # 只取前num_cols个值，防止多余数据干扰
        values = values[:num_cols]

        # 添加行到DataFrame
        df.loc[breakdown] = values
    
    # 将日期列转换为更易读的格式（可选操作）
    df.columns = df.columns.strftime("%Y-%m-%d")
    return df


# 调用示例
msft_balance_sheet = get_balance_sheet_from_yfinance("MSFT")
print(msft_balance_sheet)

关键改进点 #

• 关闭浏览器：添加driver.quit()避免浏览器进程残留
• 空值处理：对缺失数据的单元格填充pd.NA，确保DataFrame结构完整
• 异常兼容：处理数值转换异常，避免因非数值文本导致程序崩溃
• 列数匹配：强制每行数值数量与年份列数一致，解决缺失数据行的结构问题

内容的提问来源于stack exchange，提问作者Mert