★ FEATURED ARTICLE 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 }, timeout10 ) 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代码量少适合快速落盘格式控制弱多sheet处理麻烦临时分析、快速导出openpyxl能精确控制单元格样式、合并、冻结窗格大数据量下写入稍慢需要美化报表、按模板输出xlsxwriter写入性能好支持图表、条件格式不能读取已有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工作簿包含所有股票的收盘价、涨跌幅、成交额、换手率并且做一张汇总透视表。流程拆解如下获取沪深300成分股列表遍历列表逐只拉取日线接口清洗并拼接成一张大表写入Excel并做基础美化设置定时任务每天自动运行。这个流程看起来不复杂但有几个隐含难点成分股列表可能会调整不能写死逐只拉取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}, timeout10 ) 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: fBearer {self.token}}, timeout10 ) 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], errorscoerce) df[change_pct] pd.to_numeric(df[change_pct], errorscoerce) df[amount] pd.to_numeric(df[amount], errorscoerce) df[turnover] pd.to_numeric(df[turnover], errorscoerce) all_data.append(df) result pd.concat(all_data, ignore_indexTrue) result result.dropna(subset[close]) result result.sort_values([date, code])这里的关键操作是errorscoerce它能把无法转换的值变成NaN避免一整列报错。然后把close为空的记录去掉确保最终Excel里不出现半截行情。导出Excel时我会用两段式先把拼接好的大表输出为sheet然后用openpyxl补一次样式with pd.ExcelWriter(沪深300日线行情.xlsx, engineopenpyxl) as writer: result.to_excel(writer, sheet_name日线行情, indexFalse) 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_row1, max_row1): for cell in col: max_len max( len(str(cell.value)) if cell.value else 0, max(len(str(ws.cell(rowr, columncell.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(colorC00000) green_font Font(color008000) for row in ws.iter_rows(min_row2, min_col3, max_col3): 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(stopstop_after_attempt(3), waitwait_exponential(multiplier1, min2, max10)) def fetch_with_retry(url, params, headers): return requests.get(url, paramsparams, headersheaders, timeout10)返回数据乱码。有些接口返回字段带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时指定encodingutf-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方便归档和追溯。这个习惯看似简单但真到月末复盘时你会发现它救了大命。
阅读完成 · 觉得有帮助？
