如何用Python+BeautifulSoup抓取Yahoo Finance分析页数据并导出CSV？

核心优化方向 #

原代码存在未处理反爬机制、表格定位不准确、无异常容错、CSV输出重复表头等问题，以下是针对性优化后的实现方案：

优化后代码实现 #

import requests
import csv
from bs4 import BeautifulSoup
import gspread
from oauth2client.service_account import ServiceAccountCredentials

# 基础配置
BASE_URL = 'https://finance.yahoo.com/quote/{}/analysis?p={}'
CSV_FILENAME = 'stock_analysis_data.csv'
# Google Sheets配置（如需本地Excel读取可替换对应逻辑）
SHEET_NAME = 'Secondeon'
WORKSHEET_NAME = '.ANALYST'

# 模拟浏览器请求头，规避反爬拦截
HEADERS = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36'
}

def get_stock_symbols_from_gsheet():
    """从Google Sheets读取有效股票代码"""
    scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
    credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
    client = gspread.authorize(credentials)
    workbook = client.open(SHEET_NAME)
    worksheet = workbook.worksheet(WORKSHEET_NAME)
    # 提取A列第2行开始的非空代码
    symbols = [sym.strip() for sym in worksheet.col_values(1)[1:] if sym.strip()]
    return symbols

def scrape_analysis_data(symbol):
    """抓取单只股票的目标分析数据（红框标注的分析师预期类表格）"""
    url = BASE_URL.format(symbol, symbol)
    try:
        response = requests.get(url, headers=HEADERS, timeout=10)
        response.raise_for_status()  # 捕获HTTP请求错误
        soup = BeautifulSoup(response.text, 'html.parser')
        
        # 通过页面表格的class属性精准定位目标表格
        target_tables = soup.find_all('table', class_='W(100%) M(0) Bdcl(c)')
        if not target_tables:
            print(f"警告：未找到{symbol}的目标数据表格")
            return []
        
        # 整理数据，每行添加股票代码标识
        all_data = []
        for table in target_tables:
            headers = [th.text.strip() for th in table.find_all('th')]
            headers.insert(0, '股票代码')  # 表头前新增股票代码列
            rows = table.find_all('tr')[1:]  # 跳过表头行
            for row in rows:
                row_data = [td.text.strip() for td in row.find_all('td')]
                if row_data:
                    row_data.insert(0, symbol)
                    all_data.append(row_data)
        return all_data
    except Exception as e:
        print(f"抓取{symbol}失败：{str(e)}")
        return []

def main():
    symbols = get_stock_symbols_from_gsheet()
    if not symbols:
        print("未读取到有效股票代码")
        return
    
    # 写入CSV，仅在首次写入表头
    with open(CSV_FILENAME, 'w', newline='', encoding='utf-8-sig') as csv_file:
        writer = csv.writer(csv_file)
        header_written = False
        
        for symbol in symbols:
            data = scrape_analysis_data(symbol)
            if data:
                if not header_written:
                    writer.writerow(data[0])
                    header_written = True
                writer.writerows(data[1:])
    
    print(f"数据已成功导出到{CSV_FILENAME}")

if __name__ == '__main__':
    main()

关键优化说明 #

• 反爬规避：添加User-Agent请求头模拟真实浏览器，降低被Yahoo Finance拦截的概率
• 精准定位表格：通过页面表格的class属性匹配红框标注的分析师预期表格，避免抓取无关数据
• 异常容错：加入HTTP错误捕获、超时设置和空数据判断，防止单个股票抓取失败导致脚本中断
• CSV规范输出：仅写入一次表头，每行数据附带股票代码，方便后续Excel整理时区分不同股票
• 代码模块化：拆分读取代码、抓取数据、主逻辑等功能，便于维护和扩展

本地Excel读取替代方案 #

若无需从Google Sheets读取，可替换get_stock_symbols_from_gsheet函数为以下代码（需提前安装pandas）：

import pandas as pd

def get_stock_symbols_from_excel(excel_path):
    df = pd.read_excel(excel_path)
    # 假设股票代码在名为'Symbol'的列，根据实际表格调整
    symbols = df['Symbol'].dropna().astype(str).tolist()
    return [sym.strip() for sym in symbols if sym.strip()]

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