同花顺API+Python+Excel：金融数据自动化报表完整实战

1. 项目背景与整体思路拆解

1.1 为什么我会做这个自动化方案

金融数据分析师、量化爱好者、运营同事，每天最头疼的一件事就是取数。尤其是从同花顺客户端里手工复制行情数据粘到Excel，看起来简单，实际上坑特别多：复制出来的数据经常串列、日期格式乱七八糟、涨停板的股票价格显示不完整，更不用说每天重复同样的操作有多消磨耐心。

我最初做这件事，就是因为月初要给团队整理一份沪深300成分股的行情汇总表。手动操作的话，先在同花顺里一个一个翻，翻完了选中复制，粘到Excel里还要清洗格式，遇到数据缺失还得回去重新核对，一个上午基本就搭进去了。后来我发现同花顺接口其实可以把这些数据以结构化JSON格式直接拉出来，再用Python清洗、写入Excel，整个过程能压缩到几十秒。

这篇文章就是把这套“金融数据自动化”的完整思路和踩坑记录写出来，核心解决三件事：怎么稳定地从同花顺接口取数、怎么把杂乱的接口数据变成干净的Excel表、以及怎么让这个流程自动跑起来。适合有一定命令行基础但没系统做过数据自动化的读者，也适合想给团队搭建数据管道但不知道从哪入手的同学。

1.2 方案选型：同花顺API + Python + Excel的组合逻辑

选同花顺接口，是因为它覆盖面足够广：股票、基金、期货、债券都能查到，行情、财务、指标公式类的数据也都有对应接口。更重要的是，同花顺客户端本身被国内金融从业者广泛使用，接口的数据口径和你在软件里看到的数字是一致的，这一点在后续核对数据时非常省心。

很多做量化的朋友首选是聚宽、米筐这类平台，但它们的侧重点是策略回测，数据下载和本地落盘反而不够灵活。还有一些付费数据库，一年大几万，个人玩根本没必要。同花顺接口在这中间提供了一个平衡点：免费或低成本、文档完整、调用简单。

至于为什么最终要落到Excel，这个争议更小。金融行业里Excel就是通用语言，领导要看表、同事要复算、审计要留痕，这些场景下PDF和CSV都不够友好。Excel支持数据透视表、函数公式、图表联动，拿到表的人不需要写代码就能继续做分析。所以我的方案定位很明确：用代码取数清洗，用Excel做最终交付物，两方面都发挥各自长处。

1.3 这套方案能帮到什么场景

如果你属于下面这些情况，方案可以直接抄作业：

• 每天需要从行情软件整理数据，再更新到固定格式Excel报表里；
• 做投资研究，需要批量拉取多只股票的历史行情；
• 需要定期把期货指标公式输出到表格里做对比分析；
• 给业务部门做数据支持，对方只认Excel附件。

这套流程的本质，是把“手动复制粘贴”替换成“脚本自动产出”，数据口径一致、过程留痕、耗时固定，长期来看不管是做日报、周报还是专项分析，都会轻松很多。

2. 同花顺API的接入与数据获取实操

2.1 环境准备：注册账号、获取Token、安装依赖

同花顺开放平台的接口走HTTP请求，返回JSON或CSV格式，用Python调就行了。第一步是去同花顺开放平台注册开发者账号，创建应用后拿到一个appKey和secretKey，后续每个请求都要带着鉴权参数。这里有一点要提醒：secretKey不要写死在代码里，更不要传到Git仓库，建议放到环境变量，用的时候再读出来。

Python端主要需要这几个库：

• requests：发HTTP请求，简单可靠；
• pandas：处理表格型数据，几乎就是为这个场景设计的；
• openpyxl：写Excel以及调整格式。

安装一行命令搞定：pip install requests pandas openpyxl。如果你用的Python版本比较老，建议顺手升级一下，Pandas新版本对数据类型推断做得更好，后面清洗数据能少操心不少。

2.2 获取Token与首次请求示例

我以行情日线接口为例写一个最小可用的请求代码。不同接口的域名和路径略有差异，但鉴权方式基本一致：

import os import requests app_key = os.environ.get("THS_APP_KEY") secret_key = os.environ.get("THS_SECRET_KEY") resp = requests.post( "https://openapi.10jqka.com.cn/auth/token", json={ "appKey": app_key, "secretKey": secret_key }, timeout=10 ) resp.raise_for_status() token = resp.json()["data"]["token"] print(token[:20] + "...")

拿到token之后，后续的行情请求把它放到Header或者Query里就行。做一个小提醒：token一般有有效期，建议封装成函数自动刷新，别写死一个token用一年，过期了排查起来很头疼。

2.3 常用接口与字段选择

同花顺接口体系里，我实际用下来最频繁的有三类：

• 实时行情接口：返回最新价、涨跌幅、成交量、成交额等。
• 历史K线接口：日线、周线、月线，以及分钟线，做回测和趋势分析时常用。
• 财务数据接口：利润表、资产负债表、现金流量表，研究基本面必用。

如果你关注期货，同花顺期货通那边也开放了指标公式相关的API，返回格式和股票接口比较接近，可以把数据拉下来后统一走同一套清洗流程。

调用时要特别留意字段精简。有些接口默认返回几十个字段，但真正要用的可能就五六个。一定要在请求参数里显式指定需要的字段，这样响应体小、解析快、出错概率也低。

2.4 三个必须养成的数据获取习惯

第一，请求要设置超时时间和重试机制。行情接口偶尔会抖动，不设超时的话，脚本可能卡在那几分钟，定时任务就直接失败了。

第二，分页拉取要记录游标或页数。有些接口单次最多返回几百条数据，比如拉一只股票三年的日线，默认参数可能只能拿回来最近几个月，这时候必须按照接口文档里的分页参数一页页取。

第三，做好本地缓存。接口数据短时间内的变化其实不大，可以设计一个简单的缓存目录，把当天已经拉过的数据存成CSV，下次请求直接读取，减少接口压力也提升效率。

我更建议在第一次实现时就把这三个习惯写进去，哪怕暂时用不上。因为后期转成定时任务后，你很难再回头补这些容错逻辑，从一开始就做好能避免很多半夜被电话打醒的场景。

3. 从接口数据到Excel的智能转换流程

3.1 数据清洗：先把接口响应变成一张“干净的表”

接口返回的数据，无论JSON还是CSV，直接拿来跟Excel对接是不可能的。金融数据里常见的坑：日期字段可能是字符串、数值字段里有空值、股票代码的开头零被自动去掉、除数为0导致显示异常。清洗这一步，我推荐统一用DataFrame来处理，流程固定为：解析 -> 类型转换 -> 缺失值处理 -> 排序去重 -> 复权计算。

以股票代码为例，很多接口把000001返回成1，如果你合并多个数据源，很容易因为这个静默出错。解决办法是在读取时就把代码列指定为字符串，然后统一补零：

df["code"] = df["code"].astype(str).str.zfill(6)

日期字段也一样，建议在导入Excel之前就统一成YYYY-MM-DD格式。如果你在Excel里看到左上角有个小绿三角，说明这个单元格被当成文本了，这种数据后续做透视表、求合都会莫名其妙地算不出来。与其在Excel里手动修复，不如在Python侧直接解决：

df["date"] = pd.to_datetime(df["date"]).dt.strftime("%Y-%m-%d")

还有复权问题。接口返回的原始价格是前复权、后复权还是不复权，一定要确认清楚。回测用后复权、看当前价格用不复权、复盘历史走势用前复权，这个选错了分析结论全偏。我见过不少人，拿不复权数据做长周期回测，得出一个很赚钱的策略，结果一复盘发现是分红除权造成的假信号。

3.2 写入Excel的三种方式怎么选

同一种结果可以有多种写法，我整理了一个对比：

实际项目中我一般混合使用：pandas.to_excel先把数据写进去，再用openpyxl打开文件调整样式。这样兼顾速度和灵活性。

3.3 报表美化的实用技巧

Excel报表拿到手，最重要的不是多花哨，而是信息能一眼抓住重点。我固定会做这几件事：

自动调整列宽，避免数字显示成###。openpyxl可以根据每列的最大长度动态设置列宽，一般字符串列取内容最大长度加2，数值列取固定宽度就行。

冻结前几行，用户往下滚动时表头不消失。这对几百行的行情表特别重要，用ws.freeze_panes一行就能实现。

涨跌颜色标注，涨红跌绿是A股传统，写代码时注意别搞反。用openpyxl的Font类给特定列加颜色，很简单但效果直观。

多指标拆分sheet。比如一个工作簿里，行情sheet放行情、财务sheet放财务、汇总sheet放统计指标，这样既方便别人使用，也是数据规范的体现。记得把每个sheet的标签颜色区分开，观感会好很多。

3.4 批量处理与公式自动化的衔接

数据写进Excel之后，还有一批常见需求：跨表引用、多条件筛选、数据透视表。这些不一定要用代码生成，写进Excel里的公式其实也能自动计算，比如用SUMIFS这类多条件求和函数，只要你在Python里把公式当字符串写入单元格，Excel打开后会自动计算。

如果你希望自动化程度更高，可以在输出Excel之后，再配合一个VBA宏来做二次加工。比如我见过有人用宏把样式统一的多个sheet合并成一个总表，或者自动生成数据透视表。宏的界面在Excel开发工具里可以录制，录出来之后拿回Python侧根据业务场景调整参数，效率很高。

4. 完整实操案例：沪深300成分股日线行情自动报表

4.1 需求拆解与流程设计

我们设定一个实际需求：每天收盘后，自动拉取沪深300全部成分股的日线行情，输出到一个Excel工作簿，包含所有股票的收盘价、涨跌幅、成交额、换手率，并且做一张汇总透视表。

流程拆解如下：

1. 获取沪深300成分股列表；
2. 遍历列表，逐只拉取日线接口；
3. 清洗并拼接成一张大表；
4. 写入Excel并做基础美化；
5. 设置定时任务，每天自动运行。

这个流程看起来不复杂，但有几个隐含难点：成分股列表可能会调整，不能写死；逐只拉取300只股票，如果接口有频率限制，要控制并发；数据拼接后要检查有没有缺失的股票，避免“这次少了几只”还不知道。

4.2 核心代码实现

先写一个接口封装类，统一管理token和请求参数：

import time import requests import pandas as pd class THSClient: def __init__(self, app_key, secret_key): self.app_key = app_key self.secret_key = secret_key self.base_url = "https://openapi.10jqka.com.cn" self.token = self._get_token() def _get_token(self): resp = requests.post( f"{self.base_url}/auth/token", json={"appKey": self.app_key, "secretKey": self.secret_key}, timeout=10 ) return resp.json()["data"]["token"] def daily_kline(self, code, start_date, end_date): resp = requests.get( f"{self.base_url}/stock/kline/daily", params={ "code": code, "start": start_date, "end": end_date, "fields": "code,date,close,change_pct,amount,turnover" }, headers={"Authorization": f"Bearer {self.token}"}, timeout=10 ) time.sleep(0.2) # 避免请求过快 return resp.json()["data"]["list"]

这里time.sleep(0.2)是对接口频率限制的一种基本尊重，300只股票理论上跑一分钟左右，完全可接受。如果追求速度，可以用线程池把并发调高，但建议先确认你的接口并发上限，别把自家token搞封了。

接下来是清洗与导出：

all_data = [] for code in stock_list: records = client.daily_kline(code, "2024-01-01", "2024-12-31") df = pd.DataFrame(records) df["code"] = df["code"].astype(str).str.zfill(6) df["date"] = pd.to_datetime(df["date"]).dt.strftime("%Y-%m-%d") df["close"] = pd.to_numeric(df["close"], errors="coerce") df["change_pct"] = pd.to_numeric(df["change_pct"], errors="coerce") df["amount"] = pd.to_numeric(df["amount"], errors="coerce") df["turnover"] = pd.to_numeric(df["turnover"], errors="coerce") all_data.append(df) result = pd.concat(all_data, ignore_index=True) result = result.dropna(subset=["close"]) result = result.sort_values(["date", "code"])

这里的关键操作是errors="coerce"，它能把无法转换的值变成NaN，避免一整列报错。然后把close为空的记录去掉，确保最终Excel里不出现半截行情。

导出Excel时，我会用两段式：先把拼接好的大表输出为sheet，然后用openpyxl补一次样式：

with pd.ExcelWriter("沪深300日线行情.xlsx", engine="openpyxl") as writer: result.to_excel(writer, sheet_name="日线行情", index=False) from openpyxl import load_workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter wb = load_workbook("沪深300日线行情.xlsx") ws = wb["日线行情"] # 冻结首行，方便下滚 ws.freeze_panes = "A2" # 根据内容自动调整列宽 for col in ws.iter_cols(min_row=1, max_row=1): for cell in col: max_len = max( len(str(cell.value)) if cell.value else 0, max(len(str(ws.cell(row=r, column=cell.column).value)) for r in range(2, ws.max_row + 1)) ) ws.column_dimensions[get_column_letter(cell.column)].width = min(max_len + 2, 20) # 涨跌幅列标色：A股习惯涨红跌绿 red_font = Font(color="C00000") green_font = Font(color="008000") for row in ws.iter_rows(min_row=2, min_col=3, max_col=3): for cell in row: try: val = float(cell.value) except (TypeError, ValueError): continue cell.font = red_font if val > 0 else green_font if val < 0 else cell.font wb.save("沪深300日线行情_美化.xlsx")

最终生成的文件，打开后第一行是字段名，第二行开始是2024年每日的行情数据，涨跌幅红色绿色标好，表头冻结，操作和阅读体验都很接近手工整理出来的日报。

4.3 定时运行的实现

本地电脑可以设置定时任务：Windows用任务计划程序，macOS和Linux用crontab。命令核心是调用你的Python脚本。我建议在脚本开头加一个日志功能，把每天运行的时间、处理股票数、异常数写到一个log文件里，这样第二天打开电脑，扫一眼日志就知道昨晚跑没跑成功。

有一种情况要特别注意：如果当天是非交易日，接口返回的K线数据可能是空的，这不算异常。脚本里要容错处理，判断返回空列表时直接跳过，不要导致整体崩溃。

5. 实施中踩过的坑与排查实录

5.1 API侧的典型报错

鉴权失败。最常见是token过期，或者把appKey和secretKey填反了。排查时先单独跑一遍获取token的请求，看能不能正常返回，如果这一步都过不去，后面全白搭。

请求超时或连接失败。这种大概率是网络环境或接口临时抖动，代码里要加try...except和重试逻辑。我习惯用tenacity库，一行装饰器就能实现指数退避重试，比手动写循环省事很多：

from tenacity import retry, stop_after_attempt, wait_exponential @retry(stop=stop_after_attempt(3), wait=wait_exponential(multiplier=1, min=2, max=10)) def fetch_with_retry(url, params, headers): return requests.get(url, params=params, headers=headers, timeout=10)

返回数据乱码。有些接口返回字段带UTF-8的BOM头，直接resp.json()可能报错。稳妥做法是先用resp.content.decode("utf-8-sig")再转JSON，能同时解决BOM和部分中文乱码问题。

5.2 Excel侧的经典问题

Excel无法复制粘贴、粘贴无反应。这个在Excel使用中非常高频。我遇到过几种情况：一是WPS和Office同时安装，两个软件的剪贴板冲突，解决办法是关掉WPS的驻留进程；二是Excel装了太多加载项，个别加载项抢占了剪贴板事件，可以到“开发工具 -> COM加载项”里逐个禁用排查；三是剪贴板服务本身卡死，重启Excel进程通常能解决。

打开CSV文件中文乱码。用Excel直接打开UTF-8编码的CSV，中文十有八九乱码。解决办法是导入时选择UTF-8编码，或者干脆在Python生成CSV时指定encoding="utf-8-sig"，这样Excel打开就能识别。更顺手的做法是：不要输出CSV，直接输出xlsx。

股票代码、日期、小数被Excel自动转换。这是金融数据落地Excel最烦的问题。比如股票代码000001，如果直接写入，Excel可能显示成1。前面说过，Python侧先把代码列转成字符串加补零，写入Excel后本质上已经是文本，就不会被Excel吃掉。日期列要确保是日期类型而不是文本，否则后续筛选、透视会很痛苦。

Excel打开提示宏被禁用或加载项不生效。如果本机宏安全级别设置太高，VBA和加载项会无法使用。可以到“文件 -> 选项 -> 信任中心 -> 宏设置”，选择“禁用所有宏，并发出通知”，这样打开带宏的文件时会提示启用。这个对团队分发报表时经常遇到，记得提醒使用方把宏启用，否则公式和宏都跑不了。

5.3 数据质量相关的坑

除数为0导致涨跌幅为空。新股上市首日可能没有前收盘价，接口算涨跌幅时会出现NULL，清洗阶段要统一处理成NaN，不要用0去填充。否则Excel里一排0，看起来像“全部跌停了”，会吓到人。

多条件筛选失效。有时候用户手筛日期和股票代码，发现筛不出来，打开单元格一看，日期其实是文本格式。这种在Excel里不太好批量修，可以在Python导出前用pd.to_datetime统一转换，从源头解决。

金融数据里的小数位数不一致。不同接口返回的价格精度不同，比如有的保留两位、有的保留四位。如果拼在一个表里，建议在最后统一用round(4)，避免合计时出现误差。虽然Excel本身能做精度控制，但源数据一致总归更稳妥。

6. 后续扩展方向与个人经验总结

6.1 从单表输出到多维分析工作簿

基础版是输出一张大表，进阶版可以让工作簿自带数据分析能力。比如生成sheet的时候，顺便用函数公式生成一个按行业、按日期的多条件汇总区域，或者用数据透视表替代手动计算。VBA里也可以录制一个“刷新透视表”的宏，绑定到打开工作簿时自动刷新。如果想做得更好看，还可以把每个行业每天的涨跌中位数做成迷你图，Excel的迷你图功能对这块支持得不错。

6.2 与前端和其他语言场景打通

在实际团队协作中，不一定所有数据都从Python侧输出Excel。比如Web前端用Vue做了数据看板，要把多个表格导出一个Excel文件，或者后端用C#把ListView数据导出来，本质上都是“把结构化数据映射到单元格”的问题。掌握了pandas和openpyxl这套思维，迁移到js-xlsx或NPOI时思路完全一致：先定义表头字段，再逐行填充，最后做样式美化。

6.3 我的几条实操心得

第一，自动化脚本的可靠度比炫技重要。用户不会在意你用了几行代码，只在乎早上打开报表有没有数据、数据对不对。所以优先保证容错、日志和失败告警。

第二，模板思维永远有价值。不要每个报表都从零开始设计，先用一份手工Excel把布局定成模板，Python侧只负责替换数据区域，这样团队协作最容易接受，因为格式不会“每回都不一样”。

第三，API是动态的。同花顺接口的字段和频率限制随时可能调整，定期去官方文档页面看看，或者写一个字段变更的监控脚本，避免某天接口悄悄变了，导致整个报表跑挂。

最后再说一个细节：报表文件的命名，最好带上日期，比如沪深300日线行情_2024-12-20.xlsx，方便归档和追溯。这个习惯看似简单，但真到月末复盘时，你会发现它救了大命。