从同花顺里复制一段行情数据到Excel然后看着单元格里挤成一团的数字和乱码应该是每个金融打工人都有过的经历。“Excel无法复制粘贴”这类搜索常年霸榜我一度以为是Office坏了直到某天我统计了一下光是收盘后整理同花顺数据到Excel这个动作每天就要花掉我四十分钟——这还不算因为粘贴错列导致的返工。于是我决定把这整条链路彻底自动化直接从同花顺API取数经过Python清洗最终落到一份格式规整的Excel工作簿里连数据透视表和日报都一并生成好。这个项目的核心思路很简单数据获取用Python数据清洗用pandas落盘用openpyxlExcel端的交互展示交给VBA收尾。整条链路跑通之后收盘后我只需要打开那个Excel文件所有内容都已经更新完毕。这篇文章把这趟“智能转换之旅”的完整过程拆给你看包括接口选型、鉴权绕过、清洗陷阱、性能优化和上线后踩的坑。不管你是手动搬运数据的个人投资者还是被日报逼疯的运营这套思路都值得直接抄作业。1. 手动搬运数据的日子我忍了整整一年1.1 那些年复制粘贴翻的车我最早的做法和大多数人一样打开同花顺客户端选中要的数据CtrlC切到ExcelCtrlV。听起来十秒钟的事实际上每次都能整出幺蛾子。同花顺客户端的表格是带格式的直接粘贴过来经常碰到这么几种情况粘贴后单元格内容变成“文本格式”数字左上角顶个小绿三角sumifs、vlookup全算不出来这一条对应了热搜词里“excel表格怎么加小绿三角”和“excel无法粘贴”的困扰。列错位。同花顺的表头有时候是多级表头你看着是5列复制过来变成了7列中间多了两个空列之前做的公式全部错位。粘贴后小数位数丢失比如“2.35”变成“2.3”。这是因为Excel单元格格式是常规而源数据里的数字精度本来就不统一。最崩溃的一种复制到一半客户端弹了个窗口剪贴板被抢占Excel直接提示“不能粘贴”。我试过网上的偏方——先复制到记事本再从记事本复制到Excel。这个方法确实能解决部分格式问题但效率更低而且每一次复制都要选中、切换窗口、粘贴、再切换、再粘贴重复劳动的比例反而更高。1.2 自动化的目标边界踩了半年坑之后我给自己定了一个自动化项目的边界。范围必须收窄否则项目从一开始就会失控。我要做的是这几件事每日收盘后自动从同花顺API拉取自选股的日线行情。同时在周度维度拉取几只重点股票的资金流向数据。对数据进行必要的清洗消除复权、停牌、类型不统一等问题。生成一个结构化的Excel工作簿包含原始行情Sheet、清洗后数据Sheet、汇总数据透视表。最终用VBA宏实现打开工作簿时自动刷新透视表确保报表永远是新的。不做的部分同样重要不做实时盯盘不做自动下单不做回测框架。金融数据自动化最容易翻车的地方就是野心太大什么都想要最后连基本的收盘数据都跑不稳。先把一条链路打通再谈扩展。项目技术选型也一并确定Python 3.10 pandas openpyxl requestsExcel端用VBA。2. 打通同花顺API接口选型与鉴权那些事2.1 同花顺官方接口与第三方库怎么选同花顺系的数据接口有好几个方向我列了一张对比表把我实际调研过的方案都放进去方案类型成本稳定性适用场景同花顺iFinD官方金融终端高机构级收费极高机构投研、量化团队同花顺期货通指标公式API客户端内置免费中等个人用户、日线级数据第三方数据源如tushare、akshare非官方部分免费中等个人研究、原型验证爬虫/抓包非官方免费低不推荐接口一变就崩我最终选择的是“同花顺期货通指标公式API”作为主数据源。原因很简单第一它和同花顺客户端绑定数据口径一致不会出现同花顺客户端显示涨3%而第三方数据源显示涨2.8%的尴尬第二免费第三对于日线级别的行情数据它返回的结构足够稳定。这里要补充一句如果你本身已经有iFinD的账号直接走iFinD的接口会更省事因为它的鉴权和数据质量都更规范。我写这篇文章的场景是免费方案所以后续代码都以期货通指标公式API为例子。2.2 从一次登录态过期说起鉴权与会话保持刚开始调API的时候我天真地以为拿到token就能一劳永逸结果第二天脚本就跪了——返回了一个“会话过期”的错误。查了半天才发现同花顺客户端的token有时效性默认的会话保持时长并不长一旦过期所有请求都失效。解决方案是给token加两层保险第一层token持久化到本地文件。每次登录成功后把token和过期时间戳写入本地JSON文件下次启动脚本时先检查这个文件。第二层过期自动重登。如果API返回“会话过期”或者鉴权错误就自动重新调用登录接口拿到新token之后再重试一次原始请求。import json import time import requests TOKEN_FILE ths_token.json def load_token(): try: with open(TOKEN_FILE, r, encodingutf-8) as f: data json.load(f) if data[expire_time] time.time(): return data[token] except (FileNotFoundError, KeyError, json.JSONDecodeError): pass return None def save_token(token, expire_seconds3600): with open(TOKEN_FILE, w, encodingutf-8) as f: json.dump({ token: token, expire_time: time.time() expire_seconds }, f) def login(): # 这里填入实际的登录请求返回token和有效期 resp requests.post(https://api.yourprovider.com/login, json{ username: your_username, password: your_password }) token resp.json()[token] save_token(token) return token def get_token(): token load_token() if token is None: token login() return token这段代码的思路不只是为了同花顺API任何带有效期的令牌类接口都可以这么处理。别把token写死在代码里也别每次都重新登录。2.3 第一段能跑的行情拉取代码鉴权搞定之后最基础的行情拉取代码反而没什么玄机。我封装了一个fetch_kline函数按照股票代码和周期去请求日线数据import pandas as pd def fetch_kline(security_code, periodday, count250, endpointhttps://api.yourprovider.com/kline): token get_token() params { code: security_code, period: period, count: count, token: token } resp requests.get(endpoint, paramsparams) data resp.json() if data.get(status) session_expired: token login() params[token] token resp requests.get(endpoint, paramsparams) data resp.json() df pd.DataFrame(data[data]) return df这里有一个特别容易踩的坑同花顺系列API返回的字段名不一定是date、open、close可能是time、open_price、close_price甚至直接返回中文。所以拉到数据之后别急着存先打印列名心里有数再进清洗流程。我实际跑通第一版的时候返回的DataFrame长这样time open high low close volume amount 0 2024-11-01 10.25 10.48 10.18 10.42 152300 158723400.0 1 2024-11-04 10.40 10.55 10.30 10.45 134600 140873200.0看着挺正常但里面埋了不少雷下一节说清洗。3. 数据到手不等于能直接用清洗环节才是分水岭3.1 复权因子、停牌、缺失值原始行情数据最大的问题不是脏而是“口径不一致”。如果你只是看今天的涨跌幅直接用不复权数据没毛病。但如果你要在Excel里拉一个60日涨跌幅排名或者画一个均线不复权数据就会把除权除息日做成一根大阴线直接把你的分析带沟里去。所以我从第一版开始就统一使用前复权数据。前复权的含义是保持当前价格真实不变把历史价格按分红除权折算。对于日常技术分析前复权是最稳妥的选择。但要注意历史行情导出后后续又发生新的除权除息那之前拉下来的数据口径就变了所以最好每周全量刷新一次不要攒一个月再更新。停牌日的处理也很关键。API返回的数据里停牌日期直接缺行不是NaN而是根本没有那一条记录。这时候如果你用pandas的shift算涨跌幅就会把停牌前和复牌后的两个价格算在一起得到一段虚假的巨幅涨跌。我的处理方式是先建一个完整的交易日历然后用reindex把缺失的日期补上价格字段填NaN涨跌幅也置为空。这样Excel里看到的就是空白而不是一个离谱的数字。import pandas as pd def clean_kline(df): df df.copy() df.columns [date, open, high, low, close, volume, amount] df[date] pd.to_datetime(df[date]) df df.sort_values(date).drop_duplicates(subsetdate, keeplast) df df.set_index(date) # 按交易日补齐缺失日期 trade_calendar pd.bdate_range(df.index.min(), df.index.max()) df df.reindex(trade_calendar) # 计算涨跌幅缺失位置保持NaN df[pct_chg] df[close].pct_change() * 100 df[pct_chg] df[pct_chg].round(2) return df.reset_index().rename(columns{index: date})3.2 单位与精度统一别以为拉回来的数字就能直接用。我发现这个API返回的成交量单位有时候是“手”有时候是“股”而成交额单位是元但精度偶尔会飘。具体来说我需要把成交量统一换算成“手”。1手等于100股如果接口返回的是股数就除以100如果已经是手数就不动。原始字段名不会告诉你单位所以我写了一个判断逻辑如果单日成交量中位数大于100万判定为股数统一除以100否则视为手数。数字类型是另一个隐形坑。API返回的JSON里有些字段是字符串比如10.4200带引号。如果你直接把它当数值去Excel里求和sumifs会返回0。解决方案是拉下来之后立刻做一轮类型转换顺手把字符串里的逗号清理掉for col in [open, high, low, close, amount]: df[col] df[col].astype(str).str.replace(,, ) df[col] pd.to_numeric(df[col], errorscoerce)“小绿三角”问题在数据清洗阶段就能彻底消灭不需要等到Excel里去抓狂。3.3 清洗后的数据结构设计清洗之后的数据最终要落到Excel所以表结构必须在清洗这一步就定好。我的经验是把计算字段和原始字段分开不要在同一个Sheet里混着放。最终的Sheet设计是这样的raw_data原始数据尽量不加工留作核对。clean_data清洗后数据只保留标准化字段包括date、code、name、open、high、low、close、volume_lot、amount、pct_chg。daily_summary按股票日期汇总的统计结果比如区间涨跌幅、最高价、最低价、成交量总和。pivot_report数据透视表方便日常查看。字段命名统一用英文小写下划线不要用中文。为什么因为VBA脚本获取中文列名很容易乱码而英文列名在openpyxl和VBA之间不会有编码问题。4. DataFrame写进Excel格式、性能与崩溃排错4.1 先用pandas还是openpyxl数据清洗完接下来就是写Excel。这里我纠结过一阵子pandas自带to_excel几行代码就能写完但格式控制力太弱openpyxl格式控制力强但逐单元格写入大表会慢到怀疑人生。最终我的方案是分两走先用pandas.to_excel快速填充数据再用openpyxl调整格式。这样兼顾速度和控制力。不过pandas.to_excel有个老毛病单次写入的时候不会自动调整列宽表头样式也丑所以格式微调必须交给openpyxl。4.2 让Excel报表真正“能看”样式与冻结窗格做金融数据Excel最基本的三件事表头加粗、数字保留两位小数、冻结首行。别小看这三件事直接决定报表的专业感。下面这段代码是我在项目中实际使用的格式化函数from openpyxl import load_workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter def style_excel(file_path, sheet_name, header_color1F4E78): wb load_workbook(file_path) ws wb[sheet_name] # 表头样式 header_font Font(boldTrue, colorFFFFFF, size11) header_fill PatternFill(start_colorheader_color, end_colorheader_color, fill_typesolid) header_align Alignment(horizontalcenter, verticalcenter) for cell in ws[1]: cell.font header_font cell.fill header_fill cell.alignment header_align # 冻结首行 ws.freeze_panes A2 # 自适应列宽 for col in ws.columns: max_len 0 col_letter get_column_letter(col[0].column) for cell in col: if cell.value is not None: cell_len len(str(cell.value)) if cell_len max_len: max_len cell_len ws.column_dimensions[col_letter].width max_len 4 wb.save(file_path)这里有个细节列宽自适应千万别做全表遍历一旦Sheet里有几千行这个函数的执行时间会成倍增长。我的做法是只遍历前200行因为列宽主要由数据位数决定200行足够覆盖最长数字和日期了。金融数据的列宽其实相对固定日期10位价格6位左右成交量手数6到7位直接设一个固定宽度更高效。4.3 千行数据卡死分批写入与引擎选择我最早用pandas.to_excel默认引擎openpyxl一次写入五千行跑了将近一分钟。后来发现可以指定enginexlsxwriter速度快了三倍以上。但xlsxwriter不能用openpyxl加载修改已存在的文件。于是我把写入流程拆成两步新文件用xlsxwriter写入后续的格式微调用openpyxl打开再保存。如果数据量继续往上走比如一个Sheet要写几万行我建议分批写入而不是一次性构造大DataFrame这样内存占用会更平滑with pd.ExcelWriter(output.xlsx, enginexlsxwriter) as writer: for i in range(0, len(df), 2000): chunk df.iloc[i:i2000] chunk.to_excel(writer, sheet_nameclean_data, startrowi1, indexFalse, header(i 0))注意header参数只在第一次写入时为True否则每个分片都会重复写表头。实测下来分批写入三万多行数据耗时只比一次性写入多一点点但内存占用少了将近一半。如果你的电脑配置不高这个方法值得借鉴。5. VBA接力Excel端的最后一公里5.1 为什么还需要VBA宏Python把数据写进Excel之后工作并没有结束。一个只装了数据的Excel对金融分析来说还差一个环节——数据透视表。每次数据更新之后透视表的缓存不会自动刷新。如果读者打开Excel看到的是旧透视结果那整个自动化就白做了。我还设计了“打开文件自动校验行数”的逻辑如果数据行数与昨日一致说明今天的数据还没更新弹窗提醒避免拿着昨天数据做今天的决策。这些交互逻辑放在VBA里最方便因为它能直接操作Excel对象不需要再通过Python去和Excel进程通信。5.2 一个自动刷新透视表的宏这里分享一段我一直在用的VBA代码功能是打开工作簿时自动刷新所有数据透视表并弹出行数校验提示Private Sub Workbook_Open() Dim ws As Worksheet Dim pt As PivotTable Dim totalRows As Long Dim expectedRows As Long 刷新所有数据透视表 For Each ws In ThisWorkbook.Worksheets For Each pt In ws.PivotTables pt.RefreshTable pt.PivotCache.Refresh Next pt Next ws 检查clean_data行数是否达到预期 totalRows ThisWorkbook.Worksheets(clean_data).Cells(Rows.Count, 1).End(xlUp).Row - 1 expectedRows 250 * ThisWorkbook.Worksheets(config).Range(B2).Value If totalRows expectedRows * 0.9 Then MsgBox 数据行数异常当前 totalRows 行预期 expectedRows 行。请检查数据源。, vbExclamation End If Application.StatusBar 数据已更新透视表已刷新 End Sub这段代码挂在ThisWorkbook模块里而不是普通模块。放在Workbook_Open事件里能在文件打开的一瞬间执行。注意pt.PivotCache.Refresh这一行光是RefreshTable有时候不够如果透视表的源数据范围变了必须刷新缓存才能重算。我见过很多人的透视表不更新就是因为只调用了RefreshTable没有刷新缓存。5.3 Python与VBA的边界有人会问既然已经有了Python为什么不用Python直接操作Excel生成透视表还要VBA参与我是这样划分的Python负责“数据获取、清洗、写入”这是它的强项VBA负责“打开文件后的交互、刷新、校验”这是Excel原生环境最顺手的事。重点逻辑全部放在Python侧VBA只做收尾。如果你完全不想碰VBA也可以在Python里用openpyxl的PivotTable类创建透视表但代码量会非常大而且透视表的字段布局没有一个可视化界面调试起来很痛苦。我的建议是透视表用手工做一次存成模板然后让Python每次更新数据时保留透视表结构只替换数据源区域VBA负责刷新。这个组合是性价比最高的。6. 定时任务上线后我踩过的新坑6.1 脚本挂了没人知道链路跑通之后我满心欢喜地用一个Windows计划任务把脚本挂成每日16:30自动执行。结果第一周就翻车了某天同花顺客户端登录状态异常脚本在拉数阶段就抛异常退出但计划任务显示“上次结果0x1”根本不会主动通知我。第二天开盘前我打开Excel看到还是昨天的数据才知道脚本挂了。解决方案是给脚本加上日志和主动告警。日志用Python自带的logging模块写到文件告警用企业微信机器人或者钉钉机器人推一条消息到手机。关键不是告警本身而是在脚本一开始就捕获异常把所有异常都汇总到一条告警里避免一屏刷屏。import logging import traceback logging.basicConfig( filenamedaily_report.log, levellogging.INFO, format%(asctime)s %(levelname)s %(message)s ) def send_alert(msg): # 调用企业微信/钉钉webhook pass try: run_all() logging.info(今日报表生成成功) except Exception: error_info traceback.format_exc() logging.error(error_info) send_alert(金融数据自动化脚本异常\n error_info)6.2 同花顺客户端不能后台运行另一个坑是定时任务执行时同花顺客户端并没有打开或者打开了但没有成功登录导致API鉴权失败。我一开始的解决办法是在批处理脚本里先启动同花顺客户端再用Python轮询等待token生效start /d C:\Program Files\hexin hexin.exe timeout /t 15 /nobreak python daily_report.py这个方案能用但非常依赖本机环境。如果你的机器上盖了屏保或者锁屏客户端界面没渲染完登录可能就失败了。后来我把同花顺客户端的开机自动登录打开并在Python里增加了“获取token失败则等待3分钟重试”的机制才算稳定下来。6.3 增量更新与全量重建的选择一开始我图省事每天全量拉取250个交易日的行情全量重新生成Excel。跑了一个月后发现两个问题一是数据量渐大后脚本耗时增加二是如果某天拉取的数据源有问题全量重建会把整个文件的历史字段都污染。于是我改成了增量更新策略每次只拉最近5个交易日的数据更新到现有Excel的clean_dataSheet里透视表范围自动扩展。具体做法是在Python里读取Excel现有的日期最大值只拉这个日期之后的数据然后追加写入。但这里又有一个新坑Excel里如果已经有旧格式的日期字符串跟新数据的日期格式对不上追加就会产生重复或空白。我的经验是增量更新之前先把日期列统一转成datetime用pandas写回时再格式化成YYYY-MM-DD保持Excel端格式一致。这个策略运行了两周后脚本耗时从原来的40秒降到了8秒而且Excel文件体积也没有无限膨胀。7. 这套链路跑了一季度之后我的一些真实体会如果你问我最大的体会是什么我会说金融数据自动化的难点从来不在API本身而在那些接口文档没写明的边界条件上。比如token过期不会报错而是返回一个看似正常但字段为空的数据结构停牌日不会显示为NaN而是直接少一行成交量单位不一定统一可能根据指数不同而不同。这些问题靠读文档是发现不了的必须在真实行情数据上跑一段时间才会暴露。还有一件事我后来才想明白自动化并不意味着完全不需要人看。我的脚本每周仍然会做一次“人工核验”——随机挑几只股票从同花顺客户端手动看一眼收盘价跟我们Excel里的数据对一下确认没有系统性偏差。这项工作只需要十分钟但能及时抓住很多隐藏问题。最后分享一个实用小技巧我在Excel的configSheet里放了一张参数表包括股票列表、数据周期、预期数据行数等。Python脚本每天读这个Sheet来调整生成逻辑VBA宏也读同一张表来做校验。这样如果我想改股票池或者调周期不需要改任何代码只改Excel里的单元格就行。这套设计让我后续维护的负担降到了最低。这个项目能继续扩展的方向还有很多比如把日线换成分钟线做盘中监控或者把Excel输出换成SQLite数据库供回测调用。但不管怎么扩展从同花顺API到Excel这条数据链路的根基算是打牢了。只要数据源稳定清洗逻辑严谨后面的分析工作就有据可依。
同花顺数据自动导入Excel：Python清洗+VBA刷新全流程解析
从同花顺里复制一段行情数据到Excel然后看着单元格里挤成一团的数字和乱码应该是每个金融打工人都有过的经历。“Excel无法复制粘贴”这类搜索常年霸榜我一度以为是Office坏了直到某天我统计了一下光是收盘后整理同花顺数据到Excel这个动作每天就要花掉我四十分钟——这还不算因为粘贴错列导致的返工。于是我决定把这整条链路彻底自动化直接从同花顺API取数经过Python清洗最终落到一份格式规整的Excel工作簿里连数据透视表和日报都一并生成好。这个项目的核心思路很简单数据获取用Python数据清洗用pandas落盘用openpyxlExcel端的交互展示交给VBA收尾。整条链路跑通之后收盘后我只需要打开那个Excel文件所有内容都已经更新完毕。这篇文章把这趟“智能转换之旅”的完整过程拆给你看包括接口选型、鉴权绕过、清洗陷阱、性能优化和上线后踩的坑。不管你是手动搬运数据的个人投资者还是被日报逼疯的运营这套思路都值得直接抄作业。1. 手动搬运数据的日子我忍了整整一年1.1 那些年复制粘贴翻的车我最早的做法和大多数人一样打开同花顺客户端选中要的数据CtrlC切到ExcelCtrlV。听起来十秒钟的事实际上每次都能整出幺蛾子。同花顺客户端的表格是带格式的直接粘贴过来经常碰到这么几种情况粘贴后单元格内容变成“文本格式”数字左上角顶个小绿三角sumifs、vlookup全算不出来这一条对应了热搜词里“excel表格怎么加小绿三角”和“excel无法粘贴”的困扰。列错位。同花顺的表头有时候是多级表头你看着是5列复制过来变成了7列中间多了两个空列之前做的公式全部错位。粘贴后小数位数丢失比如“2.35”变成“2.3”。这是因为Excel单元格格式是常规而源数据里的数字精度本来就不统一。最崩溃的一种复制到一半客户端弹了个窗口剪贴板被抢占Excel直接提示“不能粘贴”。我试过网上的偏方——先复制到记事本再从记事本复制到Excel。这个方法确实能解决部分格式问题但效率更低而且每一次复制都要选中、切换窗口、粘贴、再切换、再粘贴重复劳动的比例反而更高。1.2 自动化的目标边界踩了半年坑之后我给自己定了一个自动化项目的边界。范围必须收窄否则项目从一开始就会失控。我要做的是这几件事每日收盘后自动从同花顺API拉取自选股的日线行情。同时在周度维度拉取几只重点股票的资金流向数据。对数据进行必要的清洗消除复权、停牌、类型不统一等问题。生成一个结构化的Excel工作簿包含原始行情Sheet、清洗后数据Sheet、汇总数据透视表。最终用VBA宏实现打开工作簿时自动刷新透视表确保报表永远是新的。不做的部分同样重要不做实时盯盘不做自动下单不做回测框架。金融数据自动化最容易翻车的地方就是野心太大什么都想要最后连基本的收盘数据都跑不稳。先把一条链路打通再谈扩展。项目技术选型也一并确定Python 3.10 pandas openpyxl requestsExcel端用VBA。2. 打通同花顺API接口选型与鉴权那些事2.1 同花顺官方接口与第三方库怎么选同花顺系的数据接口有好几个方向我列了一张对比表把我实际调研过的方案都放进去方案类型成本稳定性适用场景同花顺iFinD官方金融终端高机构级收费极高机构投研、量化团队同花顺期货通指标公式API客户端内置免费中等个人用户、日线级数据第三方数据源如tushare、akshare非官方部分免费中等个人研究、原型验证爬虫/抓包非官方免费低不推荐接口一变就崩我最终选择的是“同花顺期货通指标公式API”作为主数据源。原因很简单第一它和同花顺客户端绑定数据口径一致不会出现同花顺客户端显示涨3%而第三方数据源显示涨2.8%的尴尬第二免费第三对于日线级别的行情数据它返回的结构足够稳定。这里要补充一句如果你本身已经有iFinD的账号直接走iFinD的接口会更省事因为它的鉴权和数据质量都更规范。我写这篇文章的场景是免费方案所以后续代码都以期货通指标公式API为例子。2.2 从一次登录态过期说起鉴权与会话保持刚开始调API的时候我天真地以为拿到token就能一劳永逸结果第二天脚本就跪了——返回了一个“会话过期”的错误。查了半天才发现同花顺客户端的token有时效性默认的会话保持时长并不长一旦过期所有请求都失效。解决方案是给token加两层保险第一层token持久化到本地文件。每次登录成功后把token和过期时间戳写入本地JSON文件下次启动脚本时先检查这个文件。第二层过期自动重登。如果API返回“会话过期”或者鉴权错误就自动重新调用登录接口拿到新token之后再重试一次原始请求。import json import time import requests TOKEN_FILE ths_token.json def load_token(): try: with open(TOKEN_FILE, r, encodingutf-8) as f: data json.load(f) if data[expire_time] time.time(): return data[token] except (FileNotFoundError, KeyError, json.JSONDecodeError): pass return None def save_token(token, expire_seconds3600): with open(TOKEN_FILE, w, encodingutf-8) as f: json.dump({ token: token, expire_time: time.time() expire_seconds }, f) def login(): # 这里填入实际的登录请求返回token和有效期 resp requests.post(https://api.yourprovider.com/login, json{ username: your_username, password: your_password }) token resp.json()[token] save_token(token) return token def get_token(): token load_token() if token is None: token login() return token这段代码的思路不只是为了同花顺API任何带有效期的令牌类接口都可以这么处理。别把token写死在代码里也别每次都重新登录。2.3 第一段能跑的行情拉取代码鉴权搞定之后最基础的行情拉取代码反而没什么玄机。我封装了一个fetch_kline函数按照股票代码和周期去请求日线数据import pandas as pd def fetch_kline(security_code, periodday, count250, endpointhttps://api.yourprovider.com/kline): token get_token() params { code: security_code, period: period, count: count, token: token } resp requests.get(endpoint, paramsparams) data resp.json() if data.get(status) session_expired: token login() params[token] token resp requests.get(endpoint, paramsparams) data resp.json() df pd.DataFrame(data[data]) return df这里有一个特别容易踩的坑同花顺系列API返回的字段名不一定是date、open、close可能是time、open_price、close_price甚至直接返回中文。所以拉到数据之后别急着存先打印列名心里有数再进清洗流程。我实际跑通第一版的时候返回的DataFrame长这样time open high low close volume amount 0 2024-11-01 10.25 10.48 10.18 10.42 152300 158723400.0 1 2024-11-04 10.40 10.55 10.30 10.45 134600 140873200.0看着挺正常但里面埋了不少雷下一节说清洗。3. 数据到手不等于能直接用清洗环节才是分水岭3.1 复权因子、停牌、缺失值原始行情数据最大的问题不是脏而是“口径不一致”。如果你只是看今天的涨跌幅直接用不复权数据没毛病。但如果你要在Excel里拉一个60日涨跌幅排名或者画一个均线不复权数据就会把除权除息日做成一根大阴线直接把你的分析带沟里去。所以我从第一版开始就统一使用前复权数据。前复权的含义是保持当前价格真实不变把历史价格按分红除权折算。对于日常技术分析前复权是最稳妥的选择。但要注意历史行情导出后后续又发生新的除权除息那之前拉下来的数据口径就变了所以最好每周全量刷新一次不要攒一个月再更新。停牌日的处理也很关键。API返回的数据里停牌日期直接缺行不是NaN而是根本没有那一条记录。这时候如果你用pandas的shift算涨跌幅就会把停牌前和复牌后的两个价格算在一起得到一段虚假的巨幅涨跌。我的处理方式是先建一个完整的交易日历然后用reindex把缺失的日期补上价格字段填NaN涨跌幅也置为空。这样Excel里看到的就是空白而不是一个离谱的数字。import pandas as pd def clean_kline(df): df df.copy() df.columns [date, open, high, low, close, volume, amount] df[date] pd.to_datetime(df[date]) df df.sort_values(date).drop_duplicates(subsetdate, keeplast) df df.set_index(date) # 按交易日补齐缺失日期 trade_calendar pd.bdate_range(df.index.min(), df.index.max()) df df.reindex(trade_calendar) # 计算涨跌幅缺失位置保持NaN df[pct_chg] df[close].pct_change() * 100 df[pct_chg] df[pct_chg].round(2) return df.reset_index().rename(columns{index: date})3.2 单位与精度统一别以为拉回来的数字就能直接用。我发现这个API返回的成交量单位有时候是“手”有时候是“股”而成交额单位是元但精度偶尔会飘。具体来说我需要把成交量统一换算成“手”。1手等于100股如果接口返回的是股数就除以100如果已经是手数就不动。原始字段名不会告诉你单位所以我写了一个判断逻辑如果单日成交量中位数大于100万判定为股数统一除以100否则视为手数。数字类型是另一个隐形坑。API返回的JSON里有些字段是字符串比如10.4200带引号。如果你直接把它当数值去Excel里求和sumifs会返回0。解决方案是拉下来之后立刻做一轮类型转换顺手把字符串里的逗号清理掉for col in [open, high, low, close, amount]: df[col] df[col].astype(str).str.replace(,, ) df[col] pd.to_numeric(df[col], errorscoerce)“小绿三角”问题在数据清洗阶段就能彻底消灭不需要等到Excel里去抓狂。3.3 清洗后的数据结构设计清洗之后的数据最终要落到Excel所以表结构必须在清洗这一步就定好。我的经验是把计算字段和原始字段分开不要在同一个Sheet里混着放。最终的Sheet设计是这样的raw_data原始数据尽量不加工留作核对。clean_data清洗后数据只保留标准化字段包括date、code、name、open、high、low、close、volume_lot、amount、pct_chg。daily_summary按股票日期汇总的统计结果比如区间涨跌幅、最高价、最低价、成交量总和。pivot_report数据透视表方便日常查看。字段命名统一用英文小写下划线不要用中文。为什么因为VBA脚本获取中文列名很容易乱码而英文列名在openpyxl和VBA之间不会有编码问题。4. DataFrame写进Excel格式、性能与崩溃排错4.1 先用pandas还是openpyxl数据清洗完接下来就是写Excel。这里我纠结过一阵子pandas自带to_excel几行代码就能写完但格式控制力太弱openpyxl格式控制力强但逐单元格写入大表会慢到怀疑人生。最终我的方案是分两走先用pandas.to_excel快速填充数据再用openpyxl调整格式。这样兼顾速度和控制力。不过pandas.to_excel有个老毛病单次写入的时候不会自动调整列宽表头样式也丑所以格式微调必须交给openpyxl。4.2 让Excel报表真正“能看”样式与冻结窗格做金融数据Excel最基本的三件事表头加粗、数字保留两位小数、冻结首行。别小看这三件事直接决定报表的专业感。下面这段代码是我在项目中实际使用的格式化函数from openpyxl import load_workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter def style_excel(file_path, sheet_name, header_color1F4E78): wb load_workbook(file_path) ws wb[sheet_name] # 表头样式 header_font Font(boldTrue, colorFFFFFF, size11) header_fill PatternFill(start_colorheader_color, end_colorheader_color, fill_typesolid) header_align Alignment(horizontalcenter, verticalcenter) for cell in ws[1]: cell.font header_font cell.fill header_fill cell.alignment header_align # 冻结首行 ws.freeze_panes A2 # 自适应列宽 for col in ws.columns: max_len 0 col_letter get_column_letter(col[0].column) for cell in col: if cell.value is not None: cell_len len(str(cell.value)) if cell_len max_len: max_len cell_len ws.column_dimensions[col_letter].width max_len 4 wb.save(file_path)这里有个细节列宽自适应千万别做全表遍历一旦Sheet里有几千行这个函数的执行时间会成倍增长。我的做法是只遍历前200行因为列宽主要由数据位数决定200行足够覆盖最长数字和日期了。金融数据的列宽其实相对固定日期10位价格6位左右成交量手数6到7位直接设一个固定宽度更高效。4.3 千行数据卡死分批写入与引擎选择我最早用pandas.to_excel默认引擎openpyxl一次写入五千行跑了将近一分钟。后来发现可以指定enginexlsxwriter速度快了三倍以上。但xlsxwriter不能用openpyxl加载修改已存在的文件。于是我把写入流程拆成两步新文件用xlsxwriter写入后续的格式微调用openpyxl打开再保存。如果数据量继续往上走比如一个Sheet要写几万行我建议分批写入而不是一次性构造大DataFrame这样内存占用会更平滑with pd.ExcelWriter(output.xlsx, enginexlsxwriter) as writer: for i in range(0, len(df), 2000): chunk df.iloc[i:i2000] chunk.to_excel(writer, sheet_nameclean_data, startrowi1, indexFalse, header(i 0))注意header参数只在第一次写入时为True否则每个分片都会重复写表头。实测下来分批写入三万多行数据耗时只比一次性写入多一点点但内存占用少了将近一半。如果你的电脑配置不高这个方法值得借鉴。5. VBA接力Excel端的最后一公里5.1 为什么还需要VBA宏Python把数据写进Excel之后工作并没有结束。一个只装了数据的Excel对金融分析来说还差一个环节——数据透视表。每次数据更新之后透视表的缓存不会自动刷新。如果读者打开Excel看到的是旧透视结果那整个自动化就白做了。我还设计了“打开文件自动校验行数”的逻辑如果数据行数与昨日一致说明今天的数据还没更新弹窗提醒避免拿着昨天数据做今天的决策。这些交互逻辑放在VBA里最方便因为它能直接操作Excel对象不需要再通过Python去和Excel进程通信。5.2 一个自动刷新透视表的宏这里分享一段我一直在用的VBA代码功能是打开工作簿时自动刷新所有数据透视表并弹出行数校验提示Private Sub Workbook_Open() Dim ws As Worksheet Dim pt As PivotTable Dim totalRows As Long Dim expectedRows As Long 刷新所有数据透视表 For Each ws In ThisWorkbook.Worksheets For Each pt In ws.PivotTables pt.RefreshTable pt.PivotCache.Refresh Next pt Next ws 检查clean_data行数是否达到预期 totalRows ThisWorkbook.Worksheets(clean_data).Cells(Rows.Count, 1).End(xlUp).Row - 1 expectedRows 250 * ThisWorkbook.Worksheets(config).Range(B2).Value If totalRows expectedRows * 0.9 Then MsgBox 数据行数异常当前 totalRows 行预期 expectedRows 行。请检查数据源。, vbExclamation End If Application.StatusBar 数据已更新透视表已刷新 End Sub这段代码挂在ThisWorkbook模块里而不是普通模块。放在Workbook_Open事件里能在文件打开的一瞬间执行。注意pt.PivotCache.Refresh这一行光是RefreshTable有时候不够如果透视表的源数据范围变了必须刷新缓存才能重算。我见过很多人的透视表不更新就是因为只调用了RefreshTable没有刷新缓存。5.3 Python与VBA的边界有人会问既然已经有了Python为什么不用Python直接操作Excel生成透视表还要VBA参与我是这样划分的Python负责“数据获取、清洗、写入”这是它的强项VBA负责“打开文件后的交互、刷新、校验”这是Excel原生环境最顺手的事。重点逻辑全部放在Python侧VBA只做收尾。如果你完全不想碰VBA也可以在Python里用openpyxl的PivotTable类创建透视表但代码量会非常大而且透视表的字段布局没有一个可视化界面调试起来很痛苦。我的建议是透视表用手工做一次存成模板然后让Python每次更新数据时保留透视表结构只替换数据源区域VBA负责刷新。这个组合是性价比最高的。6. 定时任务上线后我踩过的新坑6.1 脚本挂了没人知道链路跑通之后我满心欢喜地用一个Windows计划任务把脚本挂成每日16:30自动执行。结果第一周就翻车了某天同花顺客户端登录状态异常脚本在拉数阶段就抛异常退出但计划任务显示“上次结果0x1”根本不会主动通知我。第二天开盘前我打开Excel看到还是昨天的数据才知道脚本挂了。解决方案是给脚本加上日志和主动告警。日志用Python自带的logging模块写到文件告警用企业微信机器人或者钉钉机器人推一条消息到手机。关键不是告警本身而是在脚本一开始就捕获异常把所有异常都汇总到一条告警里避免一屏刷屏。import logging import traceback logging.basicConfig( filenamedaily_report.log, levellogging.INFO, format%(asctime)s %(levelname)s %(message)s ) def send_alert(msg): # 调用企业微信/钉钉webhook pass try: run_all() logging.info(今日报表生成成功) except Exception: error_info traceback.format_exc() logging.error(error_info) send_alert(金融数据自动化脚本异常\n error_info)6.2 同花顺客户端不能后台运行另一个坑是定时任务执行时同花顺客户端并没有打开或者打开了但没有成功登录导致API鉴权失败。我一开始的解决办法是在批处理脚本里先启动同花顺客户端再用Python轮询等待token生效start /d C:\Program Files\hexin hexin.exe timeout /t 15 /nobreak python daily_report.py这个方案能用但非常依赖本机环境。如果你的机器上盖了屏保或者锁屏客户端界面没渲染完登录可能就失败了。后来我把同花顺客户端的开机自动登录打开并在Python里增加了“获取token失败则等待3分钟重试”的机制才算稳定下来。6.3 增量更新与全量重建的选择一开始我图省事每天全量拉取250个交易日的行情全量重新生成Excel。跑了一个月后发现两个问题一是数据量渐大后脚本耗时增加二是如果某天拉取的数据源有问题全量重建会把整个文件的历史字段都污染。于是我改成了增量更新策略每次只拉最近5个交易日的数据更新到现有Excel的clean_dataSheet里透视表范围自动扩展。具体做法是在Python里读取Excel现有的日期最大值只拉这个日期之后的数据然后追加写入。但这里又有一个新坑Excel里如果已经有旧格式的日期字符串跟新数据的日期格式对不上追加就会产生重复或空白。我的经验是增量更新之前先把日期列统一转成datetime用pandas写回时再格式化成YYYY-MM-DD保持Excel端格式一致。这个策略运行了两周后脚本耗时从原来的40秒降到了8秒而且Excel文件体积也没有无限膨胀。7. 这套链路跑了一季度之后我的一些真实体会如果你问我最大的体会是什么我会说金融数据自动化的难点从来不在API本身而在那些接口文档没写明的边界条件上。比如token过期不会报错而是返回一个看似正常但字段为空的数据结构停牌日不会显示为NaN而是直接少一行成交量单位不一定统一可能根据指数不同而不同。这些问题靠读文档是发现不了的必须在真实行情数据上跑一段时间才会暴露。还有一件事我后来才想明白自动化并不意味着完全不需要人看。我的脚本每周仍然会做一次“人工核验”——随机挑几只股票从同花顺客户端手动看一眼收盘价跟我们Excel里的数据对一下确认没有系统性偏差。这项工作只需要十分钟但能及时抓住很多隐藏问题。最后分享一个实用小技巧我在Excel的configSheet里放了一张参数表包括股票列表、数据周期、预期数据行数等。Python脚本每天读这个Sheet来调整生成逻辑VBA宏也读同一张表来做校验。这样如果我想改股票池或者调周期不需要改任何代码只改Excel里的单元格就行。这套设计让我后续维护的负担降到了最低。这个项目能继续扩展的方向还有很多比如把日线换成分钟线做盘中监控或者把Excel输出换成SQLite数据库供回测调用。但不管怎么扩展从同花顺API到Excel这条数据链路的根基算是打牢了。只要数据源稳定清洗逻辑严谨后面的分析工作就有据可依。
