[VBA] 抓取加權指數歷史資料 - NewBie168 投資研究院 %

[VBA] 抓取加權指數歷史資料

加權指數歷史資料來源

加權指數歷史資料 是證交所每日 下午三點前 會公布的當日交易記錄。同時，證交所網站 也提供下載連結點，方便大家下載 / 抓取兩組大盤當日交易的相關資料。

A. 發行量加權股價指數歷史資料 — 網址 : https://www.twse.com.tw/zh/page/trading/indices/MI_5MINS_HIST.html。( PS. 可查詢 : 大盤當日的開 / 高 / 低 / 收等交易記錄。)
B. 市場成交資訊 — 網址 : https://www.twse.com.tw/zh/page/trading/exchange/FMTQIK.html。( PS. 可查詢 : 大盤當日的統計資訊。)

◎ 已知證交所公布時間與大盤資訊下載網址，便能利用Excel VBA上網擷取證交所當日提供的大盤交易資訊，以及歷年來大盤的歷史交易記錄。

加權指數歷史資料抓取

[ 前置步驟 ] :
0. 作業系統 : Office 365。
1. 打開已設計好的程式檔 : 2022_三大法人現股買賣超.xlsm。
2. 新增工作表，名稱 : 抓取_大盤歷史資料。

證交所 – 抓取大盤歷史資料

◎ 市場成交資訊 : 資料來源網址 —- https://www.twse.com.tw/zh/page/trading/exchange/FMTQIK.html，畫面圖示如下 :

[ 操作步驟 ] :
0. 交易資訊標題 : 發行量加權股價指數歷史資料。
1. 選擇下載年月 : 利用下拉式選單，選擇 : 年 / 月份的歷史報表。( PS. 這組加權指數歷史資料，只會顯示月份記錄。)
2. 確定下載月份後，點選 : ，系統會依照選擇的年 / 月份選項，顯示該月份的報表網址。
3. 點選 : 列印 / HTML，按 : 滑鼠右鍵，選擇 : 在新分頁中開始連結，或直接選擇 : 複製連結網址，取得所選月份的資料網址 :
https://www.twse.com.tw/exchangeReport/FMTQIK?response=html&date=20221206，方便以VBA程式來抓取資料。
※ 參考畫面，如下 :

4. 根據 加權指數歷史資料( 等同 : 市場交易資訊 ) 網址，便能利用 錄製巨集 功能，錄製 — 登入證交所網站抓取大盤該月份歷史資料的操作步驟，並將其轉化成VBA程式指令，載入EXCEL。

錄製巨集與資料格式轉換

[ 注意 ] : 在錄製巨集這部分，因為有學員反應，可否提供舊版(Office 365以下版本)的錄製方式，那這篇就改用舊版的模式，來抓取證交所的資料。

[ 操作步驟 ] :
A. 利用 錄製巨集(舊版)，抓取大盤市場成交資訊的某年/月數據。( ◎ 請參考 : 錄製巨集(舊版) 內的錄製步驟。)

◎ 在錄製過程中，須注意的三點 :
一. 打開活頁簿，名稱 : 2022_三大法人現股買賣超.xlsm。
二. 新增工作表，名稱 : 市場成交資訊。用來存放證交所當月或某歷史月份的連結資料。
三. 從 WEB (舊版) 抓取資料時， URL 網址輸入框內，需輸入 :
https://www.twse.com.tw/exchangeReport/FMTQIK?response=html&date=20221206

B. 證交所擷取出來的表格資料，顯示如下 :

( PS. 若你實作過 每天收集三大法人現貨買賣超 < 整體數據篇 >，使用 Office 365版下載過資料，就會發現下載的資料有所不同，位置也差了一格。)

[ 注意 ] : 下載大盤歷史資料後，你會發現它是直接以月份，來作資料下載，跟前一篇 : 外資買賣超歷史資料，採用一天的方式，有所不同；既然是以月份來下載，就無需再新增一個暫存工作表，來一天天整合歷史資料。

C. 再新增一個工作表，名稱 : 大盤歷史資料。用來建置存放所有年月份的大盤歷史記錄。

D. 在 大盤歷史資料 工作表內，填入 : 大盤市場成交資訊 表頭。表頭資料顯示如下 : ( * A1欄位(日期)，設定日期函式 : =NOW()。)

E. 市場成交資料下載後，如下方(下圖)，資料稍嫌複雜，將之修改簡化，轉換成下方(上圖)，會讓表格更乾淨，方便對照。

[ 資料格式轉換設定 ] :
1. 西元年 : 將 中國年 轉換成 西元年。
2. 成交張數(萬) : 將 成交股數 ÷ 1000 轉換成張數，再 ÷ 10000，轉換單位成 : 萬張。
3. 成交金額(億元) : 將 成交金額 ÷ 100000000 轉換單位成 : 億元。
4. 成交筆數(萬筆) : 將 成交筆數 ÷ 10000 轉換單位成 : 萬筆。
5. 加權指數 : 將 發行量加權股價指數 直接資料轉換。
6. 漲跌點數 : 同上，直接資料轉換。

轉化VBA — 操作步驟

錄製巨集

A. 首先利用 錄製巨集，獲取 2022/12/06 證交所網站 大盤市場成交資訊之程式碼。程式如下 :

[ 程式碼 / 錄製巨集 ] :
Sub 巨集1()
Application.CutCopyMode = False
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;https://www.twse.com.tw/exchangeReport/FMTQIK?response=html&date=20221206", Destination:=Range("$A$1"))
    '.CommandType = 0
    .Name = "FMTQIK?response=html&date=20221206"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    .Delete  ' 刪除連線
End With
End Sub

[ 注意 ] :
◎ 使用舊版爬取出來的資料，讀取後，會在執行 .CommandType = 0 時，出現錯誤訊號。此時，只要將其註解( ‘ ) 掉，就沒事了。
◎ 直接在屬性列表上，設定 .Delete 指令，就能將其斷線，無需再作刪除連線的判別式。

更改巨集名稱

B. 更改巨集名稱 : 巨集錄製好，會自動產生名稱 : 巨集1 的副程式，為了辨識程式內容，模組名稱改為 : 抓取_大盤歷史資料。( ※Sub巨集1() 改為 Sub抓取_大盤_歷史資料() )。( PS. 不了解如何更改，請參考 : 開啟 VBA 程式 – 插入模組。)

設定抓取資料的月份變數

C. 設定月份變數 : 錄製的巨集是以某月的資料下載模式運行，月份是固定不變的。若想要下載不同月份，最便捷的方式就是將其月份(20221206)，改成 : 變數(xDtae)，之後只要更改變數值，便可下載其它月份資料。示意圖如下 :

[ 轉換方式 ] :

◎ 轉換成變數(xDate)後，只要在巨集前設置一條指令( xDate = “20221206” )，之後變更其內容值，就可以下載想下載的月份資料。

        [ 程式碼 / 設定月份變數 ]
        xDate = "20221206"  ' 也可以只打 "202212" ( PS. 因為系統只抓取年月資料。)
        
        Application.CutCopyMode = False
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;https://www.twse.com.tw/exchangeReport/FMTQIK?response=html&date=" & xDate, Destination:=Range("$A$1"))

資料格式轉化VBA程式碼

D. 依照 錄製巨集與資料格式轉換(步驟E) 的轉換方式，完成VBA程式碼的轉化動作。其轉化程式可運用在 建置大盤歷史記錄 與 大盤當日收盤資料累計。

建置大盤歷史記錄

※ 建置大盤歷史記錄 : 將 市場成交資訊 轉換資料規格後，載入至 大盤歷史資料 工作表中儲存成歷史記錄。

[ 示意圖 ] :

[ 提醒 ] : 大盤市場成交資料的歷史資料，皆可利用此步驟，將欲下載的所有月份，全部儲存至大盤歷史資料工作表中。

★ 請注意 : 下載歷史資料的起始日期，一定要與 外資買賣超歷史資料 相同；否則會導致 製作關係圖表 的時候，出現數據不正確的現象。
◎ 同時，也必須從起始月份開始遞增月份下載，才能完成大盤歷史資料的建置動作。

        [ 程式碼 / 建置大盤歷史記錄 ] :
        Sheets("市場成交資訊").Select
        xRow = Range("A65536").End(xlUp).Row
        twseRow = Sheets("大盤歷史資料").Range("A65536").End(xlUp).Offset(1).Row

        For I = 3 To xRow
            chineseDay = Range("A" & I)  ' 更改 : 中國年 --> 西元年
            ADDay = CStr(Int(Left(chineseDay, 3)) + 1911) & Right(chineseDay, 6)
            Sheets("大盤歷史資料").Range("A" & twseRow) = ADDay
            Sheets("大盤歷史資料").Range("B" & twseRow) = Range("B" & I) / 10000000  ' 成交股數
            Sheets("大盤歷史資料").Range("C" & twseRow) = Range("C" & I) / 100000000  ' 成交金額
            Sheets("大盤歷史資料").Range("D" & twseRow) = Range("D" & I) / 10000  ' 成交筆數
            Sheets("大盤歷史資料").Range("E" & twseRow) = Range("E" & I)
            Sheets("大盤歷史資料").Range("F" & twseRow) = Range("F" & I)
            twseRow = twseRow + 1
        Next

[ 程式解說 ] :
1. xRow = Range(“A65536”).End(xlUp).Row : 找出 市場成交資訊 工作表內的最後一列號，替代給 xRow。
2. twseRow = Sheets(“大盤歷史資料”).Range(“A65536”).End(xlUp).Offset(1).Row : 找出 大盤歷史資料 工作表最新空白列。( PS. 存放歷史資料的起始列。)
3. For I = 3 To xRow : 利用 For ~ Next 遞增迴圈，從 市場成交資訊 工作表的第3列開始，執行到 xRow(最後一列)。
4. chineseDay = Range(“A” & xRow) : 利用 chineseDay 變數，來接收 A欄的中國日( 111/12/01 )資料。
5. ADDay = CStr(Int(Left(chineseDay, 3)) + 1911) & Right(chineseDay, 6) : 先解 Int(Left(chineseDay, 3)) : 意思是選取 chineseDay(111/12/01)左邊3個字元(111)，將其轉換成 Int(數值)，再加上 1911，就等於 西元年。但要注意 : 因為要與後面文字相連，所以要再將其轉換(CStr)為 : 文字型態，再利用 & 黏回後面6個字元(/12/01)，就完成轉換(2022/12/01)的動作。
6. 對應的程式碼，請參考 : 資料格式轉換設定 的解說。
7. twseRow = twseRow + 1 : 執行完，迴圈內的設定動作之後，大盤歷史資料 工作表的列(twseRow)，也隨之 +1。

刪除連線工作表舊資料

E. 刪除連線工作表舊資料 : 上次連線的舊資料，若不刪除，會導致再連線抓取時，產生覆蓋資料的錯誤。( PS. 刪除指令，必須放在連線前。)

    [ 程式碼 / 刪除連線工作表舊資料 ]
    Sheets("市場成交資訊").Select
    Range("A:F").ClearContents

當日大盤市場成交資料更新

※ 當日大盤市場成交資料更新 : 大盤歷史資料全部下載建置完成。就只剩下，每天下午三點前的大盤市場成交新資料的載入。

[ 示意圖 ] :

◎ 大盤市場成交資料 的 新資料(111/12/07) 於下午三點前更新下載完成，同樣是先將新資料格式轉換，再利用對應的方式，轉貼於 大盤歷史資料 工作表內，作歷史資料累計儲存。

        [ 程式碼 / 大盤市場成交資料 ] :
        xRow = Range("A65536").End(xlUp).Row
        twseRow = Sheets("大盤歷史資料").Range("A65536").End(xlUp).Offset(1).Row

        chineseDay = Range("A" & xRow)  ' 更改 : 中國年 --> 西元年
        ADDay = CStr(Int(Left(chineseDay, 3)) + 1911) & Right(chineseDay, 6)
        Sheets("大盤歷史資料").Range("A" & twseRow) = ADDay
        Sheets("大盤歷史資料").Range("B" & twseRow) = Range("B" & xRow) / 10000000  ' 成交股數
        Sheets("大盤歷史資料").Range("C" & twseRow) = Range("C" & xRow) / 100000000  ' 成交金額
        Sheets("大盤歷史資料").Range("D" & twseRow) = Range("D" & xRow) / 10000  ' 成交筆數
        Sheets("大盤歷史資料").Range("E" & twseRow) = Range("E" & xRow)
        Sheets("大盤歷史資料").Range("F" & twseRow) = Range("F" & xRow)

[ 程式解說 ] :
◎ 大盤市場成交資料 的程式碼雷同於 建置大盤歷史記錄，只要將大盤歷史資料下載完成，便可將 建置大盤歷史記錄 程式碼修改成上方的語法樣式；若不想修改，就新增一個 SUB(副程式) 或 巨集模組，來存放 大盤市場成交資料 程式碼，即可。

完整程式碼 : 抓取大盤歷史資料

Sub 抓取_大盤_歷史資料()

        Sheets("市場成交資訊").Select
        Range("A:F").ClearContents

        xDate = "20221206"
        
        ' 抓取_證交所_大盤指數
        Application.CutCopyMode = False
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;https://www.twse.com.tw/exchangeReport/FMTQIK?response=html&date=" & xDate, Destination:=Range("$A$1"))
           '.CommandType = 0
            .Name = "FMTQIK?response=html&date=" & xDate
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
            .Delete
        End With
        
        xRow = Range("A65536").End(xlUp).Row
        twseRow = Sheets("大盤歷史資料").Range("A65536").End(xlUp).Offset(1).Row

        For I = 3 To xRow
            chineseDay = Range("A" & I)  ' 更改 : 中國年 --> 西元年
            ADDay = CStr(Int(Left(chineseDay, 3)) + 1911) & Right(chineseDay, 6)
            Sheets("大盤歷史資料").Range("A" & twseRow) = ADDay
            Sheets("大盤歷史資料").Range("B" & twseRow) = Range("B" & I) / 10000000  ' 成交股數
            Sheets("大盤歷史資料").Range("C" & twseRow) = Range("C" & I) / 100000000  ' 成交金額
            Sheets("大盤歷史資料").Range("D" & twseRow) = Range("D" & I) / 10000  ' 成交筆數
            Sheets("大盤歷史資料").Range("E" & twseRow) = Range("E" & I)
            Sheets("大盤歷史資料").Range("F" & twseRow) = Range("F" & I)
            twseRow = twseRow + 1
        Next

End Sub

結論

加權指數歷史資料 是證交所每日 下午三點前 會公布的當日交易記錄。同時，證交所網站 也提供下載連結點，方便大家下載 : 發行量加權股價指數歷史資料 及 市場成交資訊，擷取大盤當日或歷史交易資訊，再藉由VBA程式，將其格式轉換/整理，建置成完整的大盤歷史資料，提供給外資賣賣超與大盤歷史資料關係圖一文，做為數列數據源，再與外資賣賣超歷史資料搭配，製作出相對應的分析圖表。

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

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

關聯文章

1. 每天收集三大法人現貨買賣超 < 整體數據篇 >

講述如何利用 EXCEL 上網抓取三大法人當日的整體買賣投資金額，並設計表格，運用 VBA程式設計，將各數據加以整理、計算，逐日記錄三大法人買賣動態，以掌握進出籌碼，評估未來的大盤走勢。

2. 3分鐘教會你 – 製作三大法人個股買賣超排行榜

講述如何利用 EXCEL 上網抓取三大法人當日的個股分項買賣金額，再運用 VBA程式，將法人買賣超數據加以整理/排序，分類製作出買/賣超前20名股票排行榜。

3. 輕鬆教會你 – 製作外資買賣超與加權指數關係圖

由於外資買賣超對加權指數有著相對的影響性，若是利用關係圖表，能更清晰看出差異性，凸顯重點。基於此，本篇文章則採用圖文對照的方式，手把手教會你如何利用VBA圖表的語法設定 — 新增圖表 / 訂定圖表大小 / 建立數列 / 變更圖表類型 / 變更數列範圍 / 調整數列位置等功能，來達成自動完成圖表製作的目的。

3-1. 輕鬆幾步驟 – 抓取外資買賣超歷史資料

外資買賣超歷史資料可透過證交所網站提供的三大法人買賣金額統計表歷史資料下載機制，下載需要的年月日資料，再藉由VBA程式，將其整理/轉貼，建置成完整的歷史資料，提供給外資賣賣超與大盤歷史資料關係圖一文，做為數列數據源，再與大盤歷史資料搭配，製作出相對應的分析圖表。

3-2. 輕鬆幾步驟 – 抓取加權指數歷史資料

加權指數歷史資料是證交所每日下午三點前會公布的當日交易記錄。同時，證交所網站也提供下載連結點，方便大家下載 : 發行量加權股價指數歷史資料 及 市場成交資訊，擷取大盤當日或歷史交易資訊，再藉由VBA程式，將其格式轉換/整理，建置成完整的大盤歷史資料，提供給外資賣賣超與大盤歷史資料關係圖一文，做為數列數據源，再與外資賣賣超歷史資料搭配，製作出相對應的分析圖表。

V B A 資源

EXCEL & VBA功能 – 綜合筆記

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

VBA指令 – 彙整筆記

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

VBA程式語法 – 彙整筆記

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

加權指數歷史資料 來源

加權指數歷史資料 抓取

證交所 – 抓取 大盤歷史資料