Google 試算表投資分析：回測每月買入策略，自動下載歷史股價，股票及ETF適用

這次示範怎樣利用 Google 試算表，回測每月買入股票／ETF這種定期定投的策略，長線的回報。Google 試算表有 =GoogleFinance() 函式可以下載歷史股價，只需輸入股票代號和回測日期，便能自動計算回報和繪畫圖表。

影片連結 👉 回測每月買入策略，自動下載歷史股價，股票及ETF適用

首先，建立一份新的試算表。在空白的位置輸入回測的起始日期、結束日期和股票編號。然後在另一列的空白位置，輸入以下的 GoogleFinance 函式，取得在指定期間的收盤價格。

=googlefinance(A1,”CLOSE”,B1,D1,”DAILY”)

股票編號，可以在 Google 財經網站，輸入公司或基金的名稱，然後複製搜尋結果裡的編號，但要緊記把當中的空格刪除。

接著，在另一欄的空白位置，輸入公式產生每個月份。方法有很多，在這裡我採用的函式是 =EOMONTH() ，找出前一個月的最後一天，再加上一日便能得到本月第一天的日期。

=EOMONTH($B$1,-1)+1

在上方功能表選擇格式 > 數字 > 其他格式 > 更多日期和時間格式，調整至只會顯示年份和月份，便完成設定。

之後每一個月，同樣採用 =EOMONTH() 找出上個月最後一天，再加上一日。把函式向下複製，這一欄便大功告成。

再在另一欄產生每月的買入日期。為了提供彈性，我在另一空格預留了空間，讓使用者選擇在每個月的哪一天買入。產生每月買入日期的方法也是有很多，我採用的是 Index 和 Match 這兩個函式的組合。設定好參數，它會自行找出當天的收盤價，如果那一天不是交易日，會自動跳到下一個交易日。

=index($A$6:$A,match(D6+$F$1–1,$A$6:$A)+1)

至於買入價格，同樣可以用 Index 和 Match 這兩個函式的組合，查詢當天的收盤價格。

=index($B$6:$B,match(E6,$A$6:$A,0))

最後一欄是累積買入平均價格，利用 =AVERAGE() 函式，查出由第一個月至當前月份買入價格的平均數，十分簡單。

=AVERAGE(F$6:F6)

為方便檢視策略回報，我再使用 Index 和 Match 的組合，配合 =MAX() 這個函式，找出回測期間的最後價格，以及累計平均價格，然後在另一空格計算回報。

最後價格：=index(B6:B3021,match(max(A6:A3021),A6:A3021,0))

累計平均價格：=index(G6:G3021,match(max(E6:E3021),E6:E3021,0))

你可以設定系統自動以不同顏色標示盈利和虧損，方法是在上方功能表選擇格式 > 條件式格式設定。

最後是製作圖表。選取月份那一欄，在上方功能表點擊插入 > 圖表。在這個範例我選擇「面積圖」這種圖表類型。資料範圍這一項，請選擇買入日期、價格和累積平均價格這三欄。X軸選取「買入日期」，數列則是「價格」和「累積平均價格」兩項。最後，選擇「自訂」，再於「系列」和「圖例」調整格式，便完成圖表的設定。

短短不到 10 分鐘，就能完成這份回測每月買入策略的試算表。只需調整起始日期、結束日期和股票編號，便能按需要回測不同的股票和基金。如果你想取得這份試算表的範本，請到我的 Facebook 專頁發送私訊給我，同時也希望你能讚好我的專頁，給我一點鼓勵吧。

究竟每月買入股票或基金，是不是長線可取的投資策略呢？我的看法是要視乎選擇的股票或基金，是否屬於長期穩定增長的類型。另外，即使是同一支股票或基金，在不同的時期實行這種策略，收報也可以有天淵之別。讀者不妨測試一下。