options risk graph using excel

jim leahy

Member
i want to clarify my comment about the spx not accounting for dividends. the dividend is lost, from comparing the index against the total return index, but the drop in price of the index components, on their ex-date, affects the index. the yield is currently 1.25%, based on spy. if the components ex-dates were evenly distributed, it amounts to about $.22 per trading day. the actual daily price change, and how to model it, is difficult to determine.
 

status1

Active member
garyw found a way to calculate it using the Steve Speer method he mentioned and I looked into it but it's way above my head but maybe you can understand it better
 

DaveJ

Member
i want to clarify my comment about the spx not accounting for dividends. the dividend is lost, from comparing the index against the total return index, but the drop in price of the index components, on their ex-date, affects the index. the yield is currently 1.25%, based on spy. if the components ex-dates were evenly distributed, it amounts to about $.22 per trading day. the actual daily price change, and how to model it, is difficult to determine.
I'm currently assuming 1.65% dividend rate and a 1.5% interest rate. May not be correct but I guess I'm using those inputs to 'curve fit' the T+0 line to match up w TOS.
 

status1

Active member
I'm currently assuming 1.65% dividend rate and a 1.5% interest rate
Do you tweak those values to curve fit to the values for each expiration and strike on TOS ?
Otherwise I don't see how you would get the right values if you are using the IV shown on TOS
It may work for a certain strike and expiration but it will not work over the entire SPX chain
 

DaveJ

Member
Do you tweak those values to curve fit to the values for each expiration and strike on TOS ?
Otherwise I don't see how you would get the right values if you are using the IV shown on TOS
It may work for a certain strike and expiration but it will not work over the entire SPX chain
Actually no, that's not the case. When I change the expiry, all the IV's in my model update to reflect that date. My PnL line adjusts accordingly and it is still quite accurate within $10. I checked 3 different expiration's and positions. All accurate to the Analyze model PnL line.

The is really just me tinkering with a model and trying to mold it where it will be useful to me for adjustment time. Yesterday I didn't even look at it as I have other tools I'm using to monitor instantaneous PnL outside of TOS. The IV's dropping out is an issue tho. I'll have to look into something that remembers the last IV streamed when it goes blank or something.
 

status1

Active member
My PnL line adjusts accordingly and it is still quite accurate within $10
I thought you were much more accurate based on the screenshot you showed earlier unless I misunderstood something
On that screenshot you were less than 50 cents off
I suppose that's as close as you can get without providing more accurate inputs
I will try those inputs and see if it gives me p/l prices that I can work with
 

DaveJ

Member
Yes correct. I was only stating the most I saw it deviate over the span of the range and multiple expiry's. Even so, $10 is nothing when the span crosses several thousand dollars on PnL. The rtd vs DDE functions straight from TOS can deviate more than that.
 
Last edited:

status1

Active member
Thanks for clarifying
In that case I can probably ignore the dividend and interest and not enter anything and probably get about the same accuracy
I have tested a few expirations and strikes and so far I am in the same range (50 cents ) that you showed earlier
I think the reason I wasn't getting the right values before because I was trying to use either the price after close or the think back
The problem with that is that while the price is locked after trading hours the IV is still moving so it no longer matches the price at the close
At least now I know I can get close enough prices I just have to do it during trading hours which is something I have to get used to as usually try to look at any new strategy or adjustment after hours or on the weekend when I have more time
 

jim leahy

Member
i added a page to the original spreadsheet that will calculate the iv based on the inputs to the model. this page uses activex controls and a few vba functions and routines so it may not run on all excel installations and you have to enable macro permissions for this file. you enter the expiration date, the symbol, put or call, and the strike on this page. the opra and rtd formulas are automatically calculated. the screenshot is shown below. the auto update feature updates on a 15 second interval, if enabled.

rg_plus.png
 

Attachments

  • risk_graph_plus.xls
    176 KB · Views: 5

status1

Active member
I am using excel 2010 and I have a compile error as soon as I open the file
Is there a workaround for this ?
Are those lines in red necessary ?
I commented them out for now just to get rid of the error message
 

Attachments

  • compile error.jpg
    compile error.jpg
    96.4 KB · Views: 3

status1

Active member
It seems to be working without those lines
I like the AM checkbox that adds the "W" for the weekly
What does the auto update supposed to do ?
 

jim leahy

Member
getsystemtime isn't used and can be safely deleted. gettimezoneinformation is referenced in
Sub get_timezone(), Function LocalOffsetFromGMT and Function ConvertLocalToGMT. these aren't used functions but i've seen undeclared function errors pop up at times even when not used. it's better to delete these functions or comment out the call to the function in these routines and then leave the declaration commented out or delete it. i'm using 32 bit office so these errors don't show up in my system. you can also try editing it as:

Private Declare PtrSafe Function GetTimeZoneInformation

but i don't know if that will cause other problems since i can't verity it on my installation.
 

jim leahy

Member
the auto update button will start a 15 second timer and update the calculated iv as the prices change.
you can stop the timer by unchecking the checkbox next to the button.
 

jim leahy

Member
i removed the unused declarations and functions that were causing a problem in 64 bit office and updated the excel file.
 

Attachments

  • risk_graph_plus.xls
    170 KB · Views: 10

status1

Active member
There is still one function left SYSTEMTIME that is causing the problem with 64 bit I am guessing that is needed for the auto update
I removed it on the one I modified I don't need it to auto update
 

status1

Active member
I did a comparison of the original file (Risk-graph) using the RTD input of IV from tos and nothing for the Div and Interest and your updated file (Risk-graph plus)using the calculated IV and 2.5 for Div and 0.1 for interest I find that the when I use the calculated IV in the Risk-graph I get an option price difference of about 1.66 between the calculated price and the TOS price

If I use the TOS IV without Div and Interest in the Risk-graph I get a difference of about 0.33 so it looks like at this time the TOS IV is still closer to the real price
 

jim leahy

Member
if you use the calculated iv and the same other parameters and prices in both the put/call page and the stuff page, the fair value should be within a penny. they use the same model.

the auto update doesn't use the systemtime function. i found the function declarations were in another module, called ThisWorkBook. the timer routine references a variable in that module so that probably caused the object to load and gave the error for the function declaration. you can delete the declarations in that module and the auto update timer should function ok. (i think)
 

status1

Active member
if you use the calculated iv and the same other parameters and prices in both the put/call page and the stuff page, the fair value should be within a penny. they use the same model.
Yes they are now
I think I had one of the settings for weekly instead of monthly so that was throwing off the result
 
Top
Contact Us