A Basic Example of Spreadsheet Building

In my 2014 Vegas presentation, one topic I covered was how I use spreadsheets to track specific setups and try to find a statistical edge. There seemed to be a lot of interest in this process, so I'm going to go through an example of building a spreadsheet. As my example, I'll use tracking afternoon breakouts on listed stocks, but keep in mind you can do this for any setup you want to track. One important thing to note is that differing market conditions can definitely affect how different setups work. This example will use a VERY small sample size, which I would not consider statistically significant. So the more data you have, the better.

Part 1: What to Track?

The first step in this whole process is figuring out the setup you want to track and any criteria that may go along with it. What area do you want to focus on? Do you already have an idea of some set "Rules" that you may have for this setup? Or are you just starting fresh and seeing what you can discover?

We will be tracking afternoon breakouts on listed stocks. Obviously, there are many stocks in the market that have afternoon breakouts every day. I choose some very specific criteria to help narrow the scope and, hopefully, focus me in on the best opportunities. Here are the criteria that matter to me:

• % Change >= 10%
• Reasoning: I want to buy afternoon breakouts for stocks that are already up STRONGLY on the day. Also, if the stock is up 10%+ already, that means there is volatility. I'm not interested in stocks that will break out and only move a couple percent at most because they have no intraday range
• Method: I use a market scanner to find stocks that meet this criteria

• Well above-average trading volume
• Reasoning: I want a stock that is clearly drawing more interest than usual
• Method: Either a scanner filter, or I just look at the daily volume on a one-year chart to get an idea of how today compares

• At least one hour of consolidation from previous high
• Reasoning: The stock needs time to set up for the next leg up
• Method: I look at intraday charts to determine this; there isn't a scanner filter that I know of

• Breakout after 2:30 EST
• Reasoning: These are "afternoon" breakouts after all, and in my experience, later afternoon is more reliable
• Method: Look at a clock

• No Key Resistance Levels Close Overhead
• Reasoning: I don't want to buy an afternoon breakout if there's a major resistance level nearby that could stuff it
• Method: Look at a daily chart - see if there are any significant overhead levels nearby

It's very important to note that just because the above criteria are what I use, that doesn't mean it's the "right" way. You could have different ideas of what you're looking for, and you can adjust your criteria to fit with your goals. For example, maybe you want to track breakouts after 1:00 EST. That's absolutely fine! Track what is right for YOU from setup to setup; you can always adjust as you go. That being said, I don't recommend using hindsight to "data-mine" and make the results look good. Forward tracking is the best way to see if a setup is working, not adding/removing criteria from previous data to find what was successful in hindsight.

Part 2: Tracking Results

We now know what the criteria are for a stock to make our "Afternoon Breakout" list. So the next question we have to ask ourselves is, what are the results? Did the breakout succeed or fail? What factors determine this? Well, let's think about what would matter to us if we were in the trade. Here are some results that I DEFINITELY would want to track:

• After the breakout occurred, what was the low?
• Reasoning: I want to get an idea of how often the stock dips below the breakout point and how severe that dip might be

• After the breakout occurred, what was the high?
• Reasoning: I want to know how far the stock ran after the breakout, which should have been an important catalyst.

• Where did the stock close?
• Reasoning: I want to know how often these close ABOVE breakout levels.

• Where did the stock open the next morning?
• Reasoning: I want to know if the stock gapped up or down. How often and how strong these tend to gap up will influence my decision as to whether I want to hold overnight or not

• What were other key price points the following day?
• Reasoning: Next day highs and lows will help give me an idea of how well afternoon breakouts run into the next day

Below is an image of what your tracking page might look like:

Part 3: Putting it All Together

For the sake of this example, I'm going to use some old data to fill in the spreadsheet and give you an idea of how everything comes together. First, here is an image with only the basic data filled in, the information that you would manually input at the end of the day based off of the chart:

Now this is where this post gets a bit tricky. I could get into all of the formulas I use, but I don't want to turn this into a lesson on Excel. I think the best solution is to post a link to this spreadsheet so you can see for yourselves how the rest of the sheet fills out and the formulas I use to gather specific data. Feel free to ask questions in the comments section if something is unclear and I'll do my best to address it. Here is the link to view my sample spreadsheet.