[VBA] 3步驟教你抓取 - 大盤成份股即時數據 (上)

[VBA] 3步驟教你抓取 – 大盤成份股即時數據 (上)

大盤成份股即時數據 ( 上 / 下篇 ) — 介紹

大盤成份股即時數據這一篇，有關於操作方法與公式的編寫，文章有點冗長，所以分成上/下兩篇來介紹，方便大家閱讀與程式製作。

◎ 上篇 / 介紹 : 如何使用 EXCEL 連結外部資料源的功能，將外部資料匯入活頁簿內，進行資料的搬移 / 排序 / 整理。
◎ 下篇 / 介紹 : 將(上)篇的整理資料，加上 DDE/RTD 串接語法，來連結外部資料源，即時取得大盤成份股的相關資訊，進而計算出個股漲跌對大盤的相對影響。

大盤成份股是什麼

所謂的『大盤』: 就是台灣加權指數。『 成份股 』: 也叫 權值股 (權重股)，指的是納入加權指數計算的上市公司。

上市公司的市值越大，權值越重。像是台積電 / 聯發科 / 鴻海，這些市值大的公司，其股價表現，往往對加權指數的漲跌，有較大的影響。

目前 大盤成份股 有哪些? 其權值多少? 可以參考 : 證交所 – 大盤成份股 得知。

知道網址所在，就能利用程式來呼叫，串起與 證交所 – 大盤成份股 連線，作相關資料的匯入。

抓取 – 大盤成份股權重資料

首先，進入 證交所 – 大盤成份股 後，將所有個股資料複製 / 貼上到大盤權重工作表 …… ( × )。

既然學了 VBA，當然要用最便捷的方式，來設計自動化的步驟，來讓抓取工作更輕鬆，才對 !

所以『 錄製巨集 』就派上了用場，它是 VBA 的強大武器，就像是遊戲界的「按鍵精靈」。只要使用者將想要執行的動作，複製起來；電腦就會根據你的動作步驟，自動將程式碼編寫出來，省去使用者很多撰寫程式的時間。

[ 注意 ] : 我的工作系統為 Excel 365 版本，會以 365版本 來作解說。因為程式錄製的結果會跟 舊版本 有所不同；若需要舊版本，再寫信跟我索取程式。

錄製巨集

[ 開始錄製 – 抓取步驟 ] :
1. 新增工作表 : 為了存放大盤權重資料，新增一個工作表，名稱 : 大盤權重。( 工作表 — 改名方法 : 點選工作表，按滑鼠右鍵，重新命名，即可。)

2. 打開開發人員工具 : 點擊 – 功能列 開發人員，點選 :『 錄製巨集 』。

3. 輸入巨集名稱 : 此時會出現錄製巨集視窗，要求輸入巨集名稱，預設是 : 巨集1 … 2 … 3，先不改，使用預設名稱。
◎ 如下圖 : 第一次開啟，巨集預設為 巨集1，按下 : 確定，在 VBA 專案列表內，會多個 Module1 巨集模組。

4. 開始錄製 : 此時你可以發現工具列內的 錄製巨集 功能，變為 停止錄製，代表 : 錄製功能 – 已啟動。( PS. 錄製完畢，點選 : 停止錄製，即結束錄製。)

5. 連結證交所 – 大盤成份股網址 :
◎ 點選 : 功能列 – 資料。
◎ 再選 : 工具列 – 從 WEB，就是 從網路 抓取資料的方式。
◎ 點選後，會出現 從 WEB 抓取視窗，在 URL 網址輸入框內，輸入 : https://www.taifex.com.tw/cht/9/futuresQADetail 連結網址，按 : 確定。

6. 出現導覽器 : 如下圖。
◎ 點選 : Table 0，在 資料表檢視 框內的 Table 0 資料，就是我們需要的權重佔比資料。
◎ 再選 載入至 : 確定資料無誤，點選 : 載入旁的 ▼ 按鈕，選擇 : 載入至。

7. 匯入資料 : 點選 – 載入至，出現 匯入資料 視窗後 —
◎ 選擇 : 表格 (預設)。其他選項有 – 樞紐分析表 ……，看個人的需求，我們需要的是 : 表格。
◎ 選擇 : 將資料放在 目前工作表的儲存格 : 為了自動化，最好選擇 : 目前工作表的儲存格 – 設定值 : =$A$1，方便做資料處理。
◎ 新增此資料至資料模型 : 不勾選。

8. 大盤成份股 權重資料表 : 顯示如下 —

※ 到目前為止，對外連線爬取證交所的權重資料，已錄製完成。

◎ 爬取 – 證交所大盤成份股權重資料 : 公式如下 —

[ 程式碼 / 錄製巨集 - 大盤成份股權重資料 ] :
Sub 巨集1()

    Application.CutCopyMode = False
    ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    來源 = Web.Page(Web.Contents(""https://www.taifex.com.tw/cht/9/futuresQADetail""))," & Chr(13) & "" & Chr(10) & "    Data0 = 來源{0}[Data]," & Chr(13) & "" & Chr(10) & "    已變更類型 = Table.TransformColumnTypes(Data0,{{""排行"", Int64.Type}, {""證券名稱"", Int64.Type}, {""證券名稱2"", type text}, {""市值佔 大盤比重"", Percentage.Type}, {""排行2"", Int64.Type}, {""證券名稱3"", Int64.Type}, {""證券名稱4"", type text}, {""市值佔 大盤比重2"", Percentage.Ty" & _
        "pe}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    已變更類型" & _
        ""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0"
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

☆ 使用 錄製巨集 相當方便，唯一的缺點就是程式碼太多太雜，有些功能可以省略，但系統還是會全部列出。若不是很了解它的參數用法，就不要去改它，除非它出現錯誤的情況，再去了解哪個環節，出了問題。

※ 接下來，繼續錄製 — 權重資料的排序步驟，方便未來的公式建置與分析。

大盤成份股 – 資料整理

權重排行 – 排序

※ 爬取的權重資料，分左右兩邊展示，不方便做資料分析。所以將右邊的資料，全部複製 / 貼上到 A469 以下，完成排行排序 1 ~ 933。

[ 提醒 ] : 表格錄製不能使用剪下動作，因為在貼上時，不會產生程式碼，這應該是 BUG，所以改用複製來操作。( PS. 之後 E ~ H欄會刪除，所以對資料不會造成影響。)

[ 雙邊改成單邊資料 – 排列 ] : 總家數 — 933家

[ 選取 – 複製 操作步驟 ] :
1. 點選 E2，按 CTRL + SHIFT + → ( 範圍選取 E2 : H2 )，再按 CTRL + SHIFT + ↓( 再將範圍 E2 : H467 全部選取 )。
2. 在選取的範圍內，按滑鼠右鍵，選擇 : 複製或 CTRL + C。

※ [ 選取 – 複製 ] : 錄製的公式，如下 —

    [ 程式碼 ] :      
    Range("E2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy

[ 資料 – 依排行作排序 ] : 總家數 — 933家。

[ 移動游標 – 貼上 操作步驟 ] : 將複製資料貼到 A469 位置以下。
1. 點選 A2，按 CTRL + ↓( 將游標移到區域邊界 – 有資料的位置 )，所以游標會在 A468 停止，再按 ↓ 鍵，將游標再往下一列 A469，要貼上資料的開始位置。
2. 步驟是如此，但程式碼卻不如預期。只好手動修正，將前三行修改成自動移動到下一步 Offset(1)。
☆ 公式 : Range(“A2”).End(xlDown).Offset(1).Select，這樣就完成這局部的動作設定。

※ [ 移動游標 – 貼上 ] : 錄製的公式，如下 —

    [ 程式碼 ] :      
    Range("A2").Select
    Selection.End(xlDown).Select
    Range("A469").Select
    ActiveSheet.Paste

    '因為 Range("A469").Select -是錄製時，按:下鍵，所產生的程式碼，這條指令在未來資料行列有變動時，會產生錯誤，所以要進行更改。
    '更改如下:
    
    Range("A2").End(xlDown).Offset(1).Select
    ActiveSheet.Paste

個股代碼 – 排序

※ 為了查詢與資料整理的方便，將權重資料依照欄位 : 證券名稱 ( 也是 : 股票代碼 ) 來作排序。
☆ 運用 EXCEL 表格功能，設定 小 → 大 排序。

[ 證券名稱 – 排序 ] :

[ 證券名稱 – 排序 操作步驟 ] :
1. 點選 B2，證券名稱 右邊 ▼，出現排序功能選項。( PS. 進行欄位排序，其它的欄位資料，也會一起調整位置。)
2. 選擇 : 從最小到最大排序，此時 證券名稱 ( 股票代碼 )，會依照代碼的數值，作 小 → 大 排序。

※ [ 證券名稱 – 排序 ] : 錄製的公式，如下 —

    [ 程式碼 ] :   
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("大盤權重").ListObjects("Table_0").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("大盤權重").ListObjects("Table_0").Sort.SortFields.Add2 _
        Key:=Range("Table_0[[#All],[證券名稱]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("大盤權重").ListObjects("Table_0").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

☆ 因為在設定 : 匯入資料 時，選擇 : 將資料放在 目前工作表的儲存格 – 設定值 : =$A$1，系統會將資料載入到 大盤權重 工作表，所以上方的錄製公式，就無需再作其它處理。
( PS. 若是選擇 : 將資料放在 新工作表，那公式就要作更動，不是改工作表位置，就是改工作表名稱，最後步驟一定要將新工作表刪掉；否則程式一開，就會產生一個新工作表，一直累積，這樣就不是正確的設計方式。)

[ 資料 – 依個股代碼作排序 ] : 總家數 — 933家。

※ 完成 個股代碼 排序動作，如上圖。

資料欄位 – 整理

最後就剩下多餘的欄位刪除和留存的欄位更名。要清理的欄位有 : A欄和 E ~ H欄，更名的欄位 : B ~ D欄。

[ 資料欄位 – 清理 ] :

[ 資料欄位 – 刪除 操作步驟 ] :
1. 點選 E : H欄，按滑鼠右鍵，選擇 : 刪除。( PS. 因為錄製的公式，無法作不相鄰的資料刪除，只好分2個步驟處理，而且要注意 : 刪除的順序，若先刪除 A欄，那 E : H欄的位置會左移，必須改成 : D : G欄，而且在執行中，有欄位的變動，勢必不好。)
2. 再選 A欄，按滑鼠右鍵，選擇 : 刪除。

※ [ 資料欄位 – 刪除 ] : 錄製的公式，如下 —

    [ 程式碼 ] :      
    Columns("E:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft

    可以簡化公式:
    Columns("E:H").Delete
    Columns("A:A").Delete

[ 資料欄位 – 刪除完畢 ] : 資料如下 —

停止錄製巨集

※ 所有錄製動作，到此結束，按下 : 停止錄製 功能鍵。

資料欄位-更名

※ 先停止錄製巨集的原因，是更名的步驟簡單，若藉由巨集錄製出來的程式碼太多，難維護，所以改用手動設定，較為簡單。若使用者執意使用錄製，也沒問題 !

[ 資料欄位 – 更名 操作步驟 ] :
1. 點選 A1，在資料編輯列裡，將 證券名稱 改為 : 股票代碼。
2. 點選 B1，在資料編輯列裡，將 證券名稱2 改為 : 股票名稱。
3. 點選 C1，在資料編輯列裡，將 市值佔大盤比重 改為 : 權重。

    [ 程式碼 / 資料欄位 - 更名 ] : 
    [A1] = "股票代碼" 
    [B1] = "股票名稱" 
    [C1] = "權重"

[ 資料欄位 – 更名完畢 ] : 資料如下 —

切斷查詢連線

當活頁簿以 從WEB 功能，連結證交所爬取大盤成份股權重資料的同時，就會自動建立 查詢連線。資料一旦載入，連線就無需再利用，但它不會自動切斷，會一直保持連線，長久使用下來，新連線一直新增，這樣會對系統造成莫大負擔，最嚴重就是死機。
所以解決的方法，就是 切斷查詢連線。可以手動切斷處理，但有可能會忘記，下次再執行時，有可能產生錯誤，365系統會判別已有相同連線而報錯，一勞永逸的辦法，就是程式裡加一段指令 : 切斷查詢連線。

[ 查詢連線 – 連線情況 ] : 必須將其切斷。

[ 程式碼 / 切斷查詢連線 ] :
ActiveWorkbook.Queries(“Table 0“).Delete

[ 語法說明 ] :
◎ ActiveWorkbook : 目前使用的活頁簿。
◎ Queries : 查詢連線，後面加 (“Table 0”) 連線名稱。
◎ Delete : 刪除。

[ 切斷查詢連線 ] :

完整程式碼

Sub 巨集1()
    
        ' 爬取 - 證交所 / 大盤成份股 權重資料
        Application.CutCopyMode = False
        ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    來源 = Web.Page(Web.Contents(""https://www.taifex.com.tw/cht/9/futuresQADetail""))," & Chr(13) & "" & Chr(10) & "    Data0 = 來源{0}[Data]," & Chr(13) & "" & Chr(10) & "    已變更類型 = Table.TransformColumnTypes(Data0,{{""排行"", Int64.Type}, {""證券名稱"", Int64.Type}, {""證券名稱2"", type text}, {""市值佔 大盤比重"", Percentage.Type}, {""排行2"", Int64.Type}, {""證券名稱3"", Int64.Type}, {""證券名稱4"", type text}, {""市值佔 大盤比重2"", Percentage.Ty" & _
            "pe}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    已變更類型" & _
            ""
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [Table 0]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Table_0"
            .Refresh BackgroundQuery:=False
        End With
    
        ' 選取 - 複製 / 操作步驟
        Range("E2").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Application.CutCopyMode = False
        Selection.Copy
        
        ' 移動游標 - 貼上 / 操作步驟
        Range("A2").End(xlDown).Offset(1).Select
        ActiveSheet.Paste
        
        ' 證券名稱 - 排序 / 操作步驟
        Application.CutCopyMode = False
        ActiveWorkbook.Worksheets("大盤權重").ListObjects("Table_0").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("大盤權重").ListObjects("Table_0").Sort.SortFields.Add2 _
            Key:=Range("Table_0[[#All],[證券名稱]]"), SortOn:=xlSortOnValues, Order:= _
            xlAscending, DataOption:=xlSortTextAsNumbers
        With ActiveWorkbook.Worksheets("大盤權重").ListObjects("Table_0").Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
        ' 資料欄位 - 刪除 / 操作步驟
        Columns("E:H").Delete
        Columns("A:A").Delete
        
        ' 資料欄位 - 更名
        [A1] = "股票代碼"
        [B1] = "股票名稱"
        [C1] = "權重"
        
        ' 切斷 Table 0 查詢連線
        ActiveWorkbook.Queries("Table 0").Delete
 
End Sub

結論

我猜想有人看到這，還搞不清處為何要大費周章收集這些資料，對於投資到底有何用處?

首先，大家要先了解為何大數據會在這些年竄起的原因。當AlphaGo打敗世界第一的圍棋高手那一刻，AI大數據就更受大家重視，再加上網路速度與運算能力的提升，皆影響著各產業想藉由大數據的分析研究，來提升產業經濟效益。

金融投資領域也是如此。目前的教學 : 大盤與個股歷史資料的收集，為的也是想研究大盤與個股的歷史脈動，藉由回測來分析並驗證投資方法的有效性，甚至日後的 AI 機器學習及深度學習領域，也需要這些歷史資料來作依據，來達到預測股價漲跌的目的。

所以，現在已進入 AI 操盤時代，主力已經進化到科技戰的階段，你一位股市新手，真的只想戴著鋼盔往前衝嗎?

非常感謝您看到最後，若在閱讀的過程中，有任何疑問，歡迎您利用左下角 Messenger 直接提問或寫信給我，我會盡快回覆您，感謝您的閱讀，感恩 !

( PS. 如果喜歡 股小白 的文章，歡迎到 股小白臉書粉絲頁 點讚，衝人氣，目前不定期分析大盤趨勢，將來會有 每日公報 : 分析並公布跑分的個股資料，讓大家作為投資參考。 )

關聯文章

1. 3步驟教你抓取 – 大盤成份股即時數據 (上)

如何使用 EXCEL 連結外部資料源的功能，將外部資料匯入活頁簿內，進行資料的搬移 / 排序 / 整理。

2. 3步驟教你抓取 – 大盤成份股即時數據 (下)

將(上)篇的整理資料，加上 DDE/RTD 串接語法，來連結外部資料源，即時取得大盤成份股的相關資訊，進而計算出個股漲跌對大盤的相對影響。

★ 這兩篇(上/下篇)文章，主要在於權重資料的擷取與更新，有興趣學習的朋友，請依照文章順序及內文的操作步驟，一步步完成，就能順利架起 大盤成份股即時報價系統。

V B A 資源

EXCEL & VBA功能 – 綜合筆記

EXCEL & VBA功能筆記 : 將針對文章中所使用的 EXCEL 及 VBA公式 / 工具操作 / 巨集設定 / 控制項的運用，做個整合記錄，方便大家在學習當下的參考與使用。

VBA指令 – 彙整筆記

VBA 提供相當多的指令用法，VBA指令彙整筆記，將會陸續記錄 VBA文章中，所用過的 VBA指令，方便學員們對照運用。

VBA程式語法 – 彙整筆記

語法就是程式的文法。使用者要與電腦溝通，就必須遵守這些規則。VBA程式語法筆記 : 將針對 VBA文章中所使用的 VBA程式語法，做個整合記錄，方便大家在學習當下參考與使用。

大盤成份股 是什麼

抓取 – 大盤成份股 權重資料