Excel for Options Trading

Discussion in 'General Discussion' started by Kevin Lee, May 22, 2018.

  1. Kevin Lee

    Kevin Lee Well-Known Member

    I've just started a youtube video series on building simple but useful options trading applications with Excel. Meant for traders who wish to learn how to connect Excel to TOS and some other spreadsheet techniques to manipulate options data, including live data and OV export data. I just posted the first one. More to come. Hope they'll be useful to fellow traders.

    Go to youtube and subscribe my channel if you want to follow this series.

    TomKNVB and Mark17 like this.
  2. status1

    status1 Well-Known Member

    Looks interesting I will be watching
  3. status1

    status1 Well-Known Member

    After looking at the video on a big screen tv I found a couple of points where the data changed probably due to editing
    After the strike column was made the cell that previously contained the code after the column was made the same cell now contains the Root
    if you don't change the code to the root than the formula will not work

    Another section that was skipped over is after the mark column was made and the delta was checked the date was changed to Sep and the type was changed to C

    Just a small tip to those who use an older version of Excel sometimes after changing a cell the data does not show up
    In that case just save the excel sheet close it and reopen it and the data will show up
  4. Kevin Lee

    Kevin Lee Well-Known Member

    You're right... I realized I lost a segment of the videos during editing. Not sure why. My bad.

    In the next video, I zoom in more. I have a 34 inc monitor. I didn't realize how small the text can look in a normal monitor.
  5. When a TOS screen is shown in your video, it is very fuzzy and nearly impossible to read. The excel screen is quite small but clear.
    I suggest you spend a little time talking about how you pull down one and a pair of cells to extend a list. Also discuss the difference between pulling down the cell corner dot and the cell itself.
    I learned a new list trick today, thanks
  6. Kevin Lee

    Kevin Lee Well-Known Member

    Thanx for the feedback. I agree the screens were too small. I zoomed in more on the Excel sheets in Part 2. Please take a look. Just curious - did you watch in full screen and change the resolution to HD 1080P when you watch?

    I'm not sure if I totally understand your question. But let me try - pulling down the dot at the corner of a cell copy the formula from the cell to all the cells you pull to. The formula might change from one cell to another depending whether you have the $ sign in the cell address in the formula. But if I move the entire cell, I don't copy the formula but move the cell as it as to another location. If I misinterpreted your question, please tell me.
  7. Kevin Lee

    Kevin Lee Well-Known Member

    This is part 2 of the series

  8. status1

    status1 Well-Known Member

    Thanks for the detailed explanation of those formulas It's always better when someone explains it how to use it with examples rather than just copy and paste the formula Perhaps now I can use it in other spreadsheets where there is a need for this type of formula
  9. Jeffrey Suckow

    Jeffrey Suckow Active Member

    Fantastic videos (same comments applies to all the other ones you posted).

    I had no problem viewing them on a "regular" 27 inch.

    I don't agree with some of the other comments. You provide incredible and valuable information. For basic excel use, users should turn to Youtube tutorials.

    I made an easy mod to be able to vary the width of the strikes when looking at another underlying:


    placed below the cell with "highest strike" (and then pulled down the column)
    J14 = width of strike (1,5,10,25...)

    Looking forward to viewing the next ones
    Kevin Lee likes this.
  10. status1

    status1 Well-Known Member

    It doesn't appear that your mod is compatible with the existing spreadsheet
    Perhaps your code does not fall in the same cells that the video shows
    In the video B22 contains the option code so modifying that code I don't think it's going to work
    Perhaps if you show a snapshot of your spreadsheet or make it so that it coincides with the existing spreadsheet that would be more understandable for everyone
  11. Jeffrey Suckow

    Jeffrey Suckow Active Member

    You are right. This does not correspond to the sheet in the video.
    I don't do an exact copy. What is important to me is to understand and master the concepts and rules.

    You must ignore my cell numbers.

    Select a cell outside of any range and place the number you want as your strike spread width. This can be typed in manually or create a list as shown in the video.

    Let's say the cell are placed like this (as in video)

    B19 = "Width of strikes"
    C19= 10

    A23 = A22-$C$19

    Then pull down the full range.

    Now when you change the value of the C19 cell, it will change the width between the strikes.

    The main idea is to use the "$" to lock the column and then again the "$" to lock the row. This way you are not shifting cell reference as you drag down or left.

    Kevin used this many times in the video. You can lock the column, the row or both.
  12. status1

    status1 Well-Known Member

    Thanks for the clarification
    That makes more sense
    Of course it would be even better if the strikes would show up as they are automatically from tos but that would mean more programing and that may be too advanced so I think this is good enough for now
    I was thinking of SPX and how sometimes it changes from 10 to 25 strikes in the same expiration but it's probably not necessary if no one is trading that far OTM
  13. Kevin Lee

    Kevin Lee Well-Known Member

    Uploaded part 3. Remember to view in 1080P. Otherwise the words might not be clear enough.

    Srini likes this.
  14. status1

    status1 Well-Known Member

    Nicely done

    I just had to make a slight change because I have excel 2003 and I only have 3 choices for the weekday instead of 16 so I chose 1 for Sunday so my number for Jan was 2 instead of 3 and than I used 21-the number instead of 22
    The rest worked out the same except at the end where you move some of the data to another sheet
    That did not work for me probably because the expiry date is tied to those cells so once I move it I loose the expiry numbers It seems that the data validation is only working on the same page
    It's ok for now I can just move the data further to the right so it's out of the way
    Kevin Lee likes this.
  15. Kevin Lee

    Kevin Lee Well-Known Member

    When I was using 2007 or 2003, I can't remember which version for sure, I could move the validation list table to another sheet by using named range. That means firstly, you move the validation table to a blank sheet, create a named range and put the OFFSET formula in the named range. After that at the Cell where you want the drop down, when you create the validation list, you put an equal sign and then the named range you just created. See if that works.
  16. status1

    status1 Well-Known Member

    Hmm... It doesn't seem to work
    It could be because I am not sure how to do it exactly since I am not sure if I am using the named range in combination with the data validation so I tried a few things
    If I select a range of cells and let's say I call it date1 as a named range and than I go to the other sheet and use the data validation and enter date1 I get the list that I selected with date1 but it's not dynamic anymore

    If I make the data valuation on the same sheet that I made the selection with date1 I get the list on that sheet but if I name that cell let's say date2 and go to the other sheet and use the data validation and enter date2 I only get the date that is showing on the sheet that I made the data validation so I only see one date not the whole list

    So it looks like either way I have to go back and forth between the sheets either to adjust the list or to select a different date from the list unless I am not using the function correctly
  17. Kevin Lee

    Kevin Lee Well-Known Member

    You have to put the OFFSET formula inside the named ranged like below

  18. status1

    status1 Well-Known Member

    Thanks for the more detailed explanation
    I got it working now
  19. status1

    status1 Well-Known Member

    I found a bug in my formula for the weekday
    All the Fridays looked good except for Sep and Dec which showed 14 instead of 21 so I manipulated the weekday formula by choosing Sunday instead of Monday which gave the result of 1 so I added 2 to make it 3 for Jan than added an If formula to subtract 7 if the number is higher than 7 so after that all the dates matched I know it's a little messy but it works for now
  20. Jeffrey Suckow

    Jeffrey Suckow Active Member

    I'll be looking at the new video soon.

    Just a tip for that can help keep track of what is going on when you return to a sheet and don't remember what you did: place comments in the formula cell. They will show up when you point to that cell

    You add:

    =COUNTA(U21:U221)+N("counts how many items in the range U21 to U211")

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice