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.



60 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
    2. hey Tim, can you pleas explain this again! i just can't understand it, thank you very much!

      Delete
    3. hey Tim, can you pleas explain this again! i just can't understand it, thank you very much!

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

    ReplyDelete
  7. Awesome information, thanks Tim.

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

    ReplyDelete
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. i know what i want for xmas, a good scan for a parabolic, and an explanation how to trade it.

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

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

    ReplyDelete
  18. 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
  19. 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
  20. 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
  21. This comment has been removed by a blog administrator.

    ReplyDelete
  22. 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
  23. This comment has been removed by a blog administrator.

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

    ReplyDelete
  25. 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
  26. This comment has been removed by a blog administrator.

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

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

    ReplyDelete
  29. 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
  30. 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
  31. 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
  32. What if I find some profitable setups and just will search market for these setups. Would it work?

    ReplyDelete
  33. 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
  34. Awesome Tim, Thanks for sharing this great info.
    Big hug form Argentina.
    Once againg thank you man.

    ReplyDelete
  35. Hi Tim, how would the spreadsheet for short-selling look like ? What would be the criterions for strategy? Many thanks !

    ReplyDelete
  36. indices measure the price performance of shares from a specific exchange. For example, the FTSE 100 represents the 100 largest stocks trading on the London Stock Exchange. If these stocks increase in price, the FTSE 100 increases.

    ReplyDelete
  37. hi, tim this is an amazing way to track your trades and learn from them by using spreadsheets, i am lil confuse i want to know where do i get the formulas and some basics to start ?

    ReplyDelete
  38. i was once living on the street where by things were so hard for me, even to pay off my bills was very difficult for me i have to park off my apartment and start sleeping on the street of Vegas. i tried all i could do to secure a job but all went in vain because i was from the black side of America. so i decided to browse through on my phone for jobs online where i got an advert on Hackers advertising a Blank ATM card which can be used to hack any ATM Machine all over the world, i never thought this could be real because most advert on the internet are based on fraud, so i decided to give this a try and look where it will lead me to if it can change my life for good. i contacted this hackers and they told me they are from Australia and also they have branch all over the world in which they use in developing there ATM CARDS, this men who were geeks and also experts at ATM repairs, programming and execution who taught me various tips and tricks about breaking into an ATM Machine with a Blank ATM card.i applied for the Blank ATM card and it was delivered to me within 3 days and i did as i was told to and today my life has changed. there is no ATM MACHINES this BLANK ATM CARD CANNOT penetrate into, because it has been programmed with various tools and software before it will be send to you. i know this is illegal but also a smart way of living Big because the government cannot help us so we have to help our self. if you also want this BLANK ATM CARD you can contact these Hackers at
    email: skylinktechnes@yahoo.com whatsapp: +1(213)785-1553 or website: https://skylinktechnes.wixsite.com/info

    ReplyDelete
  39. i was once living on the street where by things were so hard for me, even to pay off my bills was very difficult for me i have to park off my apartment and start sleeping on the street of Vegas. i tried all i could do to secure a job but all went in vain because i was from the black side of America. so i decided to browse through on my phone for jobs online where i got an advert on Hackers advertising a Blank ATM card which can be used to hack any ATM Machine all over the world, i never thought this could be real because most advert on the internet are based on fraud, so i decided to give this a try and look where it will lead me to if it can change my life for good. i contacted this hackers and they told me they are from Australia and also they have branch all over the world in which they use in developing there ATM CARDS, this men who were geeks and also experts at ATM repairs, programming and execution who taught me various tips and tricks about breaking into an ATM Machine with a Blank ATM card.i applied for the Blank ATM card and it was delivered to me within 3 days and i did as i was told to and today my life has changed. there is no ATM MACHINES this BLANK ATM CARD CANNOT penetrate into, because it has been programmed with various tools and software before it will be send to you. i know this is illegal but also a smart way of living Big because the government cannot help us so we have to help our self. if you also want this BLANK ATM CARD you can contact these Hackers at
    email: skylinktechnes@yahoo.com whatsapp: +1(213)785-1553 or website: https://skylinktechnes.wixsite.com/info

    ReplyDelete
  40. I was searching for loan to sort out my bills& debts, then i saw comments about Blank ATM Credit Card that can be hacked to withdraw money from any ATM machines around you . I doubted thus but decided to give it a try by contacting {skylinktechnes@yahoo.com} they responded with their guidelines on how the card works. I was assured that the card can withdraw $5,000 instant per day & was credited with $50,000 so i requested for one & paid the delivery fee to obtain the card, i was shock to see the UPS agent in my resident with a parcel{card} i signed and went back inside and confirmed the card work's after the agent left. This is no doubts because i have the card & has made used of the card. This hackers are USA based hackers set out to help people with financial freedom!! Contact these email if you wants to get rich with this Via email skylinktechnes@yahoo.com or whatsapp: +1(213)785-1553

    ReplyDelete
  41. My name is Gary Jones i'm here to testify about Mr.Harry hacker ATM Blank Card. I was very poor before and have no job.I saw so many testimony about how Harry send them the ATM blank card and use it to collect money in any ATM machine and become rich. I email him also and he sent me the blank card. I have use it to get 700.000 dollars. withdraw the maximum of $ 5000 daily. Harry is giving out the card just to help the poor. Hack and take money directly from any ATM Machine Vault with the use of ATM Programmed Card which runs in automatic mode. email (harrylee62@live.com)for how to get it and its cost,and how to also hack credit cards and send the money to your self..
    whatsApp: +1(312) 298-9500

    website: https://harrylee62.wordpress.com/2020/01/08/blank-atm-card

    ReplyDelete
  42. Halo Tim Grittany, THankyou for being here. I'am Steven from Indonesia. I was listen and study from your free youtobe videos, Tim Sykes, and Steven Dux more than 600+ videos combine. You guys are realy my hero. all you teach and share are realy my live's dream. and it's my fortune to see you in this dream Profitly. so long live and keep going forward guys. I Pray my God and Savior bless you always every single day.

    ReplyDelete
  43. Have you heard about programmed ATM card? email: (williamshackers@hotmail.com) or WhatsApp +27730051607 for enquiring on how to get the ATM programmed card.
    We have special cash loaded programmed ATM card of $5000, $10000, $15000, $20000 and any amount your choice you need for you to buy your dream car, house and to start up your own business. Our ATM card can be used to withdraw cash at any ATM or swipe, stores and POS. Our card has daily withdrawal limit depending card balance you order. Contact us via Email if you need a card email: (williamshackers@hotmail.com) or WhatsApp +27730051607.

    ReplyDelete