1. 项目整体设计与思路拆解1.1 为什么非要把同花顺数据搬进Excel做投资研究或者日常盯盘的人基本都会遇到一个特别尴尬的场景行情软件里数据一目了然但一到写报告、做复盘、填表格的时候就卡住了。你看着同花顺界面上那些红红绿绿的涨跌幅、成交额、换手率心里想的是要是能一键导进Excel就好了。手动复制粘贴行数少还行一旦涉及几百只股票、拉一个多月的历史数据手速根本跟不上而且错一位小数点你都不知道。这个项目要解决的就是金融数据自动化里最刚需的一环让同花顺这一类行情数据源的API接口自动把数据拉下来清洗干净再按指定格式落到Excel里。整个过程不用打开网页、不用手动复制脚本跑完一张带格式、带条件高亮、带透视表的Excel报表就已经躺在桌面上了。适合谁用量化小白、财务分析、投研助理以及所有每天要和行情数据打交道的朋友。哪怕你完全不懂编程照着后面的步骤走也能把脚本跑起来。这事的核心难点不在于调API而在于转换这个过程怎么做到智能——数据字段要对得上、格式要能落表、日期要能对齐、除权除息要能处理这些细节才是真正的工作量。1.2 原始方案对比手动操作、VBA还是Python在做这个项目之前我也试过几条不同的路简单说下对比结果。第一手动复制粘贴。这个方法只适合一次性的小批量数据。同花顺自带的右键导出功能倒是能存成Excel文件但格式是固定的字段经常带单位、带后缀比如成交量后面跟个(手)导入之后还得手工清理。数据多了以后这个方案第一个出局。第二Excel VBA。VBA能解决一部分问题比如可以从网页抓数据或者调用一些数据接口但写起来相当痛苦。尤其是处理JSON格式的行情数据时VBA的处理能力弱正则也不好写一个简单的嵌套数据结构就能让代码变得面目全非。而且VBA的报错信息非常不友好排查一次问题半天时间就没了。第三Python脚本。这套方案最适合这类场景原因有三个一是Python处理JSON、CSV这类文本格式是天然优势解析行情接口的返回数据非常顺手二是数据清洗和格式转换的生态太成熟了pandas处理表格数据就是一把梭三是造出来的Excel文件完全可控字体、颜色、列宽、条件格式都能一键设置。所以这个项目的技术路线就定为Python 同花顺数据接口 pandas openpyxl。核心逻辑是用Python把接口返回的原始数据处理成规整的结构化表格再用openpyxl生成带样式的Excel文件。1.3 整体流程拆解一条数据从接口到Excel要走几步把这套自动化流程画在脑子里它是这样的第一步准备股票代码清单。这一步是基础代码可能是你自己维护的一个列表也可能从同花顺的板块成分股接口里拉。第二步调用行情接口。把代码清单、字段列表、时间范围传给接口拿到JSON或者DataFrame格式的数据。第三步数据清洗。这一步内容最多——去重、对齐日期、处理缺失值、把成交量单位统一成手或者股、把百分数转成数值。第四步Excel落盘。把清洗后的DataFrame写到Excel同时套上预设的格式表头加粗、涨跌用红绿颜色标出、列宽自动调整、再加一个日期筛选。这四步听起来简单但每一步都有坑。后面我会把自己踩过的坑一个一个说清楚尤其是每个参数为什么这么设、每个步骤为什么这么写的逻辑。2. 环境准备与工具选型2.1 Python环境与依赖库安装清单工欲善其事必先利其器。这个项目里我用的Python 3.9以上版本强烈建议你装个虚拟环境别直接怼到系统环境里不然以后装包版本冲突的时候真的很头疼。需要安装的核心库是这几个pandas处理表格数据的一号主角负责数据清洗和透视表生成openpyxl操作Excel文件的二号主角负责样式控制和格式设置requests发HTTP请求调用行情接口用datetime、time标准库处理日期时间戳和限频控制安装命令一条搞定直接pip install pandas openpyxl requests如果你在国内网络环境记得加个国内镜像源不然下载速度能让人等到怀疑人生。这里我特别说一下为什么选openpyxl而不是xlwings或者xlsxwriter。xlwings需要本机装了Excel才能跑服务器上一跑就废。xlsxwriter写文件效率高但它不擅长读取和修改已有文件。openpyxl是纯Python实现不依赖Excel安装读、写、改都能做还能操作单元格样式、合并单元格、设置条件格式。对于生成一份漂亮报表这个目标来说openpyxl是综合来看最合适的选择。2.2 同花顺数据接口的认知与权限申请标题里说同花顺API实际上这里要接触的是同花顺体系的金融数据接口。说到权限得先有个基本认知真正机构级的实时全量行情接口是商业付费服务个人用户能稳定拿到的接口往往是日线级别或者有一定延迟的行情数据。这不影响我们做自动化转换日线级别的数据做复盘、做分析完全够用。在动手写代码之前你得先确认自己能拿到什么权限。有的接口是注册后给一个Token有的接口需要你把本机IP加到白名单还有的是按调用次数计费。拿到Token之后把它存在环境变量或者配置文件里千万别硬编码在脚本里不然哪天代码传GitHub上Token直接泄露那才是真事故。申请好接口权限之后我建议先用Postman或者直接浏览器访问接口文档里的示例URL确认数据格式长什么样。不同数据源返回的JSON结构差异很大有的直接给DataFrame有的给嵌套的JSON还有的用逗号分隔的纯文本。这个先看原始返回结构的习惯能帮你省掉后面大量的试错时间。2.3 Excel模板设计明确落表规则再写代码很多时候代码写到一半才发现咦这个数据到底应该放在哪一列——这就是因为没提前设计Excel模板。我的习惯是先在Excel里手工做一版理想中的报表样式把列名、字段顺序、格式要求全部定下来然后再回头写Python代码去还原这个模板。这个项目里的报表模板我定的是这样左侧是日期列第一列日期、第二列股票代码、第三列股票名称、然后依次是开盘价、最高价、最低价、收盘价、涨跌幅、成交量、成交额、换手率。表头用深蓝色底、白色加粗字所有数字列保留两位小数涨跌幅用条件格式标红涨绿跌A股习惯是红涨绿跌注意别搞反了。顶部留一行标题写明报表名称和生成时间。这个过程听着很啰嗦但它决定了你后面写代码时的方向。模板不确定代码就是无头苍蝇今天加一列明天改个名效率极低。3. 核心实操从接口调用到Excel落盘3.1 行情接口调用与数据清洗实操先看第一步调用行情接口。下面这个代码是我实际在用的去掉了真正的URL和Token你替换成自己的服务就行。import requests import pandas as pd import time # 从配置文件读取Token不要硬编码在代码里 import os TOKEN os.getenv(MARKET_DATA_TOKEN) def fetch_daily_quote(codes, start_date, end_date): url https://your.market.data/api/daily headers {Authorization: fBearer {TOKEN}} payload { codes: codes, start_date: start_date, end_date: end_date, fields: ts_code,trade_date,open,high,low,close,pct_chg,vol,amount,turnover_rate } resp requests.post(url, jsonpayload, headersheaders) resp.raise_for_status() data resp.json() # 假设接口返回的data字段是list of dict df pd.DataFrame(data[data]) return df拿到DataFrame之后千万别急着写Excel先做三件清洗的活儿。第一件看字段名。接口返回的字段名可能是英文缩写比如vol代表成交量amount代表成交额你要是不确认就打开Excel看一眼前几行防止字段错位。第二件看日期格式。有的接口返回的是20240115这种字符串有的返回时间戳统一转换成datetime类型方便后面按日期排序和筛选。第三件去重。接口偶尔会重复返回数据用drop_duplicates()把所有列一起比较去重。再看看数据类型。成交量、成交额这种数值字段接口偶尔会返回字符串特别是空值显示成空字符串。这一步用pd.to_numeric(..., errorscoerce)统一转成数值类型转不了的就变成NaN后续再处理。def clean_quote_df(df): # 统一列名 df.columns [股票代码, 日期, 开盘, 最高, 最低, 收盘, 涨跌幅, 成交量, 成交额, 换手率] # 日期统一为datetime df[日期] pd.to_datetime(df[日期], format%Y%m%d, errorscoerce) # 数值化 num_cols [开盘, 最高, 最低, 收盘, 涨跌幅, 成交量, 成交额, 换手率] for col in num_cols: df[col] pd.to_numeric(df[col], errorscoerce) # 去重 df df.drop_duplicates().sort_values([日期, 股票代码], ascending[False, True]) # 删除无数据的日期 df df[df[日期].notna()] return df这一步做完数据基本就干净了可以进入Excel落盘环节了。3.2 批量抓取多只股票时的时间节奏控制单只股票的日线数据拉下来很容易但实际使用场景里很少有人只看一只股票。我这里给的方案是一次拉一组股票比如你维护了一个自选股列表里面十几只票。我的建议是别一次性全塞给接口一来接口有单次请求数量限制二来对方服务端也扛不住大包请求。更稳妥的方案是分批处理比如每50只股票一组每组之间sleep 1到2秒。这样既不会触发接口的限流机制也不会因为单次请求过大导致超时。def batch_fetch(codes, start_date, end_date, batch_size50): frames [] for i in range(0, len(codes), batch_size): batch_codes codes[i : i batch_size] df fetch_daily_quote(batch_codes, start_date, end_date) df_clean clean_quote_df(df) frames.append(df_clean) time.sleep(1.5) # 控制请求频率避免被封 return pd.concat(frames, ignore_indexTrue)sleep时间这个参数很有讲究。设得太短比如0.2秒接口很容易报错设得太长比如5秒几百只股票拉完黄花菜都凉了。1到2秒是实测下来比较稳妥的范围既能保证数据完整也不会等太久。另外提一句拉数据的时间段也很关键。盘中调用接口是实时行情频繁请求容易触发限流。做日常复盘的话建议等收盘后再跑脚本拿到的是完整日线数据数据稳定不会出现某些股票还在交易中所以数据缺失的尴尬。我们做自动化是为了让解放人力不是让程序和人一起盯盘。3.3 Excel样式与条件格式的自动化生成数据清洗完了接下来是把DataFrame变成一份看得过去的Excel报表。这一步我用openpyxl处理。思路是这样先设计一个表头区域和一个数据区域各自套用不同的格式模板。pandas自带的to_excel只能写数据样式控制能力很弱所以必须靠openpyxl做二次加工。直接看代码from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils.dataframe import dataframe_to_rows from openpyxl.formatting.rule import CellIsRule from openpyxl.utils import get_column_letter def export_to_excel(df, output_path, title市场行情日报): wb Workbook() ws wb.active ws.title 日线数据 header_fill PatternFill(start_color2F5597, end_color2F5597, fill_typesolid) header_font Font(name微软雅黑, size10, boldTrue, colorFFFFFF) thin_border Border( leftSide(stylethin, colorD9D9D9), rightSide(stylethin, colorD9D9D9), topSide(stylethin, colorD9D9D9), bottomSide(stylethin, colorD9D9D9), ) # 第一行标题 ws.merge_cells(start_row1, start_column1, end_row1, end_columnlen(df.columns)) title_cell ws.cell(row1, column1, valuetitle) title_cell.font Font(name微软雅黑, size14, boldTrue) title_cell.alignment Alignment(horizontalcenter, verticalcenter) ws.row_dimensions[1].height 30 # 第二行生成时间 ws.merge_cells(start_row2, start_column1, end_row2, end_columnlen(df.columns)) time_cell ws.cell(row2, column1, valuef数据生成时间{pd.Timestamp.now()}) time_cell.font Font(name微软雅黑, size9, color808080) time_cell.alignment Alignment(horizontalright, verticalcenter) # 第四行开始放表头 header_row 4 for col_idx, col_name in enumerate(df.columns, start1): cell ws.cell(rowheader_row, columncol_idx, valuecol_name) cell.fill header_fill cell.font header_font cell.alignment Alignment(horizontalcenter, verticalcenter) cell.border thin_border # 写入数据 for r_idx, row in enumerate(dataframe_to_rows(df, indexFalse, headerFalse), startheader_row 1): for c_idx, value in enumerate(row, start1): cell ws.cell(rowr_idx, columnc_idx, valuevalue) cell.border thin_border if isinstance(value, float): cell.number_format 0.00 if 涨跌幅 in df.columns: pct_col list(df.columns).index(涨跌幅) 1 if c_idx pct_col: # 涨红跌绿 cell.number_format 0.00% # 条件格式涨跌幅列红涨绿跌A股习惯 if 涨跌幅 in df.columns: pct_col_letter get_column_letter(list(df.columns).index(涨跌幅) 1) rng f{pct_col_letter}{header_row1}:{pct_col_letter}{header_row len(df)} ws.conditional_formatting.add(rng, CellIsRule(operatorgreaterThan, formula[0], fillPatternFill(start_colorFFC7CE, end_colorFFC7CE, fill_typesolid), fontFont(color9C0006))) ws.conditional_formatting.add(rng, CellIsRule(operatorlessThan, formula[0], fillPatternFill(start_colorC6EFCE, end_colorC6EFCE, fill_typesolid), fontFont(color006100))) # 调整列宽 col_widths [10, 12, 10, 10, 10, 10, 10, 12, 12, 12] for i, width in enumerate(col_widths, start1): ws.column_dimensions[get_column_letter(i)].width width # 冻结窗格方便查看 ws.freeze_panes fA{header_row 1} wb.save(output_path) print(f报表已生成{output_path})这一步代码里有几个细节值得展开说说。为什么条件格式用openpyxl而不是pandaspandas虽然能写数据但条件格式、单元格填充色这些Excel特性它完全不管。openpyxl是直接和Excel文件交互你能在Excel里手工设置的格式它基本都能设置条件格式用的是Excel对象模型跟你在界面上点的效果一模一样。冻结窗格这个参数数据多的时候往下滚动就看不见表头了冻结前几行可以保持表头始终可见。这不算什么高深技巧但报表给领导看的时候体验完全不一样。数字格式的坑涨跌幅这一列接口返回的通常是原始数值比如0.038表示涨了3.8%。你要是在Excel里不设置格式显示出来就是0.038很丑。设置成0.00%之后显示为3.80%这才是人看的数字。这一步虽然很简单但很多人会漏掉最后报表发给别人对方看到0.038还会问你8是什么意思。4. 数据透视与看板扩展4.1 用数据透视表做汇总分析原始数据落盘之后报表只是数据搬运工还远远不够。真正的自动化是要在数据落盘之后自动生成分析结论比如统计每个行业板块的涨跌分布、计算每只股票的区间最大回撤、找出涨跌幅排名前五的个股。pandas的透视表功能在这里就能派上用场。比如我想看每个交易日全市场上涨家数、下跌家数和平盘家数def generate_summary(df): df[涨跌状态] df[涨跌幅].apply( lambda x: 上涨 if x 0 else (下跌 if x 0 else 平盘) ) summary df.groupby([日期, 涨跌状态]).size().unstack(fill_value0) summary[总家数] summary.sum(axis1) summary[上涨占比] summary[上涨] / summary[总家数] return summary这一段代码输出的是每个日期 上涨家数 下跌家数 平盘家数 上涨占比的汇总表可以直接写入Excel的第二个Sheet。这个Sheet的作用是总览领导打开文件先看这个Sheet几秒钟就知道当天的市场情况而不需要在几千行明细数据里扒拉半天。类似地你还可以生成区间涨跌幅排名Sheet——把第一天的收盘价和最后一天的收盘价对比算区间涨跌幅然后按涨跌幅排序。这样这周哪些股票表现最好这种问题打开Excel就能回答连公式都不用写。4.2 定时调度一键刷新报表数据数据自动落盘只是完成了一半另外一半是定时触发。我调试好脚本后在Windows任务计划程序里建了一个每天下午15:30触发一次的任务跑完自动生成当日行情报表并弹窗提示。这样每天收盘后打开电脑桌面已经放好了当天的数据报表完全不用手动操作。在macOS上对应的工具是launchd或者直接用crontab写法也很简单# 每个交易日15:30执行 30 15 * * 1-5 cd /path/to/project python run_daily_report.py这里有个很小的坑* 1-5意味着周一到周五都会执行但遇上法定节假日接口不会返回数据脚本会生成一个空报表。我的处理方式是在脚本开头加一个判断如果接口返回的结果为空就往指定的企业微信/钉钉群里发一条消息提示今日无行情数据可能为节假日然后退出程序。这样既不会产生空报表误导人也不会把那几天算成程序故障。5. 常用问题排查与避坑经验5.1 接口连接失败与限流应对方案这个问题是所有接API的项目都躲不开的。常见的报错有这么几类第一类是权限错误一般报401或者403。这时候先检查Token有没有过期再检查IP白名单配置很多数据服务商要求把当前出口IP加到白名单里动态IP用户特别容易踩这个坑。第二类是请求太频繁被限流报429或者其他频率超限提示。解决方法就是我在前面说的分批sleep策略。另外实测下来退避重试比一直重试效果好。遇到429先等3秒如果还不行等待时间翻倍最多重试5次。第三类是超时报timeout。这个往往是网络环境问题或者对方服务端临时抖动。在requests请求里设置timeout(5, 10)比默认值更稳妥 至少不会让脚本卡在那里几分钟不动。5.2 Excel文件被占用导致脚本报错这是个极其常见又极其坑爹的问题。脚本运行到一半报PermissionError排查半天发现是你自己把上一份Excel报表打开了没关。Windows上文件被Excel进程锁定后Python就写不进去了。我的解决方案是脚本保存前检查文件是否存在先尝试打开一次看能不能写入如果报错就在控制台提示请关闭已打开的Excel文件再运行然后退出程序。另外还有一个更实用的经验别把输出文件路径写死文件名带上日期比如行情日报_20250101.xlsx这样就算旧文件被Excel锁着新文件也能正常生成两个文件互不影响。5.3 时间戳与复权问题对数据的影响最后这个坑不是报错型的坑是数据准确性型的坑最容易忽视但也最影响分析结果。第一个是时间戳的时区问题。有的接口返回的时间是UTC国内是东八区相差8个小时。日线数据因为只看日期不太受时区影响但分钟级、日内数据的时区问题就很严重了。更关键的是复权问题。同花顺行情接口的日线数据默认是不复权的原始价格。但上市公司经常分红、送股、配股导致除权后价格出现断崖式跳变。比如某股票除权前收盘价20元除权后直接变成15元如果你用不复权数据算连续收益率会得出一个巨亏的假象。所以做长期趋势分析时必须用前复权或者后复权数据。在调用接口时建议显式传参指定复权方式不要依赖默认值。我自己就曾经因为没注意复权在一份回测报告里算出一只大牛股的季度收益是-30%当时人都懵了排查了半天才发现是除权没处理。6. 写在最后的实操心得这个项目做完之后最大的体会是金融数据自动化的价值不在于你写了多少代码而在于你省下来多少时间。我手动复制粘贴一份20只股票的周报大概要15分钟中间还特别容易出错。换成脚本跑3秒搞定格式比手工做的还整齐。如果你也想在自己电脑上复现这套流程我建议按这个节奏来第一天先搞定接口权限申请和数据拉取第二天实现数据清洗和Excel落盘第三天加上样式和条件格式第四天配置定时调度。不用着急一步到位每天一个环节非常轻松。最后再分享一个小技巧在调试阶段把输出文件的路径指向一个临时目录比如/tmp/report_test.xlsx这样即使出错了也不会把正常工作目录里的文件搞乱。等确认没问题了再把路径改成正式位置。这个习惯帮我避开了很多次误覆盖数据的尴尬。这套流程跑通以后你完全可以把中间的数据源换成其他接口逻辑都是一样的代码稍微改改就能用。