This is a 65-MB CSV file containing timestamps, open, high, low, close, change, %change and volume for 1-min candles on $SPY
If you found this useful, thank me on Twitter or elsewhere. I love chatting with other traders.
The 12-minute Opening Range Breakout (Day Trading $SPY): Intermediate ETL Example with Python and Google Sheets
I am the first person on the Internet to coin this phrase in the context of day trading.
12-minute Opening Range Breakout
In day trading of stocks, the term Opening Range Breakout refers to waiting for FIRST candle of the day to close, and then buying a break ABOVE (or selling a break BELOW) that initial range. Most commonly, I see 3-minute ORBs and 5-minute ORBs, since those are common charts for people to watch on a site like TradingView.
But what size of ORB (in minutes) is the most profitable?
I used CSV files, Python and Google Sheets (Pivot Tables) to find out.
Extract: I downloaded $SPY stock data for the past 326 trading days, with 1-minute resolution, in CSV form, from BarChart.com. I copied and pasted a few smaller CSVs together, but could have automated this if there were too many files ...
Transform: I used Python to open the CSV, delete several columns, and cast some columns to float() as required. Then, I coded an algorithm to find the high and low of the stock over the first x minutes, and then look candle-by-candle for a break either ABOVE or BELOW that range. THEN, I followed the chart candle-by-candle to analyze whether a stop-loss would be triggered, or profit taken depending on the target set, or whether the day came to a close without either of those things happened. In those cases, I closed the position at 3:55pm, 5 minutes before close.
The outcome of every day's trade was appended to a list, and then the SUM of the profits and losses from those 326 trading days was output to the Terminal.
I also had three nested loops, which let me test various ORB sizes (in minutes), stop-loss distances (measured in number of bars compared to the original ORB) and targets (a trade would close if a particular target, in bars, was reached).
30 different ORB sizes x 5 varying stop-losses x 7 varying targets = 1050 permutations of ORBs/Stops/Targets, and each of THOSE was applied to each of the 326 trading days.
In any case, the Terminal output was 1050 lines telling me the ORB size, Stoploss, Targets, and total profit/loss for that permutation. This was small enough that I could copy/paste it into Google Sheets.
Load: Google Sheets didn't realize my data was numerical, so I had to use =SPLIT() to break apart the data from the Terminal. If I was doing this with a larger data set, I would have output to CSV instead of printing to the Terminal.
Then, I used a Pivot Table to summarize the results across varying ORB sizes. Due to outliers, I wanted to look at AVERAGE profit, MAX profit, MIN profit and MEDIAN profit across all stop-losses and targets for a particular ORB size. The results are pretty clear (see below). By all measures, a 12-minute ORB outperforms all other durations.
Actually, a 13-minute ORB is about the same, but the difference is negligible considering the randomness of the markets.
Basic ETL Example: Using Google Sheets to find stocks with Inside Day and Outside Day
DailyStratBarType is 1 if the current day's low and high are within the previous day's
DailyStratBarType is 3 if the current day's low is lower than yesterday AND the current day's high is higher as well
DailyStratBarType is 2 if either the current day's low is lower than yesterday's OR the current day's high is higher than yesterday's (but not both)
To designate this, I used a nested IF formula: =IF(AND(J4>G3,L4<H3),1,IF(AND(J4<G3,L4>H3),3,2))
Lastly, I needed to collect the ticker symbols of the stocks that have been flagged 1 (or 3, separately). This can be done with a Pivot Table, which lets me:
If you are finding this website as one of my former students, I have news for you: I will not be returning to Toronto Prep School in September.
There are many reasons for this; most are personal.
If you need a letter of reference, you can contact me via the Contact Form of this website.