options risk graph using excel

As many of you know already for 125 USD you can get a good set of tools from a programmer in Australia (Name starts with Hoad.......)
 
DaveJ said:
VERY close. :) See attached.
Click to expand...
That's close enough for me
Is this with the VBA version ?

I did play around with different expirations and found the 20 DTE close enough to use with the IIV from TOS but other expirations are not so close
 
Tb2018 said:
Name starts with Hoad...
Click to expand...
I believe I did look at that in the past but it comes down to the same thing (volatility input) It's no use paying $125 just to get the same inaccurate option price I can get using the excel spreadsheet Also I am not sure if the software is the same as the website but when I entered the volatility 10.86 and it rounded it up to 11 which will throw off the option value if it actually does that
It could be that they just don't show the decimal precision in that box
 
jim leahy
In your spreadsheet are the columns I,J and K used for anything ?
I don't see them used on any of the other tabs and columns J and K looks identical
Was that part of something else ?
 
on the puts tab, columns i, j and k are used to calculate the expiration values. although it's not necessary to
use black-scholes to calculate expiration values with trades that use the same expiration, i just chose to
do it. column i is referenced on the risk_graph tab for the expiration line.
 
Marcas said:
I don't say that modeling is useless - there are situations models are necessary, but for average or even advanced retail trader learning piano is indeed much better use of time
Click to expand...
marcas, i find that my spreadsheet model is very useful. a year or so ago, when i started trading dan's boxcar
i did a spreadsheet to calculate all the setup and adjustment parameters using data from rtd. that
evolved into adding a risk graph, and then adding adjustment capability. it's much easier to monitor my
boxcar trade in excel, than it is in another modeling tool. i have all the data for the spreads' profit and
loss, conditional cells to alert me when to adjust, and the capability to model each spread separately,
with the click of a button. all this would take a lot more effort to do in thinkorswim. i've since, modeled a
few other specific trades using this method. once you've done it once, the next time is much easier.

i wouldn't expect anyone to try to replace a full-blown options analyzer using this approach,
especially w/o using software, but to model a specific trade i think is a good use of time.
 
Hi Jim,
I agree with you 100%. Myself, I tried to convey similar thoughts on this Forum (to learn coding skills for better trading) and I didn't change my mind.
I do this, data harnessing, for some time and it's hard for me to imagine trading without that (personal feeling, not objective truth). I even dont think that full blown analyzer is out of the scope if necessity arise. Of course this would be dedicated software for specific needs not an 'universal' one.

My remarks referred to chasing the perfect or close to perfect greeks. That is what Garry talked about, at least in my understanding. I think in situation we have, where there are many alternatives for traders to obtain data, this task is a waste of time - although it is surely helpful to know how things are done, what is included in inputs and what is not, or what methods of calculations can be used in various situations - what model, what variant for some particular case is 'optimal' (quotation marks because it is relative).

I'm pretty sure that if one doesn't know much about calculating greeks but knows a lot about trading he will do OK,
while on the other hand one may have extended knowledge of modeling and can calculate most sexy greeks in the whole world and still have poor trading results.

So, spending time on 'modeling trades' aka using coding to handle all market data as in your post - YES,
but spending time on perfectioning greeks - NO (within a reason and with caveats ofc).

Individual needs may vary though.
 
Last edited:
DaveJ said:
You have to pull in the individual IV's of each option and run the calculation for each individual option across the axis in the black scholes equations
Click to expand...
What do you do when there is no IV value like yesterday where a lot of the ITM put options in the 4500 range had no IV values ?
Do you just skip that day or you derive the IV value just for that day ?
 
no - I use the rtd function to pull individual IV options. If they don't pull in, then the graph crashes. I don't derive IV. I guess I could set it to a default if there is an error but honestly haven't played with it recently. Too many projects going on!
 
Thanks for clarifying
I found that if I change the calculation to smile approximation it fixes the missing IV but than the p/l is off but even so I don't think you can pull the RTD for those IV's but I guess you could type in those values as the default just to keep the graph from crashing
 
Update
I have not actually tried it until now but the RTD does work with other calculations so it can pull in the smile approximation values for the IV in case they are missing keeping in mind that the p/l would be wrong
 
when the bid/ask spreads become wide, sometimes it creates a situation where the iv calculations can't resolve to a valid number. in that case, thinkorswim just doesn't post a value. switching to volatility smile approximation mode causes the iv for calls and puts, of the same strike, to have the same value, which is different from the values calculated using the individual implied volatility mode. that should raise warning bells in your head. in general, you should not expect the iv calculated from a different model to result in the same fair value, when used in your model. it may be close, but i've found most times it's not close enough for me.

i ported gary's iv calculation routine to visual basic. you can use this as a macro in excel. i don't know if this can be done using only equations, but i won't pursue that. btw, i think gary has a typo in his code for the high initial value. i used 500, which allowed this iv calculation to agree with the iv from my own algorithm.


Code: 
Function resolve_iv(call_flag As Integer, S As Single, X As Single, a As Single, T As Single, r As Single, iv As Single, q As Single)
 ' q = div  ,  T = time to expiration
 ' r = risk free rate . iv = volatility
 ' a = option price  , S = stock price ; X = strike price
 ' ported from python routine from gary whitlock
Dim high As Single
Dim low As Single
Dim precision As Single
Dim IVTrial As Single
Dim TheoPrice As Single

high = 500
low = 0
precision = 0.0001
While ((high - low) > precision)
    IVTrial = (high + low) / 2
    If (IVTrial < 0.00001) Then
        resolve_iv = ((high + low) / 2)
        Exit Function
    End If
   ' TheoPrice = BlackScholes(call_flag, S, X, T, r, IVTrial, q)
    TheoPrice = calc_fair_value(call_flag, S, X, IVTrial, T, r)
    If (TheoPrice > a) Then
        high = (high + low) / 2
    Else
        low = (high + low) / 2
    End If
Wend
resolve_iv = ((high + low) / 2)

End Function
 
Ah nice, I'll have to try that Jim, thanks for posting that. Question for you - where do you pull the dividend rate for the SPX? Right now, I'm just plugging assumptions for both the dividend rate and interest free rate. The Yield rtd function doesn't pull a value on the index.
 
jim leahy said:
when the bid/ask spreads become wide, sometimes it creates a situation where the iv calculations can't resolve to a valid number. in that case, thinkorswim just doesn't post a value. switching to volatility smile approximation mode causes the iv for calls and puts, of the same strike, to have the same value, which is different from the values calculated using the individual implied volatility mode. that should raise warning bells in your head. in general, you should not expect the iv calculated from a different model to result in the same fair value, when used in your model. it may be close, but i've found most times it's not close enough for me.

i ported gary's iv calculation routine to visual basic. you can use this as a macro in excel. i don't know if this can be done using only equations, but i won't pursue that. btw, i think gary has a typo in his code for the high initial value. i used 500, which allowed this iv calculation to agree with the iv from my own algorithm.


Code: 
Function resolve_iv(call_flag As Integer, S As Single, X As Single, a As Single, T As Single, r As Single, iv As Single, q As Single)
 ' q = div  ,  T = time to expiration
 ' r = risk free rate . iv = volatility
 ' a = option price  , S = stock price ; X = strike price
 ' ported from python routine from gary whitlock
Dim high As Single
Dim low As Single
Dim precision As Single
Dim IVTrial As Single
Dim TheoPrice As Single

high = 500
low = 0
precision = 0.0001
While ((high - low) > precision)
    IVTrial = (high + low) / 2
    If (IVTrial < 0.00001) Then
        resolve_iv = ((high + low) / 2)
        Exit Function
    End If
   ' TheoPrice = BlackScholes(call_flag, S, X, T, r, IVTrial, q)
    TheoPrice = calc_fair_value(call_flag, S, X, IVTrial, T, r)
    If (TheoPrice > a) Then
        high = (high + low) / 2
    Else
        low = (high + low) / 2
    End If
Wend
resolve_iv = ((high + low) / 2)

End Function
Click to expand...
FYI: the "volatility" value that my BSM function receives is NOT a percentage, but a real. I am guessing 1.00 to your BSM would be 1%, which would explain the difference. Also, that value 5 (or 500%) is good for most interesting SPX strikes, but may not be large enough for some "wilder" products like UVXY. So if one observes a resulting value of 0 or 500%, then the function did not converge, and a larger "high" value may be needed. Doubling that high value will add one more iteration to the convergence.
 
Last edited:
DaveJ said:
Ah nice, I'll have to try that Jim, thanks for posting that. Question for you - where do you pull the dividend rate for the SPX? Right now, I'm just plugging assumptions for both the dividend rate and interest free rate. The Yield rtd function doesn't pull a value on the index.
Click to expand...
FYI: I believe a close estimation to the proper interest rates for SPX is the similar term LIBOR rate, so if you are working on a 30DTE option, then the 1M may be close approximation. Seems it is avail via RTD as =RTD("tos.rtd", , "LAST", "USD1MTD156N:FRED")
. USD3MTD156N:FRED If 90day, etc. You may also obtain it from FRED (St Louis FED), but that data is 7 days old. (I use FRED data)
Here is a list that I use:
symbols = ['USDONTD156N', 'USD1WKD156N', 'USD1MTD156N', 'USD3MTD156N', 'USD6MTD156N','USD12MD156N']


The solution for proper dividend for index products, such as SPX, is not obvious or trivial. The dividend for index options assumes a continuous dividend, not a lump payments, like SPY. I use a method detailed by Steve Speer a few years ago, but that method is not for everyone, as it requires accessing all options for each expiry to resolve. So I may not be good source for alternate methods. (FWIW: I think TOS still has the dividend handling incorrect, so not everyone needs precision)
 
garyw said:
FYI: the "volatility" value that my BSM function receives is NOT a percentage, but a real. I am guessing 1.00 to your BSM would be 1%, which would explain the difference.
Click to expand...

yup, that explains it. i should have thought of that.

re spx and dividends - spx is an index and doesn't pay dividends, however, in my searching there's conflicting information about whether the spx accounts for dividends. one article from investopedia had conflicting information in the same article. i found a reference to sp500 total return index, (^sp500tr on yahoo). i have a program that plots relative performance, so i plotted ^gspc (sp500 index on yahoo), spy, and ^gsp500tr. as you probably can't see in the graph below, spy and ^gspc500tr overlay each other and outperform spx, so my assumption is spx doesn't account for dividends. further investigation is needed. in my analysis, i don't account for dividends. until a year ago i didn't have a good source for dividend data so i didn't use dividends. i haven't gotten around to fixing that yet.

1631852711233.png
 
Even during trading hours TOS sometimes goes berserk and shows no IV values or even when it does when I pull the RTD option price it's completely wrong and have no idea where it is coming from since it does not match the value from the TOS trade tab

This one strike in particular .SPXW210917P4395 shows almost constantly above 100 when the actual value shown on TOS trade tab is less than 1 at the time I am looking at it this morning
 
Even during trading hours TOS sometimes goes berserk.
status1 said:
Even during trading hours TOS sometimes goes berserk and shows no IV.
Click to expand...

This is why you can not blindly relay on TOS or any other software, even on your own. Advantage of having own software is that it is easier to know and understand potential flows while with third party it is a 'propriety' and you have to guess.

Trading is not solely about being skillful with models. Models do help but can't substitute for reality.
 
It's just a shame that with all that liquidity in SPY and SPX you can't get a reliable IV to get an option price during trading hours other than a few strikes around ATM making use of excel RTD nearly useless for making risk graphs other than for educational purposes
There seems to be a bug with one strike in nearly all expirations that screws up the graph and make it useless so I have to remove that strike from the list and to make it more fun it's not always the same expiration so for each expiration I have to remove a different strike
Also I am not sure if that strike is even stable so as it comes closer to expiration the bug may move to another expiration I have not experimented with that yet
The other more disturbing problem is that it only happens in excel when using RTD as the price on TOS does not reflect the price that RTD shows in excel so I am not sure TD can do anything about it since the RTD value is not part of the application so I am not sure where the RTD comes up with this bogus value

On the other hand the prices are more stable even though it may or may not be accurate after trading hours

On this bug if I change the RTD from MARK to LAST it gets the right value for closer expiration otherwise for further out expirations it will get the last value but it will be off since it's not updated that often
 
You must log in or register to reply here.
Top
Contact Us