EXCEL & VBA功能 – 筆記
目錄 :
DDE / RTD 語法與參數
[ 語法 ] :
※ 連結 券商伺服器 的方式，有二種 : 
※ DDE 與 RTD 的串接語法不同，請注意寫法 :
☆ 券商連結 : 各個券商連結的參數不同，必須改掉，才能連接。比如 : 富邦e01 的DDE串接語法 : =XQFAP|Quote!‘TSE.TW-ID’。
☆ 商品代號 : 需搭配 商品類別，來顯示代號商品。 舉例 : 加權指數 — TSE.TW / 台積電 — 2330.TW，台指期 — FITXN01.TF。
☆ 商品類別 : 類別分 TW : 股票( 含大盤指數 ) 及 TF : 期貨 / 選擇權。
☆ 商品報價 : 更改報價參數，來顯示其它商品資料。比如 : TSE.TW-ID — 顯示 : TSE (代碼) / TSE.TW-Name — 顯示 : 加權指數 (商品)。( 參考上方 Excel 圖表 )。
[ 常用報價 – 參數 ] :
[ 參數 – 補充 ] :
◎ BestBidSize ( 委買 ) : ( 五檔統計) 傳回目前委託資料內買單中最高的買價的委託數量。( 台股 / 期貨 / 選擇權 )
◎ BestAskSize ( 委賣 ) : ( 五檔統計) 傳回目前委託資料內賣單中最高的賣價的委託數量。( 台股 / 期貨 / 選擇權 )
◎ TotalBidMatchTx ( 累買成筆 ) : 傳回當日累計買進成交的筆數。( 期貨 / 選擇權 )
◎ TotalAskMatchTx ( 累賣成筆 ) : 傳回當日累計賣出成交的筆數。 ( 期貨 / 選擇權 )
☆ 
: 大盤 / 股票 / 期貨 / 選擇權 — 通通適用。
☆ 
: 僅適用 — 股票。
☆ 
: 僅適用 — 大盤。
☆ 
: 僅適用 — 期貨 / 選擇權。
開啟 VBA 開發人員工具
※ 文章以 Office 365 來做設定教學。( PS. 每個版本的設定方式差不多，只是位置排列不同，找一下就能找到。)
[ 操作步驟 ] :
1. 點 : 檔案。( 位置 : 左上角功能列 )
2. 點 : 選項。( 位置 : 左下角 )
3. 點 : 自訂功能區， 右邊出現 : 主要索引標籤 — 把「 開發人員 」的核准鈕，打勾 。
4. 回到 工作表，功能列就會出 開發人員 工具。
5. 點選 開發人員，會顯示下面功能選項。
6. 啟動 VBA : 點擊上圖 Visual Basic，打開 程式編輯 視窗。 
設定 工作排程器
◎ 天天手動儲存，擔心某天忘了採取動作，導致數據錯誤這時就利用 Windows 提供的工作排程器，來設定每日行程，讓電腦自動執行，既省事、又準確且避免錯誤產生，這大大節省了設計者的工作時間。
[ 工作排程器 設定步驟 ] :
1. 利用 Windows 的搜尋功能 : 搜尋 — 工作排程器。( 我目前使用的系統 : WIN 11，其他版本也適用。)
2. 進入 工作行程器 ( 如下圖 )，執行 : 建立基本工作。
3. 建立基本工作 : 設定 工作名稱，按 : 下一步。
4. 工作觸發程序 : 設定 每天 ( 代表每天 : 1次 )，按 : 下一步。
5. 建立基本工作精靈 : 目前時間 — 盤中 2022/01/17 下午 12 : 43。預計收盤後 — 下午 2點，儲存大盤資料。
※ 設定開始時間 — 調整為 : 2022/ 1/17 下午 02 : 00 : 00，每隔 : 一天 實施，再按 : 下一步。( PS. 收盤時間，可自行調整，只要是收盤後，即可。)
6. 設定執行動作 : 設定 啟動程式，按 : 下一步。
7. 設定啟動程式 : 進入設定畫面，點選 : 瀏覽，找到存放 大盤自動儲存系統 的檔案位置，點選 : 大盤自動儲存系統 後，再按 : 開始。
8. 設定好檔案位置，啟動程式系統會自動連結設定檔案(如下圖)，按 : 下一步。
9. 最後出現摘要，顯示剛剛設定的所有動作資料，確定無誤，按 : 完成，即大功告成。( PS. 下圖藍框內的核准鈕 : 不勾選。)
10. 確定 工作排程器程式庫 : 裡面多了 大盤自動儲存系統，下次執行時間 : 2022/1/17 下午 02 : 00 : 00，代表設定完成。
查詢檔案下載位置
※ 若不清楚檔案下載後的儲存位置，可利用檔案總管來查找。方法如下 :
1. 首先打開 檔案總管。
2. 在「 下載 」位置，按 : 滑鼠右鍵，選取 : 內容，再點選標籤 : 位置，便可看見目前檔案下載的儲存位置。
KD指標 – 計算公式
★ 首先要先了解 KD指標 的 K值、D值，是怎麼來的。計算公式，如下 :
[ 註解 ] : RSV ( 未成熟隨機值 ) : 代表最近 9 天的股價波動。 K(9,3) : 9 代表最近 9 天，3 是將其權重分成 3 等分來做計算。
※ EXCEL 最高值 / 最低值 之公式 : 以 K( 9 , 3 ) 為例 —
☆ 最高值 ( H ) = MAX ( 最近9日內(含今日)的最高價 )
☆ 最低值 ( L ) = MIN ( 最近9日內(含今日)的最低價 )
MACD指標 – 計算公式
★ MACD指標 : 也稱 指數平滑異同移動平均線 指標。
[ 用途 ] : 研判投資商品價格的變化強度與方向，並找出相對的支撐與壓力，以便掌握買進和賣出的進出場時機。
※ MACD 的計算公式，略顯複雜些，請參考下面的公式列表 :
[ 公式解讀 ] :
◎ 首先求得「需求指數」( Demand Index，DI ) : DI = ( H + L + 2C ) / 4，( PS. H : 最高價 / L : 最低價 / C : 收盤價 )，若想簡單些，可直接用 收盤價，來取代 DI 值。
◎ EMA12 ( 12日平滑式移動平均線 ) : 套進公式 : EMA12 = ( 昨日EMA12 ＊ ( 12 – 1 ) + 今日DI ＊ 2 ) / ( 12 + 1 )。
◎ EMA26 ( 26日平滑式移動平均線 ) : 套進公式 : EMA26 = ( 昨日EMA26 ＊ ( 26 – 1 ) + 今日DI ＊ 2 ) / ( 26 + 1 )。
◎ DIF12-26 ( 差離值，也稱 : 快線 ) : 是為 EMA12 – EMA26 的差值，代表 EMA12 偏離 EMA26 的情況。
◎ MACD9 ( 也叫 : DEM，俗稱 : 慢線 ) : 再帶進平滑修正公式 : MACD9 = ( 昨日MACD ＊ ( 9 – 1 ) + 今日DI ＊ 2 ) / ( 9 + 1 )，以求得乖離程度的平均值。( PS. 計算天數，一般均使用 9日來計算。)
◎ OSC ( 棒形圖，又稱 : MACD bar ) : 將計算出之 DIF – MACD，其差值就是柱狀圖的紅綠棒。
※ PS. DIF12-26 是一般券商預設的參考值，快慢線天數是可更改的，對此參數有研究的朋友，可自行測試。
布林通道 – 計算公式
★ 首先要先了解 布林通道 的 均值、標準差，是怎麼來的。計算公式，如下 : 
※ 均值 之計算 : 一般都以 20天 收盤價的平均值， 為參考值。( PS. 均值就相當於 MA20。)
※ 標準差 之計算 : 是一種表示分散程度的統計概念。標準差越大，表示淨值的漲跌越劇烈，風險程度也越大。詳細解說請參考 : 標準差 – 智庫百科
( PS. 廣泛運用在股票及共同基金投資風險的衡量上，主要是根據淨值於一段時間內的波動情況而計算出來的。)
[ EXCEL公式 ] :
☆ 均值 / =AVERAGE(E1:E20)
☆ 標準差 / =STDEVP(E1:E20)
☆ UB2.0 / LB2.0 : 套入上述公式 —
UB2.0 / =AVERAGE(E1:E20) + STDEVP(E1:E20) ＊ 2
LB2.0 / =AVERAGE(E1:E20) – STDEVP(E1:E20) ＊ 2
[ VBA公式 ] :
☆ 均值 / Application.Average(Range(Cells(I – 19, “E”), Cells(I, “E”)))
☆ 標準差 / Application.StDevP(Range(Cells(I – 19, “E”), Cells(I, “E”)))
☆ UB2.0 / LB2.0 : 參考上述公式介紹，2.0 表示 2倍標準差，這是一般基本的設定值。其值可更動，可以參照各個線型來做參數( 1.5倍 / 2.5倍 或 3倍標準差 ) 更動，以符合需求。
權值 vs 大盤點數 – 計算公式
個股股價漲跌 對 大盤點數 的影響，其計算公式，如下 :
個股股價漲跌 : 1點 = 大盤收盤價 × 個股權值 / 個股收盤價
◎ 以 台積電 權值第一 ( 2022 / 01月 – 市值占大盤比重 : 28.6851%，由 證交所 – 大盤成份股 得知 ) 來計算，今日( 2022 / 01 /24 ) 大盤收盤價 : 17989.04點 / 台積電收盤價 : 653元。
☆ 帶入公式計算 : 17989.04 × 28.6851% / 653 = 7.9023 點。 表示 : 目前台積電 / 漲跌 1點，將影響大盤 : 7.9023 點。
錄製巨集 ( Office 365 版本 )
◎ Office 365 版本的錄製方式，請參考 抓取 — 大盤成份股 權重資料 內的錄製步驟。
錄製巨集(舊版) / Office 365 之前版本
[ 開始錄製 – 抓取步驟 ] :
1. 新增 or 打開工作表 : 為存放下載的資料，需新增一個工作表 或 放置於既有工作表中。( PS. 既有工作表名稱 : 買賣金額統計表。)
2. 打開開發人員工具 : 點擊 – 功能列 開發人員，點選 :『 錄製巨集 』。
3. 輸入巨集名稱 : 此時會出現錄製巨集視窗，要求輸入巨集名稱，預設是 : 巨集1 … 2 … 3，先不改，使用預設名稱。
◎ 如下圖 : 第一次開啟，巨集預設為 巨集1，按下 : 確定，在 VBA 專案列表內，會多個 Module1 巨集模組。
4. 開始錄製 : 此時你可以發現工具列內的 錄製巨集 功能，變為 : 停止錄製，這表示 : 錄製功能 – 已啟動。( PS. 錄製完畢，點選 : 停止錄製，即結束錄製。)
5. 連結 證交所 – 大盤成份股 網址 :
[ 注意 ] : 以下步驟，將不同於 Office 365版本的操作方式。
◎ 點選 : 功能表 – 資料。
◎ 再選 : 工具表 – 取得資料，出現下拉選單 : 選擇 – 傳統精靈，再點選 : 從 WEB (舊版)，即可。
6. 出現導覽器 :
◎ 首先在地址輸入框內，輸入 : https://www.twse.com.tw/fund/BFI82U?response=html&dayDate=20221201&weekDate=20221128&monthDate=20221101&type=day，再按 : 到。
◎ 等待瞬間，會出現 三大法人買賣金額統計表，在左上角有 
圖樣，將其打勾 
( PS. 表示 : 選取此範圍表格。)，再進行 : 匯入。
7. 匯入資料 :
◎ 選擇 : 將資料放在 目前工作表的儲存格 : 為了自動化，最好選擇 : 設定值 : =$A$1 ( 資料放置的起始點 )，方便做資料處理。
◎ 新增此資料至資料模型 : 不勾選。
8. 三大法人買賣金額統計表 : 顯示如下 —
延遲證交所抓取時間
☆ 避免被證交所防盜機制識破，故延遲 6 秒抓取時間，以防連線中斷。
[ 指令語法 ] :
Application.Wait (Now + TimeValue(“00:00:06”))
[ 語法說明 ] :
◎ Application.Wait : 暫停執行程序，直到指定時間為止。
◎ Now + TimeValue(“00:00:06”) : 當下(Now)執行動作的時間 + 6 秒 → 表示 : 6秒後，再執行動作。
被證交所中斷連線的處理方式
◎ 若不幸，被證交所中斷連線，也不用緊張。
☆ 最簡單的方法 : 把中華電信的小烏龜背後的電源開關 – 關掉，再重開，即可。( PS. 用意是 : 系統會對外重新配置一個新的IP。)
切斷查詢連線
☆ 切斷連結外部資料源連線，以防連線膨脹，導致系統負載過大，造成死機。
[ 指令語法 ] :
ActiveWorkbook.Queries(“Table 0”).Delete
[ 語法說明 ] :
◎ ActiveWorkbook : 目前使用的活頁簿。
◎ Queries : 查詢連線，後面加 (“Table 0”) 連線名稱。
◎ Delete : 刪除。
按鈕 製作
※ 為了節省時間，避免手動的錯誤，所以將所有的步驟，整合在一個程序裡，利用『 按鈕 』來控制，達到自動儲存的目的。
[ 製作步驟 ] :
1. 打開 開發人員 工具，點選 : 插入，選擇 : 按鈕。 
2. 點選 按鈕 後，游標 變成 ＋ 字形，讓你在工作表上製作一個 四方形 的按鈕。
3. 滑鼠拖曳游標完成四方形後，放開滑鼠鍵，會出現 指定巨集 的視窗，此時點選 : 儲存大盤當日資料 的巨集程式，按 : 確定。
4. 按確定後，在剛剛畫四方形的位置，會出現 按鈕 圖型。
5. 滑鼠游標移向按鈕(不能點擊按鈕)，按滑鼠右鍵，選擇 : 編輯文字，將 按鈕名稱 更改成 : 儲存大盤當日資料，便完成所有程序。
( PS. 編輯文字的功能，有點秀逗，最好先將原設定的文字( 按鈕 3 )，先清除，再填寫上去，或是用貼上的方式處理。)
6. 完成上述步驟，製作好按鈕，此時按下「 儲存大盤當日資料 」按鈕，就會自動執行 儲存大盤當日資料 副程式。
[ 注意 ] : 一天只能按一次。否則同日期資料，會一直儲存進大盤歷史資料，導致分析錯誤。
[ 提醒 ] : 按鈕的設定，可有可無。只是方便使用者，在突發狀況使用。
☆ 比如 : 當天忘了儲存，或是設定工作排程後，執行時間到，自動啟動儲存程式，但自動儲存的程式卻開啟中，導致無法更新。
文字 組合
任何字元被 雙引號『 ” “ 』標示起來，就叫 — 字串。
『 & 』: 是字串的連接符號。字串連接時，必須以『 空白鍵 』分隔。
[ 用法 ] :
Word = “Y” & “O” & “U” — 變數 Word 的值 = “YOU“。也就是 & 的 前/後 都需要有 空白鍵 隔開。
Range( “F” & 5 ).Select = Range( “F5” ).Select — 數值 5，可以改用變數，如 : Range( “F” & A ).Select。
☆ SMA5 均線 EXCEL運算公式 : 『 =( E2 + SUM( E8297:E8300 )) / 5 』。
lastRow = 8300 — 變數 : lastRow
[H2] = “=( E2 + SUM( E” & lastRow – 3 & ” : E” & lastRow & ” )) / 5″ ‘ 將 SMA5 的運算公式，貼到 H2 儲存格。
[ 組合解說 ] :
EXCEL運算公式中的 E8297 跟 E8300，需要變數 lastRow – 3 跟 lastRow 來替換數值，所以使用 & 來組合字串與變數，其 前/後 再用 空白鍵 來隔開。
[ 常用範例 ] : 西元年 / 中國年 日期互換
☆ 資料範例 : 請參考 — 西元年 / 中國年 日期互換。
FileSystemObject 檔案處理設定
◎ FileSystemObject : 本身內建 檔案與目錄 的相關功能，可以直接引用。
[ 用法 ] :
Set fileObject = CreateObject(“Scripting.FileSystemObject“) ‘ 建立 FileSystemObject 物件
Set fileFolder = fileObject.GetFolder(“D:\2022_VBA_程式教學\借券賣出_歷史CSV檔\202212“) ‘ 建立目錄物件
For Each xFile In fileFolder.Files
[ 程式解說 ] :
1. 建立(CreateObject) FileSystemObject 物件，並設定物件名稱為 : fileObject。
2. 再設定一個目錄物件 : fileFolder，來取得(GetFolder)指定路徑下的 202212 月份目錄。
3. For Each … In : 請參考 — For Each … In 函式。
判別檔案是否存在
◎ Dir 指令功能 — 傳回磁碟目錄下的所有檔案名稱。
( PS. 運用 Dir(“D:\20230815.xlsx“) — 查找 D磁碟\20230815.xlsx 檔案，若檔案存在 — 會回傳 檔案名稱；檔案不存在 — 會回傳 ( “” ) 空字串。
[ 程式碼 / 判別檔案是否存在 ] :
xPath = "D:\"
FileName = "20230815.xlsx"
CheckName = Dir(xPath & FileName)
If CheckName = FileName Then
MsgBox CheckName & " 檔案存在"
Else
MsgBox CheckName & " 檔案不存在"
End If
儲存格 轉換格式
◎ 儲存格 轉換格式 : 意思就是 – 轉換某儲存格的格式。
[ 用法 ] :
Range(“C” & countRow & “:O” & countRow).Select
Selection.NumberFormatLocal = “G/通用格式” ‘ 將選取的儲存格格式轉成 : 通用格式。
Selection.Value = Selection.Value ‘ 將已轉換的 通用格式 置換掉 原資料格式。
Selection.Copy
[ 其他設定格式 ] :
Selection.NumberFormatLocal = “yyyy/mm/dd” → “2022/12/09”
Selection.NumberFormatLocal = “yyyymmdd” → “20221209”
最大值 / 最小值
[ 使用語法 ] :
◎ 最大值 : WorksheetFunction.Max(“A1:A50”) → 搜尋 A1 ~ A50欄位中的 最大值。
◎ 最小值 : WorksheetFunction.Min(“A1:A50”) → 搜尋 A1 ~ A50欄位中的 最小值。
陣列資料比對 (Match)
[ 使用語法 ] :
◎ xPos = Application.Match(735, [A2:A100], 0) → 從 A2 ~ A100欄位中比對 735 (目標值)，若存在，傳回目標位置；若不存在，則報錯 !
( PS. 程式執行中，若想避免報錯，可在程式前端設定 : On Error Resume Next 語法，停止報錯程序。)
※ Match 指令的第 3 個參數的設定參照 :
◎ 1 : 尋找小於或等於 735 的最大值。
◎ 0 : 尋找等於735 的第一個值。
◎ -1 : 尋找大於或等於 735 的最小值。
行號 / 字母與數值的轉換方式
[ 字母轉數值 ] :
stringCol = InputBox(“請輸入要轉換成數值的字母行號”) ‘ 輸入 : AD (字母)行號。
integerCol = Range(stringCol & “1”).Column
MsgBox (“輸入的字母行號轉成對應數值行號是 : ” & integerCol ) ‘ 列出 : 30 (數值)。
[ 數值轉字母 ] : 程式參考 MicroSoft 提供之範例。
◎ 若數值行號為 : 30，轉換成對應字母行號為 : AD。程式碼如下 :
[ 主程式碼 ] :
xWord = ConvertToLetter(30)
[ Function程式碼 ] :
Function ConvertToLetter(iCol%) As String
iCol = 30
ConvertToLetter = ""
Do While iCol > 0
a = Int((iCol - 1) / 26) ' 英文字碼進位 -- 商值。
b = (iCol - 1) Mod 26 ' 求得字母代碼 -- 餘數。
ConvertToLetter = Chr(b + 65) & ConvertToLetter ' 字母連結 -- 轉換成字母行號。
iCol = a
Loop
End Function
開啟 / 關閉螢幕更新
[ 使用語法 ] :
◎ 關閉螢幕更新 : Application.ScreenUpdating = False ( ※ 執行程式碼時，不會顯示執行的動作，將有助於程式執行的效率。)
◎ 開啟螢幕更新 : Application.ScreenUpdating = True ( ※ 程式結束後，務必將其屬性設定回 : True。)
[ EXCEL ] VLOOKUP 搜尋函式
※ VLOOKUP : 功能是從表格中搜尋特定值，再回傳相對應的值。
[ 舉例 ] :
◎ 參考下圖 — VLOOKUP(B1, C5:D15, 2, FALSE)。
[ 公式解說 ] :
[ 1 ] : B1 — 搜尋值 ( 2330 )。
[ 2 ] : C5:D15 — 搜尋範圍 ( 表格 C5 ~ D15 欄位 )。
[ 3 ] : 2 — 傳回值 ( 台積電期貨 )。( 意思是 : 於C行中搜尋到2330值後，便會傳回其第2行的資料 = 台積電期貨。)
[ 4 ] : FALSE — 參數設定。( PS. TRUE : 部分符合 / FALSE : 完全符合。)
[ 公式規則 ] :
☆ 使用 VLOOKUP 函式，要注意一點 : 它無法針對搜尋值左邊的資料，作搜尋的動作。
( 如上圖 — VLOOKUP 就無法針對B行(股票期貨代號)作搜尋動作。此時，就必須改用 INDEX + MATCH 函式，才能達成訴求。)
[ EXCEL ] INDEX + MATCH 搜尋公式
※ INDEX + MATCH : 可以靈巧地針對二維表格中搜尋特定值，再回傳相對應的值。
→ INDEX函數 : 目的是檢索範圍內的特定值。 ( PS. 無法針對二維表格資料作列號定位，必須借助 MATCH 函數，才能更有效搜尋。)
→ MATCH函數 : 目的是搜尋特定值，查找出範圍內對應的列號。
[ 舉例 ] :
◎ 參考下圖 — INDEX(B5:D15, MATCH(B1, C5:C15, 0), 1)。
[ 公式解說 ] :
[ 1 ] : B5:D15 — 搜尋範圍 ( 表格 B5 ~ D15 欄位 )。
[ 2 ] : B1 — 搜尋值 ( 2330 )。
[ 3 ] : C5:C15 — 比對範圍 ( C行 5 ~ 15 欄位 )，傳回對應的列號。( 意思是 : 於C行中搜尋到2330值後，便會傳回第8列列號。)
[ 4 ] : 0 — 參數設定。( 0 : 完全符合 / 1 : 不符合 — 則以小於搜尋值之值進行搜尋 / -1 : 不符合 — 則以大於搜尋值之值進行搜尋。)
[ 5 ] : 1 — 傳回搜尋值的行號。
→ 完成以上搜尋動作，公式 = INDEX(B5:D15, 8, 1)，便會於B2欄位，傳回 CDF ( 股票期貨代號 )。
EXCEL 365 搜尋新函數 — XLOOKUP
※ 若您的系統是EXCEL 365，可以更輕巧的運用 XLOOKUP 搜尋函數，它取代了舊版 VLOOKUP，提供更簡便的語法/參數，進行搜尋動作。
→ 以上圖為例 : 採用了 VLOOKUP + INDEX / MATCH 函數，才完成股票期貨代號及股票名稱的搜尋；若改用 XLOOKUP函數，只需一條指令便能達成。
[ B2欄 / 公式 ] :
1. 修改成 : =XLOOKUP(B1,C5:C15,B5:B15)，取得 CDF (股票期貨代號)，排除了VLOOKUP函數無法往左搜尋的問題。
2. 修改成 : =XLOOKUP(B1,C5:C15,B5:D15)， B2~B4欄位 : 則顯示 
。
3. 增加錯誤訊息參數 : =XLOOKUP(B1,C5:C15,B5:D15,”查無此號”)，若搜尋範圍內沒有搜尋值，則顯示”查無此號“。
[ EXCEL ] RTD語法搭配文字組合，取得即時股價
※ RTD語法靈活地透過文字組合的方式，取得需求的個股即時股價。
[ 舉例 ] :
◎ 參考下圖 : RTD(“xqrtd.rtdserverxqlite”,, B1 & “.TW-Price”)。
[ 公式解說 ] :
◎ 藉由文字組合，將股票代號改為 : 變數，以取得B1欄位值，便能靈活地變換B1值，取得所需求的股票即時股價。
( PS. 原RTD代碼 = RTD(“xqrtd.rtdserverxqlite”,, “2330.TW-Price”)。)
V B A 資 源
