Excel Power Query 自动抓取股票行情数据实战指南

1. 为什么我要折腾这套自动抓数据的方案盯盘的人都有个共同的痛点白天看行情已经够累了收盘后还得手动去网站把当天的开高低收、成交量一个个敲进Excel敲完还要检查有没有串行。我以前就是这么干的一只股票一天六七个字段手里盯十几只光录入就得花掉大半个小时遇到除权除息还得回头改历史数据改到怀疑人生。后来我把这套流程彻底换掉了核心工具就是Excel自带的Power Query。它藏在数据选项卡里很多人装了Office好几年都没点开过但它是真的能干活。简单说Power Query就是一个内置在Excel里的数据搬运和清洗引擎你告诉它从哪拿数据、怎么整理它就把结果吐到表格里下次只要点一下刷新所有数据自动更新不用你重新操作一遍。这套方案能解决三个具体问题第一免手工录入历史行情一次性拉全第二一键刷新每天收盘后点一下按钮最新数据自动补进来第三可复用同一套查询逻辑换个股票代码就能用不用重写。适合谁适合所有用Excel做股票记录、回测、复盘又不想学Python或者不会写代码的普通投资者。整个过程不需要装任何插件不需要付费Office 2016及以上版本、Microsoft 365都自带这个功能。我下面会把整套流程拆开讲包括数据源怎么选、查询怎么写、参数怎么设、刷新怎么配以及我踩过的那些坑。你跟着做一遍基本半小时能跑通之后每天维护成本接近于零。2. 方案整体设计与数据源选型思路2.1 为什么选Power Query而不是VBA或Python先说选型逻辑这决定了后面所有操作的方向。抓股票历史数据常见路子有三条VBA写宏、Python写脚本、Power Query做查询。VBA的问题在于维护成本高。你写一段宏去请求网页网站结构一变宏就报错而且VBA处理JSON和分页特别别扭调试起来很痛苦。Python当然强pandas加一个数据接口库几行代码就搞定但它有个门槛你得装Python环境、装库、会写代码对纯Excel用户不友好而且数据抓下来还得再导回Excel多一道手续。Power Query的好处是它就在Excel里不用切换工具抓取、清洗、加载一条龙。它内置了Web请求能力能直接读网页返回的表格或JSON还能做分页循环、字段拆分、类型转换。最关键的是它把整个流程记录成步骤你改一个参数后面所有步骤自动重跑这种可追溯性比VBA强太多。对于每天更新一批股票数据这种重复性任务Power Query是最省心的选择。提示Power Query在Mac版Excel上的功能比Windows版弱一些尤其是Web请求和部分连接器支持不完整。如果你用的是Mac建议先确认你的版本是否支持从Web获取数据否则这套方案可能跑不通。2.2 数据源的选择标准数据源是整个方案的地基选错了后面全白搭。我筛选数据源看四个指标是否免费、是否稳定、字段是否齐全、是否容易被程序读取。免费是前提付费接口对散户没必要。稳定指的是接口不会三天两头改地址或者封IP。字段齐全指的是至少要有日期、开盘、最高、最低、收盘、成交量这六个基础字段能带复权信息更好。容易被程序读取指的是返回的是结构化数据JSON或HTML表格而不是一堆需要解析的乱码。我实际用下来公开的财经数据接口里返回JSON格式的那些最适合Power Query处理因为JSON的层级结构清晰Power Query有专门的解析函数。HTML表格也能用但网页改版风险更高。具体用哪个源我在下一节会给出可操作的思路你可以根据自己的情况替换。2.3 整体流程拆解整套方案分四步走我用一个表格把每步的目标和产出列清楚步骤目标关键动作产出第一步建立数据连接用从Web填入接口地址原始JSON/表格数据第二步清洗与整形展开字段、改类型、删冗余列规整的行情表第三步参数化把股票代码抽成参数可复用的查询第四步加载与刷新加载到工作表、配置刷新自动更新的报表这个顺序不能乱。很多人一上来就想参数化结果原始数据还没理顺参数化之后一堆报错。先把一只股票的数据跑通、清洗干净再考虑复用这是最稳的路径。3. 核心细节解析与实操要点3.1 从Web获取数据的正确姿势打开Excel点数据选项卡找到获取数据→自其他源→自Web。这时候会弹出一个对话框让你填URL。这里有个细节不要直接填网页地址要填返回数据的接口地址。怎么区分你在浏览器里打开一个地址如果看到的是一个排版精美的网页那是网页地址如果看到的是一大坨花括号包起来的数据或者一个纯表格那才是接口地址。Power Query要的是后者。填进去之后Power Query会尝试识别页面里的表格。如果返回的是JSON它会提示你未找到表格这时候别慌点编辑进入Power Query编辑器在左侧的查询列表里能看到原始返回内容通常是一个Record或者List展开它就能看到数据。注意有些接口需要带请求头比如User-Agent才能正常返回否则会报403。Power Query的高级编辑器里可以手动加请求头具体在源这一步的代码里加一个Headers参数。这个后面实操部分我会给示例。3.2 字段展开与类型转换的坑数据拉进来之后第一眼看到的往往是一堆嵌套结构。比如JSON里可能是这样的一个列表每个元素是一个记录记录里有日期、有价格对象、价格对象里又分开盘收盘。这时候你要一层层展开。展开的原则是先展开列表再展开记录最后展开嵌套对象。展开的时候Power Query会问你要不要用原始列名做前缀建议勾上不然字段重名了分不清哪个是哪个。类型转换是另一个大坑。Power Query默认会把数字识别成文本尤其是从JSON来的数据价格经常是字符串格式。你必须手动把日期列改成日期类型把价格列改成小数类型。不改的话后面排序会按字符串排100会排在9前面那就乱套了。还有一个隐蔽的问题空值处理。停牌日或者数据缺失的时候某些字段会是null。如果你直接做计算null会传染整列结果都变成null。我的做法是先把null替换成0或者上一个有效值具体看字段含义。成交量缺失填0合理收盘价缺失就得考虑用前一日填充。3.3 参数化的实现方式参数化是这套方案能复用的关键。Power Query里可以建参数也可以直接在查询里写一个变量。我推荐用参数因为参数可以在Excel表格里改不用进编辑器。具体做法在Power Query编辑器里点管理参数→新建参数命名成股票代码类型选文本当前值填一个测试代码。然后在查询的源步骤里把URL里写死的代码替换成这个参数。替换的方式是在高级编辑器里改代码把URL字符串拼接上参数名。这样以后你想换股票只要在Excel里改参数值点刷新数据就换成新股票的了。如果你要同时跟踪多只股票可以建一个股票代码列表用列表循环的方式批量抓取这个稍微复杂一点我在实操部分会讲。3.4 刷新机制的配置要点数据加载到工作表之后刷新有两种方式手动刷新和自动刷新。手动刷新就是右键点表格→刷新或者点数据→全部刷新。适合每天收盘后自己点一下。自动刷新可以设置打开文件时刷新在连接属性里勾选。这样你每天打开Excel数据自动就是最新的。但要注意如果数据源响应慢打开文件会卡一会儿建议把后台刷新也勾上让它慢慢加载不阻塞你操作。还有一个进阶玩法用Windows的任务计划程序定时打开Excel刷新再关闭。这个对普通用户有点重我一般不建议除非你要做全自动的每日数据归档。4. 实操过程与核心环节实现4.1 第一步跑通单只股票的数据抓取先拿一只股票把流程走通。打开Excel新建一个空白工作簿按前面的路径进入自Web。假设我们用一个返回JSON的行情接口地址形如https://example.com/api/history?code600000start20200101end20241231这里用示例域名你替换成实际可用的接口。填进去之后如果Power Query识别不出表格点编辑进入编辑器。左侧会看到一个叫源的步骤右边显示的是一个Record。点击Record旁边的展开箭头你会看到里面可能有data、code、msg这些字段。继续展开data它通常是一个List点List旁边的展开按钮选择扩展到新行这时候每一行就是一条行情记录。展开完之后你会看到类似这样的列date、open、high、low、close、volume。如果这些字段还是嵌套的继续展开。展开到位之后选中所有列右键→更改类型→使用区域设置日期列选日期价格列选小数。这一步做完你应该能看到一张干净的行情表。如果看到的是乱码或者空表八成是接口地址不对或者需要请求头回到源步骤检查。4.2 第二步用高级编辑器加请求头有些接口不加请求头会拒绝访问。在Power Query编辑器里点高级编辑器你会看到类似这样的代码let 源 Json.Document(Web.Contents(https://example.com/api/history?code600000)) in 源要加请求头改成这样let 源 Json.Document(Web.Contents(https://example.com/api/history, [ Query[code600000, start20200101, end20241231], Headers[#User-AgentMozilla/5.0, #Refererhttps://example.com] ])) in 源注意这里我把查询参数从URL里拆出来放到了Query里这样更规范也方便后面参数化。Headers里可以加多个键值对具体加什么取决于接口要求一般User-Agent是必须的。提示Web.Contents的第二个参数是一个记录Query和Headers都是它的字段。写的时候注意括号和逗号Power Query的M语言对语法很敏感少一个逗号就报错。4.3 第三步把股票代码抽成参数在编辑器里点管理参数→新建参数名称股票代码类型文本当前值600000建好之后回到高级编辑器把代码里的600000替换成股票代码注意不要加引号参数本身就是文本类型。改完是这样let 源 Json.Document(Web.Contents(https://example.com/api/history, [ Query[code股票代码, start20200101, end20241231], Headers[#User-AgentMozilla/5.0] ])) in 源现在你关闭并加载到工作表会得到一个表格。然后在Excel里随便找个单元格比如E1输入新的股票代码再回到Power Query里把参数值改成引用这个单元格——不过参数本身不能直接引用单元格需要绕一下建一个单元格命名区域然后在参数里用公式引用。更简单的做法是直接在编辑器里改参数值虽然麻烦点但稳定。如果你要批量抓多只股票思路是建一个股票代码表然后用List.Transform或者自定义函数循环。这个稍微进阶我建议先把单只跑顺再折腾。4.4 第四步加载与刷新配置数据清洗完之后点关闭并上载→关闭并上载至选择表放到一个新工作表。这时候你会看到一张格式规整的行情表。接下来配置刷新。右键点这张表→表格→外部数据属性或者点数据→查询和连接找到对应的连接右键→属性。在弹出的对话框里勾选打开文件时刷新数据勾选启用后台刷新如果数据量大把刷新频率设成手动避免频繁请求这样配置完你每天打开文件数据会自动更新到最新。如果接口只返回历史数据不返回当天你可以在收盘后手动点一次全部刷新。4.5 参数计算与日期范围的处理日期范围我一般设成固定的起始日加动态的结束日。起始日写死比如20200101结束日可以用DateTime.LocalNow()取当天然后格式化成yyyyMMdd。在M语言里这样写结束日期 Date.ToText(DateTime.Date(DateTime.LocalNow()), yyyyMMdd)然后把这个变量拼到Query里。这样你就不用每天改结束日期了刷新的时候自动取当天。如果接口对日期范围有限制比如最多返回一年数据你就得分段请求。分段的做法是建一个日期区间列表用List.Generate生成多个区间然后对每个区间发一次请求最后把结果合并。这个逻辑稍微复杂但套路是固定的网上有很多现成的M语言模板可以参考。5. 常见问题与排查技巧实录5.1 刷新报错找不到数据源这是最常见的问题八成是接口地址变了或者参数拼错了。排查顺序先在浏览器里手动打开接口地址看能不能返回数据如果能再检查Power Query里的URL和参数是否一致如果浏览器也打不开那就是接口本身的问题换个源或者等一会儿再试。还有一种情况是接口需要登录态也就是要带Cookie。Power Query默认不带Cookie这时候要么换一个公开接口要么手动在Headers里加Cookie字段。加Cookie比较麻烦因为Cookie会过期不建议普通用户折腾。5.2 数据错位或字段对不上展开JSON的时候如果某一行的字段数量和别的不一样展开后会出现错位。比如大部分记录有6个字段某一条只有5个展开后这一条的数据会往前挤一格。解决办法是在展开之前先检查数据结构用Table.ExpandRecordColumn明确指定要展开哪些字段而不是用默认的展开所有。另外字段名大小写敏感。Close和close在Power Query里是两个不同的字段展开的时候要看清楚。5.3 刷新速度慢或者卡死数据量大的时候Power Query会一次性把所有数据拉进内存再处理容易卡。优化思路有三个一是只拉需要的字段在源步骤就用Table.SelectColumns筛掉冗余列二是限制日期范围别一次拉十年三是关掉后台刷新改成手动刷新避免打开文件时卡顿。如果接口本身响应慢那没办法只能等。我一般把刷新安排在收盘后那时候网络相对空闲。5.4 常见问题速查表问题现象可能原因解决方法报错403缺少请求头在Headers里加User-Agent返回空表接口地址错误或参数不对浏览器验证接口检查参数拼写数字变文本类型未转换手动更改列类型为小数排序错乱日期或数字存成文本改类型后重新排序刷新卡死数据量过大限制范围、筛选字段、关后台刷新字段错位JSON结构不一致明确指定展开字段参数不生效参数名拼写错误检查高级编辑器里的变量名5.5 我踩过的几个坑第一个坑是接口返回的日期格式不统一。有的返回2024-01-01有的返回20240101还有的返回时间戳。Power Query的日期转换对格式很挑格式不对就转成null。我的做法是先统一成文本用Text.Start和Text.Middle截取再拼成标准格式最后转日期。第二个坑是复权问题。很多免费接口返回的是不复权数据遇到除权除息价格会跳空回测的时候会失真。如果你要做回测得找带复权因子的接口或者自己根据除权信息算。这个对普通记录用途影响不大但做量化就得注意。第三个坑是接口限流。有些接口一分钟只能请求几次你批量抓多只股票的时候会触发限流返回空数据。解决办法是在请求之间加延时Power Query里可以用Function.InvokeAfter加等待但会拖慢整体速度。我的建议是分批抓一次抓十来只隔几分钟再抓下一批。6. 进阶玩法与效率提升技巧6.1 多股票批量抓取的实现思路单只股票跑通之后批量抓取是自然的下一步。核心思路是建一个股票代码列表对列表里每个代码调用一次抓取函数把结果合并成一张大表。具体做法是先把单只股票的查询转成自定义函数。在Power Query编辑器里右键点查询→创建函数给它起个名字比如抓行情参数就是股票代码。然后新建一个查询里面放一个股票代码列表用List.Transform对每个代码调用抓行情最后用Table.Combine合并。这个逻辑听起来简单但实际写的时候要注意错误处理。某只股票抓失败不能影响其他股票所以要在函数里加try...otherwise失败就返回一个空表。这样整体不会崩。6.2 数据落地与历史归档Power Query刷新是覆盖式的每次刷新都会把旧数据替换掉。如果你想保留历史快照比如每天存一份那就不能只靠刷新。做法是把刷新后的数据用VBA或者手动复制到另一个工作表按日期命名。或者用Power Automate定时把数据导出成CSV存档。我自己的做法是每周手动归档一次把当前数据复制到一个历史工作表标注日期。这样既保留了快照又不用天天操作。6.3 和其他Excel功能的联动数据进来之后你可以直接拿它做透视表、画K线图、算均线。Power Query加载的表是标准表格支持所有Excel原生功能。比如插入透视表把日期放行、收盘放值就能看走势。或者用条件格式标出涨跌红绿一眼看清。如果你会写一点公式还可以加计算列比如涨跌幅、振幅、换手率。这些在Power Query里加比在Excel里加更高效因为是在数据加载阶段算好的不用每次刷新重算。6.4 几个提升效率的小技巧第一个技巧是把常用参数放到一个配置表里。建一个工作表叫配置里面放股票代码、起始日期、结束日期这些参数Power Query从这张表读参数。这样你改配置不用进编辑器直接在单元格里改就行。第二个技巧是用查询依赖管理多个查询。Power Query里查询之间可以互相引用你可以建一个基础数据查询负责抓取再建几个分析查询引用它做不同处理。这样结构清晰改一处全联动。第三个技巧是定期清理查询缓存。Power Query会缓存数据时间长了文件会变大。在数据→查询和连接里可以清缓存或者干脆把查询复制到一个新文件里保持轻量。7. 关于稳定性和长期维护的几点体会这套方案我从几年前开始用中间换过几次数据源但整体框架没变过。Power Query的稳定性取决于数据源数据源稳定它就稳定。所以选源的时候多花点时间找那种公开时间长、口碑好的接口别贪图字段多就用冷门源。维护成本主要在数据源变更上。接口地址一变你得进编辑器改URL改完刷新就行不用重写整个流程。这就是Power Query比VBA强的地方逻辑和配置分离改配置不动逻辑。最后说一个心态问题别追求一步到位。我见过很多人一上来就想搞全自动、多股票、实时更新结果卡在第一步就放弃了。正确的路径是先跑通一只股票的历史数据用上一周确认稳定了再考虑批量再考虑自动化。每一步都验证过再往下走这样出问题的时候你知道是哪一步的锅。这套东西说到底就是个工具目的是把你从重复劳动里解放出来让你有时间做真正有价值的分析。工具本身不产生收益但它省下来的时间可以。