Backtest a 50/200-day crypto crossover in Google Sheets

A moving-average crossover is easy to describe and easy to backtest badly. The basic rule is to hold an asset when its 50-day simple moving average is above its 200-day average, and stay out when it is below. The useful test is not whether the rule looks convincing on a chart. It is whether it would have held up across the historical period you care about, using a consistent set of daily prices.

CryptoTrackPro supports historical price lookups and OHLCV history in Google Sheets. Use its historical lookup for daily candles, then calculate the averages and returns in neighboring columns. The add-on’s live-price formula, =cryptotrackpro("BTC-USD"), returns a live price; it is not a substitute for a historical candle series.

Set up the candle table

Install and authorize the add-on, then open it from Extensions → CryptoTrackPro → Open CryptoTrackPro to connect your Google account. For the historical data, use the add-on’s supported historical lookup for the coin pair and daily interval you want to test. The exact historical formula syntax is not specified here, so use the add-on’s current instructions rather than guessing a function name.

Place the returned daily candles in a table with one row per date. Keep the fields you need in separate columns: date, open, high, low, close and volume. For this simple crossover, the closing price is the key input; the other OHLCV fields are useful context, not part of the moving-average calculation. Sort the rows in ascending date order, with the oldest candle first. Check for duplicate dates, blank closes and gaps before calculating anything.

For a clean example, assume row 1 contains headers and the first candle is in row 2. Put the close in column E. Add “SMA 50” in F1 and “SMA 200” in G1. At F51, enter =AVERAGE(E2:E51). At G201, enter =AVERAGE(E2:E201). Copy each formula down its column. The first available 50-day average is at row 51; the first 200-day average is at row 201.

Mark the crossover days

Add a “Signal” column. Beginning on row 202, compare today’s two averages with yesterday’s. For example, if the averages are in F and G, enter this in H202 and copy it down:

=IF(AND(F202>G202,F201<=G201),"Bullish crossover",IF(AND(F202<G202,F201>=G201),"Bearish crossover",""))

This marks a bullish crossover when the 50-day average moves above the 200-day average, and a bearish crossover when it moves below. The comparison with the prior row matters: simply checking whether the 50-day average is higher would label every day in an uptrend as a new crossover.

Turn signals into a basic test

A straightforward first test is long-or-cash: hold one unit of exposure when the prior day’s 50-day average is above its 200-day average, and otherwise hold zero. Add “Position” in I1. In I202, use =IF(F201>G201,1,0) and copy down. That one-row lag means the position for the current candle follows the relationship known at the previous close, instead of pretending you could trade at a close before it was available.

Add “Daily asset return” in J1 and “Strategy return” in K1. In J202, calculate the close-to-close return with =E202/E201-1. In K202, multiply it by the position with =I202*J202. Format J and K as percentages, then copy both formulas down. A zero position produces a zero strategy return for that row; a one position passes through the asset’s return.

To summarize the test, calculate the compounded result rather than adding daily percentages. For a return range such as K202:K1000, use =PRODUCT(1+K202:K1000)-1, adjusting the final row to match your data. Google Sheets handles this as an array calculation. You can also count signal rows and compare the strategy’s compounded return with the asset’s compounded return over the same dates. Keep the start and end dates aligned for a fair comparison.

Read the result with restraint

This is a deliberately simple model, not a trading recommendation. It ignores fees, spread, slippage, taxes and the exact time a real order could be filled. It also assumes that the chosen candle history is complete and that the rule stays unchanged throughout the test. Add realistic costs and write down your execution assumption before using the result to compare strategies.

Daily candles do not make the sheet a live execution system. CryptoTrackPro’s automated sidebar refreshes are designed for price updates, not for making a historical backtest more reliable. For more on matching a spreadsheet’s update cycle to its use, see how to choose a crypto spreadsheet refresh rate.

Finally, rerun the same calculations on another asset or date range before drawing a conclusion. A crossover can look strong in one stretch and weak in another. The spreadsheet’s value is that it makes the rule and its assumptions visible, so you can inspect them before risking funds.