Basic Backtesting Guide Using Excel

Are you looking for ways to do basic backtesting in Excel? It’s hilarious as we all have a love/hate sort of relationship with Excel. It is a perfect 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 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 fundamental data analysis and visualizing data and relationships on any computer without any additional setup.

Quick Tips

If you are a beginner, then align data into rows and columns with additional calculations referring to other data elements resulting in getting calculated results that are further tabulated. All this flow will help you in building a very solid intuition for the process.

We strongly suggest traders to understand any tool they usefully. At a bare minimum, if you use any indicator like Moving Average etc., then you should know how to calculate it manually. Forcing about every indicator calculation over excel will help you better understand 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.

Drawbacks:

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 application performance. The built-in statistics will give accurate results, but you might find yourself wanting more. There could be issues that are expected when you try to get something outside of its specs. But according to the experts, there are three major concerns.

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

As you would want a system to say, ” buy today, 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 what seems too good to be accurate as they are not valid. Tests that never work always appear 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, these are not enough to ask traders for complicated calculations, which becomes more complex 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 scratch and finding where the testing went wrong.

3. Data Managing in Excel is a very hectic task.

If you 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 daily, it is a hell of many tasks, and you will need an actual database. Excel is an excellent tool, but some needs you need to do better somewhere else.