如何在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可以查詢的鏈接記錄。建議在表格中操作,這樣新增行會自動繼承格式和公式。
設置表格
- 在單元格A1中輸入 Ticker,然後在A2到A4中輸入 AAPL、MSFT 和 NVDA。
- 選中A1:A4,選擇 插入 > 表格,勾選「表包含標題」。
- 選中股票代碼單元格,打開 數據 選項卡,點擊 股票。
- 檢查每個單元格中是否出現小建築圖標。該圖標表示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文檔中複製接口地址和認證頭名稱。然後按以下步驟操作:
- 轉到 數據 > 獲取數據 > 從其他源 > 空白查詢。
- 打開 高級編輯器,粘貼下面的模板。
- 將三個佔位符替換為你的接口地址、認證頭名稱和API Key。
- 點擊 完成,使用箭頭圖標展開嵌套記錄,然後選擇 關閉並加載。
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 Query | 15-30分鐘 | 定時刷新,最快每分鐘一次 | 儀表板、外匯、加密貨幣、港股和A股數據 |
| 第三方加載項 | 安裝並登錄 | 流式推送,取決於供應商 | 不想寫代碼但需要流式數據的交易者 |
什麼時候API是更好的選擇
如果你只關注幾家美國大公司且15分鐘延遲可以接受,繼續使用股票數據類型即可。當你需要追蹤外匯、加密貨幣或亞洲市場,想要分鐘K線或Level 2深度數據,或需要在Python中使用同一數據源進行回測時,轉向API。上面的代碼是一個起點,之後你可以在腳本中複用相同的接口。
如果15分鐘延遲影響了你的工作流,不要再調整內置功能,直接切換到API。
先用內置功能,再添加API
對於快速自選股列表,將股票代碼轉換為股票數據類型,添加價格和漲跌列,設置刷新間隔即可。對於更新或更廣泛的數據,通過Power Query加載API並設置定時刷新,注意限額。當你需要流式數據而不想寫代碼時,加載項填補了這個空缺。
無論選擇哪種方式,記住Excel是刷新而非流式推送,內置價格有延遲。如果你想進一步了解實時報價、分鐘K線和Python示例,請訪問iTick博客獲取圍繞市場數據API的實戰教程。