ATS.xls was last updated on 3/10/2013. The current Version is 1.1.05 See this video in addition to the below instructions.
This page last edited 01/20/2016: added bollinger band example
Instructions for Excel/VBA Automated Trading Platform and Backtest
below are instructions for both Live trading and Backtesting. Please not that you do not have to have an interactive brokers account to use backtest mode.
- Setup a “Paper Trading” account with IB (Interactive Brokers) for testing purposes (or use their demo account if you do not yet have an account. *Update 1/20/2016, see this post regarding using the edemo account with excel).
- Download and Install Trader workstation and the API from IB (Interactive Brokers)
- From the Settings form available from the Exceltrader Menu, Edit each field/Answer the questions and press save.
- Enter your own Indicators. Cells R-RV on “BarData” are reserved for you to enter in your own indicators/Formulas.
- Return to the “Live” worksheet and enter your indicator results (from Cells R-RV on “BartData”) so that Entry/Exit signals are generated.
- Test your System using Backtest Mode
- Test your System in Live Mode with your IB papertrading account.
- Trade your real account!
Use this guide to setup Excel, then continue with the instructions below.
- Go to Interactivebrokers.com. Click on the “Software” button. Click on “Trader Workstation”. Download and install the latest non beta 32 bit Windows Standalone version. (Update 01/20/2016, It’s easy to mistakenly install the 64 bit version that does not work with Excel (DDE). See this post for how to access the 32 bit version.)
- Go to Interactivebrokers.com. Click on the “Software” button. Click on “Application Programming”. Select the “Proprietary API” tab. Download and install version API 9.62 (or higher) for Windows.
- Open TWS (trader workstation) and login.
- In TWS select “configure” then “API” and put a checkmark next to “Enable DDE clients”
- Now go to C:IB_API_9_62Excel and make sure you have a file called TwsDde.xls. (Older default installation was C:jtsExcel. If you installed IB’s (Interactive Broker’s) API to a different directory Please go to Cell E6 on the “Live” worksheet in ATS.xls and change the folder path to the one where you installed the API. ) No longer needed as of version 1.1
Personalize your ATS.xls file:
1. From the Settings form available from the Exceltrader Menu of ATS.xls, fill in all settings by answering the questions and filling in all fields.
2. Next we move over to the “BarData” sheet where you will enter your indicators in Cells R-RV (of course you don’t have to use them all!). As an example, In columns R and S you will find two Simple Moving Average indicators. You can of course, delete these and enter your own.
Bollinger Band Example:
Select the “BarData” tab of ATS.xls.
- In Column W1, type “Upper Bollinger Band”, In Column X1 Type “Lower Bollinger Band”
- In Cell W150, Enter the formula =STDEV(E132:E150). Press enter
- Next add a multiplier. This is normally a value that you will experiment with. Let’s use 3 in this example. Type “Multiplier” in Cell W2 and the number 3 in cell X2
- Update the formula in cell W150 so that is includes our multiplier. =STDEV(E132:E150)*X$2
- We now have the Standard deviation * our multiplier. All that is left if to add (subtract) this to the existing simple moving average to obtain the upper (lower) band.
- Change the formula in W150 to =T150+(STDEV(E132:E150)*X$2)
- Add the formula to X150. =T150–(STDEV(E132:E150)*X$2)
- Error Proof the formula. Select Cell W150, In Excel click “View”>”Macros” and run the macros named “ErrProofSelected”. The resulting formula will now be. =IF(ISERROR(T150+(STDEV(E132:E150)*X$2)),””,T150+(STDEV(E132:E150)*X$2))
- Repeat step 8 for cell X150
- Select Cells W150 and X150, then drag the formulas up all the way to row 4.
- Now that the indicators in in place, the next step is to use these for BUY and SELL conditions.
- Select the “LIVE” tab. In this example, we’ll create a system where a BUY signal occurs when the last price is <= the lower bb and a SELL signal when price is >= the upper bb.
- Replace the existing sample formula in (Long Entry) cell “B30″ (LIVE tab) with =IF(BarData!E150<BarData!Y150,”YES”,””) where E150 is the last price and Y150 is the lower Bollinger band.
- Replace the existing sample formula in (Long Exit) cell “H30″ (LIVE tab) with =IF(BarData!E150>BarData!X150,”YES”,””) where E150 is the last price and X150 is the upper Bollinger band. (Follow similar steps for Short Entry and Short Exit.)
- To plot Bollinger bands on the chart. See this post.
3. The last step is to enter the results of your indicators on to the “LIVE” worksheet from rows 19 and lower. This page will look a little confusing at first but really it’s not once you become familiar with it. In this section you will find:
Row 23-40: Mandatory Conditions. As an example of a mandatory condition in row 24 you will see “Is the Market Open”. I use a simple formula in B24 to answer this question. Of course I want to only enter orders when the market is open. In the mandatory section (row 24-39) we will only place formulas that must = “YES” in order for an order to be sent, (otherwise we leave it blank).
The mandatory conditions in rows 30-39 are reserved for you indicator conditions all of which must be true in order to generate a signal. An example would be something like, simple Moving average 1 is greater than Simple moving average two AND MACD is positive AND Stochastic is over 70 etc etc.
Well what if you want to enter when ANY of the above conditions are true instead of all of them? Simply move the formulas to the “OR conditions” section, rows 43-52.
* Do not alter the formulas in row 40. This counts non blank cells which is the number of cells that must = “YES”
There are Four sets of columns (row 20 and below on “Live” sheet
In each of these columns you will see two columns titled “xxx Live” and xxx Backtest”. These separate columns allow you to either duplicate your live and backtesting strategy or keep them different for testing purposes. You will notice that the example above of having it mandatory that the market is open in order to generate a live signal is not needed for backtesting. In fact it would mess us up if we run a backtest when the market is closed. So in the backtesting columns we just leave this blank and we would also do this for any other condition not needed for backtesting mode.
*Row 21 is a very important row. This is where All of you logic from your entered formulas below are condensed down into a single cell. This is where the VBA code looks for a signal to be generated. It’s important not to delete or move this row. You can move or add rows below Row 21.
*Backtest mode includes slippage of one tick per order.
In order to run back tests you will need tick data.
- Recent data up through the last trading day is located in the right side bar under “Free ES tick data” (archive.zip).
- An even Larger file of older tick data is available in the right sidebar under “Free ES tick data” (archive.rar)
- Place your unzipped archive folders on the C (or primary) drive so that the path is C:Archive. For example
C:Archive/1-29-2009/data.xls etc etc. *merge both archive.zip and archive.rar into one “archive” folder if you download both
- Next go to the Settings form available from the Exceltrader Menu of ATS.xls and enter in the location of your Archive folder (if it is different than the default C:\Archive)
There are three ways to run a backtest:
Fast backtest – runs one day at a time quickly, but you can’t see anything happening until the test is done.
Backtest – runs one day at a time and displays every tick and buy/sell on graph .
To run either “Backtest” or “Fast Backtest” :
- Go to the top of Settings form available from the Exceltrader Menu of ATS.xls and select “Backtest” (the choices are backtest or Live)
- Open any Data.xls file in your archive folder.
- Press either Fast backtest or Backtest
*ATS.xls and one Data.xls file must both be open to run either Fast Backtest or Backtest
Backtest All Data – Runs backtest on all data.xls files. Completely unattended. Each days daily P/L is saved to a separate log file for later review.
To backtest your strategy over all tick data:
- Place the following log.xls file on your C: drive so that the path to the file is C:log.xls. (If you want your log.xls file somewhere else, enter the location in cell G7 on the “Live” worksheet.)
- Now open up ATS.xls and on the Settings form available from the Exceltrader menu, enter the start date and the end date that you want to run. Next press the “backtest all data” button available from the Exceltrader menu. (Note that you do not have to manually open a data.xls file like before).
- This will run your backtest. The screen will appear to freeze because screenupdating is turned off to speed things up, but you will see the data.xls files opening and closing every 5 seconds or so. When it’s done you can go to the log file and see each days results. I chose not to save every single trade just each days total Gross and Net PL. If you want to review a days individual trades you can always go back and rerun a single day.
Once you have successfully developed a system that backtests profitably and with no technical problems you are ready for Live trading.
- From the ExcelTrader Menu select Settings. On the settings form, select “Live”
- On the settings form enter your Papertrading username.
- Press the “Save Settings” button at the bottom right of the settings form.
- Open TWS and login
- Press Start Live Trading.
Once you have a thoroughly tested ATS, the only change you need to make to trade your live “real money” account is to change the user name in cell B6 and log in to that account via TWS