@Dudetooth -

Is there a way to enhance the error tracking for cases where the option is listed but has no open interest? Here is an example - the 3rd one in the list has no open interest (the first 2 have some open interest):

HOF4 HOF4 P 2.49 -1
HOF4 HOF4 P 2.5 -1
HOF4 HOF4 P 2.51 -1



The problem is once the macro stops, if you try running "portfolio" again, no results are given. You actually have to exit the spreadsheet, and start over. I'm guessing the first time through it is writing something "bad" to the temp sheet, and then when you re-run, no results are obtained.

Thanks!

I have been playing around with currencies in the SPAN spreadsheet. Here is what I have found:

1. In the spreadsheet, the delta calculations for Euro (6E) and Yen (6J) are double what they actually are. This can be corrected easily in the code (by changing code to d1=1). @Dudetooth - can you verify this?

2. For settlement dates, the Options Express Trade Calculator gives the options expiration date as 3 days before the SPAN settlement date. This is true for all currencies I looked at (BP, EC, CD, AD, JY). So, if you are used to calculating ROI based on OEX date, the SPAN spreadsheet ROI will be lower.

For ROI calculations, I believe you want to use options expiration date, not settlement date. Can someone confirm my thinking?


3. I have added the following currencies: BP, AD, CD. Code is below:

@Dudetooth -

Is there a way to enhance the error tracking for cases where the option is listed but has no open interest? Here is an example - the 3rd one in the list has no open interest (the first 2 have some open interest):

HOF4 HOF4 P 2.49 -1
HOF4 HOF4 P 2.5 -1
HOF4 HOF4 P 2.51 -1



The problem is once the macro stops, if you try running "portfolio" again, no results are given. You actually have to exit the spreadsheet, and start over. I'm guessing the first time through it is writing something "bad" to the temp sheet, and then when you re-run, no results are obtained.

Thanks!

@Dudetooth -

Try this one, based on 12/2/2013 settlement data:




this shows delta>100, which can't be.

I know that in-the-money options should be no greater than 100, but PC-SPAN says the same thing as the spreadsheet:


Just saying that I don't think it is the spreadsheet miscalculating ... at least not in this case.

Kevin,

Thanks for the code for the other currencies ... they'll make it to the next build.

I'm not seeing the issue with delta that you were seeing. I haven't added the news currencies to the code yet, so I just calculated a couple EC and JY options with the current code to see what the differences were (I did not change the d1 variable either).

SPAN's results:


Spreadsheet's results:


Sorry, I'm not seeing the discrepancies.

With the settlement dates I was looking at the Feb and Mar options for EC and JY.

From OX Options Quotes:
Euro FX February 2014 Options (66 days to expiration) Expiration Date: 2/7/2014
Euro FX March 2014 Options (94 days to expiration) Expiration Date: 3/7/2014

From CME's Product Calendar:


The Spreadsheet is reflecting the settlement date from the risk arrays. I looked at product calendars for CL, NG, GC, EC, JY, LC, LH on CME and KC and SB on ICE, and the only options I saw that had a last trade date that was different than the settlement was LH, so as far as I know only LH ROI would be affected (there may be others I didn't check yet).

I'm not sure what is going on then. Here is an example. The DTE in the spreadsheet shows 3 days difference. As of yesterday, the spreadsheet shows DTE=70, when it was actually 67 days to expiration.

enderqa, Here is what is in the file.

https://www.cmegroup.com/confluence/display/pubspan/Type+8+Records+-+Standard