借券賣出餘額與加權指數關係

[VBA] 製作 大盤借券賣出餘額與加權指數關係 圖 – 分析外資的心態

大盤借券賣出餘額與加權指數關係 有何重要性?

◎ 首先，必須先了解大盤借券賣出餘額為何與加權指數漲跌 有著密切的關係?
根據之前 三大企業的特性說明 —外資挾帶大量的海外資金來台進行買賣投資，其標的大多以大型權重股為主，自然對加權指數的漲跌，有著相當大的影響。再者，因法人(外資)無法使用信用交易(融券放空)，促使借券成為法人放空的一種手段。因此 大盤借券賣出餘額 的變化，就被拿來分析法人(外資)對後市看法的依據。

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

如何製作 大盤借券賣出餘額與加權指數關係 圖

※ 首先，參考下圖 : 大盤借券賣出餘額與加權指數關係 圖表 – 完成圖。
借券賣出餘額與加權指數關係

[ 注意事項 ] :
◎ 若要完成上圖的圖表展現，需要先收集 大盤借券賣出餘額 及 加權指數 的歷史資料。至於如何利用 VBA程式 來抓取，我還是用兩篇文章來分別解說，比較清楚明白，不會雜亂冗長，文章連結如下 :

[ 歷史資料抓取 – 番外篇 ] :
第 1 篇 : 抓取大盤借券賣出餘額歷史資料
第 2 篇 : 抓取加權指數歷史資料

[ 注意 ] : 本篇文章 — 必須有以上兩篇文章的歷史資料，才能完成本篇的圖表製作，謹記 !

◎ 將 大盤借券賣出餘額 及 加權指數 的歷史資料，抓取完成後，便能開始製作 大盤借券賣出餘額與加權指數關係 圖表。

組合圖 — 圖表製作

[ 操作步驟 ] :
0. 開啟檔案 : 打開 2022_製作借券賣出排行榜.xlsm。( PS. 若設計過借券賣出排行版，就開啟此檔執行；若沒有，則開啟新檔，儲存成 .xlsm(巨集檔)，即可執行。

1. 插入圖表 : 在 功能區 點選 : 插入，會出現下方工具列，點擊 : ( 平面折線圖 )，此時會出現選項，再點選 : ( 折線圖 )，便自動會在儲存格位置，插入一個空白圖表。畫面如下 —-


2. 選取資料 : 空白圖表出現後，需要給予 折線圖 的相關資料數據源，此時點選 : 空白圖表，按 : 滑鼠右鍵，選擇 : 選取資料。


3. 新增圖例項目 : 出現 選取資料來源 視窗(下左)，點選 : 新增 圖例項目，會出現 編輯數列 視窗(下右)。


4. 載入資料源 : 在 編輯數列 視窗裡，有 數列名稱 及 數列值 兩個輸入框。

◎ 數列名稱 : 指的是圖表中的資料源名稱。可以直接定義名稱，參考下圖(右)，直接填入 : 大盤借券賣出餘額，在圖表中的圖例項目，便會出現 大盤借券賣出餘額 的數列名稱。( PS. 也可以直接填入 儲存格標題位址，如 : =大盤借券賣出歷史資料!$H$2，取得 : 借 券 賣 出 的數列名稱。)


◎ 數列值 : 填入欲展示的大盤借券賣出餘額資料範圍。點選 : 數列值輸入框，將工作表切換到 : 大盤借券賣出歷史資料，游標從 B4欄，拖曳到 B280欄位。
( PS. 也可直接鍵入 : =大盤借券賣出歷史資料!$L$4:$L$280 )。

[ 注意 ] : 目前的300列數，足夠展現所有資料，但未來歷史資料越多，列數就必須加以修改，才不會產生錯誤。

[ 新增一數列 : 加權指數 ] :
※ 完成 大盤借券賣出餘額 歷史資料擷取。同步驟 3 ~ 4，再新增一筆 加權指數 歷史資料進來。

◎ 數列名稱 : 點選 : 數列名稱輸入框，填入 : 加權指數。( PS. 同樣也可直接鍵入 : =大盤歷史資料!$E$3，取得 : 加權指數 的數列名稱。)
◎ 數列值 : 點選 : 數列值輸入框，將工作表切換到 : 大盤歷史資料，游標從 E4欄，拖曳到 E280欄位( PS. 也可直接鍵入 : =大盤歷史資料!$E$4:$E$280 )。

※ 圖例項目 — 輸入完成，數列欄中多了 大盤借券賣出餘額 與 加權指數 二數列，但還少個 水平坐標軸 的資料編輯，未更改前的預設資料是以 遞增數值 來表示，我們就將其改成 : 擷取日期的資料，來滿足二數列的對應。( PS. 為求畫面乾淨，也可以選擇 — 不輸入。)


◎ 水平座標值 : 點選 : 水平坐標軸編輯鈕，會出現 編輯輸入框，將工作表切換到 : 大盤歷史資料，游標從 A4欄，拖曳到 A280欄位( PS. 也可直接鍵入 : =大盤歷史資料!$A$4:$A$280 )。


[ 擷取完成圖 ] : 內含 大盤借券賣出餘額(藍線) / 加權指數(桔線) 及下方水平坐標軸的日期。

美化 大盤借券賣出餘額與加權指數關係 圖表

[ 操作步驟 ] :
1. 選擇圖表樣式: 必須先點擊 :圖表，才會在功能區出現 : 圖表設計 的選項，會出現下方工具列，點擊 : (圖表式樣)，原圖表變化如下 —- ( PS. 為了方便觀看，拉大了尺寸。)


[ 提醒] : 有時選擇圖表設計的圖表式樣，在數列上會出現像上圖的資料標籤；由於資料太多，就會產生雜亂的情況。
◎ 此時，點選 : 圖表任何位置，在圖表右上方會出現 ，點選它，會出現圖表項目視窗，裡面有個資料標籤的選項 :不打勾 — 數列上的資料標籤，就會消失。

2. 增加副座標: 在圖表設計 的工具列中，點選 : ，出現變更類型視窗，選取 : 組合圖(最下方)，其右方選單下方有個副座標軸的勾選項(參考下圖)，將加權指數選項 :打勾，就完成 副座標設定。( PS. 兩組數列各有各的數據，只有單一的主座標，無法應付，必須再叫出副座標，才能精準列表。)


◎ 增加副座標 後的圖表展示 :


3. 修改圖表標題 及 移動數列標籤: 選定圖表樣式後，會出現既有的圖表展示，其圖表標題及數列標籤都需要加以修改。
◎圖表標題: 將標題名稱，改為 : 大盤借券賣出歷史資料 VS 加權指數。
◎ 數列標籤: 由 下方 移動到 上方。( PS. 按著SHIFT鍵，可以平移，不會亂跑。)


4. 修改主副座標的列表數值 : 選定圖表樣式 後，系統會根據數列中的數值範圍，自行給予高低數列值，但這不一定是你需要的展示數據，你就必須將其修改。
※ 修改方法 : 在數列值中，點二下 : 滑鼠左鍵，在螢幕右方會出現 座標軸格式 設定區，設定方式 : 請參考下圖 。
◎ 主座標數值 (圖表左邊) : 原系統值 : 2000000000 ~ 12000000000，為方便觀看，將高低範圍改為 : 9000000000 ~ 11000000000，小數位數改為 :0。
◎ 副座標數值 (圖表右邊) : 原系統值 : 0 ~ 20000，為方便觀看，將高低範圍改為 : 12000 ~ 19000，小數位數改為 :0。


5. 調整直條圖間距大小: 選定圖表樣式 後，原預設的外資買賣超直條圖，感覺太細條，將其加粗些。
※ 修改方法 : 在外資買賣超的數列資料中，點二下 : 滑鼠左鍵，在螢幕右方會出現 資料數列格式 設定區，設定方式 : 請參考下圖 。
◎ 原設定 : 細直條 ( 類別間距約 : 200% )。利用 資料點格式 的屬性設定，點 : (數列選項)，將 數列選項屬性的類別間距，改為 :120%。
( PS. 類別間距 : 指的是直條間的間隔距離。★ 屬性值 : 介於 0到500 之間 — 0 : 無間隙/最粗 : 500 : 最細條 )
借券賣出餘額與加權指數關係

利用VBA程式自動製作關係圖表

[ 製作 大盤借券賣出餘額與加權指數關係圖 —- 前置步驟 ] :
0. 作業系統 : Office 365。
1. 打開已設計好的程式檔 : 2022_製作借券賣出排行榜.xlsm。
2. 新增工作表，名稱 : 大盤借券賣出餘額_圖表分析。
3. 於 下午10:30後 進行當日 信用額度總量管制餘額表 的數據更新。

關係圖表轉化VBA — 操作步驟

A. 插入圖表 :
◎ 建立一個新的圖表，可利用 錄製巨集 功能，錄製 插入圖片 動作程式碼，也可直接手動編寫。

[ 程式碼 / 插入圖片 ] :
ActiveSheet.Shapes.AddChart2(233, xlLine).Select
ActiveSheet.ChartObjects(1).Name = “Security Lending”
ActiveSheet.Shapes(“Security Lending”).Left = 30
ActiveSheet.Shapes(“Security Lending”).Top = 30
ActiveSheet.Shapes(“Security Lending”).Height = 600
ActiveSheet.Shapes(“Security Lending”).Width = 1800
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = “=””大盤借券賣出餘額”””
ActiveChart.FullSeriesCollection(1).Values = “=大盤借券賣出歷史資料!$L$4:$L$280”
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(2).Name = “=””加權指數”””
ActiveChart.FullSeriesCollection(2).Values = “=大盤歷史資料!$E$4:$E$280”
ActiveChart.FullSeriesCollection(1).XValues = “=大盤借券賣出歷史資料!$A$4:$A$280”

[ 程式解說 ] :
1. ActiveSheet.Shapes.AddChart2(233, xlLine).Select : 新增/插入 — 233號圖表。
2. ActiveSheet.ChartObjects(1).Name = “Security Lending” : 將圖表(1)名稱，改為 : Security Lending，方便呼叫。
3. ActiveSheet.Shapes(“TWSE”).Left = 30 : 意思是 — 距離左邊界 30點。
4. ActiveSheet.Shapes(“TWSE”).Top = 30 : 意思是 — 距離上邊界 30點。( PS. 與 Left 搭配起來，就等於是 圖表的起始定位點。)
5. ActiveSheet.Shapes(“TWSE”).Height = 600 : 意思是 — 圖表高度 600點。
6. ActiveSheet.Shapes(“TWSE”).Width = 1800 : 意思是 — 圖表寬度 1800點。
借券賣出餘額與加權指數關係

7. ActiveChart.SeriesCollection.NewSeries : 建立新數列。
8. ActiveChart.FullSeriesCollection(1).Name = “=””大盤借券賣出餘額””” : 定義數列名稱為 : 大盤借券賣出餘額。
9. ActiveChart.FullSeriesCollection(1).Values = “=大盤借券賣出歷史資料!$L$4:$L$280” : 設定數列的數值範圍 : B4 ~ B280。
10. ActiveChart.FullSeriesCollection(1).XValues = “=大盤借券賣出歷史資料!$A$4:$A$280” : 設定水平坐標軸的數值範圍 : A4 ~ A280。

[ 提醒 ] :
☆ 數列抓取的數值範圍，若大於實際的歷史數據，多出來的資料位置，因無資料，會以空格顯示。( PS. 若有新資料進來，會自動補上。)
[ 例 ] : 設定水平坐標軸的數值範圍 : A4 ~ A280，但歷史資料(下圖左)只到 243列，之後到 280列 — 無資料，則以空格顯示 (下圖右)。
借券賣出餘額與加權指數關係

B. 增加副座標

[ 程式碼 / 增加副座標 ] :
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered ‘ 設定為 : 直條圖。
ActiveChart.FullSeriesCollection(1).AxisGroup = 1 ‘ 設定為 : 主座標。
ActiveChart.FullSeriesCollection(2).ChartType = xlLine ‘ 設定為 : 折線圖。
ActiveChart.FullSeriesCollection(2).AxisGroup = 2 ‘ 設定為 : 副座標。

C. 修改圖表標題 及 移動數列標籤 :

[ 程式碼 / 修改圖表標題 ] :
ActiveChart.ChartTitle.Text = “大盤借券賣出餘額 VS 加權指數” ‘ 圖表標題設為 : 大盤借券賣出餘額 VS 加權指數。
ActiveChart.SetElement (msoElementLegendTop) ‘ 數列項目 — 移到上層。

D. 修改主副座標的列表數值 :

[ 程式碼 / 修改主副座標的列表數值 ] :
ActiveChart.Axes(xlValue).Select ‘ 選擇 : 主座標。
ActiveChart.Axes(xlValue).MaximumScale = 11000000000# ‘ 數列最大值 : 11000000000。
ActiveChart.Axes(xlValue).MinimumScale = 9000000000# ‘ 數列最小值 : 9000000000。
ActiveChart.Axes(xlValue, xlSecondary).Select ‘ 選擇 : 副座標。
ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = 19000 ‘ 數列最大值 : 19000。
ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = 12000 ‘ 數列最小值 : 12000。

E. 調整日期位置 :

[ 程式碼 / 調整日期位置 ] :
ActiveChart.Axes(xlCategory).Select ‘ 選定 : 類別(水平)座標軸。
Selection.TickLabelPosition = xlLow ‘ 調整標籤位置 : xlLow ( 低 )。

F. 調整直條圖間距大小 :

[ 程式碼 / 調整直條圖間距大小 ] :
ActiveChart.ChartGroups(1).GapWidth = 100 ‘ 調整類別間距，改為 : 100%。

完整程式碼

Sub 借券賣出餘額vs加權指數()

    Sheets("大盤借券賣出餘額_圖表分析").Select
    ActiveSheet.Shapes.AddChart2(233, xlLine).Select  ' 新增 233號 -- 折線圖表    
    ActiveSheet.ChartObjects(1).Name = "Security Lending"  ' 定義圖表名稱 : Security Lending(借券)
    
    ActiveSheet.Shapes("Security Lending").Left = 30  ' 左邊界
    ActiveSheet.Shapes("Security Lending").Top = 30  ' 右邊界
    ActiveSheet.Shapes("Security Lending").Height = 600  ' 圖表寬度
    ActiveSheet.Shapes("Security Lending").Width = 1800  ' 圖表高度
    
    ActiveChart.SeriesCollection.NewSeries  ' 建立新數列
    ActiveChart.FullSeriesCollection(1).Name = "=""大盤借券賣出餘額"""  ' 定義數列名稱
    ActiveChart.FullSeriesCollection(1).Values = "=大盤借券賣出歷史資料!$L$4:$L$280"  ' 設定數列的數值範圍    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=""加權指數"""
    ActiveChart.FullSeriesCollection(2).Values = "=大盤歷史資料!$E$4:$E$280"
    ActiveChart.FullSeriesCollection(1).XValues = "=大盤借券賣出歷史資料!$A$4:$A$280"  ' 設定水平坐標軸的數值範圍

    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered  ' 設定為 : 直條圖
    ActiveChart.FullSeriesCollection(1).AxisGroup = 1  ' 設定為 : 主座標
    ActiveChart.FullSeriesCollection(2).ChartType = xlLine  ' 設定為 : 折線圖
    ActiveChart.FullSeriesCollection(2).AxisGroup = 2  ' 設定為 : 副座標
    
    ActiveChart.ChartTitle.Text = "大盤借券賣出餘額 VS 加權指數"  ' 圖表標題設為 : 外資買賣超 VS 加權指數。
    ActiveChart.SetElement (msoElementLegendTop)  ' 數列項目 -- 移到上層
    
    ActiveChart.Axes(xlValue).Select  ' 選擇 : 主座標
    ActiveChart.Axes(xlValue).MaximumScale = 11000000000# ' 數列最大值 : 11000000000
    ActiveChart.Axes(xlValue).MinimumScale = 9000000000# ' 數列最小值 : 9000000000

    ActiveChart.Axes(xlValue, xlSecondary).Select  ' 選擇 : 副座標
    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = 19000  ' 數列最大值 : 19000
    ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = 12000  ' 數列最小值 : 12000
    
    ActiveChart.Axes(xlCategory).Select  ' 選定 : 類別(水平)座標軸。
    Selection.TickLabelPosition = xlLow  ' 調整標籤位置 : xlLow ( 低 )。
    
    ActiveChart.ChartGroups(1).GapWidth = 100  ' 調整類別間距，改為 : 100%。

End Sub

結論

因法人(外資)無法使用信用交易(融券放空)，促使借券成為法人放空的一種手段。因此 大盤借券賣出餘額 的變化，就被拿來分析法人(外資)對後市看法的依據。既然 大盤借券賣出餘額 對 加權指數 有著相對的影響性，便可利用 VBA的數據收集 與 EXCEL的圖表展現，清楚分析兩數據的差異性，以及相關的脈動關聯，比起表格中純數字的資料比對，圖表則更容易表達與凸顯重點。

基於此特點，本篇文章採用圖文對照的方式，手把手教會你如何利用 VBA圖表 的語法設定 — 新增圖表 / 訂定圖表大小 / 建立數列 / 變更圖表類型 / 變更數列範圍 / 調整數列位置 等功能，來達成自動完成圖表製作的目的。

非常感謝您看到最後，若在閱讀的過程中，有任何疑問，歡迎您利用左下角 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程式語法，做個整合記錄，方便大家在學習當下參考與使用。

相關文章