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)
Do you have an way to calculate the days to expiration (DTE) for a given open? In the SPAN files CME disseminates, they give the options year and month, very similar to an options symbol. For example: Natural Gas, 201501. This is obviously NGF15 (with some strike price).
I tried looking at the Type records on CME's Web page and nothing jumped out.
Is there a DTE in the SPAN files somewhere? Or do you have to calculate it yourself? Or is there an elegant way to find this information?
Thanks in advance.
Can you help answer these questions from other members on NexusFi?
From that you will find that there is a 7 digit string (characters 73-79) that is the Time to Expiration (in years). In the above example that would be 0145205 ... multiply by 0.000365 and you get the DTE.
Also, there is a 8 digit string (characters 92-99) which is the Expiration Date as YYYYMMDD (20140128 in the example above).
Does anyone have historical SPAN files and would be willing to kindly share those? The files on the CME FTP ftp.cmegroup.com only go back to January 2013.
CME temporarily posted 2012 data at one time, though the formatting was different. It was only there for a short period of time, and unfortunately I did not grab it.
I don't know of anyone on the thread who did grab the data. Sorry.
I read through the forum pages, thank you for the information all of you have provided - you lot are really awesome to share all of this ^^
I am currently figuring out how to pull the data for the SGX (Singapore Ex.) and CME for the TSI Iron Ore.
Was playing around with your excel - XLS SPan 04b3 - it works perfectly, but does your excel support the SGX pa2 files?
It seems like I can only scan for positions for NYB and CME only for now :/
Each commodity has to be coded into the spreadsheet so that the correct lines of data can be found. You can, however, add as many as you would like. You just need to know the product code and a little about VBA to modify the file. If you want to know how, give me an example of a commodity you'd like to track and I can walk you through the process.
I am currently trying to track the FE and FEF (Iron Ore CFR China (62% Fe Fines) Swaps & Futures respectively) and also the TIO (Iron Ore as well) from the NYMEX exchange.
The SGX parameter files can be found here. (I can't post it here, its under the SGX main website, under Clearing,Span Download PM Cycle)
I tried playing around with the automated download for the SGX files but they change the urls every day.(not just the date)
How did you manage to extract data from the pa2 files?
Really appreciate if you could give me a mini walkthrough =)
The bad: Adding the SGX exchange and commodities should have been fairly easy, but as I started to look at their pa2 files I noticed that they are missing the Type P line of data. The Type P data has info on where to place the decimals in the prices and if the data is missing it will throw an error in the spreadsheet. I will check, but I don't think that is an easy fix for this in the spreadsheet.
The good: Adding TIO and any other CME or NYB commodities is fairly easy.
1) In Visual Basic Editor, open the Function P34Bstring in the Functions module.
2) Find Case "ZB" and right above that line insert this:
Case "TIO"
strS(0) = "P NYMICT OOF"
strS(1) = "3 NY-TIO1001"
strS(2) = "4 NY-TIO01"
strS(3) = "B NYMICT OOF"
strS(4) = "82NYMTIO TIO FUT"
Final code with TIO inserted should look like this:
Case "SI"
strS(0) = "P CMXSO OOF"
strS(1) = "3 CX-SI 1001"
strS(2) = "4 CX-SI 01"
strS(3) = "B CMXSO OOF"
strS(4) = "82CMXSI SI FUT"
Case "TIO"
strS(0) = "P NYMICT OOF"
strS(1) = "3 NY-TIO1001"
strS(2) = "4 NY-TIO01"
strS(3) = "B NYMICT OOF"
strS(4) = "82NYMTIO TIO FUT"
Case "ZB"
strS(0) = "P CBT17 OOF"
strS(1) = "3 17 1001"
strS(2) = "4 17 01"
strS(3) = "B CBT17 OOF"
strS(4) = "82CBT17 17 FUT"
3) Go to the Function T8string
4) Find Case "ZB" and right above that line insert this:
Case "TIO"
str5 = "82NYMICT TIO OOF"
Final code with TIO inserted should look like this:
Case "SI"
str5 = "82CMXSO SI OOF"
Case "TIO"
str5 = "82NYMICT TIO OOF"
Case "ZB"
str5 = "82CBT17 17 OOF"
Where did I find these lines of code? All it takes is a little footwork to find the info in the pa2 file. Open a CME pa2 file in Notepad.exe. The first line to look for is the Type P line of data. You know that TIO is traded on NYMEX, so do a search for "p nymtio". You'll find "P NYMTIO FUTIRON ORE FUTURE002000 000050000000000000000001USD$STD 00 IRON ORE FUTURES", but that line if for the futures. The next line down is what you need: "P NYMICT OOFIRON ORE 62% FE002002 000050000000000000000001USD$STD 00EUROIRON ORE 62% FE,(TSI) CHINA APO". If you noticed, you don't need all of it for the code just the first characters through "OOF". Right underneath that are the Type 3, Type 4 and Type B lines. There are a lot of Type B lines, one for every futures and options contract. You have to go down quite a bit until you see "B NYMTIO FUT" which is a futures contract turn into "B NYMICT OOF" which is the first options contract. Notice that the Type B is "B NYMICT" not "B NYMTIO". Many commodities use a different code for the options than for the futures. That's what gave me the clue to search for "ICT" when finding the Type 82 line of data.
Confused yet? It sounds a lot worse than it really is. Compare the initial strings you see in the code to the lines of data you see in the pa2 file and after a while it should make sense.
With those two modifications in the spreadsheet I ran the numbers for an option and it match what CME Core was showing for it.
I attached a text file with the two modified modules so you can see the proper formatting because some of the spaces are removed in the lines of code when I post them in the reply.
Really appreciate your long and detailed post - I apologize for the really late reply.
I had played around with what you had mentioned, but I didn't make much progress. It was eating quite a bit of my time so I had decided to pass what you had said over to my colleagues who understood more about programming so they could tackle it in their free time. They were pretty amazed to see what you had come up with.
Instead, I had customized a spreadsheet that had links to my current position files (in the format they are recorded) - it converts and exports all of them as CSV files, and runs them through PC - Span to gather the margins I require. Not the most efficient way, but it currently works for me.
Thank you for your time! Awesome forum and people =)