Tradinformed Backtest Models are built using Excel formulas. They can be opened and used in most modern spreadsheet software. The guide below will help you to get started and begin testing your own strategies.
What you will learn
- Spreadsheet Skills
- How to Add Data
- Changing the Technical Indicators
- Changing the Strategy
- Further Resources
See the latest Models in the Tradinformed Shop.
Copy and Cut
In Excel, and other spreadsheet software, we use copy and cut along with paste. These functions are highly useful and allow us to move data around quickly and easily. However, it is important to realise that copy and cut are not interchangeable.
Copy is used to move data into, out of, and around the spreadsheet without affecting any incoming formulas or links. In a Tradinformed spreadsheet copy is used very often. Copy is particularly useful when we are using relative references (see below).
Cut is used when we want to move data and move the formulas that point to this data. Cut is particularly useful when we are using absolute references (see below).
One of the defining feature of a spreadsheet is that one cell can reference another. A cell containing =C4 is referencing the value contained in cell C4.
In a Tradinformed spreadsheet almost all of our formulas use references.
Absolute and Relative References
Tradinformed backtest models use relative and absolute references. They are both useful in their own way.
Relative references are the default when we enter a formula. Eg. =B2 + C2. If we copy this formula to the cell below it becomes =B3 + C3. So the references are relative to the cell that they are entered in.
Absolute references have a $ symbol. Eg. =B2+$C$2. If we copy this formula to the cell below it becomes =B3+$C$2. So the reference to cell C2 is now absolute and it does not matter what cell it is entered into. It will always point at Cell C2. Cut can be used to move the cell that an absolute reference points to. So if I cut the data in cell C2 I could move it to another part of the spreadsheet.
The reference would now point to this new cell.
See how this is done by watching the following video:
Excel is very flexible and allows us to insert columns without changing our formulas. The formulas will automatically adjust to accommodate the new columns. This means that we can expand our Tradinformed models very easily using the Insert Column function.
Care must be taken when deleting a column. If that column is being referenced by a formula in the model then you will end up with a #REF! error. This means that there is a reference error and you will need to find the formula that is causing this error. Use Ctrl + F to search for #REF to find these broken references.
How to Add Data
Historic data is the basis of all backtesting. In a Tradinformed backtest model the historic data is always at the left of the spreadsheet in the Backtest tab.
Historic data is available from many sources. Yahoo Finance is a fabulous free resource. For Forex trading strategies, historic data can be obtained from MT4 by going to History Center. There are many other free and paid-for sources of data.
Historic data is often in spreadsheet format, often as a .csv file. This means that it can be opened directly in Excel.
If the data is in text format .txt then you can use the Import Text File tool to get the data into Excel.
Most historic data is in the format:
This is the format used in a Tradinformed model and it is important that new historic data is in this format. If the data is not in this format you can cut and paste columns to arrange it correctly. Not all models will have a Time or Volume column. If you want to include these columns simply insert a new column and add the data.
Historic data in Tradinformed backtest models is usually arranged from oldest to most recent. Some historic data (e.g. from Yahoo Finance) is supplied from most recent to oldest. Rearrange the data by highlighting Column A and then Sorting A-Z.
Inserting the Historic Data
The simplest way to insert the data is to copy and paste it into the Tradinformed backtest model.
I have created a video that demonstrates how to change the data. You can view this on YouTube by clicking on the video below.
Changing the Technical Indicators
Many trading strategies rely on technical indicators, candlestick patterns and chart patterns. Many of these can be calculated using Excel.
Tradinformed Amazon Kindle eBooks
For a good range of indicators, candlestick patterns and chart patterns, there are Tradinformed eBooks available in the Amazon Kindle Bookstore. These books come with a free spreadsheet containing all the technical indicators.
You can also buy the spreadsheets directly from the Tradinformed Shop.
How to Copy in a Technical Indicator
The formulas for the technical indicators can be copy and pasted in the Tradinformed backtest model.
Before copying we need to make sure that the source spreadsheet and destination spreadsheet are set up the same.
Firstly, make sure that the price data is in the same columns in the source and destination. If the columns are different insert a column to make sure they match.
Secondly check how many columns are needed for the indicator and what letters they are. So the Stochastic Oscillator calculations might require 5 columns from G-K. Use the insert columns function to make blank columns in the Tradinformed model in columns G-K.
Thirdly, highlight the cells in the indicator spreadsheet and then copy the formulas. Make a note of the cell in the top left of the highlighted cells. Now paste the formulas into the backtest model starting from the same cell that you copied them from.
Watch the YouTube video on how to add a new technical indicator.
Changing the strategy
Changing the strategy means changing how the strategy enters and exits the market. This is the most difficult part of testing any trading strategy.
Changing the Entry Criteria
The formulas for the entry criteria are contained within the columns titled Trade Long and Trade Short. A basic long entry is confirmed with the text “Long” and a basic short entry is confirmed with the text “Short”.
For example. If we want to enter on a MACD crossing we might have the formula template:
=IF(AND(current MACD > 0, previous MACD < 0), “Long”, “”)
The above formula will enter a long position whenever there has been a crossover from below to above the 0 line.
Changing the Exit Criteria
The basic exit signals are shown in the columns titled Close. Here you can set any type of close. If you want to exit a trade when the Close Price was above the Bollinger Band, the formula template would be:
=IF(AND(Close Price>Current BB,previous Close Price< previous BB),”Close”,)
Often we might want to have some other criteria in the formula. For example most Tradinformed models will only open a position when there are no open positions. Open positions are shown in the Long Trade Running and Short Trade Running columns. This column will normally show either 1 (1 position open) or 0 (no positions open). We check there are no positions open by adding a formula that checks that the Trade Running column is 0.
I have created a video that demonstrates how to change the entry criteria. You can view this on YouTube by clicking on the video below.
Example: How to Use a Tradinformed Model
This article gives some further insight into how to use Tradinformed models: Backtesting a Trading Strategy
The Tradinformed Shop
There are a variety of different Tradinformed models available. Check out the Tradinformed Store to browse the available models.
If you would like to develop a specific model and think that you need some consulting advice. Feel free to Contact Me to discuss this further.
Good luck with your Trading!