19th Ave New York, NY 95822, USA

How To Create An Automated Trading System In Excel In 10 Steps

LOREM IPSUM DOLOR SIT AMET

The benefits of creating an automated trading system are huge. With a profitable trading robot you can spend more time doing what you enjoy and less time watching screens. You can trade quicker, smarter and without emotion.

how to create an automated trading system in excel and interactive brokers

Unfortunately, the pathway to creating an automated trading robot is a long one. Despite creating a number of useful trading systems in the past I have repeatedly hit a brick wall when it comes to implementing automation.

That changed last year when I was introduced to Peter Titus, a professional trader and expert in automation. Peter peter titusshowed me exactly what I needed. A series of logical steps that took me from beginner to advanced.

He taught me how to create algorithmic trading rules and alerts in Excel, how to size trades and how to send them directly to my Interactive Brokers account using the API.

In the rest of this article, I have teamed up with Peter to show you the steps needed to create your own trading system in Excel. Peter has also put together a comprehensive course that goes through each step in detail.

How To Create Your Own Trading Robot In Excel In 10 Steps

1. Open an account with Interactive Brokers.

Interactive Brokers are the only brokerage which offers an Excel API that allows you to receive market data in Excel as well as send trades from Excel.

IB is also the largest US electronic broker offering stock commissions of just $1 and a vast array of markets. If you want to automate your trading, then Interactive Brokers is the best choice.

To open an account with Interactive Brokers is straightforward via this link and is open to citizens of most countries around the world. A minimum deposit of USD 10,000 or USD 5,000 for IRA Account is typically required.

2. Download and install the Interactive Brokers Excel API.

The API allows the Trader Workstation (TWS) application to talk to Excel and is a prerequisite for building your automated trading system.

The API software can be downloaded from the following link:

https://www.interactivebrokers.com/en/index.php?f=5041&ns=T

Once you’ve downloaded the API you can proceed to download IB’s trading platform software Trader Workstation Latest (TWS):

https://www.interactivebrokers.com/en/index.php?f=16040

TWS Latest is now available for most operating systems, including Windows 64-bit and Mac OS. This and a copy of Excel is the only trading robot software you will need to automate your trading.

3. Think about how you can turn your trading rules into formulas you can use in Excel.

If you are already well acquainted with Excel then this step shouldn’t be too difficult but it will involve some careful consideration.

It’s important to think about your strategy and visualize what you want it to do. You don’t want to get sucked into the programming straight away then realise you’ve missed something fundamental and have to start again.

It’s a good idea to spend a day or two just thinking about your trading system and how it can be translated to Excel. I recommend plotting everything out on a big sheet of paper before you sit down at the computer.

If you’re not used to using Excel, or haven’t used it in a while, then you will want to spend some time getting to grips with it again. Here is a long list of formulas.

The course also goes over the essentials covering VBA, sub procedures, macros, loops, IF and OR statements etc.

4. Create and test your formulas.

Once you have an idea of what you want to do and what formulas you need, you can start plugging them into Excel and testing them out.

Once you have done this a few times you’ll be able to create your own trading rules in Excel from a completely blank work sheet. With the use of IF and OR statements, formulas and loops, it is possible to make complex trading rules relatively simply.

The Ranger 1.0 system developed by Peter contains many formulas and code snippets that you can pull from the spreadsheet, amend and paste into your own system.

5. Build automation to buy and sell when your rules are met.

Using the example trading system and template spreadsheets provided on the course, Peter shows how to build in the automation for your buy and sell rules.

Doing this on your own with a live account can be a daunting experience but Peter shows live examples of how to do it correctly. When trades are entered, Excel displays their order status and automatically checks for any setup errors.

Displaying market data and your trade entries side by side (just as they are in Interactive Brokers) gives you the confidence you need to run your automated trading desk and have Excel do all the heavy lifting.

6. Build time rules to manage the market open, the market close, and any other time of day criteria you have.

As you turn your system on and start to log data you will need to specify when to enter trades, how to manage your open positions and when to close them. The trade session can be separated into three parts; pre-market, the trading day and market close/after hours.

Key to this process is the implementation of timers and automated tasks to make sure your trades occur at the right times. Consideration must also be given to implementing stops and carrying positions overnight.

7. Trade with your simulated account while you debug your code.

Before you turn on your automated trading system in the live market, it makes sense to take it for a test drive first.

Fortunately, Interactive Brokers allows paper trading accounts which can be used to run the automation and see how the system is performing. It might be a good idea to run your system at a fairly high frequency at first as this will give you more opportunities to analyse performance and debug the code.

Once everything starts to look good, you can start analysing the system at it’s natural frequency.

Paper trade accounts can be accessed and reset in Interactive Brokers by going into Account Management then Manage Account > Settings > Paper Trading.

8. Once your automated trading system is running smoothly and is profitable, move it to real money.

Once the system is running as you want it to on the simulation account move it to real money and observe how it gets on. This is the exciting part where you’ll hopefully see your automated trading system making profits for your account while you sit back with your cup of tea.

When you go live, it pays to start off cautiously at first. Paper accounts can sometimes exaggerate performance for certain strategies because they don’t always accurately simulate slippage or market impact. By starting off small you can observe any difference in performance without risking too much capital.

how to build a trading robot in excel and interactive brokers. Ranger system live trades.

9. Increase your position size the more it wins and decrease it if it starts losing.

As you observe your automated trading system in the live market you will soon get an idea of its performance levels. The better the system does, the more confidence it will give you. You can slowly increase position size and start generating larger profits on your capital.

If the system starts performing worse than you’d like, you will want to decrease the position size. Under-performance could be due to changing market conditions or inaccurate simulation in the paper account, or some other reason. If this is the case, consider adjusting your system or using AI techniques to make it more dynamic.

10. Use automation to log all of your trades. Think about ways to optimize or improve your rules and automation.

Once your trading system is up and running you have the ability to log all of your trades automatically back into Excel. This gives you something that is extremely beneficial to algorithmic trading – the ability to analyse, observe and feed improvements back into the system.

By doing so you can seamlessly improve your trading system results and further eliminate stress. Using Excel to log the trades, you no longer have an excuse for failing to track your key statistics!

Find Out More

In this course, Peter goes through all of these steps and covers everything you need to create your own automated trading system in Excel.

He walks you through a simplified version of his day trading breakout system called Ranger 1.0 and allows you to borrow code snippets or build your own system from scratch using the tutorials inside the course.

Numerous resources, templates and lessons are included such as:

  • How to build automation through sub procedures in Visual Basic
  • An intro to VBA basics and how to automate any spreadsheet task
  • How to import data and do backtesting in Excel
  • How to begin using a basic trading system that is already profitable
  • How to trigger trades, set price targets and automate stops
  • How to download your own copy of Ranger 1.0
  • Use Ranger 1.0 to automate your own trading right away
  • Understand the code in Ranger 1.0 and be able to customize it to fit your own ideas
  • Add your own functions and algorithms to Ranger 1.0
  • How to automatically log trading data and automate setup procedures
  • How to build a decision-making AI in Excel that thinks like a human
  • How to run your system in automatic or manual mode
  • How to keep your orders hidden from the market with order management
  • How to set up trading alerts, timers and sounds
  • And lots more…

Once your automation is built, you no longer have to sit in front of the computer all day watching the market. Let your automation do the work for you and free yourself to enjoy your life!

How To Create A Trading Robot In Excel: Access Here


Comments (23)

do you have to sign up for a data feed from IB? Or do you get the data when you open an account?

Hi Bruce,
Once you have an account, IB provides real-time data for free or at the price charged by the exchange. There are small monthly costs from some exchanges. You can specify which ones you want access to with the Market Data Assistant.

How can I automatically buy or sell share

Is it possible to program a scalping robot for say the DJIA ? I’m a bit concerned on how Excel could be fed with real-time 1-minute bars…

Is it also possible to calculate the entry signals with the DJIA futures and tell Excel/IB to buy instead a structured product derived from this futures (a warrant for example)?

It is possible, but very difficult and beyond the scope of the course.

It will be great if you help me with following

1. I want to trade only NSE India stock can I do that?
2. If your answer is yes for 1. Can Ranger 1.0 system will be provided for download those who will enrol for the course.
3. Can I build a trading robot to trade NSE India Stock with Ranger 1.0

Hi Ashis

Yes, you can trade any instrument that is available through Interactive Brokers. The Ranger 1.0 system is fully downloadable in the course.
Thanks

Hello JB,

Could you program your buy signal on indicators ? on different time period ?

Example #1 (time period 5 minutes)
If stock (aapl) RSI >30 for 3 time period
wait for RSI to increase
Then buy

Also with the TWS API do we have access to our account data ?
Could we write a formula for the quantity of the buy ?

Let’s say :
(Account available for trade * 15%) * 2% / stockprice = Quantity to buy.

Thank you for your answers, this course look great.

Yes you can do both of those. You just need the formula to calculate RSI.

Is there a similar one for ETrade Pro Excel API ??

I have an intraday code in amibroker will I be able to plug that code Into excel and set up via the API?

It’s not as simple as that.

Is a current version of Ranger for use with Excel 365 available when taking this course? Are you periodically updating/maintaining Ranger in new releases? What exactly is the name of course for this tool, the cost, next availability and place to enroll?

Have emailed you.

I’m interested in setting up my own trading platform in Excel and this seems like exactly what I need. My only concern is that much has changed since 2017. For example, IBKR does not have the minimum $10k balance you say is required. Is this course still current and valid in 2020? Thanks.

Yes the course is still valid. You can reach out to Peter with any questions.

Hi Joe,
Related question. Is there an updated version of Ranger for the latest IBAPI? Thanks.

Hi,
Is there a similar one for ETrade Pro Excel API ?

Ref web site: CourseHope.com

I am seeing a course called Joe Marwood / How to Build an Automated Trading Robot in Excel – Peter Titus – How Build a Automated Trading Robot in Excel. Original price $125. On sale $35.

Is this the same course or am I missing something here?

What you are missing is that this is an illegal pirate copy that should not be allowed to exist on the internet. Unfortunately, no matter how many times you report these criminals, another one pops up in their place.

I would strongly advise not to buy anything these sites for numerous reasons;

1. They infringe copyrights and are illegal. It may even be illegal to purchase these products depending on your location.
2. You cannot trust them to provide you with the content you are paying for.
3. Would you really trust them with your credit card and personal data?
4. Their pirated material is poor quality and is usually incomplete and missing recent updates.
5. You miss out on the customer support and community that you get with the real product.
6. The original creator receives no benefit and therefore no incentive to continue creating worthwhile products.
7. By supporting these sites you are supporting illegal activities and often organized crime syndicates.

Totally agree!! No intention of ripping off Peter Titus.

Can you trade commodity “A” if commodity “B” is doing something else? In other words, can you program it to trade based on data of 1 or more other commodities are doing something?

Hi, I would like to learn how can I trade using excel. The link provided to buy your course doesn’t work. Could you please help here or provide some reference material.

Leave a comment