如何在Excel中获取实时股票行情数据

  1. iTick
  2. 市场数据
如何在Excel中获取实时股票行情数据 - iTick
如何在Excel中获取实时股票行情数据

将实时股票行情数据导入Excel最快的方式是使用内置的"股票"数据类型。在表格中输入股票代码,点击"数据 > 股票",添加"价格"列,然后刷新即可。整个过程约两分钟,但大多数交易所的价格会延迟15分钟或更久。如果你需要更新的报价,可以通过Power Query从市场数据API加载,或安装流式加载项。

问题在于Excel本身不会流式推送任何数据。价格仅在刷新时更新,某些安装中"股票"按钮可能缺失,从网页复制报价的方式在页面布局变化后就会失效。许多人最终手动粘贴价格,这完全违背了实时表格的意义。

本指南分四步讲解:转换股票代码、添加和刷新价格字段、从API拉取价格、以及选择适合你工作的方法。iTick是一家市场数据API提供商,你会看到API在哪些方面优于内置功能,以及哪些方面不然。

Excel中"实时"的含义及前置条件

Excel通过从数据源刷新来展示实时数据,而非逐笔流式推送。在开始搭建之前了解延迟和要求,可以避免浪费时间调试一个永远无法实时的表格。

Excel中的"实时"是刷新,不是流式推送

内置功能从LSEG Data & Analytics获取价格,微软会根据交易所施加不同的延迟。下表摘自微软的支持交易所和延迟时间列表。

交易所延迟(分钟)
纳斯达克最新成交0
Cboe BZX交易所0
印度国家证券交易所5
纽约证券交易所15
纳斯达克证券交易所15
伦敦证券交易所15
香港交易所15
多伦多证券交易所20
深圳证券交易所30

你在美东时间上午10:30读到的纽约证券交易所报价,反映的是约10:15的市场状况。微软还明确表示,这些数据不用于交易目的。

Excel刷新而非流式推送,所以把每个内置价格都视为延迟快照。

开始前需要准备什么

先检查以下项目,缺少任何一项通常是"股票"按钮不显示的原因。

  • Microsoft 365账户或免费Microsoft账户,这是微软对股票数据类型的要求。
  • Office语言偏好中的编辑语言为英语、法语、德语、意大利语、西班牙语或葡萄牙语。
  • 互联网连接,因为每次刷新都会调用在线数据源。
  • 对于API方法,需要一个API Key以及支持Power Query的Excel版本(位于"数据 > 获取数据"下)。

第1步:将股票代码转换为股票数据类型

输入股票代码并点击"数据 > 股票",会将纯文本转换为Excel可以查询的链接记录。建议在表格中操作,这样新增行会自动继承格式和公式。

设置表格

  1. 在单元格A1中输入 Ticker,然后在A2到A4中输入 AAPL、MSFT 和 NVDA。
  2. 选中A1:A4,选择 插入 > 表格,勾选"表包含标题"。
  3. 选中股票代码单元格,打开 数据 选项卡,点击 股票。
  4. 检查每个单元格中是否出现小建筑图标。该图标表示Excel已将你的文本匹配到一家公司。

转换也支持公司和基金名称,但股票代码是最不容易产生歧义的输入。如果某个代码在多个交易所上市,请加上微软表格中的交易所代码前缀,例如 XLON:VOD。

在Excel表格中转换股票代码,后续每一步都会更轻松。

修复显示问号的单元格

问号表示Excel无法匹配你的文本。修正拼写错误并按回车,Excel会重试查找。如果仍然失败,点击问号打开选择器面板,按关键词搜索,选择正确的匹配项,然后点击 选择。

第2步:添加价格字段并保持更新

单元格转换完成后,你可以将字段添加为列,然后控制刷新时间。价格仅在刷新时更新,因此这一步决定了你的表格有多"实时"。

添加价格、涨跌等列

选中已转换的单元格,点击出现的 添加列 按钮。选择 价格,然后重复添加 涨跌、前收盘、最后交易时间 或 交易所。在表格中,你也可以直接在标题行输入字段名称(如"涨跌"),该列会自动填充。

你也可以写公式。如果A2包含已转换的股票代码,=A2.Price 返回其价格,=FIELDVALUE(A2,"Price") 效果相同。按 Ctrl+Shift+F5 可列出所有可用字段。对于投资组合,添加"股数"列和"市值"列。如果价格显示200,股数显示50,市值(价格乘以股数)返回10,000。

按需或定时刷新数据

点击 数据 > 全部刷新,或右键点击已转换的单元格,选择 数据类型 > 刷新。同一菜单中还有 刷新设置,你可以设置在打开文件时刷新或按定时器刷新。

历史价格来自另一个函数。以下公式返回30天的日期和收盘价:

      =STOCKHISTORY("MSFT",TODAY()-30,TODAY(),0,1,0,1)

    

STOCKHISTORY给你历史数据,而股票数据类型给你最新的延迟报价。

第3步:通过Power Query从API获取实时价格

当延迟报价不够用,或者你关注的市场不在微软列表中时,API是下一个选择。Power Query可以调用REST接口、读取JSON并加载到可定时刷新的工作表中。

将JSON加载到工作表

从你的数据提供商的文档中获取API Key和报价接口地址。对于覆盖股票、外汇和加密货币以及中国香港和中国A股市场的iTick,从iTick文档中复制接口地址和认证头名称。然后按以下步骤操作:

  1. 转到 数据 > 获取数据 > 从其他源 > 空白查询。
  2. 打开 高级编辑器,粘贴下面的模板。
  3. 将三个占位符替换为你的接口地址、认证头名称和API Key。
  4. 点击 完成,使用箭头图标展开嵌套记录,然后选择 关闭并加载。
      let
    ApiUrl = "粘贴报价接口URL",
    ApiKey = "粘贴你的API密钥",
    Source = Json.Document(Web.Contents(ApiUrl, [Headers = [#"文档中的认证头名称" = ApiKey]])),
    AsTable = Record.ToTable(Source)
in
    AsTable

    

如果Excel要求凭据,选择"匿名",因为密钥已经在请求头中传递。

设置刷新计划并了解其限制

打开 数据 > 查询和连接,右键点击你的查询,选择 属性,勾选 刷新频率 并设置分钟数。设置为1分钟,在6.5小时的美股交易时段内你会发出390次请求,因此请核对你的套餐限额。

每次刷新是一次REST调用,这意味着这是轮询而非流式推送。真正的逐笔更新需要WebSocket连接,而Excel原生无法建立WebSocket。iTick同时提供REST和WebSocket,因此一个小脚本或加载项可以处理流式数据,而Excel读取结果。

Power Query可以每分钟轮询一次API,而WebSocket流式推送需要Excel之外的支持。

第4步:选择适合你工作的方法

根据价格需要多新鲜以及你追踪哪些市场来选择。内置功能在简单性上胜出,API在时效性和覆盖面上胜出,加载项介于两者之间。

三种方法对比

方法大致设置时间更新速度最适合
股票数据类型2分钟手动或定时刷新,大多数交易所有延迟个人自选股和简单投资组合
API + Power Query15-30分钟定时刷新,最快每分钟一次仪表板、外汇、加密货币、港股和A股数据
第三方加载项安装并登录流式推送,取决于供应商不想写代码但需要流式数据的交易者

什么时候API是更好的选择

如果你只关注几家美国大公司且15分钟延迟可以接受,继续使用股票数据类型即可。当你需要追踪外汇、加密货币或亚洲市场,想要分钟K线或Level 2深度数据,或需要在Python中使用同一数据源进行回测时,转向API。上面的代码是一个起点,之后你可以在脚本中复用相同的接口。

如果15分钟延迟影响了你的工作流,不要再调整内置功能,直接切换到API。

先用内置功能,再添加API

对于快速自选股列表,将股票代码转换为股票数据类型,添加价格和涨跌列,设置刷新间隔即可。对于更新或更广泛的数据,通过Power Query加载API并设置定时刷新,注意限额。当你需要流式数据而不想写代码时,加载项填补了这个空缺。

无论选择哪种方式,记住Excel是刷新而非流式推送,内置价格有延迟。如果你想进一步了解实时报价、分钟K线和Python示例,请访问iTick博客获取围绕市场数据API的实战教程。