Backtesting trading strategies in Google sheets
- Nov 16, 2025
- 3 min read
Backtesting is a process for any investor aiming to evaluate the effectiveness of a strategy using historical data before risking real capital. By simulating trades over past market conditions, backtesting can allow estimation of potential returns and fine-tune their strategy for optimal performance. While I’ve included a video at the end of this post, I’ll also break down the steps and explain them for anyone who prefers reading and wants to understand the process through reading.
1. Choosing the Asset Class
The first step in backtesting is selecting the type of asset to trade. Different assets have unique risk profiles, liquidity, and data availability.
- Stocks: Represent ownership in a publicly traded company. You can profit through price appreciation or dividends. Stocks are moderately risky—more volatile than ETFs but less complex than derivatives. They are a popular choice for both beginners and experienced traders due to accessible data and relatively straightforward mechanics.
- ETFs (Exchange-Traded Funds): Multiple stocks pooled into one asset, offer built-in diversification, hence considered low risk. Most ETFs also have sufficient historical data available online.
- Options: Contracts that let you buy or sell a stock at a specific price before a date. Options carry high risk due to time decay, leverage, and complexity, but they offer strategies like hedging and speculative plays. However, historical option data is harder to obtain for backtesting, especially in spreadsheet tools.
- Futures: Agreements to buy or sell an asset at a future date for a set price with leverage. Like options, they are also considered high risk. Historical data for futures is available from platforms like Dukascopy.
Each asset type serves different risk tolerances and trading styles. For beginners or those working within Google Sheets, stocks or ETFs are the most practical choices due to ease of access to clean historical data and manageable complexity.
2. Selecting the Time Interval
Time intervals play an important role in shaping a strategy’s risk and return profile. Short intervals (like 1-minute or 5-minute) are suitable for high-frequency trading or scalping, whereas longer intervals (like daily or weekly) are more suited for swing or position trading.
3. Downloading Historical Data
Google Sheets makes it easy to obtain historical stock data using the GOOGLEFINANCE() function. For other assets
- ETF prices - usually also available on google sheets
- Option prices - no historical data available as a table
- Futures prices - dukascopy.com historical data export
4. Choose an Edge
An edge is a repeatable condition or strategy that provides a statistical advantage in trading. Without an edge, a trader is merely gambling. A good edge helps increase your average returns overtime and reduces the impact of losses. Edges can come from patterns, news events, technical indicators, or inefficiencies in price action. For example,
Earnings Straddle: Buying both a call and a put option before earnings announcements, expecting a large price move regardless of direction.
Scalping: Making many quick trades to profit from small price movements, often on short timeframes like 1-minute or 5-minute charts.
Breakout Trading: Entering trades when the price breaks above resistance or below support, expecting momentum to follow.
Mean Reversion: Betting that prices will return to average levels after extreme moves (e.g., using Bollinger Bands or RSI).
Trend Following: Buying assets that are trending upward and selling those trending downward using moving averages or momentum indicators.
News-Based Trading: Capitalizing on rapid price reactions after economic reports or company news.
Whatever edge you choose, it should be clear, testable, and based on logic or observation.
Optimizing the Strategy
Once the basic simulation is set up, you can modify the rules (e.g., change the number of up/down days, add a moving average filter, or use RSI) to optimize returns. These variations can be tested by replacing and updating the conditional formulas.
Additionally, you can include a column to measure performance metrics such as:
Total return
Annualized return (CAGR)
Maximum drawdown
Sharpe ratio
These metrics help compare different versions of the strategy to identify which rule set performs best over the back tested period.
Traders can use Google Sheets and built-in data functions to model their edge and simulate past performance. While past results do not guarantee future returns, a rigorous back test provides critical insight and builds confidence in a strategy’s logic. Overall, this process is very variable and dependent on each individual strategy, so there is a video to help demonstrate. In this video, I backtest a stop loss strategy on SPY.
Comments