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

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

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

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

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

成份股權重資料 – 複製/貼上

繼(上)篇文章的資料整理，接下來，就要完成後續 DDE/RTD 的串接，連結 XQ，抓取大盤成份股即時數據。

首先，將 大盤權重 工作表複製一份到 權重更新_365 ( 工作表自建，名稱自取 )。
( PS. 當然可以直接在 大盤權重 工作表上製作，只要調整好資料串接的位置，就不會有問題。但大盤權重資料，之後會在其他分析程式上使用，所以我會另外開個工作表( 權重更新_365 )，來製作分析表格。)

[ 權重資料 – 複製/貼上 ] :

[ 權重資料 – 複製/貼上 操作步驟 ] :
1. 將上方左圖 工作表 : 大盤權重 的 A : C欄 資料選取，按滑鼠右鍵，選擇 : 複製 ( CTRL+C )。
2. 點選下方工作表，切換至 工作表 : 權重更新_365。
3. 游標移至 B1 儲存格，按滑鼠右鍵，選擇 :( 貼上 – 值 )。
4. 貼上的資料，如同上方右圖。

5. VBA – 新增模組 巨集2 ( Module2 )，巨集名稱改為 : 權重更新_Office365，Sub副程式名稱改為 : 權重更新_365，用來執行 ( 下篇 ) 的指令。

※ 權重資料 – 複製/貼上 : 程式碼，如下 —

    [ 程式碼 / 權重資料 - 複製 / 貼上 ] :
    Sheets("大盤權重").Select  '選取"大盤權重"工作表。    
    Range("A:C").Copy  '複製A~C行資料。
    Sheets("權重更新_365").Select  '切換至"權重更新_365"工作表。
    Range("B1").Select  '選取B1，準備貼上的位置。
    Selection.PasteSpecial Paste:=xlPasteValues  '貼上-值。

填補 – 成份股 / DDE指令

完成權重資料的複製，接下來，就可以設計工作表欄位，利用 VBA公式黏貼 DDE語法來連結 XQ，擷取大盤各成份股的即時資料。

首先，想好要擷取的工作表欄位資料，因為大盤成份股總共 : 935檔，資料連結越多，系統就越容易崩潰。有時會LAG，嚴重的會擷取不到資料，欄位出現 #N/A 的情況。

[ 操作步驟 ] : 如下 —
1. 確定欄位 : 資料欄位只擷取收盤價 / 漲跌 / 漲跌% / 成交量 / 均價 / 影響大盤點數 / 上漲 / 下跌。其中 漲跌% 可利用公式求得，故不使用 DDE 來擷取資料，影響大盤點數 / 上漲 / 下跌，則必須套用公式，來加總計算。

2. 設定欄位 : 於 權重更新_365 工作表欄位 : E1 ~ J1，設定欄位名稱 : 收盤價 / 漲跌 / 漲跌% / 成交量 / 均價 / 影響大盤點數 / 上漲 / 下跌。

3. 設定 VBA 程式 : 填補欄位資料 程式碼，如下 —

     [ 程式碼 / 填補欄位資料 ] :
     lastRow = Range("B1").End(xlDown).Row  '藉由 B1，往下搜尋最後一列。意思是 : 求得最後一筆資料的列數。        
     For I = 2 To lastRow  '迴圈: 2 ~ 最後一列。
         Range("E" & I) = "=XQLITE|Quote!'" & Range("B" & I) & ".TW-Price'"  '收盤價
         Range("F" & I) = "=XQLITE|Quote!'" & Range("B" & I) & ".TW-PriceChange'"  '漲跌
         Range("G" & I) = "=IFERROR(F" & I & "/E" & I & "*100,0)"  '漲跌%--公式
         Range("H" & I) = "=XQLITE|Quote!'" & Range("B" & I) & ".TW-TotalVolume'"  '成交量
         Range("I" & I) = "=XQLITE|Quote!'" & Range("B" & I) & ".TW-AvgPrice'"  '均價
         Range("J" & I) = "=IFERROR($A$13*D" & I & "/E" & I & "*F" & I & ",0)"  '影響大盤點數--公式
         Range("K" & I) = "=IF(F" & I & ">0,1,0)"  '判斷是否上漲--上漲/標示:1，下跌/標示:0，方便加總計算:上漲家數
         Range("L" & I) = "=IF(F" & I & "<0,1,0)"  '判斷是否下跌--上漲/標示:0，下跌/標示:1，方便加總計算:下跌家數
     Next

[ 填補欄位資料 語法說明 ] :
1. Range(“B1”).End(xlDown).Row : 先求得最後一筆資料的列數 lastRow，作為 For I 迴圈的 最終值。
2. For I = 2 To lastRow : 由於第一列為標頭欄位，故從 2 開始搜尋到 lastRow(最終值)。
3. 開始在 For I 迴圈內編寫 DDE 黏貼公式。

★ 工作表 E2 DDE 原指令 : =@XQLITE|Quote!’1101.TW-Price’ ==> 利用變數來取代 1101 公司代碼，並以迴圈指令，來擷取所有的公司資料。
◎ Range(“E” & I ) : 固定 E欄位置，變數 I (列數) : 會隨著迴圈從 2 ~ lastRow(最終值)。
◎ “=XQLITE|Quote!'” & Range(“B” & I ) & “.TW-Price'” : 先讀取 Range(“B” & I ) B欄儲存格的公司代碼，再利用文字組合功能，組合出 DDE 的語法公式。

[ 舉例 ] : 迴圈 I 從 2 開始 :
◎ 公式 : Range(“E” & I ) = “=XQLITE|Quote!'” & Range(“B” & I ) & “.TW-Price'”，會轉換成 : Range(“E2“) = “=XQLITE|Quote!’1101.TW-Price'”
=> 意思是 : 先讀取 Range(“B2”) 儲存格 B2 的文字資料 : 1101 (事先將B欄全設定成文字格式)，再與 DDE語法組合起來，黏貼到 E2 儲存格。
( PS. 公式 : Range(“E” & I ) / Range(“F” & I ) / Range(“H” & I ) / Range(“I” & I ) — 文字組合方式，皆相同，唯獨後面的連結參數有異。)

★ 漲跌% ( G2 ) 原計算公式 : =IFERROR(F2/E2*100,0) ==> 文字組合後，數字 2 改為變數 I，轉變成 : “=IFERROR(F” & I & “/E” & I & “*100,0)”
◎ IFERROR 指令用法 : 是個除錯指令。若執行過程中沒有發生錯誤，則顯示第一個引數資料；有錯，則顯示第二個引數資料。
◎ 第一個引數 : F2/E2*100 ==> 漲跌% 公式 : 漲跌點數 / 收盤價 * 100，求得的值，顯示在 G2 儲存格。
◎ 第二個引數 : 0 ==> 意思是 : 程式若發生錯誤，則顯示 0。主要是避免某檔股票當天停止掛牌，資料上會顯示 ” — “，若沒有 IFERROR 除錯指令，則會產生錯誤。

★ 影響大盤點數 ( J2 ) 原計算公式 :
◎ =IFERROR($A$13*D2/E2*F2,0) ==> 文字組合後，數字 2 改為變數 I，轉變成 : “=IFERROR($A$13*D” & I & “/E” & I & “*F” & I & “,0)”
( PS. 同樣引用 IFERROR 除錯指令，$A$13 代表 : 絕對位址，只抓取 A13 儲存格 — 大盤收盤點數資料。
◎ 其中 $A$13*D2/E2*F2 為個股漲跌 — 影響大盤點數的計算公式 : ==> [ 公式 ] : 個股股價漲跌 : 1點 = 大盤收盤價 × 個股權值 / 個股收盤價
( PS. 詳細解說 : 請參考 權值 vs 大盤點數 – 計算公式 )

★ 上漲 ( K2 ) / 下跌 ( L2 ) 原計算公式 :
◎ 上漲 : =IF(F2>0,1,0) ==> 意思是 : 若 F2(儲存格欄位的數值) > 0，K2(儲存格)顯示 : 1，否則為 : 0。文字組合後，數字 2 改為變數 I，轉變成 : “=IF(F” & I & “>0,1,0)”，再運用迴圈，將公式黏貼至 K欄。
◎ 下跌 : =IF(F2<0,1,0) ==> 意思是 : 若 F2(儲存格欄位的數值) < 0，L2(儲存格)顯示 : 1，否則為 : 0。文字組合後，數字 2 改為變數 I，轉變成 : “=IF(F” & I & “<0,1,0)”，再運用迴圈，將公式黏貼至 L欄。

[ 程式執行後結果 ] :

※ 上圖右邊紅框 ( E ~ F欄) 內資料，為執行結果，盤中即時數據會同步顯示。
※ 上圖左邊紫框 ( A欄 ) 內資料，是接下來，要利用公式，來計算加總的數據。

構建 – 即時加總數據

◎ 原先留下的 A欄位置，是用來放置「 加總後 」的分析數據。其作法，如下 —

[ 操作步驟 ] :
1. 製作按鈕 : ，名稱自取，巨集指定 : 權重更新_365 副程式。( PS. 按鈕作法，請參考 : 按鈕製作。)

2. 將 A5 / A7 / A9 / A12 / A15 / A18 / A21，依照上圖填寫標頭文字。

3. 注意 : A13 ( 大盤收盤點數 ) : 是固定公式。所以直接在 A13 儲存格，填寫 DDE公式 : =@XQLITE|Quote!’TSE.TW-Price’，抓取大盤收盤價的數據，即可。

4. 設定 VBA公式，對應 A6 / A10 / A16 / A19 / A22，所需要的數據公式。程式碼如下 —

    [ 程式碼 ] :
    'A欄 -- 資料總整理
    Range("A6") = Now()  '顯示更新日期
    Range("A10") = lastRow - 1 & " 家"  '顯示大盤成份股 -- 總家數
    Range("A16") = "=SUM(J2:J" & lastRow & ")"  '成份股漲跌 -- 影響大盤點數
    Range("A19") = "=SUM(K2:K" & lastRow & ")"  '加總 -- 上漲家數
    Range("A22") = "=SUM(L2:L" & lastRow & ")"  '加總 --下跌家數
        
    Range("A6").Select  '游標定位--停在A6。

[ 程式說明 ] :
1. A6 = Now() : Now() 是 VBA函數，也可以簡寫成 : Now，意思是 : 顯示目前的日期 & 時間。
2. A10 = lastRow – 1 & ” 家” : 扣掉第一個標題列，所以最後一列的列數 – 1，就等於上市公司成份股的總家數，再搭配文字組合 &，就能顯示的效果。
3. A16 = “=SUM(J2:J” & lastRow & “)” : 公式內的 SUM函數，是 EXCEL內建函數，用來做資料加總。意思是 : 將 J2 ~ J936 的資料作加總。
4. A19 和 A22 : 同第三步驟，完成上漲家數和下跌家數的資料加總。
5. Range(“A6”).Select : 指令可有可無，主要是定位游標，讓工作表畫面乾淨些而已。

完整程式碼


Sub 權重更新_365()

        ' 選取"權重更新_365"工作表
        Sheets("權重更新_365").Select
        
        ' 刪除舊資料
        totalRow = Range("B1").End(xlDown).Row  '藉由 B1，往下搜尋最後一列。意思是 : 求得最後一筆資料的列數。
        Range("E2:L" & totalRow).ClearContents  '刪除舊資料，保留原格式。

        ' 權重資料 - 複製/貼上
        Sheets("大盤權重").Select  '選取"大盤權重"工作表。
        Range("A:C").Copy  '複製A~C行資料。
        Sheets("權重更新_365").Select  '切換至"權重更新_365"工作表。
        Range("B1").Select  '選取B1，準備貼上的位置。
        Selection.PasteSpecial Paste:=xlPasteValues  '貼上-值。
        
        lastRow = Range("B1").End(xlDown).Row  '藉由 B1，往下搜尋最後一列。意思是 : 求得最後一筆資料的列數。
        
        For I = 2 To lastRow
                Range("E" & I) = "=XQLITE|Quote!'" & Range("B" & I) & ".TW-Price'"  '收盤價
                Range("F" & I) = "=XQLITE|Quote!'" & Range("B" & I) & ".TW-PriceChange'"  '漲跌
                Range("G" & I) = "=IFERROR(F" & I & "/E" & I & "*100,0)"  '漲跌%
                Range("H" & I) = "=XQLITE|Quote!'" & Range("B" & I) & ".TW-TotalVolume'"  '成交量
                Range("I" & I) = "=XQLITE|Quote!'" & Range("B" & I) & ".TW-AvgPrice'"  '均價
                Range("J" & I) = "=IFERROR($A$13*D" & I & "/E" & I & "*F" & I & ",0)"  '影響大盤點數--公式
                Range("K" & I) = "=IF(F" & I & ">0,1,0)"  '判斷是否上漲--上漲/標示 : 1，下跌/標示 : 0，方便加總計算 : 上漲家數
                Range("L" & I) = "=IF(F" & I & "<0,1,0)"  '判斷是否下跌--上漲/標示 : 0，下跌/標示 : 1，方便加總計算 : 下跌家數
        Next
        
        ' A欄 -- 資料總整理
        Range("A6") = Now()  '顯示更新日期
        Range("A10") = lastRow - 1 & " 家"  '顯示大盤成份股 -- 總家數
        Range("A16") = "=SUM(J2:J" & lastRow & ")"  '成份股漲跌 -- 影響大盤點數
        Range("A19") = "=SUM(K2:K" & lastRow & ")"  '加總 -- 上漲家數
        Range("A22") = "=SUM(L2:L" & lastRow & ")"  '加總 --下跌家數
        
        Range("A6").Select  '游標定位--停在A1

End Sub

[ 注意 ] :
為了完成自動化程序，原工作表上的資料，必須挪移、覆蓋，或是刪除，才能接收新的資料，所以程式開頭增加了幾個指令。
( PS. 因為上市公司會有新增或下市的公司，每月月底權重資料更新後，成份股總家數可能會不一樣，所以在程式開頭多了清除內容的指令。)

    [ 程式碼 / 刪除原先資料 ] :
    ' 選取"權重更新_365"工作表
    Sheets("權重更新_365").Select
        
    ' 刪除舊資料
    totalRow = Range("B1").End(xlDown).Row  '藉由 B1，往下搜尋最後一列。意思是 : 求得最後一筆資料的列數。
    Range("E2:L" & totalRow).ClearContents  '刪除舊資料，保留原格式。

[ 程式說明 ] :
1. Sheets(“權重更新_365”).Select : 意思是選取工作表(“權重更新_365”)，Sheets 是工作表指令，類似 Range 指令用法。
2. Range(“B1”).End(xlDown).Row : 從 B1 往下找尋最後一列的列數。
3. Range(“E2:L” & totalRow).ClearContents : 注意舊資料的欄位是 E欄 ~ L欄，使用 ClearContents 指令，將資料清除，但不刪除其中設定好的格式。
( PS. 因為先前資料有欄寬 / 小數點 / 對齊等設定，若使用 Clear 指令，會連同設定值一併刪除，到時又要重新設定一次。)

結論

這(下)篇 : 主要是教導大家如何運用 VBA公式，搭配 DDE語法，來擷取並加總計算出大盤即時漲跌點數。確認其方法，便能依據大盤的權重數據，再衍生出其他類型的整合分析，例如 : 台灣50 / 中型100 / 各產業股等即時股價的漲跌數據，藉由這些數據，整合分析出 50大成份股，對今日盤面的影響，或是今天領漲/領跌的是何種產業股。

講到這，下一篇就來製作台灣50 / 中型100 成份股即時數據公式。

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

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

關聯文章

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