[VBA] 製作 外資買賣超與加權指數關係 圖

外資買賣超與加權指數關係 有何重要性?

◎ 首先，必須先了解 外資買賣超 為何與 加權指數漲跌 有著密切的關係?
根據之前 三大法人 的特性說明 — 外資挾帶大量的海外資金來台進行買賣投資，其標的大多以 大型權重股 為主，自然對 加權指數 的漲跌，有著相當大的影響。所以每天 下午四點 公布的外資買賣超數據，是有其收集必要性；藉由數據的收集，進而了解外資的多空心態，對於評估大盤未來的漲跌，有著很好的參考依據。

如何製作 外資買賣超與加權指數關係 圖

※ 話不多說，直接進入重點，如標題所示 : 製作 外資買賣超與加權指數關係 圖。如下圖 —
外資買賣超與加權指數關係

◎ 首先，要完成上圖的圖表展現，需要先收集 外資買賣超 及 加權指數 的歷史資料。至於如何利用 VBA程式 來抓取，我還是用兩篇文章來分別解說，比較清楚明白，不會雜亂冗長，文章連結如下 :

[ 歷史資料抓取 – 番外篇 ] :
第 1 篇 : 抓取 外資買賣超 歷史資料
第 2 篇 : 抓取 加權指數 歷史資料

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

◎ 將 外資買賣超 及 加權指數 的歷史資料，抓取完成後，便能開始製作 外資買賣超與加權指數關係 圖表。

組合圖 — 圖表製作

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


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


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


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

◎ 數列名稱 : 指的是圖表中的資料源名稱。可以直接定義名稱，參考下圖(右)，直接填入 : 外資買賣超，在圖表中的圖例項目，便會出現 外資買賣超 的數列名稱。( PS. 也可以直接填入 儲存格標題位址，如 : =買賣超歷史總覽_舊版!$B$2，取得 : 外資 & 陸資 的數列名稱。)


◎ 數列值 : 填入欲展示的外資買賣超資料範圍。點選 : 數列值輸入框，將工作表切換到 : 買賣超歷史總覽_舊版，游標從 B4欄，拖曳到 B300欄位。
( PS. 也可直接鍵入 : =買賣超歷史總覽_舊版!$B$4:$B$300 )。

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

[ 新增一數列 : 加權指數 ] :
※ 完成 外資買賣超 歷史資料擷取。同步驟 3 ~ 4，再新增一筆 加權指數 歷史資料進來。

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

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


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


[ 擷取完成圖 ] : 內含 外資買賣超(藍線) / 加權指數(桔線) 及下方水平坐標軸的日期。

美化 外資買賣超與加權指數關係 圖表

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


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


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


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


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


5. 調整日期位置 : 選定 圖表樣式 後，原預設在中間 0軸 位置(下圖上)，要將其移動到圖表下層。
※ 修改方法 : 在日期標籤中，點二下 : 滑鼠左鍵，在螢幕右方會出現 座標軸格式 設定區，設定方式 : 請參考下圖 。
◎ 原日期字體設定傾斜 : 45°，利用 座標軸格式 的屬性設定，點 : ，將 對齊 屬性的 文字方向，改為 : 所有文字旋轉 270度。
◎ 再點擊 : (座標軸選項)，將 標籤 屬性的 標籤位置，改為 : 低。


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

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

[ 製作 外資買賣超與加權指數關係圖 —- 前置步驟 ] :
0. 作業系統 : Office 365。
1. 打開已設計好的程式檔 : 2022_三大法人現貨買賣超.xlsm。
2. 新增工作表，名稱 : 外資買賣超_圖表分析。
3. 於 下午四點後 進行當日 三大法人買賣金額統計表 與 加權指數 的數據更新。

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

A. 插入圖表 :

[ 程式碼 ] :
ActiveSheet.Shapes.AddChart2(221, xlLine).Select
ActiveSheet.ChartObjects(1).Name = “TWSE”
ActiveSheet.Shapes(“TWSE”).Left = 30
ActiveSheet.Shapes(“TWSE”).Top = 30
ActiveSheet.Shapes(“TWSE”).Height = 600
ActiveSheet.Shapes(“TWSE”).Width = 1800

ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = “=””外資買賣超”””
ActiveChart.FullSeriesCollection(1).Values = “=買賣超歷史總覽_舊版!$B$4:$B$300”
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(2).Name = “=””加權指數”””
ActiveChart.FullSeriesCollection(2).Values = “=大盤歷史資料!$E$4:$E$300”

ActiveChart.FullSeriesCollection(1).XValues = “=買賣超歷史總覽_舊版!$A$4:$A$300”

[ 程式解說 ] :
1. ActiveSheet.Shapes.AddChart2(221, xlLine).Select : 新增/插入 — 221號圖表。
2. ActiveSheet.ChartObjects(1).Name = “TWSE” : 將圖表(1)名稱，改為 : TWSE，方便呼叫。
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 = “=買賣超歷史總覽_舊版!$B$4:$B$300” : 設定數列的數值範圍 : B4 ~ B300。
10. ActiveChart.FullSeriesCollection(1).XValues = “=買賣超歷史總覽_舊版!$A$4:$A$300” : 設定水平坐標軸的數值範圍 : A4 ~ A300。

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

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 = 600 ‘ 數列最大值 : 600。
ActiveChart.Axes(xlValue).MinimumScale = -600 ‘ 數列最小值 : 600。
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 = 120 ‘ 調整類別間距，改為 : 120%。

完整程式碼


Sub 外資買賣超vs加權指數()

    Sheets("外資買賣超_圖表分析").Select

    ActiveSheet.Shapes.AddChart2(221, xlLine).Select  ' 新增 221號 -- 折線圖表    
    ActiveSheet.ChartObjects(1).Name = "TWSE"  ' 定義圖表名稱 : TWSE    
    ActiveSheet.Shapes("TWSE").Left = 30  ' 左邊界
    ActiveSheet.Shapes("TWSE").Top = 30  ' 右邊界
    ActiveSheet.Shapes("TWSE").Height = 600  ' 圖表寬度
    ActiveSheet.Shapes("TWSE").Width = 1800  ' 圖表高度
    
    ActiveChart.SeriesCollection.NewSeries  ' 建立新數列
    ActiveChart.FullSeriesCollection(1).Name = "=""外資買賣超"""  ' 定義數列名稱
    ActiveChart.FullSeriesCollection(1).Values = "=買賣超歷史總覽_舊版!$B$4:$B$300"  ' 設定數列的數值範圍    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=""加權指數"""
    ActiveChart.FullSeriesCollection(2).Values = "=大盤歷史資料!$E$4:$E$300"

    ActiveChart.FullSeriesCollection(1).XValues = "=買賣超歷史總覽_舊版!$A$4:$A$300"  ' 設定水平坐標軸的數值範圍

    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 = 600  ' 數列最大值 : 600
    ActiveChart.Axes(xlValue).MinimumScale = -600  ' 數列最小值 : 600
    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 = 120  ' 調整類別間距，改為 : 120%。

End Sub

結論

由於 外資買賣超 對於 加權指數 有著相對性的影響，所以利用 VBA的數據收集 與 EXCEL的圖表展現，便能清晰看出兩數據的差異性，以及相關的脈動關聯，比起表格中純數字的資料比對，圖表則更容易表達與凸顯重點。

基於此特點，本篇文章採用圖文對照的方式，手把手教會你如何利用 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程式語法，做個整合記錄，方便大家在學習當下參考與使用。

相關文章