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.......)


Active member
VERY close. :) See attached.
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


Active member
Name starts with Hoad...
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


Active member
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 ?

jim leahy

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.

jim leahy

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
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.


Active member
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:


Active member
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
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!


Active member
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


Active member
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
Contact Us