[VBA] 製作 借券賣出排行 榜 – 分析外資的心態

何謂「 借券賣出 」

※ 借券 : 是「 借貸有價證券 」的簡稱。意思是 : 投資人藉由券商媒合，將手中股票出借給有需求的投資人的行為。
( PS. 借券與融券雖都是放空的工具，但借券不需要立即實踐賣出股票的動作。若對借券相關名詞不了解，請參考 : 借券 / 借券賣出。)

※ 借券賣出 : 股票借出後，確實在交易市場賣出的行為。

☆ 為何 借券賣出餘額 可以用來分析外資的心態 ?
→ 主要原因是 : 法人(外資)無法使用信用交易(融券放空)，促使借券成為法人放空的一種手段。因此 借券賣出餘額 的變化，就被拿來分析法人(外資)對後市看法的依據。

◎ 借券賣出餘額 增加 : 表示法人對大盤或該檔股票看法偏空、避險需求增加，後市就容易有 下跌 的風險。
◎ 借券賣出餘額 減少 : 表示法人對大盤或該檔股票看法偏多、避險需求降低，後市就容易有 上漲 的機會。

借券賣出 公布時間與資訊

[ 公布時間 ] :
台灣證交所會在 下午10:30 公布當天 大盤及個股 融券 / 借券賣出 資訊。

[ 公布資訊 ] :
◎ 信用額度總量管制餘額表 : 網址 — https://www.twse.com.tw/zh/page/trading/exchange/TWT93U.html
→ 可查詢 : 大盤及個股當日與歷史融券/借券賣出的相關資訊。

※ 已知證交所公布時間與信用額度總量管制餘額表網址，便能利用Excel VBA上網擷取證交所當日大盤及個股的融券與借券賣出的相關數據。

借券賣出排行 查詢網站

[ 借券賣出排行 ] : 參考網站 —
◎ HiStock 嗨投資 : https://histock.tw/stock/three.aspx?m=mg&s=bsu
◎ Goodinfo! 台灣股市資訊網 : https://goodinfo.tw/tw2/StockList.asp?MARKET_CAT=熱門排行&INDUSTRY_CAT=借券賣出餘額

[ 大盤借券賣出餘額變化 ] : 參考網站 —
◎ 玩股網 : https://www.wantgoo.com/stock/margin-trading/taiex-short-lending-sale-balance

借券賣出排行 資料抓取

※ 根據公布的 信用額度總量管制餘額表 資訊，便能利用 EXCEL 上網抓取，做出不同的資料分類 — 借券賣出排行 和 大盤借券賣出餘額變化。

[ 製作 / 借券賣出排行 — 前置作業 ]
0. 作業系統 : Office 365。
1. 新增一個 EXCEL 的 空白活頁簿。
2. 先行另存新檔，存檔位置 : 自行設定，檔案名稱 : 自行設定 ( 我設 : 2022_製作借券賣出排行榜 )，存檔類型(副檔名) : .xlsm ( 啟用巨集的活頁簿 )。
3. 新增工作表，名稱 : 借券賣出排行。
4. 於 下午10:30 進行當日 大盤及個股 融券 / 借券賣出 的資料抓取。

證交所 – 網路爬蟲資料抓取

◎ 借券賣出排行 : 資料來源 — 信用額度總量管制餘額表 網址 : https://www.twse.com.tw/zh/page/trading/exchange/TWT93U.html，畫面圖示如下 :
借券賣出排行

[ 操作步驟 ] :
0. 資訊標題 : 信用額度總量管制餘額表。
1 ~ 2. 這二步驟，可省略不做。因為要製作的是 : 當日 借券賣出排行，所以資料日期無須變動與查詢。
3. 將原設定 : 顯示 / 每頁 10 筆，改為 : 顯示 / 全部。
4. 滑鼠左鍵，點選 : 列印 / HTML，會自動開啟 新分頁，取得當日 信用額度總量管制餘額表 的資料網址 : https://www.twse.com.tw/zh/exchangeReport/TWT93U，方便以VBA程式來抓取資料。( PS. 頁面開啟的畫面，如下。)
借券賣出排行

[ 注意 ] :
◎ 在我幾經測試的情況下發現，無論是利用 365版 或 舊版 的資料下載方式，均無法與前幾篇文章的操作方式一樣，順利從WEB將當日 信用額度總量管制餘額表 的交易資料下載完成。

[ 原因 ] :
◎ 證交所已取消以文本文件方式呈現，故無法再以 QueryTable (從WEB) 的方式操作。只好改用 XMLHTTP 與 操控 IE 的方式處理。

[ 解決方法 ] :
◎ CSV下載 : 利用 CSV下載 模式，將當日 信用額度總量管制餘額表 的交易數據下載完成，再藉由VBA程式，依照股票借券賣出的數量多寡，排序列表。( PS. 此方法可行。但仍屬 : 半自動模式，必須先手動完成 CSV下載動作，才能再利用VBA來完成排序，不合乎需求。)

◎ 網路爬蟲 : 為求自動完成每日更新 信用額度總量管制餘額表 的解決方法。
( PS. 由於網路爬蟲技巧，有些難度，也與教學方向不符，故不多加著墨，只會概略講解程式用法，讓程式自動完成借券賣出的排序動作。)

網路爬蟲 – XMLHTTP 物件設定與運用

建構 借券賣出排行 表頭

A. 建構 信用額度總量管制餘額表 所需的表頭資料。表頭設計如下 : ( PS. 資料欄位從 A4欄 開始列表。A1欄位(日期)，設定日期函式 : =NOW()。)

[ 注意 ] : A4欄以下所有A欄(Column)儲存格格式，均設定為 : 文字。( PS. 文字格式 — 才能顯示股票代號 : 0050 前頭 00 的數字。)

新增巨集模組

B. 新增巨集模組，並將 Module1名稱 改為 : 模組_借券賣出排行，Sub副程式檔名改為 : 借券賣出排行。( PS. 不了解如何新增，請參考 : 開啟 VBA 程式 – 插入模組。)

XMLHTTP 物件設定與運用

C. 這次無法使用 從WEB 匯入資料的方式處理，所以改用 : XMLHTTP 物件功能，抓取外部資料，匯入EXCEL。

[ 程式碼 / XMLHTTP 物件設定 ] :
Sub 借券賣出排行()
   
    Dim webXML As Object
    Set webXML = CreateObject("Microsoft.XMLHTTP")  '設定 XMLHTTP 物件
    
    Dim webHTML As Object
    Set webHTML = CreateObject("HTMLFile")  '設定 HTMLFile 物件
    Set Window = webHTML.parentWindow  '設定 Window 物件
    
    With webXML    
        .Open "GET", "https://www.twse.com.tw/zh/exchangeReport/TWT93U", False  '透過 GET - 取得 證交所 / 信用額度總量管制餘額表 網頁原始碼
        .send  '傳送
        
        Window.execScript "var xText=" & .responseText  '回傳JSON資料內容
        colLength = Window.eval("xText.fields.length")  '取得資料的欄位(Column)長度
        rowLength = Window.eval("xText.data.length")  '取得資料的列位(Row)長度
        
        For xRow = 0 To rowLength - 1
                For xCol = 0 To colLength - 1
                    Cells(xRow + 4, xCol + 1) = Window.eval("xText.data[" & xRow & "][" & xCol & "]")  '整理對應的資料列表
                Next
        Next        
    End With
    
    Set webXML = Nothing  '清除設定
    Set webHTML = Nothing
    Set Window = Nothing

End Sub

[ 程式解說 ] :
1. Dim webXML As Object : 定義 webXML 為 物件。
2. Set webXML = CreateObject(“Microsoft.XMLHTTP”) : 設定 webXML 為創建的 XMLHTTP 物件。
3. Dim webHTML As Object : 定義 webHTML 為 物件。
4. Set webHTML = CreateObject(“HTMLFile”) : 設定 webHTML 為創建的 HTMLFile 物件。
5. Set Window = webHTML.parentWindow : 引用 HTMLFile物件，再透過 parentWindow屬性，設定 Window 為 Window 物件。
6. With … End With : 針對 webXML 進行結構化的陳述式。
7. .Open “GET”, “https://www.twse.com.tw/zh/exchangeReport/TWT93U”, False : 透過 GET 數據傳送方式，同步(False)取得證交所 : https://www.twse.com.tw/zh/exchangeReport/TWT93U 的網頁原始碼。
8. .send : 同步(False)方式進行下，接收端要等到對方伺服器返回確認訊息後，才結束Send傳送指令。
9. Window.execScript “var xText=” & .responseText : 運用 .responseText 參數，將接收到的JSON格式訊息，宣告給 xText 變數，再透過execScript執行，傳送到 Window。
10. colLength = Window.eval(“xText.fields.length”) : 取得資料的欄位(Column)長度，設定給 : colLength。
11. rowLength = Window.eval(“xText.data.length”) : 取得資料的列位(Row)長度，設定給 : rowLength。
12. 利用 For ~ Next 巢狀迴圈，將 rowLength 與 colLength (列/行)的串列資料，全部對應出來。
13. Cells(xRow + 4, xCol + 1) = Window.eval(“xText.data[” & xRow & “][” & xCol & “]”) : 利用 eval 函數將 data 內的串列資料，對應到表格的各個位址。( PS. 從工作表 A4欄 開始。)
14. Set webXML = Nothing : 將 webXML 設定清除。

[ 信用額度總量管制餘額表 下載資料列表 ] :
借券賣出排行

借券賣出排行 – 大小排列

D. 從證交所下載匯入的資料，是以 股票代碼 做排序列表，但我們需要的是 當日賣出(J欄)，以大到小排列，故需要再利用VBA語法，來做排序動作。

[ 程式碼 / 當日賣出 - 由大到小排列 ]
lastRow = [J65536].End(xlUp).Offset(-1).Row
Range("A4:O" & lastRow).Sort Key1:=Range("J4"), Order1:=xlDescending, Header:=xlNo  '降冪排序

[ 程式解說 ] :
1. lastRow = [J65536].End(xlUp).Offset(-1).Row : 搜尋到有資料的最後位置(1112)，但必須再往上(Offset(-1))一列，扣除合計列，才是股票資料的最後一列(1111)。
2. Range(“A4:O” & lastRow).Sort Key1:=Range(“J4”), Order1:=xlDescending, Header:=xlNo : 針對資料列表的範圍，作排序(Sort)功能。
◎ Range(“A4:O” & lastRow) : 設定排序範圍 : A4 ~ O1111。
◎ Key1:=Range(“J4”) : 由哪(J4欄)開始作排序。
◎ Order1:=xlDescending : 排序方式 : 以大到小排列。( PS. 參數 : xlAscending(升冪排列) / xlDescending(降冪排列)。)
◎ Header:=xlNo : J4欄是資料，不是表頭。( PS. 參數 : xlYes(有表頭) / xlNo(無表頭)。)

[ 當日賣出 – 由大到小排列 資料列表 ] :
借券賣出排行

刪除舊資料列表

E. 刪除舊資料列表 : 若不刪除舊資料，有可能會因為新資料的列數，與舊資料不符，而產生資料列表的錯誤。( PS. 刪除指令，必須放在連線前。)

[ 程式碼 / 刪除舊資料列表 ] 
Sheets("借券賣出排行").Select 
Range("A4:O1200").ClearContents

刪除所有連線

F. 刪除所有連線 : 若不刪除連線，會因為舊連線存在的關係，而影響當日資料無法更新。

[ 程式碼 / 刪除所有連線 ]
Do While ThisWorkbook.Connections.Count > 0
       ThisWorkbook.Connections.Item(ThisWorkbook.Connections.Count).Delete
Loop

修改表頭標題 – 增加日期顯示

G. 修改表頭標題 : 為符合證交所的標題顯示，配合與原標題作文字組合，增加日期顯示。

[ 程式碼 / 修改表頭標題 - 增加日期顯示 ]
xToday = Format([A1], "yyyy/mm/dd")
[C1] = Left(xToday, 4) & "年" & Mid(xToday, 6, 2) & "月" & Right(xToday, 2) & "日 " & Right([C1], 11)

[ 程式解說 ] :
◎ 參考下圖，將 信用額度總量管制餘額表(11個字元)標題前，增加當日更新日期。
1. xToday = Format([A1], “yyyy/mm/dd”) : 將當日日期以 yyyy/mm/dd 格式顯示，方便定位抓取時間字元。
2. [C1] = Left(xToday, 4) & “年” & Mid(xToday, 6, 2) & “月” & Right(xToday, 2) & “日 ” & Right([C1], 11) : 利用文字組合，將原標題增加日期顯示。
( PS. 要注意的是程式後段的 Right([C1], 11)，因為每天的日期會變，但後段不變；所以固定後面11個字元，再加上前面的日期變化，即可。)
借券賣出排行

製作 – 資料更新 按鈕

※ 完成 借券賣出排行 的製作程序，為了方便執行，可在工作表內製作按鈕，直接在 下午10:30 做 資料更新 的動作。

[ 資料更新 – 按鈕製作 ] :
1. 按鈕製作方法 : 請參考 — VBA 按鈕製作。


2. 指定巨集 : 游標移動到 資料更新 按鈕上，按 : 滑鼠右鍵，點選 : 指定巨集。

3. 確定巨集名稱 : 出現 指定巨集 視窗後，選取 : 借券賣出排行 巨集名稱 。

完整程式碼 / 借券賣出排行

Sub 借券賣出排行()

    Sheets("借券賣出排行").Select
    Range("A4:O1200").ClearContents  '刪除舊資料

    Dim webXML As Object
    Set webXML = CreateObject("Microsoft.XMLHTTP")  '設定 XMLHTTP 物件
    
    Dim webHTML As Object
    Set webHTML = CreateObject("HTMLFile")  '設定 HTMLFile 物件
    Set Window = webHTML.parentWindow  '設定 Window 物件
    
    With webXML
    
        .Open "GET", "https://www.twse.com.tw/zh/exchangeReport/TWT93U", False  '透過 GET - 取得 證交所 / 信用額度總量管制餘額表 網頁原始碼
        .send  '傳送
        
        Window.execScript "var xText=" & .responseText '& ";"  '回傳JSON資料內容
        colLength = Window.eval("xText.fields.length")  '取得資料的欄位(Column)長度
        rowLength = Window.eval("xText.data.length")  '取得資料的列位(Row)長度
        
        For xRow = 0 To rowLength - 1
            For xCol = 0 To colLength - 1
                Cells(xRow + 4, xCol + 1) = Window.eval("xText.data[" & xRow & "][" & xCol & "]")
            Next
        Next
        
    End With
    
    xToday = Format([A1], "yyyy/mm/dd")
    [C1] = Left(xToday, 4) & "年" & Mid(xToday, 6, 2) & "月" & Right(xToday, 2) & "日 " & Right([C1], 11)
    
    lastRow = [J65536].End(xlUp).Offset(-1).Row
    Range("A4:O" & lastRow).Sort Key1:=Range("J4"), Order1:=xlDescending, Header:=xlNo  '降冪排序
    
    Do While ThisWorkbook.Connections.Count > 0  '刪除連線
           ThisWorkbook.Connections.Item(ThisWorkbook.Connections.Count).Delete
    Loop
    
    Set webXML = Nothing  '清除設定
    Set webHTML = Nothing
    Set Window = Nothing

End Sub

結論

製作 借券賣出排行 的目的 : 是想藉由排行來分析目前外資看空的標的物有哪些。由於法人(外資)無法使用信用交易(融券放空)，促使借券成為法人放空的一種手段。因此個股的 借券賣出餘額 變化，就被拿來分析法人(外資)對後市看法的依據。

但在製作 借券賣出排行 過程中，幾經測試發現，無論是利用 365版 或 舊版 的資料下載模式，均無法以 從WEB 匯入資料的方式處理，故採用 : XMLHTTP 物件抓取功能，擷取當日證交所 信用額度總量管制餘額表 的交易原始碼，匯入EXCEL，作資料整理列表，達成當日借券賣出的排序動作。

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

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

關 聯 文 章

1. 製作借券賣出排行榜 – 分析外資的心態

  • 製作借券賣出排行 的目的 : 是想藉由排行來分析目前外資看空的標的物有哪些。但幾經測試，均無法從WEB匯入資料處理，故採用 XMLHTTP 物件抓取功能，擷取當日信用額度總量管制餘額表的交易原始碼，匯入EXCEL，作資料整理並列表，達成當日借券賣出的排序動作。

2. 3分鐘教會你 – 抓取 大盤借券賣出餘額 歷史資料

  • 大盤借券賣出餘額 是針對大盤所有個股的借券賣出餘額，所作的統計資料。證交所將其存放於網頁底層，並提供 CSV下載；若想完成歷史資料的建置，只要將想分析的CSV檔載入電腦，再利用VBA程式，依日期順序將其呼叫/匯入/轉換格式並存檔，便能達成建置的目的。

3. 製作大盤與借券賣出餘額關係圖

  • 利用圖表展現 大盤借券賣出餘額與加權指數關係 ，可以更清晰看出外資對後市的看法。基於此，本篇文章則採用圖文對照的方式，手把手教會你如何利用VBA圖表的語法–新增圖表/訂定圖表大小/建立數列/變更圖表類型/調整數列位置 等功能，來達成自動完成圖表製作的目的。

V B A 資 源

EXCEL & VBA功能 – 綜合筆記
  • EXCEL & VBA功能 筆記 : 將針對文章中所使用的 EXCEL 及 VBA公式 / 工具操作 / 巨集設定 / 控制項的運用，做個整合記錄，方便大家在學習當下的參考與使用。
VBA指令 – 彙整筆記
  • VBA 提供相當多的指令用法，VBA指令 彙整筆記，將會陸續記錄 VBA文章 中，所用過的 VBA指令，方便學員們對照運用。
VBA程式語法 – 彙整筆記
  • 語法就是程式的文法。 使用者要與電腦溝通，就必須遵守這些規則。VBA程式語法 筆記 : 將針對 VBA文章 中所使用的 VBA程式語法，做個整合記錄，方便大家在學習當下參考與使用。

相關文章