Testing Moving Average Crossover Strategies in Spreadsheets
Summary
The article demonstrates how to use spreadsheet software to import historical price data, calculate a simple moving average, and model a trading rule based on candle bodies crossing that average. A position opens when a candle’s open and close fall on opposite sides of the average and no position is open; it closes when a later candle crosses in the opposite direction. The article also explains spreadsheet basics that support the workflow, including filling formulas down a data range and using relative and fixed cell references.
The author compares spreadsheet results with a tester’s profitability graph and reports that calculations on 5,000 versus 100,000 rows differed by about 1%, while the larger dataset took substantially longer to compute. This is an illustrative example, not a rigorous performance study: the excerpt does not give detailed assumptions, transaction costs, risk measures, or enough results to judge whether the strategy is profitable. The spreadsheet is presented as a quick prototyping and error-checking tool, especially for traders who do not program.
Key ideas
- A spreadsheet can import historical quotes and calculate indicators for a trading strategy prototype.
- The example opens a position when a candle body crosses a moving average and closes it on a crossing in the opposite direction.
- Relative cell references change when formulas are copied, while fixed references remain anchored.
- The author reports little difference between calculations on 5,000 and 100,000 rows in the cited comparison, despite longer computation time for the larger set.
- Spreadsheet results can help explore ideas and identify mistakes, but the example provides limited evidence about real-world profitability.
Tags
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.