I found the issue. For certain markets there are additional charges assessed for intra-commodity spreads. I've found that this is true for at least meats and grains ... other markets like ES do not get these additional charges.
In the spread mentioned earlier the worst risk scenario was $1593 * 1.1 = $1752 for IM as seen in the spreadsheet. My spreadsheet unfortunately does not handle the intra-commodity spread charges. In this scenario those charges were $624, so ($1593 + $624) * 1.1 = $2439.
I haven't even looked at trying to figure out how to code the intra-commodity spread charges. Sorry, but my spreadsheet won't be of much use in these markets.
The following 2 users say Thank You to Dudetooth for this post:
Lesson for me: always check the various source (and first: your broker)! Anyway your spreadsheet is excellent!
I would ask about "... the worst risk scenario...": does it mean that IM have preset values (f.e., if I have short ESH6 P 1950 and ES close at 1932 on 01/07, IM is 4236$ and if ES close at 1911 on 01/08 the same position IM is 4326$)? In others words: can I in advance to know IM of my positions at certain levels of commodity?
When SPAN is calculated there are 16 different scenarios that they run for risk assessments that include changes in prices and/or volatility. It is a best guess according to the exchanges of how far a position can go against you in the next trading day based on the recent past price action.
If anyone is still interested in an XLS-SPAN fix for ICE's new SP6 format, I have some good news and some bad news.
Good news: I think I have a solution. ICE publishes the SPAN files as SP6 and CSV files. The CSV files are a lot easier to extract data from, but there are some down sides. For example, they no longer give an expiration date, so one has to be calculated in order to get DTE.
Bad news: Over the last year I have been working with a new spreadsheet and rewrote almost all of the main code addressing issues like calculating IM for portfolios/multi-legged spreads and futures. I worked through my solution for ICE's new risk files on the new spreadsheet. I looked, but it would be extremely difficult to rework the solution into the existing XLS-SPAN because the processes and layout are so different. I'm not sure I can commit the time and effort required into reinventing the wheel, especially when I've already reinvented it (so to speak).
More bad news: The new spreadsheet was a cooperative project with another individual. He is not inclined to share the spreadsheet publically.
Possible solution: I am willing to share the code I worked on (main calculations and functions). If there is interest, I can see about pulling out my portions of the code and put them in a bare-bones spreadsheet.
The following user says Thank You to Dudetooth for this post:
With ICE starting to charge too much for real time data ($110/month) on April 1st, I am inclined to tell you not to do the work to make this available to us because I suspect not many will be trading ICE products after April 1st.
I might be but not right away.
Are any other posters going to be trading ICE products after April 1st?
Thank you for all you have done so far Dudetooth! I use your spreadsheet every day.