Portfolio Backtesting allows a trader to construct portfolios using Stocks, Bonds, ETFs, etc; and simulate a trading strategy using historical data. The results help gauge the effectiveness of the strategy. In this article, I am sharing a Google Sheet template to backtest a portfolio and compare the returns to the S&P500 benchmark.
I have used a variation of Stocks-Bonds mix portfolio using ETFs.
The Google Sheet can be used to backtest portfolios such as the classing 60% Stocks — 40% Bonds or Ray Dalio’s all-weather portfolio which can be constructed using ETFs
All-weather portfolio example:
Stocks 30% — VTI; Long term bonds 40% — TLT; Intermediate Bonds 15% — IEF; Commodity 7.5% — DBC; Gold 7.5% — GLD
The only function required here is GOOGLEFINANCE.
GOOGLEFINANCE is a real-time function that fetches current or historical financial security data. The syntax of this function is:
GOOGLEFINANCE(ticker, attribute, start_date, end_date)
For example, GOOGLEFINANCE(MSFT, “price”, Today()-30,Today())
The first argument is mandatory which can be a ticker of stock, bond, ETF, or mutual fund.
Feel free to make a copy of the sheet to backtest your trading ideas. You would be able to tweak asset allocation or period to determine what would have performed the best historically.
