Basic Backtesting Guide for Trader Using Excel

basic-backtesting-guide-for-trader-using-excelLooking for ways to do basic backtesting in Excel. It’s very funny as we all have love/hate sort of relationship with Excel. It is a very good way of doing some quick and dirty testing. Along with Excel being very convenient because of its easy availability, it has some limitations too. However, the learning curve will be pretty short and quick; so you would be able to run some basic backtesting in a matter of no time.
It will be the first data analysis introduction to developing traders. Trader’s often come up asking – Why Use Excel? Features are its availability, allowing basic data analysis and visualizing data and relationships on any computer without any additional setup required. If you are a beginner then aligning data into rows and columns with additional calculations referring to other data elements resulting in getting calculated results which further tabulated. All this flow will help you in building a very solid intuition for the process.
We strongly suggest traders to fully understand any tool they use. At a bare minimum if you use any indicator like Moving Average etc then you should know how to calculate it manually. Forcing about each and every indicator calculation over excel will help you in better understanding the indicator and it’s working. Many new traders take the indicators as a magic line like “buying when a line cross” but once you understand what is being measured you would be more skeptical and few steps further down this road might be the path of enlightenment.
As there are multiple reasons for using Excel, we will talk here about understanding its limitations.


Excel is not built for data analysis only, so investors might need to stretch this tool to its limits, perhaps resulting in app crashes or poor performance of the application. The built-in statistics will give accurate results but you might find yourself wanting more. There could be the issues that are expected when you try to get something outside of its specs. But according to the experts, there are 3 major concerns.

1. Excel won’t be doing anything to protect you from yourself

As you would be wanting a system to say ” buy today is the market will be higher a week from Today”. Excel would easily refer you to the data from the future which we could not have known at this time. A tiny leakage in data would completely obsolete your test. Your first level of defense should be to always pay attention to the thing which seems too good to be true as they are not true. Tests which never works always appears to be fantastic in tests.

2. Complicated trading rules become very complicated.

In excel, we start with calculating simple measures on price, triggering trading rules, basic stats. However, all of these are not enough which ask traders for complicated calculations which turns into becoming more complicated than ever before, which is a very mind troubling task. So, traders need to always save their work in mids, as this can save the trouble of starting the process from the scratch as well as in finding where the testing went wrong.

3. Data Managing in Excel is a very hectic task

If you simply want to create a database out of historical data and do some A/B testing. It’s not hard at all. But when it comes to maintaining data in excel on a daily basis, then it is a hell lot of task and you will definitely need a real database. Excel is a very good tool, but some needs you need to do better somewhere else.

Leave a Reply

Your email address will not be published. Required fields are marked *