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 119,818 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)
  #101 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


enderqa View Post
@Dudetooth, Maybe we can start small.

enderqa,

Here's my attempt to break down the SPAN process. Hope it helps.

***Edit: A few minor edits ... I guess I should have read through the CME material a little more closely before I posted this. I think I have everything correct now.

Attached Files
Register to download File Type: docx SPAN Process Edit.docx (20.2 KB, 80 views)
Reply With Quote
The following 4 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #102 (permalink)
 mu2pilot 
Dallas, TX
 
Experience: Advanced
Platform: T4, Zaner360, TOS
Broker: DeCarley Trading
Trading: Options
 
Posts: 104 since Sep 2013
Thanks: 134 given, 52 received


Dudetooth View Post
I read through some of their material when I was researching the script language, but I somehow missed this part. With everything spelled out like this, I think you could eventually get Excel to do everything that PC-SPAN does.

You mention the standard record layout, but as I pointed out to Ron, this is not the correct file structure. The correct structure is the "expanded" record layout. Note the length of the Commodity (Product) Code. In the standard format, the Commodity Code has a length of 2, but the expanded format shows the Commodity Code having a length of 10.

The expanded "layout guide" matches what we see in the .pa2 files.

Here is a link to that page: https://www.cmegroup.com/confluence/display/pubspan/Type+8+-+Expanded

Reply With Quote
 
(login for full post details)
  #103 (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: 957 given, 5,708 received

Wow. I inadvertently had a wrong thing in columns A-D. Program locked up. Got it out of that and it still wouldn't work.

Found out that the Temp sheet had over 5,000 lines from the data array in it. Copied a correct Temp sheet over the incorrect one and that fixed it.

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


mu2pilot View Post
You mention the standard record layout, but as I pointed out to Ron, this is not the correct file structure. The correct structure is the "expanded" record layout. Note the length of the Commodity (Product) Code. In the standard format, the Commodity Code has a length of 2, but the expanded format shows the Commodity Code having a length of 10.

The expanded "layout guide" matches what we see in the .pa2 files.

Here is a link to that page: https://www.cmegroup.com/confluence/display/pubspan/Type+8+-+Expanded

Sorry, you are correct. The general point was just that I was unaware that CME had such a detailed description of the risk arrays and armed with that info it could be possible to have Excel do everything PC-SPAN does (though it would take a lot of time and effort to do it all).

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #105 (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
Wow. I inadvertently had a wrong thing in columns A-D. Program locked up. Got it out of that and it still wouldn't work.

Found out that the Temp sheet had over 5,000 lines from the data array in it. Copied a correct Temp sheet over the incorrect one and that fixed it.

I had that happen when I was testing (multiple lines in the risk array that match the search criteria). I tried to put some code in to prevent it from writing more than one line for each chunk of data that it was searching for, but I guess there are still holes. Do you remember what the values in columns A-E were?

Reply With Quote
 
(login for full post details)
  #106 (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: 957 given, 5,708 received


Dudetooth View Post
I had that happen when I was testing (multiple lines in the risk array that match the search criteria). I tried to put some code in to prevent it from writing more than one line for each chunk of data that it was searching for, but I guess there are still holes. Do you remember what the values in columns A-E were?

I had inadvertently copied the header (rows 1-3) down into cells below row 3.

Rows 1 & 2 of the header were blank. Row 3 had the headers (text) plus the popup for column E (Net).

Started this thread Reply With Quote
 
(login for full post details)
  #107 (permalink)
 SMCJB 
Legendary Market Wizard
Houston, TX
 
Experience: Advanced
Platform: Trading Technologies
Broker: Primary Advantage Futures. Also ED&F and Tradestation
Trading: Primarily Energy but also a little GE, GC, SI & Bitcoin
 
Posts: 4,089 since Dec 2013
Thanks: 3,401 given, 8,095 received


datahogg View Post
I noticed there is a $10.00 charge to download the PC Span. Are there any other charges for the data used
for calculating span??

WOW. When I bought mine (2009) it cost $500 and if you wanted a hard copy it cost an extra $35!

Reply With Quote
 
(login for full post details)
  #108 (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: 957 given, 5,708 received


SMCJB View Post
WOW. When I bought mine (2009) it cost $500 and if you wanted a hard copy it cost an extra $35!

Same here. And you would think that a multi-billion dollar company would rewrite 25 year old code when charging $500!!!

Started this thread Reply With Quote
 
(login for full post details)
  #109 (permalink)
vp62
Moscow Russia
 
 
Posts: 7 since Oct 2012
Thanks: 113 given, 0 received

Dudetooth,

I tried both versions of your software. When I enter a date and click the 'Download Risk Arrays' button it downloads, unzips and saves the risk arrays. But when I enter position data and click the Portfolio or Single Position buttons, nothing happens, just blank gray field in the Main Tab and almost all Zeroes in the Temp Tab. Am I doing something wrong?

Thank you in advance.

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


vp62 View Post
But when I enter position data and click the Portfolio or Single Position buttons, nothing happens, just blank gray field in the Main Tab and almost all Zeroes in the Temp Tab.

Can you show me what are you entering in columns A:E? It may be an issue with the contract or strike (the spreadsheet is a little finicky).

Reply With Quote
 
(login for full post details)
  #111 (permalink)
vp62
Moscow Russia
 
 
Posts: 7 since Oct 2012
Thanks: 113 given, 0 received

I used the spreadsheet as it was: CCH4 CCH4 C 3500 -1 etc., just changed the dates in A1.

Reply With Quote
 
(login for full post details)
  #112 (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: 957 given, 5,708 received


vp62 View Post
Dudetooth,

I tried both versions of your software. When I enter a date and click the 'Download Risk Arrays' button it downloads, unzips and saves the risk arrays. But when I enter position data and click the Portfolio or Single Position buttons, nothing happens, just blank gray field in the Main Tab and almost all Zeroes in the Temp Tab. Am I doing something wrong?

Thank you in advance.

Is your spreadsheet in the same Windows folder as the risk arrays?

Started this thread Reply With Quote
 
(login for full post details)
  #113 (permalink)
vp62
Moscow Russia
 
 
Posts: 7 since Oct 2012
Thanks: 113 given, 0 received

The spreadsheet was in another folder. I moved it to the same folder as the risk arrays but nothing changed.

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

In the Setup tab, did you set the default path?

By default the spreadsheet is assuming that you have PC-SPAN installed. If you don't, you need to change your default path so it knows where to download and where to find the files.

If that is not the issue, try typing in CCH4 CCH4 C 3500 -1 in row 4 again, click Single position, select any cell in row 4 and click ok. If there is nothing returned in the gray area on the Main tab, let me know if you see any data at all in the first 5 rows of the Temp tab.

Reply With Quote
 
(login for full post details)
  #115 (permalink)
vp62
Moscow Russia
 
 
Posts: 7 since Oct 2012
Thanks: 113 given, 0 received

I have PC-SPAN installed and I see downloaded and uzipped Risk Array files in C:\Span4\Data Folder.
I typed in CCH4 CCH4 C 3500 -1 in row 4 again. There were nothing returned in the gray area and the first 5 rows in the Temp tab are blank.

Reply With Quote
 
(login for full post details)
  #116 (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's nothing wrong with macros running if you can confirm that the spreadsheet is downloading the risk arrays properly. The fact that rows 1-5 are blank on the Temp tab makes me think that it isn't finding the correct lines because of an error.

I'll private message you a copy of the spreadsheet I use for troubleshooting ... maybe we can figure out why it is giving you grief.

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

vp62,

It just dawned on me that I don't think you won't be able to get the attachment if I do it through a private message, so here it is.

Please bear with me on this one and don't take it personally if the instructions seem too basic ... I'm just trying to locate the error.

I attached a 97/2003 version so it didn't have to be zipped and I hid the code that forces and exit when there is an error.

Please go through these steps:
-Open the spreadsheet and click Enable Macros if a security warning appears.
-On the Setup tab, click Set Default Path and navigate to the folder where you want the data and then click OK.
-On the Main tab click Download Risk Arrays to download the files for Dec10.
-When that is done, click Portfolio.

Please let me know the results. If an error is generated please let me know what is highlighted in the VBA. If any of this doesn't make sense just let me know.

Thanks.

Attached Files
Register to download File Type: xls Margin_Without_PC-SPANv2.xls (100.0 KB, 39 views)
Reply With Quote
 
(login for full post details)
  #118 (permalink)
vp62
Moscow Russia
 
 
Posts: 7 since Oct 2012
Thanks: 113 given, 0 received

I went through all the steps but unfortunately everything remained as it was. Nothing is highlighted in the VBA. There were the following files downloaded to my C:\Span4\Data folder - cme.20131210.s.pa2 and nyb.20131210.s.pa2.

Reply With Quote
 
(login for full post details)
  #119 (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: 957 given, 5,708 received

You should also have cme.s.pa2 & nyb.s.pa2 in the C:\Span4\Data Folder too.

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


vp62 View Post
There were the following files downloaded to my C:\Span4\Data folder - cme.20131210.s.pa2 and nyb.20131210.s.pa2.

It seems like something is interrupting the code execution on your machine. The spreadsheet I sent you had code in it that should have renamed the cme.20131210.s.pa2 to cme.s.pa2, rename the nyb.20131210.s.pa2 to nyb.s.pa2, and then delete the zip files.

I'm not sure I know where to go from here. How familiar are you with editing VBA?

Reply With Quote
 
(login for full post details)
  #121 (permalink)
vp62
Moscow Russia
 
 
Posts: 7 since Oct 2012
Thanks: 113 given, 0 received

Thank you, Dudetooth and Ron!
I manually downloaded cme.s.pa2 from CME site and renamed the nyb.20131210.s.pa2 to nyb.s.pa2. Now it seems to work OK!
I can edit VBA only following step by step instructions.

Reply With Quote
 
(login for full post details)
  #122 (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: 957 given, 5,708 received

After reading what vp62 did, I checked the CME website and realized that there is a file named cme.s.pa2.zip that is the current file without the date in it. There is also an unzipped version too. cme.s.pa2

I did not know that.

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

Does anyone happen to know what CME uses net delta for? This goes back to Kevin's observation that the delta seems too high for currencies when calculated by PC-SPAN. The delta in the risk arrays is referred to composite delta by CME. Depending on the market, the composite delta is multiplied by a scaling factor to create the net delta. I'm wondering if this net delta can be ignored when we are looking at finding the delta of a single position or simple spread.

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

Here's the latest spreadsheet with a few updates (just in time for the Holidays):
  • Added Aus Dollar, Brit Pound, Can Dollar, Soybean Meal, Soybeal Oil, Copper, T-Bills, T-Notes, Eurodollars (see Setup tab in spreadsheet for all commodities supported).
  • Modified the selection process so that you can now calculate for multiple options. Click + drag or ctrl + click to select your option(s). Run multiple calculations without running the entire portfolio or running each one at a time. The process will seem a little backwards at first: now you select the option(s) first and then click the button. 'Add to Portfolio' and Delete Selection' use the same selection method allowing you to use that feature on multiple options. Selection for the Spread Risk is done the old way (click button first and then select option).
  • No longer displaying the SPAN IM, SPAN MM and Total MM, just showing Total IM.
  • Now displaying the 'composite delta' for the individual option. As noted earlier in conversations, EC, JY, S, W, and C have their delta multiplied by a certain factor when run through PC-SPAN. From what I have been able to gather this is called 'net delta' by CME and is used when calculating for the offsetting of positions within a portfolio. 'Net delta' shouldn't really be a factor when analyzing individual options.
  • Added ROI to the calculated fields with the ability to adjust the multiple of IM you want held for that position.
  • Tried to streamline code to run a little faster by stopping Excel from making unnecessary calculations while running the code and grabbing more from the risk arrays rather than making calculations (a lot of what we need is right in the arrays).
  • Tried to trap most of the errors so that they cause the code to simply exit, except when running Portfolio Risk (errors will skip current line and continue). So if you calculate for an option and no data is populated it is likely that the option was not found (wrong month or strike).
  • Added some basic portfolio tools to track trades and delete old calculations.
  • Old pa2 files and their zips are deleted by default so you don't get a build-up in your folder.

I'm just supplying the 97/2003 version .xls to make things simpler.

I've run a couple of tests using options for each commodity that can be calculated in the spreadsheet at this time and I am pretty sure I have most of the bugs worked out. I've noticed that Total IM may be off from what is reported in PC-SPAN, but with the discrepancies I saw it was less than $1. As usual, please let me know if you find any issues and I will try to get them fixed.

Merry Christmas!

Margin Without PC-SPANv3.xls

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

Dudetooth,

How is the spreadsheet you developed different from the pc-span software created by the exchange? Is the spreadsheet able to calculate able to compute span margins without the pc-span software installed? Thanks.

Reply With Quote
 
(login for full post details)
  #126 (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: 957 given, 5,708 received

You don't need PC-SPAN to use Dudetooth's spreadsheet. And it works far easier than PC-SPAN.

Started this thread Reply With Quote
 
(login for full post details)
  #127 (permalink)
 mu2pilot 
Dallas, TX
 
Experience: Advanced
Platform: T4, Zaner360, TOS
Broker: DeCarley Trading
Trading: Options
 
Posts: 104 since Sep 2013
Thanks: 134 given, 52 received


ItalianBmT View Post
Dudetooth,

How is the spreadsheet you developed different from the pc-span software created by the exchange? Is the spreadsheet able to calculate able to compute span margins without the pc-span software installed? Thanks.

Like Ron said, you don't need the PC-SPAN software installed, BUT you DO need the PC-Span risk files. You'll notice in the spreadsheet a data field in the upper left corner of the spreadsheet next to the "Download Risk Arrays" button. You enter the most recent trading date (at least after 6pm Chicago time) and click on "Download Risk Arrays" button. This downloads the PC-Span risk files. Once you have that you should be able to use the spreadsheet to figure SPAN margins.

Reply With Quote
 
(login for full post details)
  #128 (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
Dudetooth,

How is the spreadsheet you developed different from the pc-span software created by the exchange? Is the spreadsheet able to calculate able to compute span margins without the pc-span software installed? Thanks.

The spreadsheet also differs from PC-SPAN in that it is limited in what it can calculate. It cannot compute margin for complex spreads and for entire portfolios. The setup tab in the spreadsheet takes you step by step through what the spreadsheet can do.

Reply With Quote
 
(login for full post details)
  #129 (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: 957 given, 5,708 received

I made a copy of Margin without PC SPAN for just ES weeklies and changed CalcRisk to look for 3 digits in con1 and added the product codes for EW and each weekly contract like EW1. I added a space after the 2 digit symbols and that worked OK for them. So ES and CL are working.

But it is not working for EW or EW1. It just comes back with the ES option not the EW or EW1 option.

ElseIf con1 = "ES " Then
str1 = "CMEES ES OOF" & cp1
str1 = str1 & "201" & yr1 & umo1 & " 201" & yr2 & omo1
strSearch = str1 & " " & Application.WorksheetFunction.Text(stk1, "0000000")
strSearch2 = "3 SP"
strSearch3 = "4 SP"
strSearch4 = "B CMEES OOF201" & yr1 & umo1 & " 201" & yr2 & omo1
imm2 = 1
v1 = 0.5
ElseIf con1 = "EW " Then
str1 = "CMEEW ES OOF" & cp1
str1 = str1 & "201" & yr1 & umo1 & " 201" & yr2 & omo1
strSearch = str1 & " " & Application.WorksheetFunction.Text(stk1, "0000000")
strSearch2 = "3 SP"
strSearch3 = "4 SP"
strSearch4 = "B CMEEW OOF201" & yr1 & umo1 & " 201" & yr2 & omo1
imm2 = 1
v1 = 0.5
ElseIf con1 = "EW1" Then
str1 = "CMEEW1 ES OOF" & cp1
str1 = str1 & "201" & yr1 & umo1 & " 201" & yr2 & omo1
strSearch = str1 & " " & Application.WorksheetFunction.Text(stk1, "0000000")
strSearch2 = "3 SP"
strSearch3 = "4 SP"
strSearch4 = "B CMEEW1 OOF201" & yr1 & umo1 & " 201" & yr2 & omo1
imm2 = 1
v1 = 0.5

Note the code above does not show the correct number of spaces in lines. If you click on quote that shows the correct number of spaces in lines in this code.

Is it a mistake with this part of the code or somewhere else? If I use the PC SPAN program the EW & EW1 work OK with the same disk array.

Started this thread Reply With Quote
 
(login for full post details)
  #130 (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 made a copy of Margin without PC SPAN for just ES weeklies ...

Can you give a few examples of options you were looking to calculate (month/strike)?

Reply With Quote
 
(login for full post details)
  #131 (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: 957 given, 5,708 received


Dudetooth View Post
Can you give a few examples of options you were looking to calculate (month/strike)?

EW G4 ES H4 P 1600 -1
EW1G4 ES H4 P 1600 -1

OK I just got it to work by changing the Under Month to EW H4 or EW1H4 instead of ES H4.

Should it do that?

Started this thread Reply With Quote
 
(login for full post details)
  #132 (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: 957 given, 5,708 received

I haven't downloaded v3 because it doesn't display SPAN IM.

Started this thread Reply With Quote
 
(login for full post details)
  #133 (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
EW G4 ES H4 P 1600 -1
EW1G4 ES H4 P 1600 -1

OK I just got it to work by changing the Under Month to EW H4 or EW1H4 instead of ES H4.

Should it do that?

The code is looking to the underlying contract when creating the search string. By changing to EWH4 the code goes to the EW section for creating the search string rather than the ES section.

If you add the following code under the ES section in the original code you could use 4 or 5 characters in column A (e.g. EWG4 or EW1G4 ... you'll still need EWH4 in column B) without changing any other code and it should calculate properly:

ElseIf con1 = "EW" Then
If Len(c1) = 5 Then
str1 = "CME" & Left(c1, 3) & " ES OOF" & cp1
strSearch4 = "B CME" & Left(c1, 3) & " OOF201" & yr1 & umo1 & " 201" & yr2 & omo1
Else:
str1 = "CMEEW ES OOF" & cp1
strSearch4 = "B CMEEW OOF201" & yr1 & umo1 & " 201" & yr2 & omo1
End If
str1 = str1 & "201" & yr1 & umo1 & " 201" & yr2 & omo1
strSearch = str1 & " " & Application.WorksheetFunction.Text(stk1, "0000000")
strSearch2 = "3 SP"
strSearch3 = "4 SP"
imm2 = 1
v1 = 0.5

There are 7 spaces between " and ES in the 3rd line ... spaces stripped when posted.

To get the correct expiration date you'll have to change the strSearch4 as well. Code above is one way to do it.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #134 (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: 957 given, 5,708 received


Dudetooth View Post
Here's the latest spreadsheet (v3) with a few updates (just in time for the Holidays):

As usual, please let me know if you find any issues and I will try to get them fixed.

Thanks. I got it converted for my needs. I wish I had marked the all the changes I made in the code of v2.

In the Sub SpreadRisk() it doesn't check cell O2 to get the excess factor for ROI. Calc Risk does do that.

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #135 (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: 957 given, 5,708 received

Dudetooth, your v3 fixed the milk contract option value being wrong for options that settled at cabinet. It was showing 100 and now it correctly shows 10.

Thanks.

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

No problem ... after you posted the link that detailed the Type 8 records I realized I didn't read it through as thoroughly as I should have, so I went back trough the code to see what I could extract from the risk arrays rather than trying to calculate trying to speed things up ... cabinet values happened to be some of the data it grabs now.

Reply With Quote
 
(login for full post details)
  #137 (permalink)
 mu2pilot 
Dallas, TX
 
Experience: Advanced
Platform: T4, Zaner360, TOS
Broker: DeCarley Trading
Trading: Options
 
Posts: 104 since Sep 2013
Thanks: 134 given, 52 received


Dudetooth View Post
No problem ... after you posted the link that detailed the Type 8 records I realized I didn't read it through as thoroughly as I should have, so I went back trough the code to see what I could extract from the risk arrays rather than trying to calculate trying to speed things up ... cabinet values happened to be some of the data it grabs now.

Dudetooth, you mention speeding things up. I was under the impression that the file read access was the bottleneck in the calc process. Sequentially searching thru 500k lines of text is a slow process. I've looked at pulling the span files into a sql db and querying it to speed up the process and may do that at some point, but I've got other project to do before that.

mu2pilot

Reply With Quote
 
(login for full post details)
  #138 (permalink)
 kevinkdog   is a Vendor
 
 
Posts: 3,002 since Jul 2012
Thanks: 1,598 given, 5,963 received


mu2pilot View Post
Dudetooth, you mention speeding things up. I was under the impression that the file read access was the bottleneck in the calc process. Sequentially searching thru 500k lines of text is a slow process. I've looked at pulling the span files into a sql db and querying it to speed up the process and may do that at some point, but I've got other project to do before that.

mu2pilot

I have a 2 year old PC, and I am able to run through about 3700 options in an hour. One thing I did to speed it up was add a "pre-processor" - since I am only interested in options with delta < .05, I skip any that will be above this criteria. Cuts the time required by about 66%.

Thanks again to @Dudetooth for this incredible tool. I am rarely wowed by free stuff, but his spreadsheet has improved my decision making by a ton:

Before I started using his spreadsheet, I had to manually hunt and peck for options to sell. My median ROI for those trades was 4.0%. I considered that pretty good.

Now, with the spreadsheet, I can find many higher quality options. Since I started using dude's spreadsheet, my median ROI has gone up to 5.5%!!!!!! I know it is because it is easier now to find the right options.

/end of unpaid testimonial

Follow me on Twitter Reply With Quote
 
(login for full post details)
  #139 (permalink)
 mu2pilot 
Dallas, TX
 
Experience: Advanced
Platform: T4, Zaner360, TOS
Broker: DeCarley Trading
Trading: Options
 
Posts: 104 since Sep 2013
Thanks: 134 given, 52 received


kevinkdog View Post
I have a 2 year old PC, and I am able to run through about 3700 options in an hour. One thing I did to speed it up was add a "pre-processor" - since I am only interested in options with delta < .05, I skip any that will be above this criteria. Cuts the time required by about 66%.

Thanks again to @Dudetooth for this incredible tool. I am rarely wowed by free stuff, but his spreadsheet has improved my decision making by a ton:

Before I started using his spreadsheet, I had to manually hunt and peck for options to sell. My median ROI for those trades was 4.0%. I considered that pretty good.

Now, with the spreadsheet, I can find many higher quality options. Since I started using dude's spreadsheet, my median ROI has gone up to 5.5%!!!!!! I know it is because it is easier now to find the right options.

/end of unpaid testimonial

I agree 100%. My post was in no way meant to be a criticism of what Dudetooth has created. It is a GREAT RESOURCE. I hope to help make it even better.

Reply With Quote
 
(login for full post details)
  #140 (permalink)
 kevinkdog   is a Vendor
 
 
Posts: 3,002 since Jul 2012
Thanks: 1,598 given, 5,963 received


mu2pilot View Post
I agree 100%. My post was in no way meant to be a criticism of what Dudetooth has created. It is a GREAT RESOURCE. I hope to help make it even better.

Sorry, I did not think you were criticizing his work. Faster is always better, and a database may be the way to go. I wish I was able to do that - that's why I had to pre-process instead.

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


mu2pilot View Post
Dudetooth, you mention speeding things up. I was under the impression that the file read access was the bottleneck in the calc process. Sequentially searching thru 500k lines of text is a slow process. I've looked at pulling the span files into a sql db and querying it to speed up the process and may do that at some point, but I've got other project to do before that.

mu2pilot

The search is the bottleneck from what I can tell ... I was hoping that anything I could extract from the risk arrays would help. The biggest gain so far was from forcing the spreadsheet to stop any calculations while running the macro. The only other thing I can think to try is to see if more data can be captured from each read through the risk arrays and cut the number read times.

If you ever do find a way to pull the risk arrays into a db please let me know ... I'm pretty weak with db, which is why I use Excel. When all you have is a hammer, all of your problems become nails.

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


kevinkdog View Post
/end of unpaid testimonial

Thanks ... for a limited time you can pre-order a special collector's edition ... act now and avoid future regret!

Seriously though, the way you were grabbing a ton of strikes and calculating them made me start to think about looking for a way to scan the risk arrays. I trying to get it so the spreadsheet may only need to making one pass through the risk array for each commodity, pulling the data for each option that meets a set criteria. I'll let you know if I have any luck.

Reply With Quote
 
(login for full post details)
  #143 (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: 957 given, 5,708 received


Dudetooth View Post
Thanks ... for a limited time you can pre-order a special collector's edition ... act now and avoid future regret!

Seriously though, the way you were grabbing a ton of strikes and calculating them made me start to think about looking for a way to scan the risk arrays. I trying to get it so the spreadsheet may only need to making one pass through the risk array for each commodity, pulling the data for each option that meets a set criteria. I'll let you know if I have any luck.

And to go further with this, would it be able to only pull certain commodities? I'm sure there a thousands of options that any of us will never use.

I know that on my ICE (nyb) softs spreadsheet page it calculates them extremely fast. It's the CME contracts that take so long because there are so many CME options.

Started this thread Reply With Quote
 
(login for full post details)
  #144 (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
And to go further with this, would it be able to only pull certain commodities? I'm sure there a thousands of options that any of us will never use.

The idea would be to extract as much as possible from the risk arrays, making the code more modular. I think there will have to be at least some code in the macro for each commodity that you would want to find options for, but the less you need to define in the code the easier it should be to expand.

Reply With Quote
 
(login for full post details)
  #145 (permalink)
 mu2pilot 
Dallas, TX
 
Experience: Advanced
Platform: T4, Zaner360, TOS
Broker: DeCarley Trading
Trading: Options
 
Posts: 104 since Sep 2013
Thanks: 134 given, 52 received


Dudetooth View Post
The search is the bottleneck from what I can tell ... I was hoping that anything I could extract from the risk arrays would help. The biggest gain so far was from forcing the spreadsheet to stop any calculations while running the macro. The only other thing I can think to try is to see if more data can be captured from each read through the risk arrays and cut the number read times.

If you ever do find a way to pull the risk arrays into a db please let me know ... I'm pretty weak with db, which is why I use Excel. When all you have is a hammer, all of your problems become nails.

Well, not to swell your head too big, you have done a yeomans job on this spreadsheet. I've been all through the code and the risk files and it was not an easy task that you have accomplished. Many thanks. I can't make any promises that I can make it better, but if I do, I will certainly share it here.

Reply With Quote
 
(login for full post details)
  #146 (permalink)
 Big Mike 
Site Administrator
Swing Trader
Data Scientist & DevOps
Manta, Ecuador
 
Experience: Advanced
Platform: Custom solution
Trading: Futures & Crypto
 
Big Mike's Avatar
 
Posts: 50,068 since Jun 2009
Thanks: 32,534 given, 98,490 received

I suggest using Quick Summary post #2 to keep track of the most recent version (link to it), or if you upload it to the Downloads section, then each time you make a new version if you'll just make a post in this thread saying so I will send out the automatic download update emails which alerts anyone who has previously downloaded the file of the new version.

Mike

We're here to help -- just ask

For the best trading education, watch our webinars
Searching for trading reviews? Review this list

Follow us on Twitter, YouTube, and Facebook

Support our community as an Elite Member:
https://futures.io/elite/

Visit other sites? Please spread the word about your experience with our community!
Follow me on Twitter Visit my futures io Trade Journal Reply With Quote
 
(login for full post details)
  #147 (permalink)
 SMCJB 
Legendary Market Wizard
Houston, TX
 
Experience: Advanced
Platform: Trading Technologies
Broker: Primary Advantage Futures. Also ED&F and Tradestation
Trading: Primarily Energy but also a little GE, GC, SI & Bitcoin
 
Posts: 4,089 since Dec 2013
Thanks: 3,401 given, 8,095 received

Amazing thread guys, and an excellent resource.

Most of the discussion seems to be focused on extracting information from risk arrays. Has anybody looked at all on generating and loading position or portfolio files?

My broker sends me a .dat file each evening with my positions in it that I can load into SPAN and duplicate their margin calculations. What I want to be able to do is generate some hypothetical portfolio's and load them into SPAN. Deciphering the data in the dat file was actually a lot easier than I expected (copy of the .dat file. copy of my position and this CME link). Where I've become stuck is in saving/creating new dat files that I can then import into SPAN. I've never created dat files before and the format/spacing of the file itself is causing me problems.

Any suggestions?

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


SMCJB View Post
Amazing thread guys, and an excellent resource.

Most of the discussion seems to be focused on extracting information from risk arrays. Has anybody looked at all on generating and loading position or portfolio files?

My broker sends me a .dat file each evening with my positions in it that I can load into SPAN and duplicate their margin calculations. What I want to be able to do is generate some hypothetical portfolio's and load them into SPAN. Deciphering the data in the dat file was actually a lot easier than I expected (copy of the .dat file. copy of my position and this CME link). Where I've become stuck is in saving/creating new dat files that I can then import into SPAN. I've never created dat files before and the format/spacing of the file itself is causing me problems.

Any suggestions?

I haven't played with any .dat files in conjunction with PC-SPAN ... I'm not even seeing where you can create a .dat file within PC-SPAN (maybe I'm missing something). From the description on the CME link it looks like it might just be a file with text similar to the pa2 files. If that is the case, then it should be easy to manipulate. If you can provide a .dat file I will take a look at it and see what I can come up with.

Reply With Quote
 
(login for full post details)
  #149 (permalink)
 SMCJB 
Legendary Market Wizard
Houston, TX
 
Experience: Advanced
Platform: Trading Technologies
Broker: Primary Advantage Futures. Also ED&F and Tradestation
Trading: Primarily Energy but also a little GE, GC, SI & Bitcoin
 
Posts: 4,089 since Dec 2013
Thanks: 3,401 given, 8,095 received


Dudetooth View Post
I haven't played with any .dat files in conjunction with PC-SPAN ... I'm not even seeing where you can create a .dat file within PC-SPAN (maybe I'm missing something). From the description on the CME link it looks like it might just be a file with text similar to the pa2 files. If that is the case, then it should be easy to manipulate. If you can provide a .dat file I will take a look at it and see what I can come up with.

I'm receiving the .dat files directly from my FCM. I like you though can not find anything on CME SPAN site that even mentions .dat files but I can confirm these do load and work properly. I've also been able to decode them easily but not create new ones.

As it happens the answer to my question appears to be to use the SPAN Position Converter Utility available directly from CME. I've already 'saved' some dummy portfolio's out of SPAN to check the format and it all looks very logical. Thanks to @Dudetooth for this.

If anybody is searching/reading and interested in position/portfolio, @Dudetooth in message #12 and @ron99 in message #14 give examples of using the SPAN Position Converter Utility to convert excel/CSV files to SPAN xml files.

Reply With Quote
 
(login for full post details)
  #150 (permalink)
Mo111
Detroit MI/USA
 
 
Posts: 21 since Jan 2014
Thanks: 12 given, 10 received

Great excel sheet @Dudetooth!!! Its already saved me hours!

What do the SOM and Risk columns mean?

Also, is the Price in the sheet the ask price or the bid price for the contract?

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

Glad it was a help to you. SOM is short option minimum and risk is the highest risk in the risk array scenarios. The highest of the two is used for calculating initial margin. Price is the settlement price... Bid and ask prices do not show up in the risk arrays. I should have a newer spreadsheet available soon that should be even faster.

Sent from my Nexus 5 using Tapatalk

Reply With Quote
 
(login for full post details)
  #152 (permalink)
Mo111
Detroit MI/USA
 
 
Posts: 21 since Jan 2014
Thanks: 12 given, 10 received

thanks for the explanation. The excel sheet is extremely fast on my home computer. I am excited to see what the next update holds.

Btw, how is ROI calculated in the excel sheet?

I'm using ROI = (Option premium) / (Option total IM x 3), but its not the same number that it shows in the sheet.

Attached Thumbnails
Click image for larger version

Name:	screenshot.15.jpg
Views:	99
Size:	68.0 KB
ID:	134067  
Reply With Quote
 
(login for full post details)
  #153 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received

The spreadsheet uses (premium / (IM * variable)) / DTE * 30. The variable should be cell o2 on the risk tab. I think it was set to 2 when I uploaded it.

Sent from my Nexus 5 using Tapatalk

Reply With Quote
 
(login for full post details)
  #154 (permalink)
 SMCJB 
Legendary Market Wizard
Houston, TX
 
Experience: Advanced
Platform: Trading Technologies
Broker: Primary Advantage Futures. Also ED&F and Tradestation
Trading: Primarily Energy but also a little GE, GC, SI & Bitcoin
 
Posts: 4,089 since Dec 2013
Thanks: 3,401 given, 8,095 received

I would like to thank everybody who has contributed to that thread but especially @ron99 and @Dudetooth.
This thread is one of the most helpful and useful threads I've seen in years of watching forums like this.
Primarily because of this thread I have decided to upgrade to an elite membership (hoping there are other threads as valuable behind the elite gates.)
Thanks @ron99 and @Dudetooth again.

Reply With Quote
The following user says Thank You to SMCJB for this post:
 
(login for full post details)
  #155 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

DudeTooth -

I downloaded v3 earlier today and so far, so good. I put in a couple of sample trades and a data date of Friday, January 17th and the results look right to me. So, a big thank you in advance because I think this tool can improve capital allocation and risk management and it's also a quick and easy way to compare which of my brokers is running at or close to SPAN. I know IB gets a bad rap from option sellers, and sometimes it's deserved; other times they are very close or lower than RJO, which seems to be fairly close to SPAN minimum).

Anyway, one question: I'm pretty sure I can change the cell formatting - fonts, text color, cell shading, column width, etc - without messing up any of your code behind the scenes, but what about conditional formatting? For example, if I enter a list of real positions, maybe I want all "Cs" (calls) to be in bold red text (cuz' that's where I usually get screwed) or maybe I want to color code (traffic light) the delta column for various ranges. Will conditional formatting screw anything up?

ˇMuchas Gracias!

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


CafeGrande View Post
Will conditional formatting screw anything up?

Conditional formatting / formatting in general should be fine. The code just messes with the values of the cells.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #157 (permalink)
 SMCJB 
Legendary Market Wizard
Houston, TX
 
Experience: Advanced
Platform: Trading Technologies
Broker: Primary Advantage Futures. Also ED&F and Tradestation
Trading: Primarily Energy but also a little GE, GC, SI & Bitcoin
 
Posts: 4,089 since Dec 2013
Thanks: 3,401 given, 8,095 received

spanit utility

This is wierd. I consistently get errors using the spanit utility but as far as I can tell the error isn't actually causing any problems

My spanscript.txt file
Load C:\SPAN4\ice.20140120.pa5
Load C:\SPAN4\PORTFOLIO.pos
Calc
Save C:\SPAN4\Risk.xml
My SPAN-BATCH.bat file
c:\SPAN4\Bin\spanit.exe C:\SPAN4\SpanScript.txt
c:\SPAN4\RptModule\spanReport.hta C:\SPAN4\Risk.xml


and then


It's definitely not creating any log files anywhere.. but everything seems to be running correctly.
Any ideas??

riskreporter.exe utility

My RiskReporter-BATCH.bat file
C:\Span4\Bin\RiskReporter.exe C:\Span4\Risk.xml /PbReq_CSV /Pos_CSV /ScanRiskContr_CSV C:\Span4\Data\MySpanCalc.log
The only log file this seems to be creating is RiskReporter.txt in the SPAN4\ directory which says
RiskReporter® utility, version 4.37
Copyright© CME Group 2007-2011
Cannot open input file C:\Span4\Data\MySpanCalc.log
Input XML file: C:\Span4\Risk.xml
Loaded SPAN XML file C:\Span4\Risk.xml
List of generated Batch reports:
- /PbReq_CSV C:\Span4\PbReq.csv
- /Pos_CSV C:\Span4\NetPos.csv
- /ScanRiskContr_CSV C:\Span4\ScanRiskContr.csv
Return Value: 0
The program is creating NetPos.csv PbReq.csv & ScanRiskContr.csv correctly though so maybe I should worry.

Combining spanit & riskreporter.exe utilities

You can actually combine the spanit and riskreporter batch files and have a single file.

My spanscript.txt file
Load C:\SPAN4\ice.20140120.pa5
Load C:\SPAN4\PORTFOLIO.pos
Calc
Save C:\SPAN4\Risk.xml
My MEGA-BATCH.bat file
c:\SPAN4\Bin\spanit.exe C:\SPAN4\SpanScript.txt
c:\SPAN4\RptModule\spanReport.hta C:\SPAN4\Risk.xml
C:\Span4\Bin\RiskReporter.exe C:\Span4\Risk.xml /PbReq_CSV /Pos_CSV /ScanRiskContr_CSV C:\Span4\Data\MySpanCalc.log

Reply With Quote
 
(login for full post details)
  #158 (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: 957 given, 5,708 received

SMCJB, on the first one I have no idea. What browser are you using and is it running something when you are running the script?

On the second one I'm not sure either. I see you have the different elements in different folders. I have everything in one folder. The Bin folder. I do output reports to a different folder.

I use SaveCalcSummary. You are just using Save. I don't know if that is a problem.

I probably wasn't much help. Sorry.

Started this thread Reply With Quote
 
(login for full post details)
  #159 (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 haven't used that utility in a few months, so I've forgotten quite a bit already. You batch and script look the same as the last versions I used. I set up a test and got the same error as your second (error saving the log). I still got the reports, but the rptBatch.log was not saved, nor the rptBatch.err. I'm pretty sure I had seen that error before, but I'm afraid I can't remember why it was happening or if I was able to fix it. CME's help file on it didn't really help other than to list the default locations where the reports and logs go.

If it were me, I'd just ignore the error ... your still getting the reports. Sorry, that probably wasn't much help either.

Reply With Quote
 
(login for full post details)
  #160 (permalink)
 SMCJB 
Legendary Market Wizard
Houston, TX
 
Experience: Advanced
Platform: Trading Technologies
Broker: Primary Advantage Futures. Also ED&F and Tradestation
Trading: Primarily Energy but also a little GE, GC, SI & Bitcoin
 
Posts: 4,089 since Dec 2013
Thanks: 3,401 given, 8,095 received


ron99 View Post
SMCJB, on the first one I have no idea. What browser are you using and is it running something when you are running the script?

I normally use Chrome but I get the error even with no browser open.


ron99 View Post
On the second one I'm not sure either. I see you have the different elements in different folders. I have everything in one folder. The Bin folder. I do output reports to a different folder.

I might try using different folders... everything keeps getting cluttered.

ron99 View Post
I use SaveCalcSummary. You are just using Save. I don't know if that is a problem.

Haven't tried that. Thanks for the tip.

Reply With Quote
 
(login for full post details)
  #161 (permalink)
 SMCJB 
Legendary Market Wizard
Houston, TX
 
Experience: Advanced
Platform: Trading Technologies
Broker: Primary Advantage Futures. Also ED&F and Tradestation
Trading: Primarily Energy but also a little GE, GC, SI & Bitcoin
 
Posts: 4,089 since Dec 2013
Thanks: 3,401 given, 8,095 received


Dudetooth View Post
I haven't used that utility in a few months, so I've forgotten quite a bit already. You batch and script look the same as the last versions I used. I set up a test and got the same error as your second (error saving the log). I still got the reports, but the rptBatch.log was not saved, nor the rptBatch.err. I'm pretty sure I had seen that error before, but I'm afraid I can't remember why it was happening or if I was able to fix it. CME's help file on it didn't really help other than to list the default locations where the reports and logs go.

If it were me, I'd just ignore the error ... your still getting the reports. Sorry, that probably wasn't much help either.

I've been having the error consistently for several weeks, doesn't seem to effect anything. I wouldn't worry except that I'm finally about to start automating the dozens of portfolio's I'm running and their reports and am worried that the error message will cause the code execution to break. I might try contacting CME but in my past experience their customer service is not good.

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

SMCJB,

I think I may have a solution for you.

Try running your "c:\SPAN4\RptModule\spanReport.hta C:\SPAN4\Risk.xml" as a separate batch file and have it placed in the "C:\Span4\RptModule" or any other subfolder within "C:\Span4". It appears as though the spanReport.hta is looking to find the Reports folder in a particular location.

One the error it states: "..//reports/rptBatch.log" which looks sort of like the old DOS command when you would type in "CD.." backing you out one folder level. When I did this it ran the reports and did not generate an error. Even tried placing it in the "C:\Span4\Data" and it worked as well.

Reply With Quote
 
(login for full post details)
  #163 (permalink)
Mo111
Detroit MI/USA
 
 
Posts: 21 since Jan 2014
Thanks: 12 given, 10 received

Hi @Dudetooth,

I'm using your spreadsheet to do some backtesting - I'm looking up prior option premiums for a certain strike in the past at different dates. It works well by typing in the prior date in the past in A1, and then downloading the data, and then copying down the premium and IM. Then I'd repeat with another date.

I was wondering if there is anyway to put in multiple dates, and have it download and calculate the margin and premium at a certain strike for different dates?

Thanks!

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


Mo111 View Post
I was wondering if there is anyway to put in multiple dates, and have it download and calculate the margin and premium at a certain strike for different dates?

If you can hang on a few more days, I'm ironing out the last few kinks in a new spreadsheet that includes the ability to track an option history. I'm hoping to post it by this weekend at the latest.

Thanks.

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

Here's the latest and greatest.

Here's a few of the features:
-Added Scanner and Historic tools
-Internalized calculations to eliminate the need of a Temp tab
-Extracted more data from pa2 files, reducing need for data to present in the code and making it easier to add new commodities
-Increased the speed of the code by utilizing arrays to minimize the number of times the pa2 files need to be scanned
-Added the ability to select SPAN or Total IM
-Added futures price, historic volatility and implied volatility to option data extracted

Special thanks to Ron, Kevin and Jay for ideas and troubleshooting to help me make this better.

Beta updates:
-Fixed ROI sort on scanner to include column G
-Fixed contract sort on scanner to include column G
-Fixed contract sort on scanner to sort by futures then option then strike
-Fixed issue with SOM compounding on tracker that was calculating incorrect margin
-Added sort ROI / contract on tracker
-Added erase all results on tracker
-Cell A3 is now selected on historic after changing dates / before running calculations
-Fixed blank rows causing error in calculation
-Changed ROI to 365/DTE/12*net premium/(IM*IM Multiplier)
-Changed instructions on hist tracker added a helpful hints form
-pa2 files are now saved with date in name unless Setup A18 is changed, then back to old way
-Added clear tracker results if no option data in columns A-D
-Cabinet settlement price displayed as 0.5
-Fixed grain price adjustment
-Added more date tools for historic
-Fixed error with option at expiration causing error in calculations

Enjoy.

Attached Files
Register to download File Type: xls XLS-SPAN.xls (679.0 KB, 193 views)
Reply With Quote
The following 20 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #166 (permalink)
 Physicsman 
London + Great Britain
 
Experience: Master
Platform: Interactive Brokers, OptionsXpress, TradeMonster, RJO, AmiBroker, Wealthlab
Trading: Futures, Options, Equities
 
Physicsman's Avatar
 
Posts: 19 since May 2013
Thanks: 85 given, 25 received

Just clicking the "Thanks" button wasn't adequate to express just how extraordinarily good your Excel SPAN margin calculator sheet is now!! I'm pretty much out of superlatives - I think "Wow" gets close though!

Thank you very much @Dudetooth!! It is appreciated more than you could know!

Reply With Quote
 
(login for full post details)
  #167 (permalink)
Mo111
Detroit MI/USA
 
 
Posts: 21 since Jan 2014
Thanks: 12 given, 10 received

Thank you @Dudetooth! This excel sheet will definitely open up new ways to analyze and backtest strategies.

I'm having trouble with the spreadsheet. I have Windows 8 64 bit and excel 2010. I got this error when I click on anything in the sheet:





Any thoughts? I had no trouble using the previous (v3) sheet.

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


Mo111 View Post
Any thoughts? I had no trouble using the previous (v3) sheet.

A minor addition seems to be the source of the problem.


In the Functions Module delete:

'Written: October 07, 2007
'Author: Leith Ross
'Summary: Add Minimize, and Maximize/Restore buttons to a VBA UserForm

Private Const GWL_STYLE As Long = -16
Public Const MIN_BOX As Long = &H20000
Public Const MAX_BOX As Long = &H10000

Const SC_CLOSE As Long = &HF060
Const SC_MAXIMIZE As Long = &HF030
Const SC_MINIMIZE As Long = &HF020
Const SC_RESTORE As Long = &HF120

Private Declare Function GetWindowLong _
Lib "user32.dll" _
Alias "GetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long) As Long

Private Declare Function SetWindowLong _
Lib "user32.dll" _
Alias "SetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long

'Redraw the Icons on the Window's Title Bar
Private Declare Function DrawMenuBar _
Lib "user32.dll" _
(ByVal hwnd As Long) As Long

'Returns the Window Handle of the Window accepting input
Private Declare Function GetForegroundWindow _
Lib "user32.dll" () As Long

Public Sub AddToForm(ByVal Box_Type As Long)

Dim BitMask As Long
Dim Window_Handle As Long
Dim WindowStyle As Long
Dim Ret As Long

If Box_Type = MIN_BOX Or Box_Type = MAX_BOX Then
Window_Handle = GetForegroundWindow()

WindowStyle = GetWindowLong(Window_Handle, GWL_STYLE)
BitMask = WindowStyle Or Box_Type

Ret = SetWindowLong(Window_Handle, GWL_STYLE, BitMask)
Ret = DrawMenuBar(Window_Handle)
End If

End Sub


View the code of the Help form and delete:

Private Sub UserForm_Activate()
AddToForm MIN_BOX
End Sub

Reply With Quote
 
(login for full post details)
  #169 (permalink)
Mo111
Detroit MI/USA
 
 
Posts: 21 since Jan 2014
Thanks: 12 given, 10 received


Dudetooth View Post
A minor addition seems to be the source of the problem.


In the Functions Module delete:

It worked like charm! This excel sheet is truly amazing. I still cant believe it can do all this. My jaw literally just dropped.

WOW @Dudetooth

Thank you!


Reply With Quote
 
(login for full post details)
  #170 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

Thanks again! Very impressive. I think adding the volatility measures can help everyone become a better risk manager, if not trader.

One question: Does the PC-SPAN documentation tell you how many trading days are used for the historical volatility (HV) calculation, and does the day count vary across exchanges?

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


CafeGrande View Post
Thanks again! Very impressive. I think adding the volatility measures can help everyone become a better risk manager, if not trader.

One question: Does the PC-SPAN documentation tell you how many trading days are used for the historical volatility (HV) calculation, and does the day count vary across exchanges?

Good question ... I can't find any mention on how HV is calculated (within the pa2 files under the Type B record it is referred to as "Base Volatility"). I couldn't find any mention from either CME or ICE, but my understanding is that however they calculate it, that methodology is the same across the exchanges that use SPAN.

Reply With Quote
 
(login for full post details)
  #172 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

^

Thanks. I couldn't get the Mar Nat Gas Hist Vol figure from Friday to match with common 20, 40, or 50 day HV measures, so maybe they're using an oddball day count. I can figure that out with trial and error and see if it applies to the other products. I think (hope) they're using the standard HV method based on "close only" - it could get more difficult if they're using one of the methods based on daily ranges. Anyway, not a big deal, it's nice to have the HV number in there.

Quick question about the columns that appear to be unused (see image). I made a 'work copy' and I'm messing around with some sample trades, making some format changes and so on. I noticed in a spread calculation that column F populates, so I'll leave that one alone, but could I...

- hide column G?
- insert an aggregate Delta (No. of contracts x per contract Delta) in column AE?
- insert my percent OTM calculation in column AF?

...without messing things up?

Again, great tool and I appreciate all the effort that went into it.

Attached Thumbnails
Click image for larger version

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


CafeGrande View Post
I couldn't get the Mar Nat Gas Hist Vol figure from Friday to match with common 20, 40, or 50 day HV measures, so maybe they're using an oddball day count. I can figure that out with trial and error and see if it applies to the other products. I think (hope) they're using the standard HV method based on "close only" - it could get more difficult if they're using one of the methods based on daily ranges.

Whatever their methodology, it appears to change throughout the day. I just took a quick look at the mid-day snapshot (i.pa2) for the 7th and it showed 70.88% for the NGH4 where the settlement showed 62.4%. That's kind of bugging me now. Please let me know if you figure it out.


CafeGrande View Post
Quick question about the columns that appear to be unused (see image). I made a 'work copy' and I'm messing around with some sample trades, making some format changes and so on. I noticed in a spread calculation that column F populates, so I'll leave that one alone, but could I...

- hide column G?
- insert an aggregate Delta (No. of contracts x per contract Delta) in column AE?
- insert my percent OTM calculation in column AF?

On the Tracker tab there is no data that gets populated into column G, so hiding it will not mess anything up ... nor will adding your calculations to columns AE and AF. The only thing would be if you go to delete an option it will only erase over to column AD ... that would have to be adjusted within the code (let me know if you need help with that).

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #174 (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: 957 given, 5,708 received

When I try to copy a worksheet from my master sheet to XLS-SPAN I get a message saying that the XLS-SPAN does not have as many rows and columns as my worksheet and it refuses to copy the sheet.

Is there a way to make it work? Otherwise it is telling me to copy and paste the cells which would take more time.

Started this thread Reply With Quote
 
(login for full post details)
  #175 (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
When I try to copy a worksheet from my master sheet to XLS-SPAN I get a message saying that the XLS-SPAN does not have as many rows and columns as my worksheet and it refuses to copy the sheet.

Is there a way to make it work? Otherwise it is telling me to copy and paste the cells which would take more time.

I believe it is because it is currently an Excel 97-2003 worksheet which limits the rows and columns (I was doing that for compatibility issues). I'm gathering you have Office 2007 or 2010 ... if your open XLS-SPAN and save it as an Excel Macro-Enabled worksheet, close it and reopen it you should be good to go. Max row will go from 65536 to1048576 ... max column will go from IV to XFD.

Hope this helps.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #176 (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: 957 given, 5,708 received


Dudetooth View Post
I believe it is because it is currently an Excel 97-2003 worksheet which limits the rows and columns (I was doing that for compatibility issues). I'm gathering you have Office 2007 or 2010 ... if your open XLS-SPAN and save it as an Excel Macro-Enabled worksheet, close it and reopen it you should be good to go. Max row will go from 65536 to1048576 ... max column will go from IV to XFD.

Hope this helps.

My XLS-SPAN was in compatibility mode. After I converted it, it worked. Thanks.

Started this thread Reply With Quote
 
(login for full post details)
  #177 (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: 957 given, 5,708 received

I found out that if you are using Tracker in XLS-SPAN, that you can change the date on the Scanner page (cell G1) and Tracker will run the data using that date (provided you have that day's array in your folder). It doesn't use the current report.

So if you want to see what the margin and premium was for your options in Tracker 5 days ago for example, just change the date and run it.

Here I ran Tracker for 20140207 and then copied and pasted the options below that set in columns A:E and ran Track Select for the 2nd set using the date 20140212.


Started this thread Reply With Quote
 
(login for full post details)
  #178 (permalink)
MGBRoadster
Lancashire UK
 
 
Posts: 52 since Jan 2013
Thanks: 42 given, 32 received

This is a wonderful tool Dudetooth. Thanks for sharing your work.

A question. On the Scanner sheet HEJ4 86P has an Initial Margin of $164. I understand that this is the exchange minimum.




At OptionsXpress the Initial Requirement for LHJ486P is $124.20.



Since OX can't charge less than exchange minimum I must be misunderstanding something here. Can anyone help me out?


Thanks,

Chris

Reply With Quote
 
(login for full post details)
  #179 (permalink)
 kevinkdog   is a Vendor
 
 
Posts: 3,002 since Jul 2012
Thanks: 1,598 given, 5,963 received


MGBRoadster View Post
This is a wonderful tool Dudetooth. Thanks for sharing your work.

A question. On the Scanner sheet HEJ4 86P has an Initial Margin of $164. I understand that this is the exchange minimum.




At OptionsXpress the Initial Requirement for LHJ486P is $124.20.



Since OX can't charge less than exchange minimum I must be misunderstanding something here. Can anyone help me out?


Thanks,

Chris

You are seeing SPAN + Value. There is an option on the setup sheet to show that or just IM. Select "Span" on that page, rerun and you should see $124. If you don't, try redownloading the data file.

My spreadsheet shows $124

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


kevinkdog View Post
You are seeing SPAN + Initial Margin. There is an option on the setup sheet to show that or just IM. Select "Span" on that page, rerun and you should see $124. If you don;t try redownloading the data file.

My spreadsheet shows $124

Chris,

As Kevin pointed out, the spreadsheet is set by default to calculate Total IM, which is the SPAN IM plus the net option value. SPAN IM will always be lower and give you a better ROI, but a word of caution ... make sure you know how your brokerage is calculating your margin requirements.

For example, at OX you can use either formula when looking at your overall margin requirements (what will drive a potential margin call):
1- Total Cash (account balance) - Total IM (SPAN IM + net option value)
2- Account Value (liquidation value, or Total Cash - net option value) - SPAN IM

They both incorporate the current net option value in the calculations. On the example you posted, OX listed $124 as the IM, but you'll see $164 listed as the Total Requirements, which is the same thing as Total IM.

For me, it is easier to track Total Cash (which changes only when a transaction occurs) - Total IM. Plus, since the net option value is being used when determining potential margin calls at the firms is use, I feel Total-IM is more accurate when calculating ROI.

My two cents worth.

Reply With Quote
The following 3 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #181 (permalink)
 mu2pilot 
Dallas, TX
 
Experience: Advanced
Platform: T4, Zaner360, TOS
Broker: DeCarley Trading
Trading: Options
 
Posts: 104 since Sep 2013
Thanks: 134 given, 52 received

Can someone tell me why numerous options have gaps in the strike prices listed in PC SPAN? For example, SBM4 Call Options. Above 21, there are only 2 options that show a SPAN entry and SPAN margin. The highest entry is 22 and it has a Delta of 5.82% (as of 3/14). I would think with the way Sugar has been moving there would be a lot of more activity on the Call side. Is it because there is no Open Interest?

On the put side, Span entries seem to correspond to only those strike prices that have Open Interest. Does SPAN only provide data for instruments that have Open Interest?

TIA.

Reply With Quote
 
(login for full post details)
  #182 (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: 957 given, 5,708 received


mu2pilot View Post
Can someone tell me why numerous options have gaps in the strike prices listed in PC SPAN? For example, SBM4 Call Options. Above 21, there are only 2 options that show a SPAN entry and SPAN margin. The highest entry is 22 and it has a Delta of 5.82% (as of 3/14). I would think with the way Sugar has been moving there would be a lot of more activity on the Call side. Is it because there is no Open Interest?

On the put side, Span entries seem to correspond to only those strike prices that have Open Interest. Does SPAN only provide data for instruments that have Open Interest?

TIA.

Yes

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #183 (permalink)
 mu2pilot 
Dallas, TX
 
Experience: Advanced
Platform: T4, Zaner360, TOS
Broker: DeCarley Trading
Trading: Options
 
Posts: 104 since Sep 2013
Thanks: 134 given, 52 received


ron99 View Post
Yes

How do you compute an expected ROI in such cases? Don't you sometimes sell options where you make up the entire OI? I sold 40 EW4H4 1420 Puts and I still make up all the OI. I don't remember how I came up with an expected ROI, but I know I wouldn't have entered it without something to go on. In cases where there is no OI in option/strike combinations near where you want to trade, how do you do the math?

Reply With Quote
 
(login for full post details)
  #184 (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: 957 given, 5,708 received


mu2pilot View Post
How do you compute an expected ROI in such cases? Don't you sometimes sell options where you make up the entire OI? I sold 40 EW4H4 1420 Puts and I still make up all the OI. I don't remember how I came up with an expected ROI, but I know I wouldn't have entered it without something to go on. In cases where there is no OI in option/strike combinations near where you want to trade, how do you do the math?

I believe SPAN is calculated for all ES/EW contracts even without OI.

For example the ESk4p900 has zero OI but it does have a SPAN margin.

There usually are strikes close by to where I am trading. I don't think I have done an option when there wasn't.

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #185 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

I don't know why but I could not get "Begin + Days" or "Begin - End" to fill the F Column with dates.

So I changed the procedure as follows:

Sub ChangeDate2() '140206*** New date tool for Historic tab
Dim startDate, endDate As Date
On Error GoTo GetOut
If ActiveSheet.Name = "Historic" Then

DefaultDate = Range("F3")
DefaultDate2 = Range("F3")
startDate = InputBox("Enter Beginning Date DD/MM/YYYY", "Beginning Date", DefaultDate)
endDate = InputBox("Enter Ending Date DD/MM/YYYY", "Ending Date", DefaultDate2)

lastrow = Range("F" & Rows.Count).End(xlUp).Row
Range("F3:F" & lastrow).ClearContents

dayDate = startDate
fRow = 3
Do Until dayDate = DateAdd("d", 1, endDate)

Range("F" & fRow) = Format(dayDate, "YYYY") & Format(dayDate, "MM") & Format(dayDate, "DD")



dayDate = DateAdd("d", 1, dayDate)
fRow = fRow + 1
Loop

End If
GetOut:
End Sub

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


BlueRoo View Post
I don't know why but I could not get "Begin + Days" or "Begin - End" to fill the F Column with dates.

I'm not sure which version you have, and I have been making some minor tweaks on mine since the last release, so I have attached the code I've been using that works in my spreadsheet.

One thing that I noticed is that you have removed any reference to Col A on the Historic tab. Starting at A6 I have the available dates that I have SPAN files for in my C:\Span4\Data folder (skipping weekends, holidays). The code was designed to simply gather those dates from Col A to copy over to Col F.

Hope this helps.


Attached Files
Register to download File Type: txt ChangeDates.txt (2.1 KB, 8 views)
Reply With Quote
 
(login for full post details)
  #187 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received


Dudetooth View Post
I'm not sure which version you have, and I have been making some minor tweaks on mine since the last release, so I have attached the code I've been using that works in my spreadsheet.

One thing that I noticed is that you have removed any reference to Col A on the Historic tab. Starting at A6 I have the available dates that I have SPAN files for in my C:\Span4\Data folder (skipping weekends, holidays). The code was designed to simply gather those dates from Col A to copy over to Col F.

Hope this helps.



Yes it helps. As I download risk files my list of previous downloaded dates update. I have not coded it so that the user is informed whether or not the selected date range is within that of the downloaded history. I have started to code a data archive report that would present the user with a date range (with any missing dates within) for the downloaded risk files. I see now that F3 needs to have a date for the changedate code to work. I am working on the idea that:

1. The user needs to know the date range of the risk files downloaded to their nominated directory.
2. That a current list of downloaded dates for say the last 30 days is updated as each download occurrs.
3. That selection of any date range utilises the previous knowledge to inform the user if the selected date range is not represented by the risk files downloaded.

Where am I going with this...

1. I am using the XLS SPAN file within a "Shell" file I have been using to do option analysis.
2. I am interested in time series analysis and charting option prices, volatility and delta for 90 DTE to Expiry.

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


BlueRoo View Post
1. The user needs to know the date range of the risk files downloaded to their nominated directory.
2. That a current list of downloaded dates for say the last 30 days is updated as each download occurrs.
3. That selection of any date range utilises the previous knowledge to inform the user if the selected date range is not represented by the risk files downloaded.

-Once the dates are in Col A things have been working fine for me. The date in cell C5 is the last date in Col A, giving me the range of dates available. The issue would seem to be getting the initial listing of SPAN files in your C:\Span4\Data folder.

-One possible solution would be to have a macro that could query your C:\Span4\Data folder to grab the name of each SPAN file to populate the dates in Col A. Each time you download a SPAN file the code can add that date to the end of the list.

-The code is currently set so that if there isn't an exact match for the date you entered it will grab the next closest date. That way you don't have to look at a calendar to ensure you aren't trying a weekend.

Reply With Quote
 
(login for full post details)
  #189 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

I have this code in my shell file. When I choose to set a date range for generate historic data, the getRiskFileDates returns the date range for saved risk files.




Function getRiskFileDates()
Set p = Workbooks("OptionAnalysis.xls").Sheets("Parameters")

With Application.FileSearch
.LookIn = "M:\Trading\Data\CME SPAN\RiskArrays\"
.SearchSubFolders = False
.Filename = "cme*.pa2"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
numberOfFiles = .FoundFiles.Count
For i = 1 To .FoundFiles.Count
'MsgBox .FoundFiles.Count
Filename = .FoundFiles(i)
Length = Len(.FoundFiles(i))
dateInFileName = Mid(.FoundFiles(i), 41, 8) '.Filename

If i = 1 Then
startDate = convertDateCodeToDateProper(dateInFileName)
ElseIf i = numberOfFiles Then
endDate = convertDateCodeToDateProper(dateInFileName)
getRiskFileDates = startDate & " - " & endDate
End If

Next i
Else
MsgBox "There were no files found."
End If
End With
Continue:

End Function

Reply With Quote
 
(login for full post details)
  #190 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

Selecting track spread allows the user to select the two legs of a spread through input boxes and then calculates the combined result. If there are several spreads in tracker it seems that this manual selection needs to be done for each one for the results of each spread to be updated. Here is some simple code that could automate this process by a single click.

The issue is that the calMargin code in XLS SPAN consistently seems to change the spread number of the last spread in Col F.

May I suggest that some type of automation as follows is added to the file and the issue of the renumbering of the last spread is addressed.

Private Sub cbTrackAllSpreads_Click()
Set t = Workbooks("OptionAnalysis.xls").Sheets("Tracker")
tRow = 3
Do Until t.Range("A" & tRow) = ""
If t.Range("F" & tRow) <> "" Then
If t.Range("F" & tRow) = t.Range("F" & tRow + 1) Then
putrow = tRow
callrow = tRow + 1
Range("A" & putrow & ",A" & callrow).Select
Call CalcMargin(3, 0)
tRow = tRow + 1
End If
End If
tRow = tRow + 1
Loop
End Sub

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


BlueRoo View Post
The issue is that the calMargin code in XLS SPAN consistently seems to change the spread number of the last spread in Col F.

I fixed that issue by leaving the Col F alone if there was an existing value.

I also wanted a way to track all my positions on the Tracker tab with one click. The code I came up with runs through each position and as long as all positions of the spread have the same number in Col F and are adjacent they will be calculated as a spread, one spread at a time. Then it calculates all the remaining positions individually. My code might not match up with yours because I have modified the Track Spread to accommodate more than two positions, but if you are interested I can get you the code.

Also, if you are interested in tracking several positions historically I'd suggest looking at running that through the Tracker rather than the Historic tab (use code to change date on Scanner, track positions on Tracker, and save the data ... then examine results in a pivot table).

Reply With Quote
 
(login for full post details)
  #192 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received


Dudetooth View Post
I fixed that issue by leaving the Col F alone if there was an existing value.

I also wanted a way to track all my positions on the Tracker tab with one click. The code I came up with runs through each position and as long as all positions of the spread have the same number in Col F and are adjacent they will be calculated as a spread, one spread at a time. Then it calculates all the remaining positions individually. My code might not match up with yours because I have modified the Track Spread to accommodate more than two positions, but if you are interested I can get you the code.

Also, if you are interested in tracking several positions historically I'd suggest looking at running that through the Tracker rather than the Historic tab (use code to change date on Scanner, track positions on Tracker, and save the data ... then examine results in a pivot table).

I have not modified any of the code in my copy of your file. Can you post how you fixed the code so Col F is not modified by calMargin(3,0).

Yes, I am interested in getting the code you have for updating all positions on tracker with one click. I am sure we have both done it slightly differently. But looking at both may produce the better code.

Yes, in my shell code I have tracker positions being record historically for each EOD Risk File, so that positions can be seen through DTE.

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


BlueRoo View Post
Can you post how you fixed the code so Col F is not modified ... Yes, I am interested in getting the code you have for updating all positions on tracker with one click.

Here's what I came up with. The fix for the Col F is built into the modified CalcMargin sub.

It will no longer prompt you to select each side of the spread. It is now like the Track Select ... highlight the positions in your spread and then click the Track Spread button. If it is a new spread (no number in Col F) it will give it a number. If it already has a number in Col F it leaves it as is. I've tested it with spreads that have four legs and it works fine, theoretically it should be able to handle as many legs as you want in a spread (the next step for true portfolio margin calculation). It will also add two new columns of info: AE is the current ROI if position was exited early and AF is the current $ P/L.

It should be a simple swap-out of the existing subs in your spreadsheet with those attached ... just let me know if you have any trouble with it.

If you don't mind sharing, how are you analyzing the data you get tracking multiple positions through the tracker ... are you just running it in a pivot table or something else?

Attached Files
Register to download File Type: txt Mod Code.txt (24.6 KB, 14 views)
Reply With Quote
 
(login for full post details)
  #194 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

Your modified calcMargin has a call to a new function called optionvalue. Could you post this missing code.

Yes I am happy to share what I am doing with my shell code optionanalysis.xls. I will post this later. Essentially I am looking at the data your engine provides and processing it graphically and statistically.

Not sure what your plans are for your code but it has been a great help.

Is the code to scan risk files in your default directory of any value?

I am very happy to contribute whatever I can as a sign of appreciation for the work you have already done.

You probably can already tell that I am not essentially a coder. I am more of a app designer and architect. But I can code and have been for 15 years to support my trading activity. My code will just be a little messy and in the past has be reworked by coders after a prototype or design stage (app work I have done in CRM during my career).

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


BlueRoo View Post
Your modified calcMargin has a call to a new function called optionvalue. Could you post this missing code.

Here are the missing functions. If there are too many more missing parts I can look to post my latest spreadsheet (after a while you lose track of all the changes you make).


BlueRoo View Post
Essentially I am looking at the data your engine provides and processing it graphically and statistically ... Not sure what your plans are for your code ...

I am looking forward to seeing the way you are processing and analyzing the data. Right now I mainly use this for risk management, but I have been looking more at back-testing ideas ... mainly with spreads. That and I'd love to have full portfolio margin calculation, but right now that looks pretty daunting.


BlueRoo View Post
Is the code to scan risk files in your default directory of any value?

I'm not sure I fully understand your question. Were you asking about the code I use to list the pa2 files? If so, I have included that with the attachment ... if not please help me to understand what you asking.


BlueRoo View Post
I am very happy to contribute whatever I can

Thank you ... I always love getting a glimpse at what other people are doing with their analysis.

Attached Files
Register to download File Type: txt OptionValue.txt (2.2 KB, 16 views)
Reply With Quote
 
(login for full post details)
  #196 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

The optionValue Function is what I was missing.

I will test the Listpas2Files Procedure. It is similar to the code for the same task that I posted earlier. But it actually reports the date range for the saved risk files. Yours rebuild the list in Col A. My approach is to say you don't need Col A. If you know the date range of your save risk files (which my code reports to the user in the input box) then the dates for the selected date range can be directly entered into Col F. At this moment my approach does not remove the dates with no returned data. Visually I find this easier to look at in bock so weeks. My approach would be then to removed dates with no returned data when I take the time series and move it for analysis. This is because when presenting the time series in a chart two axis's are required. I chart vol and delta on one axis and option price on the other. why because of the different numbers, it is easier to reformat vol and delta to normal decimal numbers e.g. vol at 20% becomes .2 and delta of 20 becomes .2 - therefore the two values are compatible for assignment to a secondary access in a chart which then leaves the primary axis for option price. (let me know if this is not clear.) So you will be able to deduce that I reprocess the data from tracker and historic into newly formatted data sets for analysis.

Use for analysis. Yes we are thinking alike. I see your file useful for both risk management and candidate search.

The ability to retrieve both snapshot and time series data sets is the key. An ability without the work you have down I would not have access too. Again thank you.

Yes, if you wish to develop the code further and I can help maybe a current XLS SPAN file is a good idea.

Reply With Quote
 
(login for full post details)
  #197 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

Okay I see you have removed the input boxes. Now you simply need to select the vertical cels for the spread you wish to trade and click track spread. This great.

The code I have goes the next step. if there are 10 spreads in tracker, with one click the are all process without any need for the user to selected any cells.

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


BlueRoo View Post
The optionValue Function is what I was missing.

If you do any grains you'll need the GrainValue function as well.


BlueRoo View Post
Yours rebuild the list in Col A.

I went the lazy way ... it was the easiest way to get things done. If I wanted to test how an idea would have worked last year at this time, I set my historic dates to start on 5/10/13. I have no idea what day of the week that was, but the code will find the closest pa2 file I have in my folder and then give me dates for the next 30 trade dates. I understand the angle you are going, I just found it easier to simply have a list of the dates available and grab from that. It's a horse apiece I guess.


BlueRoo View Post
So you will be able to deduce that I reprocess the data from tracker and historic into newly formatted data sets for analysis.

I left the rough code I have been using for back-testing along with a sample of the results on the Tracker tab so you can see the route I was taking. Unfortunately, you have to dig through the code in the Backtest module a bit (I don't have all the user-friendly buttons for these subs).

I have focusing primarily on the price to see if an idea is viable, but I could see where incorporating other variables would be beneficial. Any ideas on tweaking the back-testing would be appreciated.


BlueRoo View Post
The code I have goes the next step. if there are 10 spreads in tracker, with one click the are all process without any need for the user to selected any cells.

The code that is in for Track All will calculate for spreads and individual positions alike. Once a spread has been identified (you used Track Spread on multiple rows and they now have a number in Col F), click Track All and it will calculate each spread down the list and then the remaining individual positions. Try it on the one I uploaded to see what I'm talking about.

There's a couple of other tweaks in there from the last release, pretty much my "work in progress" spreadsheet.

Attached Files
Register to download File Type: xls XLS-SPAN (04b).xls (1.90 MB, 42 views)
Reply With Quote
 
(login for full post details)
  #199 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

Appreciate the new file. It is considerably larger and with a quick look at the code there is a bit to take in.

I feel that I should apologize. I see my suggestions where redundant. You are doing a great job on improving what is already a tool that has real value.

So rather than stepping on your toes I would be happy to take your direction on testing anything you wish to have checked.

The back-testing is a fantastic addition.

Here are two screen shows that are generated on the fly by the code in my option analysis file that utilises the data from your file. This is a snapshot. Based on the option chain.



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

No need to apologize. I hope I didn't come across as dismissing your ideas, I just hadn't thought about posting what I have been toying with in the spreadsheet until I saw that you had fixed some of the same issues I had found.

I don't really have any direction I am going with the spreadsheet right now. My main focus is to come up with some new trading ideas, so I have been looking at the back-testing. Normally, I come up with new tools as I run into issues/problems or when I see what other folks are doing.

From the looks of your spreadsheet, I highly doubt that you would be stepping on any toes with me ... screenshots look impressive. I must admit that I immediately wanted to take a look "under the hood" and see what you have in there. If/when your are willing to share some details, I'd love to see some the tools you have developed and get a glimpse of the way you are analyzing potential trades.

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

Journal Challenge w/$1,800 in prizes!

May 7

The Cold Hard Truth: Maybe I Am Not Good Enough w/Chris Gray @ Earn2Trade

Elite only
     



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