PC-SPAN - futures io
futures io



PC-SPAN


Discussion in Options

Updated
      Top Posters
    1. looks_one Dudetooth with 218 posts (213 thanks)
    2. looks_two ron99 with 115 posts (54 thanks)
    3. looks_3 CafeGrande with 30 posts (4 thanks)
    4. looks_4 BlueRoo with 24 posts (5 thanks)
      Best Posters
    1. looks_one Dudetooth with 1 thanks per post
    2. looks_two SMCJB with 1 thanks per post
    3. looks_3 ron99 with 0.5 thanks per post
    4. looks_4 BlueRoo with 0.2 thanks per post
    1. trending_up 120,615 views
    2. thumb_up 316 thanks given
    3. group 76 followers
    1. forum 608 posts
    2. attach_file 159 attachments




Welcome to futures io: the largest futures trading community on the planet, with well over 125,000 members
  • 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 and simple.

-- Big Mike, Site Administrator

(If you already have an account, login at the top of the page)

 
Search this Thread
 

PC-SPAN

(login for full post details)
  #401 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received

Because of the CME's screwy idea of putting the files for December 31st in the following year's folder for 2014 & 2015 (2013 is OK), XLS-SPAN will not download those files.

Started this thread Reply With Quote
 
(login for full post details)
  #402 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ron99 View Post
Because of the CME's screwy idea of putting the files for December 31st in the following year's folder for 2014 & 2015 (2013 is OK), XLS-SPAN will not download those files.

Try this:

Go into the Download module and find the Sub downloadRiskFile(targetDate)
Look down until you see the section 'download risk inside that sub

Replace:
Else:
If SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & namecme, path & "\" & namecme) = True Or _
SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & Left(dateit, 4) & "/" & namecme, path & "\" & namecme) = True Or _
SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & Left(dateit, 4) & "/" & Left(dateit, 6) & "/" & namecme, path & "\" & namecme) = True Then


with:
Else:
Dim dateyear As Double
dateyear = Left(dateit, 4)
If SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & namecme, path & "\" & namecme) = True Or _
SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & dateyear & "/" & namecme, path & "\" & namecme) = True Or _
SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & dateyear + 1 & "/" & namecme, path & "\" & namecme) = True Or _
SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & dateyear & "/" & Left(dateit, 6) & "/" & namecme, path & "\" & namecme) = True Then

Reply With Quote
The following 2 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #403 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received

I have found a bug in the latest XLS-SPAN.

If I have an option or spread on Track and click on Select or Spread to get the data for the date in cell A1 and then I click on H-Track to do a Historic Track of the range of dates from the date in A1 to the date in D1, the results on HistData and Hist Pivot do not include the date in cell A1. They start with the next date.

If you are able to fix this, besides posting a fixed XLS-SPAN spreadsheet, please let us know what changes were made to the macros so we can make those changes rather than having to make all of our modifications to your fixed spreadsheet.

Started this thread Reply With Quote
 
(login for full post details)
  #404 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ron99 View Post
I have found a bug in the latest XLS-SPAN.

If I have an option or spread on Track and click on Select or Spread to get the data for the date in cell A1 and then I click on H-Track to do a Historic Track of the range of dates from the date in A1 to the date in D1, the results on HistData and Hist Pivot do not include the date in cell A1. They start with the next date.

Sorry for the late response.

It's actually not a bug, it was intentional coding. It was designed to prevent days with missing risk array files from being populated in the Hist_Data (i.e. weekends would show up as 3 consecutive days with the same data). It is comparing the DTE before and after it runs the TrackSelect code for that day. If the DTE is the same it is considering the risk arrays to be missing and prevents a rewrite of the data. If the DTE is different then it is new data and it is copied to Hist_Data.

To get all of your dates when running H-Track:
1) Fill in your option or spread on the Track tab and have your start date in A1 and end date in D1 ... just as normal.
2) If you are tracking a spread add a matching number in S1 column for all legs of that spread.
3) Highlight all options you want to historically track (it can be a mix of spreads an single options).
4) Then instead of clicking Select or Spread to get the first day's data, just click H-Track and the first date will be included in the results. H-Track was designed to run without having to prime the pump with the first day's data.

Hope this helps.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #405 (permalink)
keong
Singapore + Singapore
 
 
Posts: 13 since Nov 2015
Thanks: 10 given, 2 received

Hi Dudetooth,

Fantastic program. I'm in awe of your programming skills and your generosity in sharing this with the forum.

I have a question if you don't mind.

I managed to get the XLS-SPAN (05a) to work.

The Download and Run macro buttons work beautifully as does the Select macro.

1. In the Track worksheet, I input today's date into cells A1 and D1, before running the Select macro, which populates the necessary data, other than the record-keeping columns.

How to do I populate the record-keeping columns (e.g. Entry Date, Entry DTE, etc.) with my existing portfolio (with positions being entered at different dates) and still get the select button to work. Everytime I key directly into the white cells (e.g. column I) before the Entry Date, the program bugs out on me with a "Run-time Error 6: Oveflow". I'm guessing it's because I can only key into the green cells, but I can't seem to find where I can enter these record-keeping data.

I ran the Entry Values macro, and it populates with today's start date. However, it doesn't seem to allow me to enter earlier dates. Is it because I need to download the risk arrays starting from my entry dates until todate?

Question is how can I populate the entry data for my existing portfolio (positions opened at different dates) and get the Select and Entry values macros to work?

2. Secondly, the All macro doesn't seem to work for me. I get a "Compile error: File or sub function not defined:"

Sorry for asking what must seem to be a noob question, but I've spent the past few hours trying to get this to work with no success.


Many thanks!

Cheers,
K

Reply With Quote
 
(login for full post details)
  #406 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received

@keong,

I'll try to troubleshoot your issue and give you a few pointers as well:


keong View Post
In the Track worksheet, I input today's date into cells A1 and D1 ...

A1 is the start date and D1 the end date if you are doing multiple dates, like Historic (H-Track or H-Scan) or multiple downloads. If you are just doing a single day Track, Scan or Download you only need a value in A1.


keong View Post
How to do I populate the record-keeping columns (e.g. Entry Date, Entry DTE, etc.) with my existing portfolio (with positions being entered at different dates) and still get the select button to work. Everytime I key directly into the white cells (e.g. column I) before the Entry Date, the program bugs out on me with a "Run-time Error 6: Oveflow". I'm guessing it's because I can only key into the green cells, but I can't seem to find where I can enter these record-keeping data.

The routine Entry Values is using the current date (A1) to calculate and the record that as the first day's data (any column header with Entry in the name). So to set up your portfolio correctly you will need the date you entered the trade in A1, highlight a cell in the row of the option position(s) you entered that day and then click Entry Values. It will crunch the numbers and transfer data to the Entry columns. Typically you only change values in green cells ... you shouldn't have to change anything in the white cells unless the actual price you got for your option is different than the closing price (recorded as Entry Price).


keong View Post
I ran the Entry Values macro, and it populates with today's start date. However, it doesn't seem to allow me to enter earlier dates. Is it because I need to download the risk arrays starting from my entry dates until todate?

Yes. If you put the oldest date you need in A1 and the most recent date in D1 and click Download it will download all of the risk files in that date range.


keong View Post
2. Secondly, the All macro doesn't seem to work for me. I get a "Compile error: File or sub function not defined:"

I had a bit of a coding error there when I adapted that part of the code into this spreadsheet ... you're the first to find this issue. I'm not sure how familiar you are with VBA, so I'll try to walk you through the fix.
1) Press Alt+F11 to open the Marco Editor
2) In the left panel click + next to Modules to see all of them
3) Double-click on the Track module ... that modules code should appear in the main panel to the right
4) Scroll down to find a module with the first line that reads "Sub TrackAll()"
5) Replace all of that code for that module (down until you see "End Sub") with this code:

Sub TrackAll()
Dim rAll As Range, lastrow As Integer
If ActiveSheet.Name = "Track" Then
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Set rAll = Range("A3:A" & lastrow)
rAll.Select

With InfoForm
.Caption = "Track All Options"
.tbInfo.Value = "Tracking All ..."
.Top = Int(((Application.Height / 2) + Application.Top) - (.Height / 2))
.Left = Int(((Application.Width / 2) + Application.Left) - (.Width / 2))
.Show vbModeless
End With
DoEvents

Call TrackSelect

Range("A1").Select

Else: Sheets("Track").Select
End If
End Sub


6) Save ... it should work now.

If that doesn't work, or if you have any issues with the fix let me know.

Reply With Quote
The following 2 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #407 (permalink)
keong
Singapore + Singapore
 
 
Posts: 13 since Nov 2015
Thanks: 10 given, 2 received

Hi Dudetooth,

Works like a charm now.

I noticed that the TrackAll windows doesn't close automatically after running the macro, so I added in an additional line into the TrackAllNow sub.

A bit of cosmetics but if I can save an extra click, that's one less click that I have to do...

Cheers and thanks so much for your kind help,
K

Sub trackAllNow(control As IRibbonControl)
With InfoForm
.Caption = "Track All Options"
.tbInfo.Value = "All options in 'Open' accounts being tracked ..."
.Top = Int(((Application.Height / 2) + Application.Top) - (.Height / 2))
.Left = Int(((Application.Width / 2) + Application.Left) - (.Width / 2))
.Show vbModeless
End With
DoEvents

Call TrackAll

With InfoForm
.Caption = "Track All Options"
.tbInfo.Value = "Tracking all 'Open' options complete."
.Top = Int(((Application.Height / 2) + Application.Top) - (.Height / 2))
.Left = Int(((Application.Width / 2) + Application.Left) - (.Width / 2))
.Show vbModeless
End With
DoEvents
Application.OnTime Now + TimeValue("00:00:02"), "killInfoForm"
End Sub

Reply With Quote
The following user says Thank You to keong for this post:
 
(login for full post details)
  #408 (permalink)
keong
Singapore + Singapore
 
 
Posts: 13 since Nov 2015
Thanks: 10 given, 2 received

Hi Dudetooth,

Now that I've managed to get the xls working (with your help), I ran the Track worksheet for 2 option positions, both singly and as a spread.

A. The IMs for the short position and the spread is correct (compared with the Zaner platform), however, I find that the long leg seems a little off. For e.g., on 20170113,

-1 OEW3J7 P1890 has an IM of 1102
-1 OEW3J7 P1890 +2 OEW3J7 P1675 has an IM of $297

However, the following long positions have IMs which differ slightly from Zaner platform.

+2 OEW3J7 P1675 has an IM of $205, compared to $200 on the Zaner platform.
+2 OESH7 P2025 has an IM of $570, compared to $0 on Zaner.

So now, I'm a little puzzled as to which is correct? Any thoughts?


B. Secondly, how do we enter in the Entry price (if different from the closing price) and have the Track worksheet calculate the Current ROI% and P/L? Will the macro automatically compute or do we have to manually enter in the formulas ourselves?

Looking forward to hearing from you.


Cheers,
K

Reply With Quote
 
(login for full post details)
  #409 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


keong View Post
However, the following long positions have IMs which differ slightly from Zaner platform.

+2 OEW3J7 P1675 has an IM of $205, compared to $200 on the Zaner platform.
+2 OESH7 P2025 has an IM of $570, compared to $0 on Zaner.

So now, I'm a little puzzled as to which is correct? Any thoughts?

IM for long options is kind of misleading, since there isn't any really. From CME:

For long option value (money you pay) we create a credit in the PC Span calculation and for short option value (money you collect) we create a debit. This way you are getting credit against the margin (span risk) calculation for the money that you have already paid out. Long option value is always greater than the span risk that is calculated because we understand that you cannot lose more than what you have paid for a long option. For a short option we create a debit because we want to make sure that you are keeping the premium for that option in the account until you unwind the position or it expires. In other words, a short option will also have additional margin that has to be posted that is not covered by the premium collected.

Normally, you will see that the IM calculated for a long option will be nearly identical to the value of the option.


keong View Post
B. Secondly, how do we enter in the Entry price (if different from the closing price) and have the Track worksheet calculate the Current ROI% and P/L? Will the macro automatically compute or do we have to manually enter in the formulas ourselves?

If you use the Entry Values macro to populate the Entry price and it is different that what you actually bought/sold the option, just make the change manually in the Entry Value column to reflect your price. From then on, when you use the Track Select/Spread/All it will calculate profit/loss based on that value.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #410 (permalink)
illinoisrei
Seattle, WA
 
 
Posts: 4 since Dec 2016
Thanks: 2 given, 0 received

Dudetooth,

I echo the praise of many before - amazing work on this tool and some mad VBA skills!!

I've been using Windows Task Scheduler to run a batch file that loads the risk file at the end of the day along with a position file (.pos) covering multiple accounts, exports the results in .xls and sends it out in an email.

This process is automatic but requires PC-SPAN to run.

It looks like it may be possible to open the .pos files in Excel as .xml and perhaps move the position info into your tool and get the summary data that way, sans PC-SPAN.

Does that sound feasible?

Reply With Quote
 
(login for full post details)
  #411 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


illinoisrei View Post
It looks like it may be possible to open the .pos files in Excel as .xml and perhaps move the position info into your tool and get the summary data that way, sans PC-SPAN.

Does that sound feasible?

Sure. If you can get your positions into Excel you should be able to plug them into the spreadsheet.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #412 (permalink)
uwevoelker
Hamburg, Germany
 
 
Posts: 15 since May 2014
Thanks: 14 given, 4 received

I have a general question about the SPAN data files. Is the price of the underlying (ES in my example) part of the .c.pa2 files? I see it in Ron99's backtest spread sheet.

I have looked for it in the SPAN file format description, but could not find it.

Reply With Quote
 
(login for full post details)
  #413 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


uwevoelker View Post
I have a general question about the SPAN data files. Is the price of the underlying (ES in my example) part of the .c.pa2 files? I see it in Ron99's backtest spread sheet.

I have looked for it in the SPAN file format description, but could not find it.

Yes, the underlying price is in the span files.

Check out CME for more details about what is in the risk files.
Risk Parameter File Layouts for the Positional Formats - SPAN - Confluence

Reply With Quote
The following 2 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #414 (permalink)
uwevoelker
Hamburg, Germany
 
 
Posts: 15 since May 2014
Thanks: 14 given, 4 received

Now I found it! I was looking for a field "underlying price" or similar. Instead I have to search for CME/ES/FUT. It is in Type 82 (like for the options).

Reply With Quote
The following user says Thank You to uwevoelker for this post:
 
(login for full post details)
  #415 (permalink)
illinoisrei
Seattle, WA
 
 
Posts: 4 since Dec 2016
Thanks: 2 given, 0 received

Hi Dudetooth,

I wanted to track the regular S&P and the associated weekly's (EV, EV1, EV2, EV3, EV4) so I added the necessary cases to the COption class module. I was able to mimic the code for the minis so it was relatively easy and seems to work.

I'd also like to track the Wednesday weekly's (E1C, S1C, etc.) but I doubt I can use the same approach.

How did you find the string values to associate with each commodity?

I'm loving this tool!

Reply With Quote
 
(login for full post details)
  #416 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


illinoisrei View Post
Hi Dudetooth,

I wanted to track the regular S&P and the associated weekly's (EV, EV1, EV2, EV3, EV4) so I added the necessary cases to the COption class module. I was able to mimic the code for the minis so it was relatively easy and seems to work.

I'd also like to track the Wednesday weekly's (E1C, S1C, etc.) but I doubt I can use the same approach.

How did you find the string values to associate with each commodity?

I'm loving this tool!

I'm glad you are finding it useful.

You should be able to use the same approach for the E1C and S1C as you did for the EV1.

The best way find that info is to open up a pa2 file in notepad and just start looking around. You'll see the order that they have all of the information. The Type P line of data is the first for each instrument followed by the 2, 3, 4, and B lines. They'll repeat that for all of the instruments in that exchange before they list the Type 81 and 82 lines for all of those instruments.

Hope that makes sense.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #417 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received

@Dudetooth Would it be possible to get BV (Base Volatility) on the Hist Data sheet? The only place I see it now is the Scan sheet.

Started this thread Reply With Quote
 
(login for full post details)
  #418 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ron99 View Post
@Dudetooth Would it be possible to get BV (Base Volatility) on the Hist Data sheet? The only place I see it now is the Scan sheet.

@ron99 It's already baked in there.
On the Track Tab, insert a new column in between two existing ones and it will copy the header data validation list. Select the header cell of the new column and in the dropdown menu you will find BV.



Then you can run your Hist_Track and it will fill in the BV column for you.



And it will be copied over to the Hist_Data Tab.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #419 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received


Dudetooth View Post
@ron99 It's already baked in there.
On the Track Tab, insert a new column in between two existing ones and it will copy the header data validation list. Select the header cell of the new column and in the dropdown menu you will find BV.

I inserted a new column on Track and clicked on header row (row 2). The little thing was there like it was a drop down menu but nothing appeared when I clicked on it. I manually typed BV in the header and ran H-Track. The data for BV showed up on Track.

Then you can run your Hist_Track and it will fill in the BV column for you.

And it will be copied over to the Hist_Data Tab.

BV did not show up on Hist Data. Any idea why?

I am using XLS-SPAN (05a)

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #420 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ron99 View Post
BV did not show up on Hist Data. Any idea why?

I am using XLS-SPAN (05a)

I tried to duplicate your error ... I even went to the end of the header row and just typed in BV, but it worked on my copy. If I remember correctly, you have a modified version ... perhaps there is a conflict in the code.

I'm not sure if you'd be willing to let me take a look at your spreadsheet, but that would be the easiest way to troubleshoot.

Reply With Quote
The following 2 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #421 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received

FYI for some reason the delta that is on the XLS-SCAN Scan sheet, which comes from CME files, does not match the delta on CME's Daily Bulletin.

For example for Feb 16th the Daily Bulletin shows the delta for a June 1900 put as .044. But XLS-SPAN shows it as 4.61. I realize that .044 is the same as 4.40. But that is not the same as 4.61.

They both do have the same settlement price of 5.90.

Anybody know why?

Started this thread Reply With Quote
 
(login for full post details)
  #422 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ron99 View Post
FYI for some reason the delta that is on the XLS-SCAN Scan sheet, which comes from CME files, does not match the delta on CME's Daily Bulletin.

For example for Feb 16th the Daily Bulletin shows the delta for a June 1900 put as .044. But XLS-SPAN shows it as 4.61. I realize that .044 is the same as 4.40. But that is not the same as 4.61.

They both do have the same settlement price of 5.90.

Anybody know why?

From what I can gather, it is the difference between Current Delta and SPAN Composite Delta. SPAN Composite Delta is the base delta that is multiplied by a certain factor to get a "net delta" that CME uses in portfolio calculations. The risk arrays actually have both values, but for XLS-SPAN I decided to use the SPAN Composite Delta so that it would generate the same delta that you would see in PC-SPAN. The CME bulletins are posting the Current Delta.

Reply With Quote
The following 2 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #423 (permalink)
dkpmba
Toronto
 
 
Posts: 5 since Aug 2015
Thanks: 3 given, 1 received

Dudetooth - Is there a way to add Theta to Track and Hist Data worksheets?

Reply With Quote
 
(login for full post details)
  #424 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


dkpmba View Post
Dudetooth - Is there a way to add Theta to Track and Hist Data worksheets?

It's already there, just select it from the drop-down of the header (cTheta). If that header is on the Track tab when you run the H-Track code it will be included on the Hist Data.



Cheers

Reply With Quote
 
(login for full post details)
  #425 (permalink)
dkpmba
Toronto
 
 
Posts: 5 since Aug 2015
Thanks: 3 given, 1 received


Dudetooth View Post
It's already there, just select it from the drop-down of the header (cTheta). If that header is on the Track tab when you run the H-Track code it will be included on the Hist Data.



Cheers

Thanks, Dudetooth! I chime with others for this Great piece of work.

Reply With Quote
 
(login for full post details)
  #426 (permalink)
lawchan526
hong kong China
 
 
Posts: 1 since Apr 2017
Thanks: 0 given, 0 received

I am trying to get margin requirement of commodity option. but i get the error message - attempted an unsupport operation when i tag to " New portfolio ". How can i solve it ?

Reply With Quote
 
(login for full post details)
  #427 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


lawchan526 View Post
I am trying to get margin requirement of commodity option. but i get the error message - attempted an unsupport operation when i tag to " New portfolio ". How can i solve it ?

Sorry, I've never seen that error before ... then again I don't use PC-SPAN too much. If you step me through the specifics of what you are doing in PC-SPAN I'll try to help.

Reply With Quote
 
(login for full post details)
  #428 (permalink)
ItalianBmT
Java/IN
 
 
Posts: 13 since Nov 2013
Thanks: 2 given, 0 received

Pardon me as I am unfamiliar with programming. I am using the v05 file but I am unable to get the download macro from working correctly. I got the error that the download from the server failed. I tried out multiple dates.

Reply With Quote
 
(login for full post details)
  #429 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ItalianBmT View Post
Pardon me as I am unfamiliar with programming. I am using the v05 file but I am unable to get the download macro from working correctly. I got the error that the download from the server failed. I tried out multiple dates.

@ItalianBmT

Please try using XLS-SPAN v.05a ... it's easier for me to troubleshoot if we are on the same page.

Also, what dates did you try downloading? The only time I have seen the message that the download from the server failed was because a risk file for that date did not exist on CME or ICE.

Thanks

Reply With Quote
 
(login for full post details)
  #430 (permalink)
ItalianBmT
Java/IN
 
 
Posts: 13 since Nov 2013
Thanks: 2 given, 0 received

I am using the version 5a and i had tested it across mutliple dates.

thanks in advance

Reply With Quote
 
(login for full post details)
  #431 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ItalianBmT View Post
I am using the version 5a and i had tested it across mutliple dates.

thanks in advance

Sorry, I meant can you give me specific dates that you tried so I can see if I can reproduce the error.

Reply With Quote
 
(login for full post details)
  #432 (permalink)
ItalianBmT
Java/IN
 
 
Posts: 13 since Nov 2013
Thanks: 2 given, 0 received

I tried using 10 april, 15 and most recently 26 april

Reply With Quote
 
(login for full post details)
  #433 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ItalianBmT View Post
I tried using 10 april, 15 and most recently 26 april

Can you get to these links through your browser?
https://ftp://ftp.cmegroup.com/pub/span/data/cme/cme.20170426.s.pa2.zip
https://www.theice.com/publicdocs/span_files/icus/2017/04/NYB0426F.csv.zip

I tried April 10 and 26 in ver.05a and was able to download without that error. I'm wondering if you are being affected by a firewall.

Reply With Quote
 
(login for full post details)
  #434 (permalink)
ItalianBmT
Java/IN
 
 
Posts: 13 since Nov 2013
Thanks: 2 given, 0 received

Hi Dudetooth,
I am able to download both links. Clicking through the excel sheet still fails. Not sure what should I do next?

Reply With Quote
 
(login for full post details)
  #435 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ItalianBmT View Post
Hi Dudetooth,
I am able to download both links. Clicking through the excel sheet still fails. Not sure what should I do next?

I'm not really sure what is going on. At this point, it's going to be kind of difficult to troubleshoot without seeing what's going on.

I'm sorry I couldn't be more of a help, but if you'd like we could see about getting together online where you could share your screen and I could see if I can fix things for you. Just message me if you'd like to set something up.

Reply With Quote
 
(login for full post details)
  #436 (permalink)
ItalianBmT
Java/IN
 
 
Posts: 13 since Nov 2013
Thanks: 2 given, 0 received

It is ok no worries. You have been great help to everyone here thus far. Just by any chance could this be an issue with 64 bit office?

Maybe you could guide me to do more trouble shooting ie. Looking at debugger on the vba?

Reply With Quote
 
(login for full post details)
  #437 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ItalianBmT View Post
Just by any chance could this be an issue with 64 bit office?

Not that I am aware of.


ItalianBmT View Post
Maybe you could guide me to do more trouble shooting ie. Looking at debugger on the vba?

That error message was programmed into the code to be used only if the request for the downloaded file came up empty.

I'm not sure how comfortable you are with VBA, but the sub that you would be looking to check would be the SaveRiskFiles() in the Download module. If you step and take note of where you are in the code when that error show up for you that might help.

The only thing I can think of would be the date. The only way that I can get the spreadsheet to give me that error is if the date is incorrect. Can you confirm for me that if you go to the Track tab, enter "20170426" into cell A1, and you click Download that you still get the error "Unable to download Risk files from servers"?

Note: The date should be entered as YYYYMMDD in cell A1, but it should remain formatted as General ... make sure Excel does not format that cell into a date.

Reply With Quote
 
(login for full post details)
  #438 (permalink)
ItalianBmT
Java/IN
 
 
Posts: 13 since Nov 2013
Thanks: 2 given, 0 received


Dudetooth View Post
Not that I am aware of.


That error message was programmed into the code to be used only if the request for the downloaded file came up empty.

I'm not sure how comfortable you are with VBA, but the sub that you would be looking to check would be the SaveRiskFiles() in the Download module. If you step and take note of where you are in the code when that error show up for you that might help.

The only thing I can think of would be the date. The only way that I can get the spreadsheet to give me that error is if the date is incorrect. Can you confirm for me that if you go to the Track tab, enter "20170426" into cell A1, and you click Download that you still get the error "Unable to download Risk files from servers"?

Note: The date should be entered as YYYYMMDD in cell A1, but it should remain formatted as General ... make sure Excel does not format that cell into a date.

I got the date in the format above, and it is also format as general. Strange that it diesnt work for me somehow

Reply With Quote
 
(login for full post details)
  #439 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ItalianBmT View Post
I got the date in the format above, and it is also format as general. Strange that it diesnt work for me somehow

What version of Windows do you have (7 or 10 ... 32 bit or 64 bit)? What version of Office (2010, 2013, or 2016 ... 32 bit or 64 bit)? Maybe there is some compatibility issue.

Reply With Quote
 
(login for full post details)
  #440 (permalink)
ItalianBmT
Java/IN
 
 
Posts: 13 since Nov 2013
Thanks: 2 given, 0 received

I am using windows 10. 64 bit running on office 2016 64 bit as well..

Reply With Quote
 
(login for full post details)
  #441 (permalink)
ItalianBmT
Java/IN
 
 
Posts: 13 since Nov 2013
Thanks: 2 given, 0 received

I am not sure whether vba just skips when there is error because i do not get the prompt that the file exists, it just gors strUght to m5 unable to download

Reply With Quote
 
(login for full post details)
  #442 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received

I may have an idea of what may be happening. I should have a fix for you to try tomorrow. It may be related to the 64-bit version of Office.

Reply With Quote
 
(login for full post details)
  #443 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received

@ItalianBmT

It looks as though Office 64-bit has issues with some functions. The Lib "urlmon" may need to have "PtrSafe" added to the beginning and the Long variables as LongPtr to safely run in 64-bit Office. I don't currently have the 64-bit version of Office, so I am not sure if this will solve the issue.

Try going into the Download module at the top under "Option Explicit" and replacing:

Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long


with this:

Private Declare PtrSafe Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As LongPtr, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As LongPtr, _
ByVal lpfnCB As LongPtr) As LongPtr


Let me know if we need to adjust.

Reply With Quote
 
(login for full post details)
  #444 (permalink)
ItalianBmT
Java/IN
 
 
Posts: 13 since Nov 2013
Thanks: 2 given, 0 received

When I run, another part of the code sprung an error

Got a mismatch error here.

Private Function SaveWebFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then SaveWebFile = True
End Function

Reply With Quote
 
(login for full post details)
  #445 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ItalianBmT View Post
When I run, another part of the code sprung an error

Got a mismatch error here.

Private Function SaveWebFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then SaveWebFile = True
End Function

You mat need to change:
Private Function SaveWebFile to Private PtrSafe Function SaveWebFile

or perhaps:
Dim lngRetVal As Long to Dim lngRetVal As LongPtr

I'm afraid I won't be of much help with actually troubleshooting the code, but this may help:
https://msdn.microsoft.com/library/gg264421.aspx

It looks like there are a limited number of areas where you may encounter errors running VBA in Office 64-bit according to the article above. It would seem as though if you change the declarations that you should be OK.

Hope this helps.

Reply With Quote
 
(login for full post details)
  #446 (permalink)
TFOpts
Los Angeles, CA
 
 
Posts: 64 since May 2017
Thanks: 49 given, 136 received

@Dudetooth,

I'm very impressed by your XLS-Span tool, you did amazing work; thank you so much for taking the time do build this for us.

I have a question related to spread margins. Is this something you calculate in the tool (I couldn't figure out how you got the numbers)? Or is it something you get from the CME data? I couldn't find good references on how to calculate it online and was hoping for your guidance on that.

Thanks again for the excellent tool; and thanks in advance for your help.

Reply With Quote
 
(login for full post details)
  #447 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received

Thank you, glad that it is helpful.

Margin on spreads is derived from the data in the risk files from CME & ICE and calculated, based on the SPAN methodology. Check out https://www.cmegroup.com/clearing/files/span-methodology.pdf

Let me know if you have any other questions or want some specific details.

Reply With Quote
 
(login for full post details)
  #448 (permalink)
TFOpts
Los Angeles, CA
 
 
Posts: 64 since May 2017
Thanks: 49 given, 136 received

Thanks Dudetooth.

Can you think of a way to extract a few variables for each option that would allow us to calculate spread margins across options?

Your documentation says the following about calculating spread margins: [Position IM for all legs= Net * (Initial to Maintenance Ratio * Max(Risk Scenarios 1-16, Short Option Minimum)); Initial to Maintenance Ratio extracted from PA2].

If I understand correctly, the formula in the VBA code is:
If calcType = 3 Then
'*********** Risk scenarios added together for spreads
dicSprd.Item("R1") = dicSprd.Item("R1") + clsOpt.r1
dicSprd.Item("R2") = dicSprd.Item("R2") + clsOpt.R2
dicSprd.Item("R3") = dicSprd.Item("R3") + clsOpt.R3
dicSprd.Item("R4") = dicSprd.Item("R4") + clsOpt.R4
dicSprd.Item("R5") = dicSprd.Item("R5") + clsOpt.R5
dicSprd.Item("R6") = dicSprd.Item("R6") + clsOpt.R6
dicSprd.Item("R7") = dicSprd.Item("R7") + clsOpt.R7
dicSprd.Item("R8") = dicSprd.Item("R8") + clsOpt.R8
dicSprd.Item("R9") = dicSprd.Item("R9") + clsOpt.R9
dicSprd.Item("R10") = dicSprd.Item("R10") + clsOpt.R10
dicSprd.Item("R11") = dicSprd.Item("R11") + clsOpt.R11
dicSprd.Item("R12") = dicSprd.Item("R12") + clsOpt.R12
dicSprd.Item("R13") = dicSprd.Item("R13") + clsOpt.R13
dicSprd.Item("R14") = dicSprd.Item("R14") + clsOpt.R14
dicSprd.Item("R15") = dicSprd.Item("R15") + clsOpt.R15
dicSprd.Item("R16") = dicSprd.Item("R16") + clsOpt.R16

'*********** Short option minimum added together for spreads
dicSprd.Item("PosSOM") = dicSprd.Item("PosSOM") + clsOpt.PosShortMin

'*********** Write all risk scenarios to array and find max scenario/SOM to define total risk
If iArrPos = totOpt Then
vArrayS = dicSprd.items()
Risk2 = Application.WorksheetFunction.Max(vArrayS)
If Risk2 = 0 Then Risk2 = 1
dicFut(i)("SprdRisk") = Risk2
End If
End If
To make this work I think we would have to extract the initial maintenance ratio, R1-16 and the PosSOM for each option. With that information we should be able to calculate the spread IM in Excel fairly easily. Would you agree?

Thanks

Reply With Quote
 
(login for full post details)
  #449 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received

I guess I'm not tracking what you are looking to do. It seems as though you are asking how to get XLS-SPAN to calculate spread IM ... It already does that (extracting all the needed info for each option from the pa2 files). Am I misunderstand?

Reply With Quote
 
(login for full post details)
  #450 (permalink)
TFOpts
Los Angeles, CA
 
 
Posts: 64 since May 2017
Thanks: 49 given, 136 received

Sorry, I should've explained.

I'm looking to back test different strategies more quickly. So far I've exported complete ES put data from XLS-Span to a database. I'm now querying the database to back test different strategies.

The problem is I can only get option level data (the standard output from the "Scan" capability of XLS-Span). To allow me to quickly test spread strategies, I would need to load enough information in the database to be able to independently calculate the spread margin.

So that's why I'm wondering if it's possible to export the data necessary to calculate spread margins at the option level. That way I can combine any group of options and get the spread IM requirement with database queries instead of relying on XLS-Span to do the calculation.

Hopefully that clarifies my purpose.

Reply With Quote
 
(login for full post details)
  #451 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received

Ok, I'm following you now.

SOM and initial to maintenance ratio are both constants, or at least they change very rarely. For ES SOM is $35 and initial to maintenance ratio is 110%.

You would just need the 16 risk scenarios for each option in your database. You could add lines in the WriteResults routine to get R1-R16 to show up on the spreadsheet and it should work out the way you are looking to use it.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #452 (permalink)
TFOpts
Los Angeles, CA
 
 
Posts: 64 since May 2017
Thanks: 49 given, 136 received

I was able to extract the necessary data and match what XLS-Span is calculating as a spread margin.

If others are interested, the formula for the SPAN initial margin on spreads is:

IMSpread = Init Maint Ratio * MAX(Short Minimum * # of short positions, Weighted R1 through R16)

Where,
  • Initi Maint Ratio is typically 1.1
  • Short Minimum is now $37 but can change
  • Weighted R# is R# scenario result multiplied by -number of positions (so if you sell 2 options and you therefore have -2 positions, you multiply by 2, not -2) for each option and then added together for each R#. Do this for each R# from 1 to 16 and take the maximum of those results or the Short Minimum value * the number of short positions.

An example on the weighted R. Say you are selling 1 option A and buying 2 option B. So number of positions for A is -1 and number of positions for B is +2. R1 value for A is 100 and for B it's 25. Weighted R1 is 100 * -(-1) + 25 * -(2) = 50.

For those interested in the coding changes I made to extract this data, here they are:
In the WriteResults subroutine, define the following variables:
Dim colSOM As Integer, colMaintRatio As Integer, colR1 As Integer, colR2 As Integer, colR3 As Integer, colR4 As Integer
Dim colR5 As Integer, colR6 As Integer, colR7 As Integer, colR8 As Integer, colR9 As Integer, colR10 As Integer
Dim colR11 As Integer, colR12 As Integer, colR13 As Integer, colR14 As Integer, colR15 As Integer, colR16 As Integer
Then add the following to find the columns in which to populate the results. Note that you'll need to include column headers in the appropriate sheet that match the names below. For example, if you want R1, you need to add a column header in the "Scan" tab that says "Risk Scen 1".
'*********** Find column headers for intermediate spread IM, skip if not found
colSOM = getColumn("Short Min")
colMaintRatio = getColumn("Init Maint Ratio")
colR1 = getColumn("Risk Scen 1")
colR2 = getColumn("Risk Scen 2")
colR3 = getColumn("Risk Scen 3")
colR4 = getColumn("Risk Scen 4")
colR5 = getColumn("Risk Scen 5")
colR6 = getColumn("Risk Scen 6")
colR7 = getColumn("Risk Scen 7")
colR8 = getColumn("Risk Scen 8")
colR9 = getColumn("Risk Scen 9")
colR10 = getColumn("Risk Scen 10")
colR11 = getColumn("Risk Scen 11")
colR12 = getColumn("Risk Scen 12")
colR13 = getColumn("Risk Scen 13")
colR14 = getColumn("Risk Scen 14")
colR15 = getColumn("Risk Scen 15")
colR16 = getColumn("Risk Scen 16")
And finally write the results.
'*********** Write spread IM intermediate values
If colSOM > 0 Then Cells(iFillRow, colSOM) = clsOpt.PosShortMin: Cells(iFillRow, colSOM).NumberFormat = "#0.0#"
If colMaintRatio > 0 Then Cells(iFillRow, colMaintRatio) = clsOpt.InitialRatio: Cells(iFillRow, colMaintRatio).NumberFormat = "#0.0#"
If colR1 > 0 Then Cells(iFillRow, colR1) = clsOpt.r1: Cells(iFillRow, colR1).NumberFormat = "#0.0#"
If colR2 > 0 Then Cells(iFillRow, colR2) = clsOpt.R2: Cells(iFillRow, colR2).NumberFormat = "#0.0#"
If colR3 > 0 Then Cells(iFillRow, colR3) = clsOpt.R3: Cells(iFillRow, colR3).NumberFormat = "#0.0#"
If colR4 > 0 Then Cells(iFillRow, colR4) = clsOpt.R4: Cells(iFillRow, colR4).NumberFormat = "#0.0#"
If colR5 > 0 Then Cells(iFillRow, colR5) = clsOpt.R5: Cells(iFillRow, colR5).NumberFormat = "#0.0#"
If colR6 > 0 Then Cells(iFillRow, colR6) = clsOpt.R6: Cells(iFillRow, colR6).NumberFormat = "#0.0#"
If colR7 > 0 Then Cells(iFillRow, colR7) = clsOpt.R7: Cells(iFillRow, colR7).NumberFormat = "#0.0#"
If colR8 > 0 Then Cells(iFillRow, colR8) = clsOpt.R8: Cells(iFillRow, colR8).NumberFormat = "#0.0#"
If colR9 > 0 Then Cells(iFillRow, colR9) = clsOpt.R9: Cells(iFillRow, colR9).NumberFormat = "#0.0#"
If colR10 > 0 Then Cells(iFillRow, colR10) = clsOpt.R10: Cells(iFillRow, colR10).NumberFormat = "#0.0#"
If colR11 > 0 Then Cells(iFillRow, colR11) = clsOpt.R11: Cells(iFillRow, colR11).NumberFormat = "#0.0#"
If colR12 > 0 Then Cells(iFillRow, colR12) = clsOpt.R12: Cells(iFillRow, colR12).NumberFormat = "#0.0#"
If colR13 > 0 Then Cells(iFillRow, colR13) = clsOpt.R13: Cells(iFillRow, colR13).NumberFormat = "#0.0#"
If colR14 > 0 Then Cells(iFillRow, colR14) = clsOpt.R14: Cells(iFillRow, colR14).NumberFormat = "#0.0#"
If colR15 > 0 Then Cells(iFillRow, colR15) = clsOpt.R15: Cells(iFillRow, colR15).NumberFormat = "#0.0#"
If colR16 > 0 Then Cells(iFillRow, colR16) = clsOpt.R16: Cells(iFillRow, colR16).NumberFormat = "#0.0#"
Thanks.

Reply With Quote
The following user says Thank You to TFOpts for this post:
 
(login for full post details)
  #453 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received

TFOpts, could you attach your XLS-SPAN Excel file with the changes you made? Give it another name other than what Dudetooth gave to his.

This looks like it could be valuable. Thanks

Started this thread Reply With Quote
 
(login for full post details)
  #454 (permalink)
TFOpts
Los Angeles, CA
 
 
Posts: 64 since May 2017
Thanks: 49 given, 136 received


ron99 View Post
TFOpts, could you attach your XLS-SPAN Excel file with the changes you made? Give it another name other than what Dudetooth gave to his.

This looks like it could be valuable. Thanks

See attached. My programming skills are not nearly as advanced as Dudetooth but I tried to stay true to the XLS-SPAN architecture. The new fields start in column AC of the "Scan" tab.

Attached Files
Register to download File Type: zip XLS-SPAN (05a)_SprdIM.zip (281.9 KB, 13 views)
Reply With Quote
The following user says Thank You to TFOpts for this post:
 
(login for full post details)
  #455 (permalink)
TFOpts
Los Angeles, CA
 
 
Posts: 64 since May 2017
Thanks: 49 given, 136 received


ron99 View Post
I don't think your Excel file is there. I doubt it could only be 22 bytes. I get nothing when I try to download it.

@ron99. My mistake, thanks for pointing this out; the attachment has been updated.

Reply With Quote
 
(login for full post details)
  #456 (permalink)
TFOpts
Los Angeles, CA
 
 
Posts: 64 since May 2017
Thanks: 49 given, 136 received

@Dudetooth,

Do you know if there is volume data in the CME output used in XLS-SPAN? I'm looking at higher DTE strategies (140+) but I'm not sure how viable these strategies are if there is very little activity in longer term options.

Reply With Quote
 
(login for full post details)
  #457 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received

Negative, pa2 files have no volume info ... Sorry. I'm not sure where you'd find that historically. You may just have to look at current volume for high DTE options to see if it is viable.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #458 (permalink)
ItalianBmT
Java/IN
 
 
Posts: 13 since Nov 2013
Thanks: 2 given, 0 received


Dudetooth View Post
You mat need to change:
Private Function SaveWebFile to Private PtrSafe Function SaveWebFile

or perhaps:
Dim lngRetVal As Long to Dim lngRetVal As LongPtr

I'm afraid I won't be of much help with actually troubleshooting the code, but this may help:
https://msdn.microsoft.com/library/gg264421.aspx

It looks like there are a limited number of areas where you may encounter errors running VBA in Office 64-bit according to the article above. It would seem as though if you change the declarations that you should be OK.

Hope this helps.

Thanks Dudetooth. But unfortunately this doesn't solve the issue even when adding PtrSafe. Thanks anyway and I shan't disturb you further. Hopefully others who are using 64 bit version could chime in if they experience similar instance on their machine?

Thanks.

Reply With Quote
 
(login for full post details)
  #459 (permalink)
TFOpts
Los Angeles, CA
 
 
Posts: 64 since May 2017
Thanks: 49 given, 136 received

Additional data from CME was provided for the first trading day of each month from 5/1/08 to 4/1/09. The hope was to understand how different strategies would have behaved during a recession.

The files provided by CME are attached. A review of the compatibilty with XLS-SPAN is still ongoing. One thing Ron found was that by changing the name from ccl to cme he was able to scan in the data into XLS-SPAN but was not able to use the tracking functionality of the tool.

There's an active project to add SPAN historical data to Datamine (CME's database service) with an estimated completion date of late 2017.

Note: this is a cross-post with this: . It is more appropriate in this thread.

Attached Files
Register to download File Type: zip ccl.20090202.s.zip (20.67 MB, 4 views)
Reply With Quote
The following user says Thank You to TFOpts for this post:
 
(login for full post details)
  #460 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received


TFOpts View Post
Additional data from CME was provided for the first trading day of each month from 5/1/08 to 4/1/09. The hope was to understand how different strategies would have behaved during a recession.

The files provided by CME are attached. A review of the compatibilty with XLS-SPAN is still ongoing. One thing Ron found was that by changing the name from ccl to cme he was able to scan in the data into XLS-SPAN but was not able to use the tracking functionality of the tool.

There's an active project to add SPAN historical data to Datamine (CME's database service) with an estimated completion date of late 2017.

Note: this is a cross-post with this: . It is more appropriate in this thread.

I tested these files with CME's PC-SPAN program. They work with that program.

These files are for CME grains, equity, meats, dairy, & currencies. They do not contain NYMEX (energies) or COMEX (metals) products.

Started this thread Reply With Quote
 
(login for full post details)
  #461 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


TFOpts View Post
A review of the compatibilty with XLS-SPAN is still ongoing. One thing Ron found was that by changing the name from ccl to cme he was able to scan in the data into XLS-SPAN but was not able to use the tracking functionality of the tool.

Small code fix from what I can tell in order to get Track to work with the older PA2 files.

1) Go to Class Modules COption

2) Find:

Public Property Let OptContract(Value As String)
pOptContract = Value
If pCSVPA2 = "CSV" Then
pType8Opt = pOptContract & pOptType & pStrike '"50,201" & Right(pOptContract, 1) & conv2num(Mid(pOptContract, Len(pOptContract) - 1, 1)) & "00," & pOptType & "," & pStrike
Else
pType8Opt = pType8O & pOptType & "201" & Right(pFutContract, 1) & conv2num(Mid(pFutContract, Len(pFutContract) - 1, 1)) _
& " 201" & Right(pOptContract, 1) & conv2num(Mid(pOptContract, Len(pOptContract) - 1, 1)) & " " & pStrike
End If
End Property


3) And replace with:

Public Property Let OptContract(Value As String)
pOptContract = Value
Dim tempdate As String
If pCSVPA2 = "CSV" Then
pType8Opt = pOptContract & pOptType & pStrike '"50,201" & Right(pOptContract, 1) & conv2num(Mid(pOptContract, Len(pOptContract) - 1, 1)) & "00," & pOptType & "," & pStrike
Else
If Left(pDatePA2, 3) & Right(pFutContract, 1) >= Left(pDatePA2, 4) Then
tempdate = Left(pDatePA2, 3) & Right(pFutContract, 1)
Else
tempdate = Left(pDatePA2, 2) & Mid(pDatePA2, 3, 1) + 1 & Right(pFutContract, 1)
End If
pType8Opt = pType8O & pOptType & tempdate & conv2num(Mid(pFutContract, Len(pFutContract) - 1, 1)) _
& " " & tempdate & conv2num(Mid(pOptContract, Len(pOptContract) - 1, 1)) & " " & pStrike
End If
End Property


I'll continue to troubleshoot any other issues that come from these older PA2 files.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #462 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received

How will XLS-SPAN know the difference between ESH8 for 2008 and 2018?

Started this thread Reply With Quote
 
(login for full post details)
  #463 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ron99 View Post
How will XLS-SPAN know the difference between ESH8 for 2008 and 2018?

I looks at the year of the PA2 file. With the 20080501.s.pa2 it takes the digit for the decade (0) and combines it with the single digit for the year of the contract (8). That year (08) should be >= the year of the PA2 file or it adds to the decade. That should work when we get 2009 PA2 files with 2010 contract data.

Reply With Quote
 
(login for full post details)
  #464 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received

@Dudetooth when I make the change above, Track quits working for me even for 2017 contracts. When I change it back to the way it was before the change, it works again for 2017 contracts but not 2008 contracts.

I copied from the post above and pasted it on top of old part. Do I need to compile VBS project? Or just save changes? Are the indents necessary?

Started this thread Reply With Quote
 
(login for full post details)
  #465 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received

@ron99,

You should just have to hit save, in either the VBA editor or in Excel itself.

I did a quick check with 20170524's PA2 and it was working for me.

I'm not sure if something gets messed up when copying and pasting the code to the forum, but I uploaded it just to be safe and so you could compare.

Attached Files
Register to download File Type: zip XLS-SPAN (05b).zip (290.0 KB, 34 views)
Reply With Quote
The following 2 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #466 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received


Dudetooth View Post
@ron99,

You should just have to hit save, in either the VBA editor or in Excel itself.

I did a quick check with 20170524's PA2 and it was working for me.

I'm not sure if something gets messed up when copying and pasting the code to the forum, but I uploaded it just to be safe and so you could compare.

I downloaded this version and then copied and pasted the macro and everything works OK.

I found the problem. When copy and pasting to forum it lost some spaces between quotes.


Your post with macro changes was missing the spaces.

Started this thread Reply With Quote
The following 2 users say Thank You to ron99 for this post:
 
(login for full post details)
  #467 (permalink)
TFOpts
Los Angeles, CA
 
 
Posts: 64 since May 2017
Thanks: 49 given, 136 received

@Dudetooth,

Do you know if the SPAN files include margin requirements on the underlying futures (instead of just on options)? If yes, would it required extensive modifications to XLS-SPAN to extract this information?

Reply With Quote
 
(login for full post details)
  #468 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received


TFOpts View Post
@Dudetooth,

Do you know if the SPAN files include margin requirements on the underlying futures (instead of just on options)? If yes, would it required extensive modifications to XLS-SPAN to extract this information?

XLS-SPAN does give margin for futures. Just put a "F" in the C/P column and nothing in strike column.

Started this thread Reply With Quote
The following 2 users say Thank You to ron99 for this post:
 
(login for full post details)
  #469 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


TFOpts View Post
@Dudetooth,

Do you know if the SPAN files include margin requirements on the underlying futures (instead of just on options)? If yes, would it required extensive modifications to XLS-SPAN to extract this information?

Just to add to ron99's comment, the futures contact have the same 16 risk scenarios that the options do, so futures can be added to a spread with options to calculate margin.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #470 (permalink)
uuu1965
Riga Latvia
 
 
Posts: 107 since Jan 2013
Thanks: 441 given, 72 received

@Dudetooth
Sorry, I forgot how to get simple futures price, f.e. CLQ7

Reply With Quote
 
(login for full post details)
  #471 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received


uuu1965 View Post
@Dudetooth
Sorry, I forgot how to get simple futures price, f.e. CLQ7

I tried to get this and other NYMEX and COMEX contracts and got this



but when I tried LH or ZC or CC or ES I got the futures data for 20170609???

Started this thread Reply With Quote
 
(login for full post details)
  #472 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ron99 View Post
I tried to get this and other NYMEX and COMEX contracts and got this



but when I tried LH or ZC or CC or ES I got the futures data for 20170609???

I had just developed the code for tracking futures back when I first put this out and didn't get a lot of troubleshooting in on it. NYMEX and COMEX contracts had issues because the future contracts were being skipped in the code.

It's not too difficult of a fix, though there may be other issues I haven't seen yet. I've uploaded the spreadsheet because posting the code in the forum may not show the proper spacing and you can copy the code over to your existing spreadsheet. There are three areas to address ...

1) In MainCalculation module - add the bit about clsOpt.TypeB1:

'*********** TypeB ... option month details: dte, base volatility, delta scaling factor
Case 3
If InStr(rline, clsOpt.TypeB) = 1 Or InStr(rline, clsOpt.TypeB1) = 1 Then '170611 TypeB1 needed for loading COMEX/NYMEX Fut contracts



2) In COption class module - At the top add pTypeB1:

Private pTypeB As String, pTypeB1 As String, pType8F As String, pType8O As String, pType30


3) Down further in the COption class module - add this new set of code:

Public Property Get TypeB1() As String '170611 needed for loading COMEX/NYMEX Fut contracts
Select Case pCommodity
Case "CL": TypeB1 = "B NYMCL "
Case "GC": TypeB1 = "B CMXGC "
Case "HG": TypeB1 = "B CMXHG "
Case "HO": TypeB1 = "B NYMHO "
Case "NG": TypeB1 = "B NYMNG "
Case "RB": TypeB1 = "B NYMRB "
Case "SI": TypeB1 = "B CMXSI "
Case Else: TypeB1 = "XXXX"
End Select
End Property


I tucked it in between Public Property Get TypeB1() and Public Property Get Type8F(), but anywhere should do. There should be 8 spaces between the last letter of the B code and the last quotation mark.

Let me know if there are any other issues.

Attached Files
Register to download File Type: zip XLS-SPAN (05c).zip (309.3 KB, 11 views)
Reply With Quote
The following 3 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #473 (permalink)
Pawan Mishra
New Delhi India
 
 
Posts: 1 since Jun 2017
Thanks: 0 given, 0 received

Hi

This is Pawan Mishra. I'm new to PC-SPAN, but I know basics of batch scripting. I'm looking for how to process .spn/.pa2 file using scripting. I tried below suggested way, it is working for .spn / .pa2 span files. But, it is failing while loading .pos file.

1- Getting "Error processing script" while loading .pos file. What could be the reason?

2- How can I create .pos file from my existing .csv or .txt files?

Thanks in advance for your kind help

Thanks and Regards





Dudetooth View Post
Just reposting this from the Selling Options on Futures thread:

ron99 asked about using a batch file to run SPAN and generate a report.

I've been able to get this to work to create a performance bond risk summary of the portfolio ... not sure yet how to get anything like the Scan Risk Contributions in the Reports tab ... looks like that will need a different kind of script to run the spanReport utility.

Here's what I did:

- In the C:\Span4 folder (default location for installation of program) I placed a copy of the following:
-- Unzipped risk array files (cme.20131018.s.pa2 & nyb.20131018.s.pa2)
-- My portfolio file that I generated in PC-SPAN by right-clicking on my portfolio and selecting Copy Positions To File to generate a .pos file (RJO 131018.pos)

- Then I opened Notepad to create my script and typed in the following lines:
Load C:\Span4\cme.20131018.s.pa2
Load C:\Span4\nyb.20131018.s.pa2
Load C:\Span4\RJO 131018.pos
Calc
SaveCalcSummary C:\Span4\Summary.csv
-- I saved it as a text file named SpanScript.txt in the C:\Span4 folder

- Then in Notepad, on a clean slate I typed in:
spanit C:\Span4\SpanScript.txt
-- Saved this as a batch file (File->Save As changing the Save as type to All Files) named SPAN-Batch.bat in the same folder where the spanit.exe file is (should be C:\Span4\Bin)

Just double-click my batch file (SPAN-Batch.bat), and it will load the risk arrays and portfolio, calculate and create a csv file named Summary in the C:\Span4 folder with my performance bond summary.

Hopes this helps.


****UPDATE****
Make the following changes to the instructions above to see the Position and PB Requirement Reports:

- In the SpanScript.txt type the following lines:
Load C:\Span4\cme.20131018.s.pa2
Load C:\Span4\nyb.20131018.s.pa2
Load C:\Span4\RJO 131018.pos
Calc
Save C:\Span4\Risk.xml

- In the SPAN-Batch.bat file type the following lines:
C:\Span4\Bin\spanit.exe C:\Span4\SpanScript.txt
C:\Span4\RptModule\spanReport.hta C:\Span4\Risk.xml

This will create the reports in the C:\Span4\Reports folder.


Reply With Quote
 
(login for full post details)
  #474 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


Pawan Mishra View Post
Hi

This is Pawan Mishra. I'm new to PC-SPAN, but I know basics of batch scripting. I'm looking for how to process .spn/.pa2 file using scripting. I tried below suggested way, it is working for .spn / .pa2 span files. But, it is failing while loading .pos file.

1- Getting "Error processing script" while loading .pos file. What could be the reason?

2- How can I create .pos file from my existing .csv or .txt files?

Thanks in advance for your kind help

Thanks and Regards

Have you tried the SpanPosConv utility?



It may be something that can help.

Reply With Quote
 
(login for full post details)
  #475 (permalink)
illinoisrei
Seattle, WA
 
 
Posts: 4 since Dec 2016
Thanks: 2 given, 0 received


Pawan Mishra View Post
Hi

This is Pawan Mishra. I'm new to PC-SPAN, but I know basics of batch scripting. I'm looking for how to process .spn/.pa2 file using scripting. I tried below suggested way, it is working for .spn / .pa2 span files. But, it is failing while loading .pos file.

1- Getting "Error processing script" while loading .pos file. What could be the reason?

2- How can I create .pos file from my existing .csv or .txt files?

Thanks in advance for your kind help

Thanks and Regards

The SpanPosConv from CME works great for that purpose. I use it every day to convert csv's to .pos (xml files).

However, it does not work consistently when going from .pos to csv. I recommend using the functionality in Excel with the proper xml map (xsd) for that.

Reply With Quote
 
(login for full post details)
  #476 (permalink)
illinoisrei
Seattle, WA
 
 
Posts: 4 since Dec 2016
Thanks: 2 given, 0 received


ItalianBmT View Post
Thanks Dudetooth. But unfortunately this doesn't solve the issue even when adding PtrSafe. Thanks anyway and I shan't disturb you further. Hopefully others who are using 64 bit version could chime in if they experience similar instance on their machine?

Thanks.

I had the same problem.

You should only change "Dim lngRetVal As Long" to "Dim lngRetVal As LongPtr"

That should do it.

Reply With Quote
 
(login for full post details)
  #477 (permalink)
Hills
Barcelona
 
 
Posts: 14 since Mar 2015
Thanks: 7 given, 7 received

I trade options on currencies futures. Some month ago 6A was the pair with less margin required so the ROI was the best one. Nowadays when I use the SPAN spreadsheet I can't find any pair. Does anyone know if margin in currencies futures has changed?

Reply With Quote
 
(login for full post details)
  #478 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


Hills View Post
I trade options on currencies futures. Some month ago 6A was the pair with less margin required so the ROI was the best one. Nowadays when I use the SPAN spreadsheet I can't find any pair. Does anyone know if margin in currencies futures has changed?

Actually, I was just noticing that issue this morning. It seems like the options are now structured like ES with one code for Mar, Jun, Sep & Dec options, another code for the off months, and another code for weekly options.

It'll take me a little bit to change the code to assimilate the new products.

Reply With Quote
The following 2 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #479 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received

There is some sight modifications to the COption Class Module and MainCalculation Module to add the new currency options contracts for months with no underlying futures contract (Jan, Feb, Apr, May, Jul, Aug, Oct, Nov). Option contracts with underlying futures (Mar, Jun, Sep, Dec) still use the older code.

The new codes are located on the Parameters tab (ADU, GBU, CAU, EUU, JPU).

So, if you were scanning for 6A options with 30-90 DTE on the Scan tab you would have one line with AD and one line with ADU to see if either contract meets your time-frame.



It seems to be working correctly, but there may be some bugs ... just let me know if there are any and I'll try to sort them out.

Attached Files
Register to download File Type: zip XLS-SPAN (05c).zip (292.6 KB, 41 views)
Reply With Quote
The following 5 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #480 (permalink)
uuu1965
Riga Latvia
 
 
Posts: 107 since Jan 2013
Thanks: 441 given, 72 received

to @Dudetooth
HI,
some ES contract are displayed on Scan sheet but cann`t work on Track sheet, f.e. EWZ7 P 2565 (I use 05c version of XLS-SPAN). Please see attachment

Attached Thumbnails
Click image for larger version

Name:	Capture.PNG
Views:	90
Size:	69.1 KB
ID:	242963  
Reply With Quote
 
(login for full post details)
  #481 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


uuu1965 View Post
to @Dudetooth
HI,
some ES contract are displayed on Scan sheet but cann`t work on Track sheet, f.e. EWZ7 P 2565 (I use 05c version of XLS-SPAN). Please see attachment

@uuu1965, I think I tracked down the issue ... in the Class Module COption find the section that begins with "Public Property Let OptContract(Value As String)" ... replace that section of code with the code on the attached txt and you should be ok. Let me know if there are any other issues.

Attached Files
Register to download File Type: txt XLS-SPAN (05c) fix.txt (954 Bytes, 6 views)
Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #482 (permalink)
uuu1965
Riga Latvia
 
 
Posts: 107 since Jan 2013
Thanks: 441 given, 72 received


Dudetooth View Post
@uuu1965, I think I tracked down the issue ... in the Class Module COption find the section that begins with "Public Property Let OptContract(Value As String)" ... replace that section of code with the code on the attached txt and you should be ok. Let me know if there are any other issues.

Thanks @Dudetooth! All works fine!

Reply With Quote
 
(login for full post details)
  #483 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received

@Dudetooth I am having a strange problem. When I run H-Track on the first spread



I get this.



The spread IM is 396 from 20171011 to 20171031 even though all other data changes. When I run the 2nd spread I get 396 for every day for spread IM.

I used PC-SPAN and I get different Spread IM than 396. For 20171031 PC-SPAN gives me 277 for 1st spread.

I ran it in both XLS-SPAN (05a) and (05c). Same result. Does the same thing if I do Track Spread for one day. I redownloaded the 20171012 & 20171013 arrays from CME and that made no difference.

On 20171012 I copied the spreadsheet to a laptop to use while on vacation. When I got back I copied the spreadsheet back to my desktop. But I didn't do that to the (05c) version.

Does this problem happen on your spreadsheet?

Started this thread Reply With Quote
 
(login for full post details)
  #484 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ron99 View Post
Does this problem happen on your spreadsheet?

@ron99,

So I think I tracked it down. Short story - seems like sometime in June the SOM for SP increased drastically and that was impacting the spread IM. I believe that they use the delta scaling factor to modify the SOM, which was missing from my code. I modified only two procedures in the COptions Class Module: Public Property Let TypeBstr(Value As String) and Public Property Let Type82str(Value As String) in order to remedy this ... at least it seems like it is working correct. You can copy it from the attached update.

Attached Files
Register to download File Type: zip XLS-SPAN (05d).zip (315.0 KB, 51 views)
Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #485 (permalink)
uuu1965
Riga Latvia
 
 
Posts: 107 since Jan 2013
Thanks: 441 given, 72 received

@Dudetooth
When I open a synthetic position that uses the various months of future and option, XLS-SPAN (05d) does not calculate the total margin.
F.e., Short ESH8 and long EWF8 C 2665 (Zaner platform shows margin of $2570)

Reply With Quote
 
(login for full post details)
  #486 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


uuu1965 View Post
@Dudetooth
When I open a synthetic position that uses the various months of future and option, XLS-SPAN (05d) does not calculate the total margin.
F.e., Short ESH8 and long EWF8 C 2665 (Zaner platform shows margin of $2570)
Attachment 244269

@uuu1965

Sorry, I haven't been able to work out how to do that in XLS-SPAN ... it sees those as two separate commodities.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #487 (permalink)
ItalianBmT
Java/IN
 
 
Posts: 13 since Nov 2013
Thanks: 2 given, 0 received

Hi Dudetooth, downloaded the latest and there is still an issue for 64bit users.. In this section to download the risk arrays file, it output a file mismatch error. On rectifying by entering a Private PtrSafe Function, the before mentioned SaveWebFile gave an error that it was not defined. Which was puzzling because it was defined as per the code below.

Private Function SaveWebFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then SaveWebFile = True
End Function

Reply With Quote
 
(login for full post details)
  #488 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ItalianBmT View Post
Hi Dudetooth, downloaded the latest and there is still an issue for 64bit users.. In this section to download the risk arrays file, it output a file mismatch error. On rectifying by entering a Private PtrSafe Function, the before mentioned SaveWebFile gave an error that it was not defined. Which was puzzling because it was defined as per the code below.

Private Function SaveWebFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then SaveWebFile = True
End Function

Sorry I can't be of much help troubleshooting this. If a solution is ever found I'll be sure to get the fix into the latest version.

Reply With Quote
 
(login for full post details)
  #489 (permalink)
 jokertrader 
NYC, NY
 
Experience: Intermediate
Platform: Sierra Qtrader TT
Broker: Amp/CQG/TT, Optimus, ADM
Trading: Mainly CL. Spread researcher currently
 
Posts: 645 since May 2013
Thanks: 541 given, 354 received

Sorry for a slight off topic question. Trying to get historic settlement or end of day data for exchange traded crude calendar spreads say for at least 4 years. can I do that somewhere somehow? I have sierra and cqg I can load individual individual months for past data one by one, use the spreadsheet study then export and then calculate the spread but is there a better way? So as example I can load June 2015 symbol ad then Dec 2015 then extract and calculate the difference but any ready way to get data readily for the 6 month exchange spread from CME?


Sent using the futures.io mobile app

Visit my futures io Trade Journal Reply With Quote
 
(login for full post details)
  #490 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received


jokertrader View Post
Sorry for a slight off topic question. Trying to get historic settlement or end of day data for exchange traded crude calendar spreads say for at least 4 years. can I do that somewhere somehow? I have sierra and cqg I can load individual individual months for past data one by one, use the spreadsheet study then export and then calculate the spread but is there a better way? So as example I can load June 2015 symbol ad then Dec 2015 then extract and calculate the difference but any ready way to get data readily for the 6 month exchange spread from CME?


Sent using the futures.io mobile app

I have a spreadsheet that has all CL settlements for the last 9 months of a contract for all contracts since 2006. You'll have to calculate the spread prices yourself. Do you want it?

Started this thread Reply With Quote
 
(login for full post details)
  #491 (permalink)
 jokertrader 
NYC, NY
 
Experience: Intermediate
Platform: Sierra Qtrader TT
Broker: Amp/CQG/TT, Optimus, ADM
Trading: Mainly CL. Spread researcher currently
 
Posts: 645 since May 2013
Thanks: 541 given, 354 received

That would be awesome will pm u u have been an inspiration to so many here and thank you for that


Sent from my iPhone using futures.io mobile app

Visit my futures io Trade Journal Reply With Quote
 
(login for full post details)
  #492 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


jokertrader View Post
Sorry for a slight off topic question. Trying to get historic settlement or end of day data for exchange traded crude calendar spreads say for at least 4 years. can I do that somewhere somehow? I have sierra and cqg I can load individual individual months for past data one by one, use the spreadsheet study then export and then calculate the spread but is there a better way? So as example I can load June 2015 symbol ad then Dec 2015 then extract and calculate the difference but any ready way to get data readily for the 6 month exchange spread from CME?

In the sub GetIM_ROI change the portion of your code as such (hides If clsOpt.OptType and End If lines):

'*********** Spread Calculations
If calcType = 3 Then
' If clsOpt.OptType <> " " Then '##### mod 2.3
dicFut(i)("SprdValue") = dicFut(i)("SprdValue") + clsOpt.PosValue
' End If
dicFut(i)("SprdPrice") = dicFut(i)("SprdPrice") + clsOpt.PosPrice
dicFut(i)("SprdNumber") = dicFut(i)("SprdNumber") + Abs(iPos)
dicFut(i)("SprdDelta") = dicFut(i)("SprdDelta") + clsOpt.PosDelta
If iArrPos = totOpt Then
dicFut(i)("SprdNet") = dicFut(i)("SprdValue") - (dicFut(i)("SprdNumber") * dFees)
End If
End If


It looks to calculate the values and margin, but I haven't done thorough tests. Then you could do a Historic Track.

Update: margin seems to be off because of the way margin is calculated for calendar spreads.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #493 (permalink)
peternguyen
Ho Chi Minh City
 
 
Posts: 11 since Dec 2017
Thanks: 5 given, 0 received

Hi everyone,
I'm new to Future Option selling. I have been reading the book by James Cordier The Complete Guide to Option Selling.
I have tried 4 trades and sadly 3 go in the money and I sustain heavy losses.
The problem is I'm using Saxo Capital and I don't know if they charge SPAN margin or not. But if I want to sell option Too far out of the money, my premium is very little, below $50 for a 3 month trade with margin requirement of $1350 in the case of Brent Crude for example. I have to go close to the current price in order to earn enough margin (my aim is 10% Premium on Margin for a 3-4 month contract and usually the market can easily move against me.

Their margin requirement for Option on Future is usually 50% of the margin on Future Contract. For example Brent Crude Future Option margin is $1350 overnight, E-mini S&P is $2250, Wheat is $475.

Can you tell me if they are chargin SPAN margin? Which brokers can you recommend that charge SPAN margin? How much should SPAN Margin be for a Brent Crude short put?

In doubt and seriously need help.
Thanks

Reply With Quote
 
(login for full post details)
  #494 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


peternguyen View Post
Can you tell me if they are chargin SPAN margin? Which brokers can you recommend that charge SPAN margin? How much should SPAN Margin be for a Brent Crude short put?

If you give us a contract and strike you are talking about we should be able to give you what the SPAN margin should be.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #495 (permalink)
peternguyen
Ho Chi Minh City
 
 
Posts: 11 since Dec 2017
Thanks: 5 given, 0 received


Dudetooth View Post
If you give us a contract and strike you are talking about we should be able to give you what the SPAN margin should be.

For example this is from SaxoCapital:
E-mini S&P 500 Put March 2018 (91 days) Strike 2000: the margin is $2250, Premium is only $87.50
Henry Hub Natural Gas Call March 2018 (70 days) Strike 4000: the margin is $1100, Premium is $140

They charge a fixed margin, usually 50% of Future margin, which is a preset amount. The margin doesn't depend on the Strike or how far the contract is.

Thanks

Reply With Quote
 
(login for full post details)
  #496 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 958 given, 5,708 received


peternguyen View Post
For example this is from SaxoCapital:
E-mini S&P 500 Put March 2018 (91 days) Strike 2000: the margin is $2250, Premium is only $87.50
Henry Hub Natural Gas Call March 2018 (70 days) Strike 4000: the margin is $1100, Premium is $140

They charge a fixed margin, usually 50% of Future margin, which is a preset amount. The margin doesn't depend on the Strike or how far the contract is.

Thanks

This is first time I have ever heard of margin for options being fixed amount from futures margin and strike and month don't matter. Are you sure? If so then find another place to trade.

I also suggest your read these threads to learn more about selling options



Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #497 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


peternguyen View Post
For example this is from SaxoCapital:
E-mini S&P 500 Put March 2018 (91 days) Strike 2000: the margin is $2250, Premium is only $87.50
Henry Hub Natural Gas Call March 2018 (70 days) Strike 4000: the margin is $1100, Premium is $140

They charge a fixed margin, usually 50% of Future margin, which is a preset amount. The margin doesn't depend on the Strike or how far the contract is.

Thanks

Calculated SPAN margin as of the 14th of Dec risk files
ESH8 P2000 = $282
NGH8 C4 = $308

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #498 (permalink)
peternguyen
Ho Chi Minh City
 
 
Posts: 11 since Dec 2017
Thanks: 5 given, 0 received


Dudetooth View Post
As of the 14th of Dec
ESH8 P2000 = $282
NGH8 C4 = $308

Superb. Can you please tell me which broker you are using? Are they based in US?

Reply With Quote
 
(login for full post details)
  #499 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


peternguyen View Post
Superb. Can you please tell me which broker you are using? Are they based in US?

RJO Futures, based in US. They apparently accept non-US customers, but I'm not sure what countries they will work with.
https://rjofutures.rjobrien.com/open-futures-account/

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #500 (permalink)
peternguyen
Ho Chi Minh City
 
 
Posts: 11 since Dec 2017
Thanks: 5 given, 0 received

Thank you. I'm Australian citizen so probably can.
Any other broker do you recommend? I will try the other if RJObrien doesn't accept me.
What trading platform do you use? is it user friendly?

Reply With Quote


futures io Trading Community Traders Hideout Options > PC-SPAN


Last Updated on March 6, 2021


Upcoming Webinars and Events
 

NinjaTrader Indicator Challenge!

Ongoing
 

Our 12-year anniversary w/ $$,$$$ prizes (check soon)

June
     



Copyright © 2021 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, Ph: +507 833-9432 (Panama and Intl), +1 888-312-3001 (USA and Canada), info@futures.io
All information is for educational use only and is not investment advice.
There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts