Excel 與 VBA 財務應用 第十課
Eugene Chang (張佑成)
December 25th 2024
# Excel 與 VBA 財務應用初階班  --- # 範例 Excel 檔案 請下載：[Lecture10 - IRR 與規劃求解](https://drive.google.com/uc?export=download&id=1PbxvXigd3ssgM7p8TpB3rSWjNTVjyAq6) --- # IRR 函數 讓 Excel 幫我們計算内部報酬率 --- # IRR 函數 ```bash IRR(values, [guess]) ``` - Values: 現金流資料 - Guess: 猜測内部報酬率的值 - 從 guess 開始，IRR 會不斷循環計算，直到誤差小於 0.00001% 為止。 如果計算 20 次之後，IRR 依舊無法求得結果，則會傳回 #NUM! 錯誤值。 - 在多數的情況下，您不需要為 IRR 計算提供 guess。 如果省略 guess，將假設它是 0.1 (10%)。 --- # IRR 函數 先將現金流的資料整理好：  *現金流資料必須包含至少一個正數和一個負數 --- # IRR 函數  --- # IRR 函數 [官方文件](https://support.office.com/zh-tw/article/irr-%E5%87%BD%E6%95%B8-64925eaa-9988-495b-b290-3ad0c163c1bc) --- # IRR 函數的極限 - IRR的參數並沒有絕對日期，只有『一期』的觀念。 - 每一期可以是一年、一個月或一天 - 若現金流資料並非是定期的會出現錯誤 --- # XIRR 函數 若現金流資料沒有期數的概念，也就是每一筆資料的時間間隔是不固定的 可以使用 XIRR 函數讓 Excel 幫我們計算内部報酬率 --- # XIRR 函數 ```bash XIRR(values, dates, [guess]) ``` - values: 現金流資料 - dates: 與現金流對應的日期 - Guess: 猜測内部報酬率的值 --- # 但是... 若今天我們每一期的報酬率不同時，該如何處理？ --- # 規劃求解 一個 Excel 的進階功能，簡單來説就是讓 Excel 幫你解方程式 --- # 規劃求解 首先我們需要開啓規劃求解增益集  --- # 規劃求解 接下來到**資料 -> 規劃求解**把規劃求解的界面打開：  --- # 規劃求解 接下來我們把可以被轉成數學公式的問題用 Excel 公式寫出來：  --- # 規劃求解 規劃求解可以針對目標儲存格的，被公式計算出來的值進行： - 最大化 - 最小化 - 最佳化 --- # 規劃求解 但必須遵守工作表上其他公式儲存格之值的限制式或限制  --- # 求解方法 規劃求解有以下三種方法： - 單純 LP - GRG 非綫性 - 演化 --- # 綫性 vs 非綫性 - 若你的問題在轉成數學公式之後，是一個綫性問題，請使用**單純 LP** - 若你的問題在轉成數學公式之後，是一個非綫性問題，請使用**GRG 非綫性**或**演化法** --- # GRG 非綫性 vs 演化法 - 差別在求解的精準度，GRG 非綫性是使用登山法 - 區域最佳解 vs 全域最佳解 - 一般來説 GRG 非綫性已經夠用 - 若對 GRG 非綫性不滿意，可用演化法 - 若使用演化法，須給參數上界下界 --- # 練習1 用規劃求解找出第一與第二期的報酬率：  --- # 練習1 爲了增加一些限制，我們可以把規劃求解改成：  將猜測與實際金額兩者的差距最小化 --- # 練習1 找出飼料1、飼料2、飼料3 的比例，讓養豬的成本最小化：  --- # 自動化樞紐報表 樞紐報表（Pivot Table）是一個進階的 Excel 功能。可以將大量的資料，依照指定的資料樣式重新分類整合。只要確定了新分類的表格央視，透過拖曳欄位即可輕鬆產生新的報表。 請下載：[Lecture 10 樞紐報表範例](https://drive.google.com/uc?export=download&id=1b1XDi0rq3wf3x1ojcV6f3fUOClFNl_ta) --- # PivotTable 物件 ```vb Dim pTable As PivotTable ``` --- # PivotTableWizard 樞紐報表精靈物件，與手動建立樞紐報表的精靈類似。 建議輸入以下 ```vb Set pTable = ReportSheet.PivotTableWizard(SourceType:=xlDatabase, _ SourceData:=DataRange, _ TableDestination:=ReportSheet.Range("B2")) ``` 官網文件：[連結](https://docs.microsoft.com/zh-tw/office/vba/api/excel.worksheet.pivottablewizard) --- # PivotTableWizard 建議輸入以下參數： - SourceType 代表資料來源的報表，預設為 xlDatabase，代表 Excel 工作表 - SourceData 代表實際資料所在的位置，可以使用 Range 物件 - TableDestination 指定顯示樞紐報表的位置，可以使用 Range 物件 --- # 設定樞紐報表欄位 ```vb pTable.AddFields RowFields:="產品" pTable.AddDataField pTable.PivotFields("金額"), "產品類別加總", xlSum ``` 官網文件：[連結](https://docs.microsoft.com/zh-tw/office/vba/api/excel.pivottable.adddatafield) --- # 通道套利策略 股票市場的行情是隨機而起，一個價格的震盪到底隱含了趨勢或是雜訊？通道模型適度的解決了這個問題。它利用過去一段時間内的價格訊息，繪製出上下兩條通道綫(上、下界)，借此設定股價的相對高低界限。 --- ## 做空（Short） vs 做多（Long）  --- # 通道套利策略：範例 Excel 檔案 請下載：[Lecture8 - 通道套利策略 ](https://drive.google.com/uc?export=download&id=1gWbJINzBiKpjhpiucIs6PpFcgkpOft-R) --- # 通道套利策略：Donchain Channel Donchain Channel 流行於上個世紀 70 年代，由著名海龜交易員 Richard Donchain 發明，規則如下： - 假設短期的上漲或下跌，在最後會回復到常態 - 每天取**過去 20 日的最大收盤價為上界** - 每天取**過去 20 日的最小收盤價為下界** - 利用每天的**上界與下界的平均值當作中界** --- # Donchain Channel  --- # 通道套利策略：Donchain Channel Donchain Channel 有兩種策略 --- # 通道套利策略：Donchain Channel 策略 A： 1. 若收盤價大於過去 20 日收盤價的最大值，代表不正常上漲，所以就放空股票，直到回到中心點平倉 2. 若收盤價小於過去 20 日收盤價的最小值，代表不正常下跌，所以就做多股票，直到回到中心點平倉 3. 針對同一支股票，通道套利策略同時只能進行一種操作 (不可能同時放空與做多) --- # 通道套利策略：Donchain Channel 策略 B： 1. 若收盤價大於過去 20 日收盤價的最大值，代表目前是上漲趨勢，所以就做多股票，直到股價跌破中心點平倉 2. 若收盤價小於過去 20 日收盤價的最小值，代表不正常下跌趨勢，所以就做空股票，直到股價漲破中心點平倉 --- # 通道套利策略：Donchain Channel ***筆者碎碎念：大部分網路上的文件都是以策略 B 爲主，不過筆者自己的經驗是，不同策略適用與不同個股，請先將不同策略針對個股進行回測比較結果** ***備注：非投資建議，本課程提供的資料及交易策略，只可作為參考用途，學員在投資前，務請運用個人獨立思考做出抉擇，如因此招致任何損失，概與本課程無涉。** --- # 通道套利策略：Bollinger Band (布林帶) - **Bollinger Band(布林帶)**，假設股票的價格是服從**常態分佈** - 從分配的中心點，也就是平均值，往**兩邊加減兩倍的標準差的範圍，占了所有範圍大約 95% 的比例** --- ## 通道套利策略：Bollinger Band (布林帶)  --- # 通道套利策略：Bollinger Band (布林帶) - 短期的上漲或下跌，勢必會回復到常態 - 給予一段觀察期(通常是20天)，用前20日的資料算出平均值與標準差 - 若接下來的股價是超出平均值 + 兩個標準差，代表**不正常上漲**，所以就**放空股票，直到回到中心點平倉** - 若接下來的股價是超出平均值 - 兩個標準差，代表**不正常下跌**，所以就**買入股票，直到回到中心點平倉** --- # 配對交易（Pairs Trading） 若單邊做多，投資者的資產就容易受到市場波動影響，在非理性的市場中，這種波動所帶來的風險尤其難規避。 配對交易（Pairs Trading）就是為這種困境提供一種技能避險又能盈利的策略。其中又被稱爲統計套利交易。是一種風險小，又收益穩定的市場中性策略。 一般的做法是尋找兩支歷史價格相近的股票進行對冲， --- # Distance Approach （最小距離法） 最簡單易懂的一種配對交易算法，畫成圖非常的直覺 --- # 配對交易（歷史背景） - 1980年代Morgan Stanley的計量金融人員 ( quant ) Nunzio Tartaglia所運用的 模型 - Gatev, Goetamann與Rouwenhorst 等三位耶魯大學與波士頓學院的教授於2006 年做過實證研究，發現確實可以捕捉到市場的套利機會 --- # 範例 Excel 檔案 請下載：[Lecture 10 - Distance Approach](https://drive.google.com/uc?export=download&id=1eNzT4iqv5yf5ev3XPBiegnfuoYCBABBA) --- # 配對交易（Pairs Trading） 此方法先將該策略分為兩階段 : - 配對形成期 (Formation Period) : 即是使用該段期間來觀測選擇配對，通常時間長度為 一年。 - 交易期 (Trading Period) : 開始對所選擇的配對進行交易策略，通常為期半年。 --- # 配對交易（Pairs Trading） 如何找出配對的兩支股票？ 1. 算出過去同一段時間歷史價格差距的加總** 2. 時間歷史價格差距的加總越小，意味著兩支股票的走勢約接近 --- # 但是... 三支股票都是在不同的價格範圍内，這樣很難判斷到底那兩支股票的差距是最小的 因此，就需要利用報酬率，幫每一支股票的歷史股價標準化 --- ## 配對交易（Pairs Trading） 1. 若兩支股票的差距忽然不正常的變大，代表其中一隻股票被高估，另一支股票被低估 2. 若兩支股票的差距忽然不正常的變小，也代表其中一隻股票被高估，另一支股票被低估  --- # 完成版 [Lecture 10 - Distance Approach (完成版)](https://drive.google.com/uc?export=download&id=1rddyQzeuMZdSulG3tfjm-y3CYf_5UVpe)
Thanks for Watching
http://kyosei.ai
Contact: yuyueugene84@gmail.com
Download PDF
