【原】Excel如何獲取實時股票數據

ExcelEasy 2022-07-19 發(fā)布于北京

展開全文

本文介紹在Excel中如何獲取實時股票數據。

文末有視頻講解

獲取股票實時數據的方法也不少。但是相對簡單和可行的不太多。經過嘗試，本文介紹一種自動獲取股票實時數據的方法，并且同時介紹兩種在Excel中的存儲和展現方式。

獲取實時股票數據

我們需要從網頁上獲取實時股票數據。基本上，財經類網站都提供股票數據，而且網頁的布局基本類似。以東方財富網為例，是這樣的形式：

這里展示選中的一個股票的信息。

當然，我們希望一次性獲取多個股票信息，網站上也有這樣的數據，就是類似于行情，或者自選股之類的頁面：

網上的部分教程中，說可以從這個頁面獲取數據。但是目前來說，鑒于各網站采取的技術，這么做稍微有點困難。我們就不介紹這種方法了，而是介紹一種相對簡單的方法。

首先，需要自己制作一個自選股表：

這個表格的名稱為“自選股票”：

其中，交易所代碼中，如果股票在上海上市，就用sh，在深圳上市，就用sz。

導入到Power Query中，并添加URL列：

然后使用Web.Page函數獲取實時股票數據：

其中的{8}[Data]代表每個網頁上我們需要的股票數據，見下圖：

去掉多余的列并展開后，得到：

我們需要將Column1和Column2合并成一列：

這一步可以通過逆透視然后刪除屬性列得到。（具體參見下面的完整代碼或上面的視頻）

直接拆分即可得到結果：

上載到Excel中：

為了保證得到實時數據，需要修改刷新頻率。

選中查詢的結果表中任意單元格，點擊“數據”選項卡中的全部刷新的下拉箭頭：

點擊連接屬性，將刷新頻率修改為2分鐘：

點擊確定即可。

這個查詢的完整代碼是：

let    源 = Excel.CurrentWorkbook(){[Name="自選股票"]}[Content],    添加URL = Table.AddColumn(源, "URL", each "http://quote.eastmoney.com/" & [交易所代碼] & Text.From([股票代碼]) &".html"),    獲取網站數據 = Table.AddColumn(添加URL, "Data", each Web.Page(Web.Contents([URL])){8}[Data]),    刪除多余列 = Table.RemoveColumns(獲取網站數據,{"交易所代碼", "URL"}),    提取網站數據 = Table.ExpandTableColumn(刪除多余列, "Data", {"Column1", "Column2"}, {"Column1", "Column2"}),    逆透視網站數據 = Table.Unpivot(提取網站數據, {"Column1", "Column2"}, "屬性", "值"),    刪除屬性列 = Table.RemoveColumns(逆透視網站數據,{"屬性"}),    拆分網站數據列 = Table.SplitColumn(刪除屬性列, "值", Splitter.SplitTextByDelimiter("：", QuoteStyle.Csv), {"值.1", "值.2"}),    命名項目和數值列 = Table.RenameColumns(拆分網站數據列,{{"值.1", "項目"}, {"值.2", "數值"}})in    命名項目和數值列

用自定義類型存放和展示股票實時數據

我們還可以用另外的形式存放和展示股票數據。

這也是通過Power Query實現的。

在Power Query中得到這樣的數據后

對值.1列進行逆透視：

選中股票名稱和后面的所有屬性列，并點擊鼠標右鍵：

點擊“創(chuàng)建數據類型”，

修改數據類型名稱和顯示列。

關閉并上載至Excel：

其中第二列股票就是一個復合數據類型數據，顯示的是股票的最新股價，前面的圖標表示這是一個復合類型。點擊該圖標，顯示該股票的明細信息：

在超級表的右上角有一個圖標：添加列，點擊該圖標，打開該類型包含的所有屬性：

選擇其中一個屬性，比如股票名稱，就可以在表格中添加股票名稱：

在其他單元格或公式中引用股票數據時，使用"B2."的形式：

結果和公式如下：

用Power Query獲取數據并創(chuàng)建自定義類型的完整代碼如下：

let    源 = Excel.CurrentWorkbook(){[Name="自選股票"]}[Content],    添加URL = Table.AddColumn(源, "URL", each "http://quote.eastmoney.com/" & [交易所代碼] & Text.From([股票代碼]) &".html"),    獲取網站數據 = Table.AddColumn(添加URL, "Data", each Web.Page(Web.Contents([URL])){8}[Data]),    刪除多余列 = Table.RemoveColumns(獲取網站數據,{"交易所代碼", "URL"}),    提取網站數據 = Table.ExpandTableColumn(刪除多余列, "Data", {"Column1", "Column2"}, {"Column1", "Column2"}),    逆透視網站數據 = Table.Unpivot(提取網站數據, {"Column1", "Column2"}, "屬性", "值"),    刪除屬性列 = Table.RemoveColumns(逆透視網站數據,{"屬性"}),    按分隔符拆分列 = Table.SplitColumn(刪除屬性列, "值", Splitter.SplitTextByDelimiter("：", QuoteStyle.Csv), {"值.1", "值.2"}),    更改的類型 = Table.TransformColumnTypes(按分隔符拆分列,{{"值.1", type text}, {"值.2", type text}}),    已透視列 = Table.Pivot(更改的類型, List.Distinct(更改的類型[值.1]), "值.1", "值.2"),    創(chuàng)建的數據類型 = Table.CombineColumnsToRecord(已透視列, "股票", {"股票名稱","最新", "均價", "漲幅", "漲跌", "總手", "金額", "換手", "量比", "最高", "最低", "今開", "昨收", "漲停", "跌停", "外盤", "內盤"}, [DisplayNameColumn="最新", TypeName="Excel.DataType"]),    更改的類型1 = Table.TransformColumnTypes(創(chuàng)建的數據類型,{{"股票代碼", type text}})in    更改的類型1