How to Build an Automated Trading System
In Excel, Python, and Build Alpha

It is no secret that many traders fail to achieve success and a level of consistency. The persistent traders will eventually understand they need to quantify what their edge is. This inevitably leads them down the road of systematic or quantitative trading but with no direction on how to begin. 

I am often asked how to build an automated trading system or how to create a trading algorithm or become a software trader. In this post, I will walk through testing a simple two rule system for the SP500 using Excel, Python and Build Alpha. The goal is to show how simple investigating quantifiable edges can be. 

I will use the same simple trading algo idea in all three platforms to hopefully show some algo trading 101 level steps in each platform. The strategy will buy when IBR (defined later) is less than 20 and the closing price is below the 10 period simple moving average. It will hold for 1 day and will not have any position sizing or risk management. Very simple to create.

We will also create visuals for the equity curve (P&L graph) and drawdown.

How to Create a Trading Algo in Excel

Starting with excel makes the most sense as the majority will be most familiar with it. I will share many screenshots and explain the steps one by one. If you are an excel expert this may feel like a slow walk in the park – that is by design!

Step 1: Open the data file in Excel. Finance data is almost always going to be displayed in this format of Date, Time, Open, High, Low, Close, Volume (and Open Interest if futures or options). It is often referred to as OHLCV data. 

Step 2: Calculate IBR or Interbar Rank. This is sometimes referred to as IBS or Interbar strength. It is a 1 period stochastics where we view where the close is in relation to the bar’s trading range. An IBR of 20% or lower would mean the close was in the lower 20% of the bar’s range.

The formula is simple and can be seen below. It is the difference between the close and the low of the bar divided by the range of the bar. Please note I added 0.0001 to the denominator to avoid any divide by 0 errors when/if the high and low are equal to each other. I have also multiplied IBR by 100 in order to express it as a percent.

After typing in the formula, drag this value down for all rows of data in order to calculate the IBR for each bar.

Step 3: Calculate the 10 period Simple Moving Average. Go down to the 10th bar and add the formula below. Drag this down for all rows of data to calculate the 10SMA for each bar.

Step 4: Let’s add a new column and calculate our 10 period SMA signal. I have named this column Signal1. We will check if the current close is below the current SMA.

In order to check this, we can use an excel if statement. If the close is below the SMA return a 1 else return a 0. 

Step 5: Adding another column named Signal2 in order to calculate our IBR signal. Again using an if statement to check if the current IBR is less than or equal to 20%. That is, if the current bar’s IBR is less than 20% return a 1 else return a 0.

Step 6: We need to combine our two signals into one signal. That is, whenever we close below the 10 SMA and have a weak close in the bottom 20% of the day’s range we want to return a 1 otherwise return a 0. A 1 would mean a green light to trade and a 0 would sit us on the sidelines as we have no edge.

All we need to do is check the sum of the previous two columns we created. If their sum is 2 then we return a 1 else we return a 0.

Step 7: Next let’s calculate the raw one day returns on the SP500. We have been using the closing price in all of our signals so we cannot truly know if our signal(s) are true until the close of the bar. Thus we cannot enter until the next bar’s open. 

Let’s assume we get a true signal on Monday’s close. We would then enter on Tuesday’s open and hold for one bar exiting on Wednesday’s open. We could exit on Tuesday’s close but for simplicity’s sake we won’t in this example.

Our excel function checks if we have data the next two days by checking if the opening price is non-zero and exists. If we have data, we can then subtract tomorrow’s open from from the open of two days from now. That is, take the difference of Wednesday and Tuesday’s opens per our example and store it in Monday’s row.

This gives us a data column that says, if we have a signal on this bar and buy the next open, hold for one day, what would our return be?

Step 8: Now let’s get our trade returns. If we have an actual signal in our ‘SignalFinal’ column from our two indicators what would our return be. 

I also multiplied by 50 to represent the point value of the SP500 emini futures contract. That is, if you buy at 3346 and sell at 3347 then you’ve earned $50 per contract not $1 per contract. Each futures contract has a point value and this one’s is 50.

Step 9: Let’s copy our Date column and Trades column into a new tab. I clicked on the ‘A’ above ‘Date’ to highlight the entire column. I then hold ctrl and click on the ‘O’ above the ‘Trades’ column. This should highlight both columns.

Press ctrl + c to copy. Open a new tab and press ctrl + v to paste.

Step 10: Let’s do some house-keeping to remove the days we did not trade. We first need to sort by our Trade column and delete all days with “—” in the cell.

After deleting non-trade days, I then re-sort by date from oldest to newest.

To sort please highlight the columns, go to the data menu and select sort. 

Step 11: Time to create our P&L Graph. We need to add each trade value to a rolling sum of all trade values. The simple formula is below.

Drag this formula down to calculate the equity curve’s value after each trade.

Step 12: In order to calculate the drawdown of our simple trading program we need to first calculate the high watermark or the rolling maximum amount our account would have achieved following this simple trading system.

Once we have the maximum or high watermark, we can then subtract our current P&L from the maximum to determine the current drawdown. I have added new columns for the maximum P&L (MAX) and the Drawdown (DD).

Step 13: Now we can plot our P&L Graph and Drawdown. Simply highlight the column, go to the Insert menu and select a line chart.

How to Build a Trading Algo in Python

I have done a few other blogs on how to read a text file in python using pandas but this example will show not only how to read in data, but how to complete all the steps above in one simple python script. That way you have a very rudimentary framework for testing automated trading systems and creating trading algos in python. Hopefully this serves as an intro example of how to backtest a trading program or strategy.

Step 1: Create a new file, import our plotting library and pandas. Matplotlib is arguably the most popular python visualization library.

In the pandas read_csv call I have specified the file I’d like to read in, how to separate my columns, and what column should be the index of my pandas dataframe. 

Step 2: Let’s calculate our indicators. To calculate IBR we will use list comprehension which is a very cool python trick. It allows us to do a for loop over our entire dataframe all in one line of code. The calculation is the same as excel and will create a dataframe column named IBR as we had in excel.

We will then use the pandas rolling function to create our 10 period simple moving average (mean). I have saved our moving average values in a new dataframe column named ‘SMA’ similar to excel.

Step 3: Following similar steps as we did in excel, we can convert our indicators into actionable signals using ‘if’ logic. We will use python’s list comprehension feature which allows us to loop through our entire dataframe in a single line of code.

For instance, we will create a new dataframe column named Signal1 which will store either a 1 if IBR is less than 20 or a 0 if it is not less than 20.

Step 4: Similar to excel’s footsteps let’s continue creating our python trading algo by calculating the returns and then mapping our returns and signals into actual trade results.

First, we can use pandas built-in shift function to access tomorrow’s opening price and the opening price from two day’s from now. This is similar to how we calculated returns in excel.

To create the trade results, we need to know if there was a signal or not. We can multiply our SignalF column and our return column. If we have a signal we will have a trading return and if we do not have a signal (0) then the return will get zeroed out. I’ve also multiplied by 50 to account for the SP500 emini future contract’s point value to convert our Trades into actual dollar values for 1 contract.

Step 5: In excel we had to copy and paste our trade returns into a new tab, sort them, remove days we did not trade, etc. In Python we can simply create a new pandas dataframe, named Trades, and filter our original dataframe. We will simply return only the rows of our original dataframe where our signal was equal to 1. That is, only returning the values when we took a trade.

Step 6: To calculate our P&L and drawdown we can use the following two simple lines of code. Since our P&L is simply the cumulative sum of our trades we can easily just call the cumsum function on our Trades column. 

In order to calculate the drawdown we can just subtract our current P&L from the rolling cumulative maximum P&L. Python and pandas has a built-in cummax function we can use which will save us from creating an additional column to store our maximum values (high watermarks). This means one less column than excel.

Step 7: Plotting in python is simple. We already imported matplotlib – our plotting library – in the first step of this python walk through. Now we can call the pandas plot function, specify that we want to use subplots, and then display our plot with the show function.

How to Build a Trading Algo in Build Alpha

Step 1: Configure Build Alpha’s main screen. Set the symbol to ES which is the symbol for the SP500 emini futures contract.

Set the date range to start in 1997 and end near Sep 2020 to match the same data used in excel and python.

In the lower left set the max holding time to 1 bar in order to again match excel and python.

Step 2: Let’s select our signals. Type IBR into the ‘Filter’ search bar near the top. Scroll down and select the signal IBR <= 20 as an ‘Entry’.

Then type in SMA in the ‘Filter’ search bar near the top. Scroll down until you find the signal Close <= SMA(10). Select this signal as an ‘Entry’. 

Then hit Simulate!

Step 3: In the Results window you will view our two rule strategy at the top (highlighted in blue). Double click on the strategy to view the P&L graph. Toggle drawdown off and on by hitting the drawdown button.

What's Next?

Some logical next questions might be how can I add a stop? How can I add more signals? How can I trade automated strategies after I’ve tested in excel or python? I encourage you to try adding on to this simple trading program idea in both excel and python. Then there can be a greater appreciation for how simple Build Alpha can make things!

Want to add a stop? Click a button. Want to add a signal? Click a button.

I am often asked how to build automated trading systems or how to create automated trading systems in excel or what is the best automated trading software and what software do professional traders use, etc. This blog post and the rest of the Build Alpha blog can answer those questions. The information is out there, the tools are out there.

Trading is not easy, but it is simple. Hunt for edges, collect them, execute them.

If you want access to the python script or the excel sheet please send me an email at david@buildalpha.com.

Another Success Story [$30,000+ in one month]

I am back! I want to share another Build Alpha success story with you guys for a few reasons. First, I have been very quiet on social media, etc. lately and that is because I do not need to promote Build Alpha every day (although I probably should). I haven’t tweeted in month(s), but I have maintained my typical support, however. Anyways, think twice about traders that push their service, product, software, etc. every single day – it means they rely on it. Secondly, this Build Alpha user is the EXACT reason why I started on this journey of making Build Alpha publically available. His story also echoes my own – which is why I am so fascinated by it and could not resist sharing it with everyone.

First, let me reiterate my main goal with Build Alpha, the one written on the homepage of the site  since day one: “Bridge the gap between the programming world, the quantitative trading world, and the money manager/trader who seeks to evolve with the times.”

In other words, can I take someone with no programming/trading experience and help turn them into a successful systematic trader? That is what I set out to do. I know the depths of the trader struggle and I want to pull as many traders as I can from that pain.. because man, do I still remember it like it was yesterday!

I do not wish to teach programming or wish to give you some PhD in statistics/finance, etc. My main goal was to create a tool to bring a trader who is completely unexposed (or partially unexposed) to algorithmic trading the ability to create their own portfolio of strategies, understand how pros think about risk, AND automate these strategies  all without ANY programming. That is, take someone from no programming experience to algo trading profits without any programming.

The trader who I am talking about wishes to remain nameless (for now) and I will refer to him as TraderX. His email to me and account statement are below.

*click to zoom*

His story is eloquently explained in his email as only a trader experiencing the full journey could. I’ve only summarized his highlights for convenience, but his words are better.

Highlights

  • +$34,000 in one month, trading small size across a diverse basket of  markets, timeframes.
  • Started learning from online sources and not formally educated by any big bank, business school, etc. with regard to trading. Takeaway: Self-taught is possible!
  • Payed for educators and services, alerts, chat rooms with mixed to negative results. Takeaway: there are no shortcuts in this game.
  • Went ALL-IN on learning and understanding BuildAlpha and its training videos. Takeaway: Build Alpha is a tool, but ultimately the end user is  responsible for his/her own success with it.
  • No desire to program and still does not care to learn… because he does not need to. Takeaway: tools needed to succeed exist in BA
  • Diversified his portfolio across assets/symbols, timeframes, strategy types, etc. Takeaway: no holy grail hunting, but only professional portfolio building.
  • Understands how to set expectations and let systems play out. Takeaway: zero to hero. Simplicity usually wins.

TraderX is a great case study because he was  literally the struggling/learning trader I can relate to from my own experiences and watching/talking to him now with his evolved understanding of trading is truly incredible and cannot wish this success to a more deserving trader. The answer is simple: admit where you are, agree to put the work in, watch the videos, do the work, be patient. This is a general recipe for success in life – TraderX only got into trading after selling his own business. Some people just get it… others need to hear it first; that is why I do these posts. The answers are certainly there and exist!

I normally hate publishing these testimonials but am glad to have explained why I do. The results are simply the byproduct of great workflow/process learned through hardwork, Build Alpha training videos and experimenting. TraderX is not alone in the BA community in this regard. Here is another user’s statements over the past few months where you can see successful trading. He is taking his time, abiding by the numbers/expectations he’s created and ultimately growing himself and his account. Remarkable.

*click to zoom – had to cut his original image into two images so some overlap between*

To learn more, please contact me at david@buildalpha.com. Reminder: Build Alpha comes with 50+ training videos where I explain system trading principles, system trading basic, and of course the details of how to maximize the Build Alpha software.

Thanks for reading,

David

Free Friday #15 – Downloading Custom Data for Build Alpha using Python

Happy Friday!

For this Free Friday edition, I am going to do something new. I am going to make this slightly educational and give away some code.

I get tons of questions every week, but they mainly fall into two categories. The first question is in regards to adding custom data to Build Alpha. You can add intraday data, weekly data, custom bar type data, sentiment data, or even simple single stock data. The second question is in regards to using or learning Python.

In this post, I will attempt to “kill two birds with one stone” and show a simple Python code to download stock data from the Yahoo Finance API.

In fact, we will use Python to pull and save data ready formatted for Build Alpha for all 30 Dow stocks in less than 30 seconds.

You can view the entire script later in this code or in the video below.

The first few lines are simple to import statements pulling public code that we can reuse including the popular pandas library.

import pandas as pd
from datetime import datetime
from pandas_datareader import data

I then define a function that downloads the data using the built-in DataReader function of the pandas_datareader library. I also adjust the open, high, low and close prices by the split ratio at every bar. This ensures we have a consistent time series if a stock has undergone a split, for example. **Please note other checks could be recommended like verifying high > open and high > close and high > low, but I have left these up to Yahoo in this post**. I then end the function returning a pandas data frame that contains our downloaded data. This get_data function will be valuable later in the code.

def get_data(symbol, start_date, end_date):

dat = data.DataReader(symbol, "yahoo", start_date, end_date)
dat['Ratio'] = dat['Adj Close'] / dat['Close']
dat['Open']  = dat['Open']  * dat['Ratio']
dat['High']  = dat['High']  * dat['Ratio']
dat['Low']   = dat['Low']   * dat['Ratio']
dat['Close'] = dat['Close'] * dat['Ratio']
return dat

I then go ahead and put all 30 dow tickers in a Python list named DJIA. I also go ahead and create our start and end dates in which we desire to download data.
DJIA=["AAPL","AXP","BA","CAT","CSCO","CVX","KO","DD","XOM","GE","GS","HD","IBM","INTC","JNJ","JPM","MCD","MMM","MRK","MSFT",
"NKE","PFE","PG","TRV","UNH","UTX","V","VZ","WMT","DIS"]
start = datetime(2007,1,1)
end   = datetime.today()

Finally, and the guts of this code, I loop through all 30 of our tickers calling the get_data function on each one of them. After downloading the first one, AAPL in our case, I open a file named AAPL.csv and then loop through the downloaded price series retrieved from our get_data function. I then write each bar to the file appropriately named AAPL.csv. I then close the AAPL.csv file before downloading the second symbol, AXP in our case. This process is repeated for each and every symbol. The result is 30 seconds to download 30 stocks worth of data! Each symbol’s data is saved in a file named Symbol.csv.

for ticker in DJIA:

DF = get_data(ticker,start,end)
fh = open("%s.csv" % ticker,'w+')
for i,date in enumerate(DF.index):
fh.write("%s,%.2f,%.2f,%.2f,%.2f,%dn" % (date.strftime('%Y%m%d'),DF['Open'][i],DF['High'][i],DF['Low'][i],DF['Close'][i],DF['Volume'][i]))
fh.close()

Now to the second part. Using this data in BuildAlpha is as simple as clicking on settings and searching for your desired file. I’ve attached a photo below that shows how the trader/money manager can now run tests on the newly downloaded AAPL data using the symbol “User Defined 1”. Pictures below for clarity.

I’m showing a strategy created for $AAPL stock, but it is only to prove this Python code and Build Alpha feature work. There is major selection bias creating a strategy on a stock that has basically been in a major uptrend for 90%+ of its existence. That being said, and in a later post, I will show a new Build Alpha feature that allows you to test strategies across different symbols to make sure the strategy holds up on both correlated and uncorrelated securities. Either way here is the AAPL strategy.

Buy Rules:

1.Today’s Low > Open of 3 Day’s Ago
2.Today’s 14 Period Stochastics > Yesterday’s 14 Period Stochastic
3. Today’s Upper Keltner Channel > Yesterday’s Upper Keltner Channel

Exit Rules:
1. Two Day Maximum Hold
2. 1.00 * 20 Period ATR Stop Loss

I like this strategy because it is convex. We limit the downside, but let the market give us as much as possible in 2 days. Below is the equity graph with the highlighted part being out of sample and based on 1 share as this is just for demonstration purposes!