Python爬取股票财务数据导出Excel仅显示单个工作表问题求助

• 核心问题是xlwriter.save()方法放置位置错误：该方法被写在for循环内部，第一次循环写入首个工作表后就会直接关闭ExcelWriter对象，后续三次循环的工作表数据无法写入已关闭的文件流，最终仅保留第一个工作表。
• 额外可优化点：未做反爬规避，高频请求可能被网站拦截，导致部分页面数据抓取失败。

优先使用上下文管理器管理ExcelWriter对象，无需手动调用save/close方法，避免资源提前关闭的问题，同时可增加请求间隔降低反爬触发概率。

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

headers = {'user-agent' : 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:93.0) Gecko/20100101 Firefox/93.0', 'Access-Control-Allow-Origin': '*',
    'Access-Control-Allow-Methods': 'GET',
    'Access-Control-Allow-Headers': 'Content-Type',
    'Access-Control-Max-Age': '3600'}

ticker = "BABA"

urls= {}
urls['income annually']= f"https://stockanalysis.com/stocks/{ticker}/financials/"
urls['balance sheet annually']= f"https://stockanalysis.com/stocks/{ticker}/financials/balance-sheet/"
urls['cash flow annually']= f"https://stockanalysis.com/stocks/{ticker}/financials/cash-flow-statement/"
urls['ratios annually']= f"https://stockanalysis.com/stocks/{ticker}/financials/ratios/"

with pd.ExcelWriter(f'financial statements ({ticker}).xlsx', engine='xlsxwriter') as xlwriter:
    for key in urls.keys():
        response = requests.get(urls[key], headers=headers)
        soup = BeautifulSoup(response.content, 'html.parser')
        df = pd.read_html(str(soup), attrs={'id': 'financial-table'})[0]
        df.to_excel(xlwriter, sheet_name=key, index=False)
        # 请求间隔1秒，可按需调整
        time.sleep(1)

如果要保留原有写法，仅需将xlwriter.save()移动到for循环外侧即可：

xlwriter = pd.ExcelWriter(f'financial statements ({ticker}).xlsx', engine='xlsxwriter')

for key in urls.keys():
    response = requests.get(urls[key], headers=headers)
    soup = BeautifulSoup(response.content, 'html.parser')
    df = pd.read_html(str(soup), attrs={'id': 'financial-table'})[0]
    df.to_excel(xlwriter, sheet_name=key, index=False)

# 所有工作表写入完成后再统一保存
xlwriter.save()
xlwriter.close()

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