如何获取股票历史数据并导出为Excel？

Question

如何使用Python高效获取A股历史行情数据并导出为Excel文件？常见问题包括：使用`akshare`或`baostock`等开源库时，频繁请求导致被限流、数据缺失或日期格式不统一；多只股票批量下载时性能低下；导出Excel时未正确处理索引和编码，导致中文乱码或时间列显示异常。如何设计健壮的爬取机制并规范保存结构化数据？

Answer 1

一、Python高效获取A股历史行情数据并导出为Excel的完整实践路径

1. 基础工具选型与数据源接入

在Python生态中，akshare 和 baostock 是获取A股历史行情最常用的开源库。两者各有特点：

• akshare：接口简洁，支持多维度金融数据（包括股票、基金、期货），但依赖第三方API，稳定性受外部限制。
• baostock：由百度提供，专为A股设计，需登录会话，请求频率受限更严格，但数据结构规范。

安装命令如下：

pip install akshare baostock pandas openpyxl

以 akshare 获取单只股票日线数据为例：

import akshare as ak
df = ak.stock_zh_a_daily(symbol="sh600036", adjust="qfq")

date	open	close	high	low	volume
2020-01-02	34.75	35.19	35.30	34.60	12345678
2020-01-03	35.20	36.05	36.20	35.10	23456789

2. 高频请求限流问题分析与应对策略

常见现象是调用接口返回空值或抛出异常，本质是服务端设置了IP级或会话级速率限制。例如，baostock 要求每秒最多1次请求，而批量抓取上百只股票极易触发限制。

解决方案包括：

1. 引入固定延迟：time.sleep(0.5) 控制请求间隔；
2. 使用指数退避重试机制，在失败后自动延长等待时间；
3. 构建请求队列 + 多线程调度，实现并发可控；
4. 本地缓存已获取数据，避免重复拉取。

示例代码片段：

import time
import random

def safe_request(func, *args, max_retries=3):
    for i in range(max_retries):
        try:
            return func(*args)
        except Exception as e:
            wait = (2 ** i) + random.uniform(0, 1)
            time.sleep(wait)
    raise Exception("Max retries exceeded")

3. 批量下载性能优化：从串行到并行

当处理超过100只股票时，串行请求耗时可达数分钟以上。通过异步IO或多进程可显著提升效率。

推荐使用 concurrent.futures.ThreadPoolExecutor 实现线程池控制并发度：

from concurrent.futures import ThreadPoolExecutor
import akshare as ak

def fetch_stock(symbol):
    try:
        data = ak.stock_zh_a_daily(symbol=symbol, adjust="qfq")
        data['symbol'] = symbol
        return data
    except:
        return None

symbols = ["sh600036", "sz000001", "sh601398"] * 10  # 模拟批量
with ThreadPoolExecutor(max_workers=5) as executor:
    results = list(executor.map(fetch_stock, symbols))

graph TD A[开始批量下载] --> B{读取股票列表} B --> C[提交任务至线程池] C --> D[每个线程执行safe_request] D --> E[成功则返回DataFrame] D --> F[失败则指数退避重试] E --> G[汇总所有结果] F --> G G --> H[合并为总数据集]

4. 数据标准化与清洗流程

不同来源的数据存在日期格式不统一、字段缺失、索引类型混乱等问题。必须进行统一预处理：

• 确保日期列为 datetime 类型：pd.to_datetime(df['date'])；
• 设置统一索引：df.set_index('date', inplace=True)；
• 补全缺失字段（如复权因子）；
• 统一列名命名规范（如全小写、下划线分隔）。

清洗函数模板：

def standardize_df(raw_df, symbol):
    df = raw_df.copy()
    df['date'] = pd.to_datetime(df['date'])
    df.set_index('date', inplace=True)
    df.sort_index(inplace=True)
    df['symbol'] = symbol
    return df[['open', 'close', 'high', 'low', 'volume', 'symbol']]

5. 导出Excel中的编码与格式陷阱

直接使用 to_excel() 可能导致中文乱码或时间列显示为数字（Excel内部序列）。关键在于正确配置参数。

推荐写法：

with pd.ExcelWriter('a_share_data.xlsx', engine='openpyxl') as writer:
    standardized_df.to_excel(writer, sheet_name='HistoricalData', index=True)

注意事项：

• 使用 openpyxl 引擎支持大文件和样式控制；
• 避免将索引作为普通列重复写出；
• 若含中文路径或表名，确保系统编码为UTF-8；
• 可在Excel中手动设置时间列格式为“yyyy-mm-dd”。

6. 构建健壮的数据管道：模块化设计建议

为保障长期运行稳定性，应将整个流程拆分为独立模块：

1. DataFetcher：封装请求逻辑与重试机制；
2. DataCleaner：负责类型转换、字段对齐；
3. StorageManager：支持导出Excel/CSV/数据库；
4. MonitorLogger：记录成功率、耗时、异常堆栈。

最终输出的Excel文件结构示例：

date	open	close	high	low	volume	symbol
2020-01-02	34.75	35.19	35.30	34.60	12345678	sh600036
2020-01-03	35.20	36.05	36.20	35.10	23456789	sh600036
2020-01-02	13.50	13.60	13.70	13.45	8765432	sz000001
2020-01-03	13.61	13.80	13.85	13.55	9123456	sz000001
2020-01-04	13.82	13.75	13.90	13.70	7654321	sz000001
2020-01-05	13.76	13.95	14.00	13.72	8888888	sh601398
2020-01-06	13.96	14.10	14.15	13.92	9999999	sh601398
2020-01-07	14.11	14.05	14.20	14.00	1111111	sh601398
2020-01-08	14.06	14.25	14.30	14.02	2222222	sh601398
2020-01-09	14.26	14.40	14.45	14.22	3333333	sh601398