Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
Genuine reviews from real traders, not fake reviews from stealth vendors
Quality education from leading professional traders
We are a friendly, helpful, and positive community
We do not tolerate rude behavior, trolling, or vendors advertising in posts
We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community. It's free for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
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.
That's fine by me, but it seems to me that it would be rather difficult to understand the process without seeing the corresponding data in the spreadsheet.
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.
FYI, when I open a .pa2 file in a text editor, it appears to be the "expanded" version of the file layout. Here is the file structure for the expanded .pa2 file: