3 EXCEL進階功能.

Presentation on theme: "3 EXCEL進階功能."— Presentation transcript:

1 3 EXCEL進階功能

2 學習目標 分析藍本—特定變動因子的改變對整體的影響 資料表(舊版EXCEL稱為運算列表)—當改變的因子為兩個變數時對整體的影響
規劃求解功能—在特定的限制條件下求最佳解 分析工具箱 VBA的應用 EXCEL函數抽樣 以VBA抽樣

3 分析藍本 假如有一位國際指數基金經理人，擁有50億基金準備投資在四個主要的國際金融指數上面，分別是日本的日經225指數、巴黎證商公會CAC 40指數、道瓊工業以及台灣加權股價指數，四個指數的報酬率自2003年2月至2004年6月的期間的平均月報酬率分別為2.07%、0.89%、1.41%、1.52%，投資組合中持股比例各為0.25，為了觀察各別股票指數對於整個投資組合報酬率的影響，可以使用分析藍本來執行

4 不同藍本:改變持股比重 步驟一：先定義名稱例如選取儲存格B4，利用 插入名稱定義，定義名稱為『持日股比重』
步驟二：工具分析藍本，先給定第一個藍本的名稱為『改變日股比重』，接著選擇變數儲存格為『B4：E4』就是基金在四個國家指數上的持股比重

5 不同藍本:改變持股比重 步驟三：對持股比重做設定，假設將日股的持股比重提高到40%，而降低台股的持股比重到10%，其他兩種資產維持原來比重，在對應的空格中輸入所要的設定，輸入完按下『確定』 步驟四：再設定其他藍本 ，將法股持股降到15%；同以上的步驟進行，『改變美股比重』藍本中將美國道瓊比率降到10%

6 不同藍本:改變持股比重 步驟五：在各個藍本都設定好之後按下『摘要』，接著選取目標儲存格變數於儲存格F2
在比對各個藍本之後，會發現變更美國持股比重藍本對整個基金報酬率貢獻達到最佳。第二高藍本為改變日股比重

7 資料表 本範例為已知在各種經濟景氣下的A、B兩種投資標的的之報酬率，若想知道在各種不同的資產配置之下，A、B兩種投資標的的之投資組合的報酬率與風險之關係為何？

8 資料表 步驟一： 步驟二： 步驟三： 預先選取儲存格A13~B24範圍
先在儲存格B13鍵入『=F7』得到投資組合報酬之標準差，在儲存格D13鍵入『=E7』，得到投資組合報酬率的期望值E(RP)。 步驟二： 然後預先在儲存格 A14至A24輸入數值，於儲存格A14中鍵入0.0，往下儲存格以增量0.1填入，直至儲存格A24時填入值為1，當A股的持股比率為0的時候，表示將全部的資金投入到B股上。 步驟三： 預先選取儲存格A13~B24範圍

9 資料表 步驟四：在工具列中的『資料』『假設狀況分析』『資料表』，出現資料表對話方塊後，請在欄變數儲存格的內容選擇E9儲存格，同一欄內表示不同的持股比重配置(A14~A24)將產生不同的投資組合報酬率 步驟五：在按下確定後整個被圈選的範圍（B14~B24）將被填滿不同的值，每一個值都表示對應該持股配置比重下的投資組合標準差

10 CAPM圖 為呈現不同的持股比重突顯出投資組合風險與報酬的非線性關聯，將水平軸設定為風險，縱軸設定為報酬，展現風險與報酬率關係的CAPM圖型

11 EXCEL規劃求解 目標函數（forecast variable） 決策變數（decision variable）
限制條件過濾（Constraints Filter） 其他條件過濾（Requirements Filter）

12 求最小投資組合變異數的持股比重 假設持有三檔股票1、2、3，其過去的平均報酬率、報酬率的標準差及相關係數，現求一最適持股比重，能夠讓整體投資組合變異數達到最小

13 求最小投資組合變異數的持股比重 步驟一： 對各區域進行名稱定義: 公式->定義名稱

14 求最小投資組合變異數的持股比重 步驟二：計算共變數矩陣，以HLOOKUP函數計算
步驟三：計算投資組合報酬，在儲存格B19鍵入『=SUMPRODUCT(個股報酬率，資金配置比例)』 步驟四：計算投資組合變異數，請在B21儲存格鍵入『=MMULT(資金配置比例,MMULT(共變數矩陣,TRANSPOSE(資金配置比例)))』，即可求得投資組合的變異數

15 求最小投資組合變異數的持股比重 步驟五：呼叫規劃求解視窗，設定目標變數在上方的『設定目標變數儲存格』選取儲存格B21，也就是投資組合變異數，且選取『最小值』 步驟六：設定各類限制條件 步驟七：在每一個限制條件以及目標變數均設定好之後，按下『求解』鍵，會出現一個『規劃求解結果』對話方塊，選取『保留運算結果』便可得到三個最適的資金配置比率

16 EXCEL-VBA VBA為Visual Basic for Application的縮寫，透過Visual Basic程式碼的撰寫，將想要執行的指令依附在特定Office文件進行資料的處理與轉換等程序，例如重複運算的資料步驟繁瑣，例如巨集（Macro）即為運用VBA語言所撰寫出來的應用程式

17 VBA巨集做法 巨集的做法有三種，一種是以VB編輯器撰寫巨集指令，另一種是利用錄製巨集的方式，電腦會自動幫您紀錄其過程，撰寫成電腦型態的程式語言，而第三種則是呼叫控制工具箱，選取適當的物件，在物件中撰寫程式碼

18 VBA程式碼基本撰寫 進入VBA的設計模式後會看到Microsoft Visual Basic視窗，整個程式碼的與物件的編輯都在此視窗中操作，大致上可以分為三大結構，專案管理區、屬性視窗區以及程式碼撰寫區

19 常用物件 Cells — 表示儲存格，Cells（列數, 行數），例如Cells（2 , 3）表示試算表中的C2儲存格。
Range — 表示某個特定範圍，例如Range（ Cells（1 , 1），Cells（2 , 5）），表示儲存格A1到E2的範圍，當你要整個範圍排序，或者清除內容時，這個屬性的使用是很重要的。

20 迴圈的使用 迴圈是所有程式語言的基礎，藉由迴圈的結構才能把電腦大量且將複製的強大功能發揮。在這裡只先介紹簡單的迴圈For-Next結構
For xx=1 to 8 Cells(xx, 1)=xx+1 Next xx

21 控制程式的邏輯的處理 IF –THEN敘述 槽狀IF敘述 IF-THEN- ELSE GO-TO敘述

22 EXCEL與抽樣 重複抽樣—使用RAND函數 ROUND（儲存格位置，取到小數點的位數） RANDBETWEEN函數

23 VBA撰寫不重複抽樣 VBA中的不重複抽樣語法必需藉由迴圈來達成，並且使用一些判斷技巧，例如可以再已經抽過的撲克牌旁邊做上記號，再用該記號判斷是否已經抽出

24 樞紐分析表 樞紐量表是Excel中專責處理交叉分析的小工具，更夠迅速的將結果分類，整理出經過分組或者歸納後的資訊
範例:以台積電、聯電的股價關聯性為例，先蒐集2008年1/7日至3/18日兩檔股票每日收盤價格，一共有45個交易日，可產生44筆兩檔股票漲跌紀錄。現在如果想知道，兩者之間的漲跌是否存在高度的相關，一檔上漲另一檔易跟著上漲

25 樞紐分析表 計算第一筆漲跌記號的D4儲存格鍵入IF函數，目的讓分析者識別當日個股的動向，於是在該儲存格中鍵入: =IF(B4>B3,”漲”,”跌”) ，可以先圈選已經完成區域(D4:E4)，再利用儲存格控制填滿點，將公式迅速複製到全區域

26 樞紐分析表 先將整個資料範圍，包含欄位名稱均圈選起來，然後在工具列中找尋”資料”下的”樞紐分析表及圖報表”項目

27 樞紐分析表 選取輸出樞紐量表位置儲存格後，再選取”版面配置”按鍵
右邊會出現「聯電漲跌」、「台積電漲跌」和「次數」三個欄位。分別把「聯電漲跌」按下滑鼠左鍵拖曳至”欄(C)”，將「台積電漲跌」拖曳至”列(R)”內

28 樞紐分析表 44個交易日紀錄裡面，聯電和台積電同時下跌的有17天，而二者同時上漲的有12天。一漲一跌的情況各為6、9天，歸納結論發現，兩檔股票之價格連動程度明顯

29 Summary 以分析藍本來控制單一變動的因子對於所關注的目標變數（例如：投資組合報酬率）的影響，到可同時觀察二維變數更細緻的變動對於目標變數影響的資料表(運算列表)分析 EXCEL-VBA功能導入，介紹了VBA程式碼基本撰寫如常用物件、迴圈的語法等 樞紐分析表