Wouldn't it be easier to just post your VBA code? Understanding your steps aren't that difficult if you know a little about programming and can inspect the code. I can post it, but don't want to without Dudetooth's permission.
I took a quick look over at CME ... most other products we have in this spreadsheet seem to have the same date for last trade and settlement, even LC. I checked a couple on ICE and the date in the SPAN is what they list as the last trade date. It almost looks like LH is odd man out.
Yeah, you're probably right. The other option is to download a trial copy of excel and look at the code that way. Assuming he has a windows box to load it on. If he has no windows computers, then that's a non-starter.
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:
Maybe we can start small. Here are four lines from the CME settlement's file
I believe these are Crude Oil options. This is because "NYMLO" and "CL" are symbols CME uses for Crude Oil. Here is what I can surmise from the fields. The fist number, e.g., "354561", is NOT in the file, it is the line number. After the colon ":" the actual file begins.
The first feild is 81NYMLO or 82NYMLO. I don't know the difference between the 81 and 82. But I see it in a number of lines.
The "CL" field probably lists the underlying future
The OOFC201403/OOFP201403 lists "options on futures" and then "C" or "P" for call or put. Then the year and month of the underlying future.
The next date "201403" is probably the expiration of the option, in this case the same as the future.
Then it looks like there is a series of numbers I don't understand. There is a "00092" which I think may the the option's strike price. But then it's all Greek to me.
If you could help me understand the long string of numbers, and let me know how you converted that into the Initial Margin (IM) that would be great. I don't need to calculate the Delta. You have some other fields: price, value, DTE, Risk, etc. I dont' need those either at the moment. Like I said, I want to start small and just get the IMs. Then I can look to see how easy or hard it is too add other things.
Lastly, I did download Dudetooth's Excel Macro (xlsm) file. I can load it in LibreOffice but I can't see the underlying "guts" of the program. (I do have a Window's box, but I don't feel like trying to setup Excel just to do one thing).
As for posting the source code. I would recommend you use pastebin: Pastebin.com - #1 paste tool since 2002!. You post the code there. The Web site give you a URL, and then post the URL/link here in the forum. This way the forum isn't cluttered with code.