用 Excel 輕鬆建立投資回測（下）
前言：
上一篇中我們將資料匯入了Excel，在工作頁中有了歷史報價資料。本篇則是透過Excel函數算出報酬率，以及具體如何回測某個策略。重申一次，回測本來就不應該用Excel做，因此本篇都是用最簡單也最慢的方法的來做，只是提供初學者一個輕易入手的方式嘗試看看如何測試某種投資策略。
（上集連結：https://reurl.cc/9E9kQj）
大綱：
1. 如何使用Excel公式計算報酬率
2. 實際回測某種策略（以均線交叉為例）
如何使用Excel公式計算報酬率
以C5為例，從圖中可以看到，我們是從A1格的Ticker名稱，以及A5的買入日期，B5的賣出日期。取得兩個價格（買入價、賣出價），取得價格後計算（賣出價 — 買入價） / 買入價，就是報酬率啦。報酬率的計算公式很簡單，所以重點在於「我們怎麼從之前抓下來的報價資料取得我們想要的價格。」
這邊是取得買價的公式：
INDEX(Adjclose!$1:$1048576,MATCH(A5,Adjclose!$A:$A),MATCH(A$1,Adjclose!$1:$1,0))
可以看到我們主要用了兩個函數 ：Index以及Match。
Index函數有三個參數：（選取範圍，直行定位座標，橫列定位座標）
因此第一個參數「選取範圍」我們用了「Adjclose!$1:$1048576」，意思是選擇Adjclose這個工作頁（存放報價的地方）的全範圍。
第二個參數 MATCH(A5,Adjclose!$A:$A)則是將A5（買入日期），在Adjclose工作頁中的A行定位，Match的用途是在選取範圍中比對，看看目標是第幾個，因此我們可以透過Match得知我們的買入日期，在Adjclose工作頁中，是A行從上而下的第幾個，藉此取得直排座標。
第三個參數也是用Match，是定位我們的目標Ticker是從左到右第幾個，作法跟第二個差不多。但有一點要注意的是：
第二個參數：MATCH(A5,Adjclose!$A:$A)
第三個參數：MATCH(A$1,Adjclose!$1:$1,0))
除了一個定位第A行，一個定位第1列的差別外，第三個參數中最後多設定了一個「0」作為參數，這是Match函數中的Match type。
Match type詳見：（https://reurl.cc/g7oAqX）
主要的原因是，設定為0的意思是比對要完全準確，因為是Ticker，不應該有誤差。而第二個參數是在定位日期，很可能我們策略的買入日期在假日，假日沒有交易，報價資料不會出現Adjclose工作頁，因此我們會希望出現的價格是前一個交易日（e.g 禮拜五）的報價，所以不加「0」這個Match type。
而由於Match type的預設是「1」，也就是選取小於等於目標的上一個，所以我們就會得到前一個交易日的報價了～～當然你想要手動加個「1」，結果也是一樣啦。
接著賣出日期也是一樣的作法，至此我們已經可以計算報酬率了。
C5格完整的公式：
=(INDEX(Adjclose!$1:$1048576,MATCH(B5,Adjclose!$A:$A),MATCH(A$1,Adjclose!$1:$1,0)) — INDEX(Adjclose!$1:$1048576,MATCH(A5,Adjclose!$A:$A),MATCH(A$1,Adjclose!$1:$1,0))) / INDEX(Adjclose!$1:$1048576,MATCH(A5,Adjclose!$A:$A),MATCH(A$1,Adjclose!$1:$1,0))
看起來很複雜，但其實就是（賣出價 — 買入價） / 買入價而已。
至於報酬標準差，我們直接使用Aggregate這個集成函數，裡面有很多常用的統計方法，可以google看看。
實際回測某種策略（以均線交叉為例）
做好回測介面之後，只要把買賣日期填入，就完成回測了。然而重點是怎麼憑空生出這些日期？我們怎麼知道我們的策略在哪些日子要買進？
正常的回測方法當然是寫程式直接跑，然而Excel除了用VBA，要這樣可能會有困難，因此我們採用最土砲的方式，用Excel的複製貼上以及篩選功能，慢慢得到日期XD
這邊以我之前做過的某種指標的均線交叉作為例子（非均價線，但差不多）
從圖中可以看到，我們先建立好想要回測的指標的資料工作表，計算rolling 5日、10日的均值（深橘色部分前兩行），這邊也是用Aggregate函數就能輕易做到。重點在於最右邊那一行（L行），我們需要知道這個指標該日期是否出訊，出訊的話顯示日期，否則顯示False即可。
L3的公式：=IF(AND(K3>0.4,F3>1.5), A3, FALSE)
這邊很簡單，因為我用了K行跟F行的資料作為出訓判斷，所以在AND中設了兩個條件，不然直接用IF即可，若是條件成立的話，顯示A3（即日期），否則顯示FALSE。
到目前為止，L行應該充滿了「日期、FALSE、以及#N/A」，會出現#N/A是因為指標的原始資料可能有缺，EXCEL的函數報錯會顯示#N/A，因此我們的重點在於如何篩選出其中的日期，把多餘的FALSE跟#N/A去掉，這邊簡單用篩選跟複製貼上即可。
首先先複製整排L行，貼到新的工作頁。
接著再把整個E行圈選，用尋找中的「特殊目標」點選尋找空格，即可框起所有空白，再點選刪除即可，這時我們便可得到一排數字，這其實是用阿拉伯數字表達的日期，圈起來更改格式成一般日期即可。
費盡千辛萬苦，終於得到萃取出的日期了，複製到回測表的買入日期就好嘍，至於賣出日期，也可以用一樣的方法，但若是你的策略沒有特別的賣出日期，可以直接在回測表的賣出日期格設定公式：
=DATE(YEAR(A5)+1,MONTH(A5),DAY(A5))
意思是設定在A5買入日期的一年後賣出，這樣便會得到報酬嘍～～因為是使用調整價，所以不用考慮除權息的問題。而且因為我們計算報酬是使用座標定位，A1格的Ticker可以隨時改成其他檔股票，藉此比對同一個策略下不同股票的表現。（前提是你要測的股票有預先抓下來在工作頁中）
至於要做一般的價格交叉，譬如五日線突破十日線，原理也是一樣。重申一次，這樣做真的很麻煩，功能也很有限，譬如要計算部位權重可能就很難做，所以單純是提供初學者參考練習而已～～～
之後再慢慢介紹一個很強大的回測平台 —— quantopian，再之後有機會的話我再練習自己搭一個回測系統，但可能要非常久之後。
