In my Excel spreadsheet, using TOS RTD, I wish to find the option strike in a given expiration cycle that has a given delta value, or whose delta is nearest that value. For example, in the SPX 22 May 23 expiration cycle, I wish to query TOS to find the put strike with the delta that is closest (less than or equal) to -.16. I have done this with brute force, building a massive table of option codes by expiration date and strike price, with an RTD for each option code, but I am looking for a more elegant and efficient solution. Can anyone assist me with this? Thanks!
Find the Strike for a Specified Delta
- Thread starter Jim
- Start date
DaveN
Member
@Jim, I'm interested and watching your question as well. I currently run a quick lookup across the table of quotes that I'm monitoring and snapshotting for history, but that seems so *coarse* 
It will be a mathematical "solve for" Strike instead of calculating Delta from the Price.
I've started down this road in Excel, backing into the Black-Scholes model. I had a number of cells of intermediate values including some probability functions in Excel that I was collapsing into a single, very large equation to be handled in a single cell. I'm not finished but happy to share the work in process if you want to try to pick up where I left off.
I'm hoping that someone has a simpler, more elegant approach.
It will be a mathematical "solve for" Strike instead of calculating Delta from the Price. I've started down this road in Excel, backing into the Black-Scholes model. I had a number of cells of intermediate values including some probability functions in Excel that I was collapsing into a single, very large equation to be handled in a single cell. I'm not finished but happy to share the work in process if you want to try to pick up where I left off.
I'm hoping that someone has a simpler, more elegant approach.
Thanks for the suggestion, but I can find the strike in the same manner using TOS or ONE; this approach requires that I view the option chain and visually locate the target delta and strike. What I wish to accomplish is to specify a delta and programmatically find the closest associated strike.
DaveN
Member
@Jim if you are OK with the above approach, you could pull the options chain of interest and use a VLOOKUP or HLOOKUP in Excel with the [range lookup] parameter set to TRUE. The Lookup function will return the nearest value which should get what you are looking for for strike value based on nearest Delta to what you are looking for.
if you know vba, there is a way to get this from WorksheetFunction.RTD. i've never used this but you have to
be careful of bad data being returned, rtd isn't always reliable. you could start with an at-the-money strike and
loop through strikes to find the delta you want.
be careful of bad data being returned, rtd isn't always reliable. you could start with an at-the-money strike and
loop through strikes to find the delta you want.
@jim leahy, Thanks, I suspected that the solution would involve VBA, but was hoping someone had a solution that would allow me to query TOS via RTD directly. Although I am very skilled in Excel, and a retired software programmer/analyst, I don't know VBA, and have been a bit reluctant ("lazy") to put in the effort to learn it, especially since I would only rarely use it, as in this specific application. Still hoping someone can offer an easier, non-VBA solution.
Since you lack interest in VBA (I avoid it like the plague myself), would you have interest in coding a bit in Python? -- There are nice, easy to use python packages for interacting with Excel sheets, so your (python) code can do the interesting work, and place results back in Excel if you prefer that as a user interface.
while waiting for divine intervention on my other project i thought i'd take a crack at this. it's much harder than
i imagined, mainly because the WorksheetFunction.RTD didn't work. the function call returned immediatley
with null. the problem seems to be that the rtd response is asynchronous to excel and no amount of delays,
DoEvents, and recalculations solved the problem. the rtd response appears to be delayed until the code
finishes. i won't bore everyone with details of the many things i tried, but the only thing i succeeded in was
crashing excel several times. i've just about given up on making this work.
i have a couple of suggestions for alternatives, but the real solution is to use an api. in the absence of that,
you can get a pretty reasonable delta value by using the black-scholes model to calculate it. delta isn't that sensitive
to small changes in iv, especially if using longer dated expirations. you can use the at-the-money iv and do some
quick fudge-factor calculation for different strikes. you can get easily get all the other parameters. another
alternative is to use free option data available online. it's delayed by 20 minutes, but may be good enough
to use. at least you can get a, possibly, more accurate iv than to fudge it. i used my delta calculation as a
user defined function in excel and did a few quick tests. it was within a few points of thinkorswim delta. none of
these alternatives can be done w/o some software, but you can take gary's suggestion and use python instead of
vba.
i imagined, mainly because the WorksheetFunction.RTD didn't work. the function call returned immediatley
with null. the problem seems to be that the rtd response is asynchronous to excel and no amount of delays,
DoEvents, and recalculations solved the problem. the rtd response appears to be delayed until the code
finishes. i won't bore everyone with details of the many things i tried, but the only thing i succeeded in was
crashing excel several times. i've just about given up on making this work.
i have a couple of suggestions for alternatives, but the real solution is to use an api. in the absence of that,
you can get a pretty reasonable delta value by using the black-scholes model to calculate it. delta isn't that sensitive
to small changes in iv, especially if using longer dated expirations. you can use the at-the-money iv and do some
quick fudge-factor calculation for different strikes. you can get easily get all the other parameters. another
alternative is to use free option data available online. it's delayed by 20 minutes, but may be good enough
to use. at least you can get a, possibly, more accurate iv than to fudge it. i used my delta calculation as a
user defined function in excel and did a few quick tests. it was within a few points of thinkorswim delta. none of
these alternatives can be done w/o some software, but you can take gary's suggestion and use python instead of
vba.
I still don't see the point of all this exercise
Is it just to get the strike by other means than what is the easiest ?
I still have not heard what is the plan after this is solved
Is it to compare strikes and see what is the farthest strike in a certain expiration, something else ?
Is it just to get the strike by other means than what is the easiest ?
I still have not heard what is the plan after this is solved
Is it to compare strikes and see what is the farthest strike in a certain expiration, something else ?