Need help with the spreadsheets?
Post your question into our Google group. Add a link to the page where you got the spreadsheet from, so that others might find it later.
This is a little resource for algo traders and for classic manual traders who are backtesting an indicator or a strategy and want to see how profitable it is.
This is just a simple kind of backtesting report spreadsheet, not much there is “done-for-you”. But it’s versatile. You can use it to backtest your real past trades. Download the trading log from your exchange and see how you do.
Manual Traders: Ideas for using the backtesting report spreadsheet
This spreadsheet can be used on any market, even though it was made with crypto in mind. Here are a couple of things you can do with it:
- Make a bunch of copies of this and run it on buy/sell signals from a couple of TradingView indicators. Just load the indicator and read the signals off the chart.
- Run it on your own trading logs of different markets: Futures, small alt coins, big cap cryptos, etc. To see if you perform better in certain markets.
- Download your trading logs as CSV and in Excel, filter your trades by size. Then backtest separately your “large trades” and your “small trades” to see where you do better.
- Do the same for timeframe, if you can get that kind of information from your trading log.
Algo traders and developers: How to use this template
The spreadsheet here is only good as a report for backtesting that you ran somewhere else. It will let you compare the results easily as you refine your strategy. But it is not an automated Excel template where you do your programming magic and run the actual backtest.
If advanced, automated backtesting is what you want to do, I recommend looking into one of the backtesting platforms that now exist even in crypto. You won’t be able to backtest on a free account, but then you still need historical data to run a backtest in Excel, so it should still be worth it.
Coinpub is a website dedicated to crypto trading, so I’ll recommend the Coinrule platform for automated crypto trading and backtesting.
What data do you need to use the backtest report?
You need data in the format of a trading log: Date (of entry), Entry, Exit, Size.
This data you just paste into the columns that have the blue header. The columns that have grey header include formulas and change automatically - do not write into them.
If you are backtesting a homelab’d algo strategy and you have a script that produces a time series with buys and sells, you can’t use that with this template. You’ll need to code your output to provide the classic log format, the way you would get it from a crypto exchange.
Several things probably come into consideration.
You are possibly using several indicators that come together in various ways and make the signal to enter or exit. A common thing is also to have your script check on several timeframes. So, none of this matters.
Make your script so that it will produce just a log of trades it would make…This is the kind of data you need.
Your results
The evaluation script just gives you your profitability, the number of wins and losses, and the average money won and lost. The averages already take into account position sizing.
So, it’s all very basic.
You will get to see if your strategy is profitable, but you will need to go through your full logs and scripts to see where the losing trades happened and if something needs to be optimized.
And you will probably need to use some other tool in addition to this spreadsheet.
Backtesting Template
So, at last, here is the backtesting spreadsheet report.
Some general info on backtesting
How does manual backtesting spreadsheet help?
A backtesting spreadsheet is mostly to help you compare different strategies and what you expect from each of them.
Spreadsheets are meant for tabulating values, and there’s no end to the number of strategies that can be put through this backtest report.
The backtesting spreadsheet will help you see if your strategy makes sense or not, or just for an extended backtest to see if your strategy follows through momentum swings and keeps making profits regardless.
This backtesting spreadsheet can save you time in backtesting, but it is still not automated so compiling the backtest data will be required.
Use this backtesting spreadsheet as a checklist of what things to track when you run an advanced backtest.
It’s also helpful because price action is dynamic - there are news and market changes constantly happening that will affect prices.
And if your strategy does well in backtesting, then at the very least you have a good idea on where your edge is against other traders in the market.
Is manual backtesting better than automated backtesting?
No, backtesting is backtesting. I would recommend backtesting in both Excel and in a backtesting platform such as Coinrule because the backtest report generated by Coinrule is much more detailed than Excel’s backtest spreadsheet.
Excel’s backtest spreadsheet will generate your trade log file for you so you can analyze it to understand where losing trades came from and why profitable trades worked out well.
You will still need to manually go through and calculate profits or losses that happened during the time period covered by your backtest.
Ideally what we want is not only an explanation of how we made money, but also a reason why we made money through explaining market context like news, market sentiment, etc., which is something that backtesting platforms do as well as backtest spreadsheet.
Manual backtesting is different from automated backtesting in that automated backtesting has a system set up to automatically look for the next trade and enter it, which is impossible if you’re doing manual backtesting.
But people don’t usually want to spend years backtesting strategies and refining their strategy before they put it on the buy and sell orders - they will prefer to use a backtesting platform like Coinrule to see what works immediately.
Usually traders don’t backtest at all because of time constraints or lack of skills to make things easier with spreadsheets; I made this spreadsheet just so that people can learn how backtests work without having to worry about manually inputting data into a backtesting spreadsheet.