2021年10月19日

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 專頁發送私訊給我,同時也希望你能讚好我的專頁,給我一點鼓勵吧。

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

4 則留言:

  1. 實在太出神入化。
    請教一下,能找到每月那一天是最佳月供日嗎?
    我之前大概統計了一下,像SPY,好像每月的頭三天是每月的最低位。

    回覆刪除
    回覆
    1. 謝謝保羅兄。
      我覺得長線來說,分別未別很大。但未有測試過。

      刪除
  2. 非常實用, 多謝分享, 要時間研究下啲formula先 =]

    回覆刪除

命運是宿命嗎?從《無間道》講起!陳永仁注定要死?兵賊掉轉都係整定?

 《無間道》20周年,至今仍被譽為港產片天花板。 片中有好多隱喻,不說自明。但當中有個宿命主題,你又知不知道? 由兵變賊,由賊變兵,是命運注定嗎?如果陳永仁不做臥底,同樣會慘死?還是一個好警察? 從這套電影,講講命和運是甚麼,人生有沒有宿命。