Description

My basic trading philosophy can be summed up by one simple quote:

"Trade the ticker, not the company" - Nate Michaud


Saturday, December 6, 2014

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.



48 comments:

  1. Hi Tim, is there a way to set up this or any other criteria on esignal and send It to a spreadsheet? Thanks much .

    ReplyDelete
    Replies
    1. I'm unfamiliar with esignal, so unfortunately I'm not sure. Sorry!

      Delete
  2. Great post! Actually been working on a similar spreadsheet for Late-Day faders this morning after reading your SPEX Breakout post. I feel like your confidence in taking positions after doing this type of dd increases quite a bit.

    ReplyDelete
  3. What sample size would you consider as statistically significant? >30? Would you recommend generating different simple size per market conditions e.g. bullish, bearish accumulation / distribution markets?

    ReplyDelete
    Replies
    1. Yeah it's definitely important to take note of different market conditions, setups will definitely react differently. Statistically significant yeah I would say at the very least 30, enough to start seeing some consistent patterns at least

      Delete
  4. Great post Tim, thanks for letting us see that your strategies are not rocket science but simple well thought out trading plans.

    ReplyDelete
  5. I'm having trouble understanding what tab 1 column A is saying " 25% better than... 50% better than..." what is it better than? if you could help me see what I am not I would greatly appreciate it.

    ReplyDelete
    Replies
    1. Yeah so for example let's take cell B4, that's saying, "25% of afternoon breakouts tracked had a post-breakout low of 0% or higher from the breakout point." Cell C4 is saying "25% of afternoon breakouts tracked had a post-breakout high of 12.42% or higher." One last example, cell D6 is saying "50% of afternoon breakouts tracked closed up 8.1% or higher from the breakout point." Let me know if this clears it up!

      Delete
  6. Thanks for the post Tim, I appreciate your help! Glad you're getting back in your groove.

    ReplyDelete
  7. Hello Tim, I'm going to open an account with 2000 wich brocker would you recomend, I live in Mexico

    ReplyDelete
    Replies
    1. Please read my posts on brokers, thanks!

      Delete
  8. Hi, Tim! Thanks for post!
    Please answer, how do you recognize that it's time to close position? Whats signals for you?

    ReplyDelete
    Replies
    1. Either my risk level is hit and I'm forced to cut the loss, or if the position is profitable I usually just lock in along the way and slowly pay myself. Although if I notice some sort of trend-break in the chart that could be a signal the stock is about to reverse and go against me, then I'll get out more aggressively. It's really case-by-case and has a lot to do with the specific setup and what my goals for the trade are

      Delete
  9. Regarding the one-hour consolidation from previous high scan: Perhaps you could filter your results based on bollinger band width decreasing over a specific intraday time period, in order to speed up this process.

    I'll do some testing and let you know if I find anything.

    ReplyDelete
  10. Hi Tim Thanks for your blog, greate information you share here.
    I want to open an account in shuretrader with 2000, but after Reading about the commissions and fees, I don't know the amount to open it. Wich is the quantity recommended to open the account to avoid being loosing for the commissions?

    ReplyDelete
    Replies
    1. I just opened up a sure trade account myself with only 500$ the fees aren't too outrageous not that I know whats too expensive guess its a matter of opinion. Few things I will note is when you fund your account by credit card they charge you 3.5% on whatever amount you choose to fund 500>. Also make sure at the end of the day if your using margin don't go over 2 to 1 margin they will liquidate your position if over 2 to 1 at close and hit you with a 55 dollar fee which I learned the hard way the other day. Also in Tim G's older post if your read you can negotiate the the share trade price meaning instead of 4.95 for every 1000 shares if you only trade penny stocks you can get that up to 4.95 for up to 10,000 shares. Those are a few things off the top of my head. I don't use the desktop pro software I use thinkorswim for free chart reading and have my web for sure tradeer up next to it for quick trading just so I can cut the costs. The desktop pro software is like 100+ a month. for the web though its only like 15 or so a month to be able to trade pink sheets and otcs. Those are the only things I can think of off the top of my head I like them so far customer service seems great you can live chat with them anytime. Give ti a shot!

      Delete
    2. Thanks for your feedback Tim P., With an account of 500 you buy and short stocks?, and to avoid the 2 to 1 margin, wich cost of shares do you look for and how many (100, 200...). Talking about the free charting of thinkorswim, do you have to open an account with them, because I didn't found free charting. Thanks for your feedback

      Delete
  11. Hey Tim, when recording Day 1 closing numbers are you using the 4pm market close price or the after-hours close price? Same question with the Day 2 open, are you using the open price in pre-market or the 9:30am open price? Thanks for the blog post, very helpful.

    ReplyDelete
    Replies
    1. I try to use 4:00 close and 9:30 open for recording

      Delete
  12. Hey Tim, how about highs and lows.... do you use just regular trading hours numbers or do you use extended hours and pre-market hours numbers for highs and lows. I'm just trying to get a better understanding so I'm calculating my numbers properly. Thanks for the feedback.

    ReplyDelete
    Replies
    1. Yeah I just use regular market hours for that too. But don't worry about doing it "right".... remember like I said in the post do what is best for YOU and focus on the data that you're interested in! Totally okay to do your own thing it's just a matter of personal preference

      Delete
  13. Unrelated Question: In a lecture I heard of you, you told people to ask you for some Level II videos and you would be happy to send them some, I saw your Level II videos on here but are you still sending out any others? Thanks Tim

    ReplyDelete
  14. i know what i want for xmas, a good scan for a parabolic, and an explanation how to trade it.

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. Thank you for the education, Tim. Just want to let you know you inspire us traders. God bless you

    ReplyDelete
  17. Hi Tim,
    What are some indicators, technical or otherwise, that you look at to know when a stock is peaking to a drop versus spiking and settling into a price range?

    ReplyDelete
  18. Are you using this data to give you better entries/exits or...? Because in terms of getting a statistical edge I don't quite see how it would work... Unless you're trading a specific instrument like SPY. But even then it all depends on who is in the market at the time, and that can change day to day. Let me know if I am missing something, and thanks for taking the time to share your knowledge!

    ReplyDelete
  19. Tim for the past 2 months i have been reading your blog religiously, especially the BreakOut Strt and i have been paper Trading the strategy all that time, the question i have for you is Why dont you Trade the BreakOut the leads to the first High if it Price Action Breaks old high's from the pervious day?

    ReplyDelete
  20. This comment has been removed by a blog administrator.

    ReplyDelete
  21. Tim where can I find a recording of you 2014 Vegas conference presentation? I can't seem to find it anywhere on YouTube or Profit.ly. Thanks.

    ReplyDelete
  22. This comment has been removed by a blog administrator.

    ReplyDelete
  23. This comment has been removed by the author.

    ReplyDelete
  24. Thank you Tim for sharing this incredible idea how to make spreadsheet and get statistical edge, i downloaded the spreadsheet files, all i needed how to make formulas and it was found in those files, i wish you all the best in your life and luck in your trading Coach Tim

    ReplyDelete
  25. This comment has been removed by a blog administrator.

    ReplyDelete
  26. This comment has been removed by a blog administrator.

    ReplyDelete
  27. This comment has been removed by a blog administrator.

    ReplyDelete
  28. Do you track each setup a minimum of 2days, unless it's an immediate play?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. i.e. How long (on average) do you track a typical setup before trading it?

      Delete
  29. Hi Tim, does this strategy "works" in bear market as well as bull market or for each market there are different strategy?
    Thank you for the great information
    I ve added some new criteria in order to understand more how these stocks react
    Regards

    ReplyDelete
  30. What time frame do you trade? I tried D1 because for me there's no reason to trade smaller TF with a tiny deposit.

    ReplyDelete
  31. What if I find some profitable setups and just will search market for these setups. Would it work?

    ReplyDelete
  32. Hi Tim, I build this trade journal inspire on your teaching from trading the ticker and your webinar on Tim Sykes showing us how you keep your trade journal. I hope this tool can help you guys as much as it helped me https://www.tradersync.com/ - here is a quick video showing you just some of the benefits I built https://youtu.be/Py1mmFpmnXs

    ReplyDelete
  33. Awesome Tim, Thanks for sharing this great info.
    Big hug form Argentina.
    Once againg thank you man.

    ReplyDelete