Excel for Options Trading

K

Kevin Lee

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 Thinkorswim (TOS) and some other spreadsheet techniques to manipulate options data, including live data and OptionVue (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.

 
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
 
stts said:
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
Click to expand...
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.
​
 
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
Norm
 
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
 
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:

=B22-$J$14

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
 
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
 
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.
 
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
 
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
​
 
stts said:
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​
​
Click to expand...

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.
 
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
 
You have to put the OFFSET formula inside the named ranged like below

upload_2018-6-3_10-56-46-png.2985
 
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
 
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:
+N("Comment")

Example:
=COUNTA(U21:U221)+N("counts how many items in the range U21 to U211")
 
Kevin,
Do you use the RTD from TOS to Excel to check if the positions mid price (of a butterfly, spread, put) is off compared to the same synthetic position to get better entry, exit prices or better realistic P&L on any given day ?

Jeff
 
Suckow said:
Kevin,
Do you use the RTD from TOS to Excel to check if the positions mid price (of a butterfly, spread, put) is off compared to the same synthetic position to get better entry, exit prices or better realistic P&L on any given day ?

Jeff
Click to expand...

I used to record synthetic mid prices along with many other parameters using VBA every 15 mins every day. That was part of my learning and getting familiar with prices in different market environment. But I don't do that anymore. Nowadays I just use OV Spread function to compare before I do a trade.
 
You must log in or register to reply here.
Top
Contact Us