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": f"Bearer {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, json=payload, headers=headers) 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(..., errors="coerce")统一转成数值类型，转不了的就变成NaN，后续再处理。
def clean_quote_df(df): # 统一列名 df.columns = ["股票代码", "日期", "开盘", "最高", "最低", "收盘", "涨跌幅", "成交量", "成交额", "换手率"] # 日期统一为datetime df["日期"] = pd.to_datetime(df["日期"], format="%Y%m%d", errors="coerce") # 数值化 num_cols = ["开盘", "最高", "最低", "收盘", "涨跌幅", "成交量", "成交额", "换手率"] for col in num_cols: df[col] = pd.to_numeric(df[col], errors="coerce") # 去重 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_size=50): 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_index=True)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_color="2F5597", end_color="2F5597", fill_type="solid") header_font = Font(name="微软雅黑", size=10, bold=True, color="FFFFFF") thin_border = Border( left=Side(style="thin", color="D9D9D9"), right=Side(style="thin", color="D9D9D9"), top=Side(style="thin", color="D9D9D9"), bottom=Side(style="thin", color="D9D9D9"), ) # 第一行：标题 ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(df.columns)) title_cell = ws.cell(row=1, column=1, value=title) title_cell.font = Font(name="微软雅黑", size=14, bold=True) title_cell.alignment = Alignment(horizontal="center", vertical="center") ws.row_dimensions[1].height = 30 # 第二行：生成时间 ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=len(df.columns)) time_cell = ws.cell(row=2, column=1, value=f"数据生成时间：{pd.Timestamp.now()}") time_cell.font = Font(name="微软雅黑", size=9, color="808080") time_cell.alignment = Alignment(horizontal="right", vertical="center") # 第四行开始放表头 header_row = 4 for col_idx, col_name in enumerate(df.columns, start=1): cell = ws.cell(row=header_row, column=col_idx, value=col_name) cell.fill = header_fill cell.font = header_font cell.alignment = Alignment(horizontal="center", vertical="center") cell.border = thin_border # 写入数据 for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=False), start=header_row + 1): for c_idx, value in enumerate(row, start=1): cell = ws.cell(row=r_idx, column=c_idx, value=value) 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_row+1}:{pct_col_letter}{header_row + len(df)}" ws.conditional_formatting.add(rng, CellIsRule(operator="greaterThan", formula=["0"], fill=PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid"), font=Font(color="9C0006"))) ws.conditional_formatting.add(rng, CellIsRule(operator="lessThan", formula=["0"], fill=PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid"), font=Font(color="006100"))) # 调整列宽 col_widths = [10, 12, 10, 10, 10, 10, 10, 12, 12, 12] for i, width in enumerate(col_widths, start=1): ws.column_dimensions[get_column_letter(i)].width = width # 冻结窗格，方便查看 ws.freeze_panes = f"A{header_row + 1}" wb.save(output_path) print(f"报表已生成：{output_path}")这一步代码里有几个细节值得展开说说。
为什么条件格式用openpyxl而不是pandas：pandas虽然能写数据，但条件格式、单元格填充色这些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_value=0) summary["总家数"] = summary.sum(axis=1) 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，这样即使出错了，也不会把正常工作目录里的文件搞乱。等确认没问题了，再把路径改成正式位置。这个习惯帮我避开了很多次误覆盖数据的尴尬。这套流程跑通以后，你完全可以把中间的数据源换成其他接口，逻辑都是一样的，代码稍微改改就能用。
