基于Beautiful Soup实现SEC资产负债表自动规整至Dataframe的技术需求

Got it, let's refine your approach to build a robust function that works with any SEC filing URL and returns a properly structured balance sheet DataFrame. Your current code is a great start, but it misses handling variations in SEC table formatting, header detection, and data normalization. Let's fix that step by step.

Issues with the Original Function #

Your existing code has a few key limitations:

• Only matches the exact string CONSOLIDATED BALANCE SHEETS (misses singular, lowercase, or date-suffixed variants like "Consolidated Balance Sheet as of December 31, 2019")
• Doesn't distinguish between header rows and data rows, leading to messy output
• Fails to handle numerical formatting (e.g., $123,456, (789) for negative values)
• Returns a raw list instead of a normalized DataFrame

Improved Solution #

Here's a revised function that addresses these gaps. It uses flexible pattern matching, cleans up table structure, normalizes numerical values, and returns a clean pandas DataFrame.

First, make sure you install pandas (if you haven't already):

pip install pandas

Full Code #

import requests
import re
import pandas as pd
from bs4 import BeautifulSoup

def get_normalized_balance_sheet(url):
    # Fetch and parse the page
    page = requests.get(url)
    soup = BeautifulSoup(page.content, 'html.parser')  # Specify parser for consistency
    
    # Flexible regex to match balance sheet titles (covers singular/plural, case variations, dates)
    balance_sheet_pattern = re.compile(r'consolidated balance sheet', re.IGNORECASE)
    balance_sheet_titles = soup.find_all(text=balance_sheet_pattern)
    
    if not balance_sheet_titles:
        raise ValueError("No consolidated balance sheet found in the provided URL")
    
    # Get the first valid balance sheet table (adjust if you need multiple)
    balance_sheet_table = None
    for title in balance_sheet_titles:
        table = title.find_next("table")
        if table:  # Ensure we found a table associated with the title
            balance_sheet_table = table
            break
    
    if not balance_sheet_table:
        raise ValueError("No table found associated with the balance sheet title")
    
    # Extract rows and clean cells
    rows = []
    for tr in balance_sheet_table.find_all("tr"):
        cells = []
        for td in tr.find_all(["td", "th"]):  # Include header cells too
            text = td.get_text(strip=True)
            # Clean numerical values: remove $, commas, convert (xxx) to -xxx
            if re.match(r'^\$?[\d,]+\.?\d*$', text) or re.match(r'^\([\d,]+\.?\d*\)$', text):
                text = text.replace('$', '').replace(',', '')
                if text.startswith('(') and text.endswith(')'):
                    text = '-' + text[1:-1]
            cells.append(text)
        # Skip empty rows
        if any(cells):
            rows.append(cells)
    
    # Detect header row (usually the row with date columns)
    header_idx = None
    for i, row in enumerate(rows):
        # Check if row contains date-like strings (e.g., 12/31/2019, Dec 31, 2019)
        date_count = sum(1 for cell in row if re.match(r'(\d{1,2}/\d{1,2}/\d{4})|([A-Za-z]+ \d{1,2}, \d{4})', cell))
        if date_count >= 2:  # Balance sheets typically have at least two periods
            header_idx = i
            break
    
    if header_idx is None:
        # Fallback: assume first non-empty row is header if no dates found
        header_idx = 0
    
    # Split into headers and data
    headers = rows[header_idx]
    data_rows = rows[header_idx+1:]
    
    # Create DataFrame
    df = pd.DataFrame(data_rows, columns=headers)
    
    # Clean up: fill empty account names with previous row (handles indented/sub-items)
    df.iloc[:, 0] = df.iloc[:, 0].ffill()
    
    # Convert numerical columns to float
    for col in df.columns[1:]:
        df[col] = pd.to_numeric(df[col], errors='coerce')
    
    return df

How It Works #

Let's break down the key improvements:

• Flexible Title Matching: Uses regex to match any variation of "consolidated balance sheet" regardless of case or extra text (like dates)
• Cell Cleaning: Automatically converts SEC-style numerical formatting (e.g., $(1,234) becomes -1234)
• Header Detection: Identifies the header row by looking for date values (common in balance sheets with multiple reporting periods)
• Data Normalization: Fills in missing account names (for indented sub-items) and converts columns to numerical types
• Error Handling: Raises clear errors if no balance sheet or table is found

Test the Function #

Let's use your example URL to test:

url = 'https://www.sec.gov/Archives/edgar/data/1326801/000132680120000013/fb-12312019x10k.htm'
balance_sheet_df = get_normalized_balance_sheet(url)
print(balance_sheet_df.head())

This will output a clean DataFrame with account names as the first column, and numerical values for each reporting period, ready for analysis.

Additional Tips #

• Handling Multiple Tables: If a filing has multiple balance sheets (e.g., parent vs consolidated), modify the code to loop through all found tables instead of taking the first one
• Rate Limiting: SEC has rate limits for scraping—add delays between requests if you're processing multiple URLs
• Edge Cases: Some filings might use nested tables; you can add logic to check for nested <table> tags and skip them if needed

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