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)
  #1 (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

We will use this thread to discuss working with the CME PC-SPAN program.

Started this thread Reply With Quote
The following 7 users say Thank You to ron99 for this post:

Journal Challenge April 2021 results (now extended!):
Competing for $1800 in prizes from Jigsaw
looks_oneMaking a Living with the Microsby sstheo
(602 thanks from 60 posts)
looks_twoSalao's Journalby Salao
(147 thanks from 26 posts)
looks_3Learning to Profit - A journey in algorithms and optionsby Syntax
(112 thanks from 26 posts)
looks_4Deetee’s DAX Trading Journal (time based)by Deetee
(94 thanks from 30 posts)
looks_5Maybe a little bit different journalby Malykubo
(46 thanks from 29 posts)
 
Best Threads (Most Thanked)
in the last 7 days on futures io
I finally blew up an account
512 thanks
The Crude Dude Oil Trading System
70 thanks
Spoo-nalysis ES e-mini futures S&P 500
63 thanks
The tiyfTradePlanFactory indicator
22 thanks
Building a Crypto Mining Rig
18 thanks
 
(login for full post details)
  #3 (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


Here is the opened tree showing where you can find product codes.

Started this thread Reply With Quote
 
(login for full post details)
  #4 (permalink)
 datahogg 
Knoxville Tennessee USA
 
Experience: Intermediate
Platform: TOS
Trading: ES, NQ, CL, /6E futures options.
 
Posts: 327 since Oct 2012
Thanks: 134 given, 134 received

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??

Reply With Quote
 
(login for full post details)
  #5 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 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??

Nope ... you can download the daily risk arrays for free.

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

Just reposting this from the Selling Options on Futures thread:

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

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

Here's what I did:

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

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

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

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

Hopes this helps.


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

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

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

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

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

This is my first take at some VBA in a spreadsheet to help automate some of the tasks ... it's not pretty, but does some of the basic functions I was looking for (download risk arrays, unzip, rename, run batch file). It would be easy to tweak to have it open the Summary.csv and edit, etc. if you are comfortable with VBA. If not, let me know and I'll see what I can modify to have it do what you are looking for.

Attached Files
Register to download File Type: zip PC-SPAN.zip (18.2 KB, 199 views)
Reply With Quote
The following 4 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #8 (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,

Do you use the RiskReporter in PC-SPAN to calculate the risk per position ... I believe they refer to it as Scan Risk Contributions? If you do, do you use the command line to generate the report?

Thanks

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

Do you use the RiskReporter in PC-SPAN to calculate the risk per position ... I believe they refer to it as Scan Risk Contributions? If you do, do you use the command line to generate the report?

Thanks

No I do not.

Started this thread Reply With Quote
 
(login for full post details)
  #10 (permalink)
 datahogg 
Knoxville Tennessee USA
 
Experience: Intermediate
Platform: TOS
Trading: ES, NQ, CL, /6E futures options.
 
Posts: 327 since Oct 2012
Thanks: 134 given, 134 received


I have my copy of the PC Span, and it seems to work.

"Here is where you acquire the daily array files.
ftp://ftp.cmegroup.com/pub/span/data/"


Can some one direct me to the file name for the ES Span values(as an example) as a point to get started?

Thanks HH.

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


datahogg View Post
I have my copy of the PC Span, and it seems to work.

"Here is where you acquire the daily array files.
ftp://ftp.cmegroup.com/pub/span/data/"


Can some one direct me to the file name for the ES Span values(as an example) as a point to get started?

Thanks HH.

To get the risk arrays for ES options, click the CME link (https://ftp://ftp.cmegroup.com/pub/span/data/cme/).

Scroll down to the date you want to download ... name should look like this: cme.20131101.s.pa2.zip.

You'll notice other files with the same date but with different letters after the date. You should just need the 's' file for the settlement risk arrays. If you want a better understanding of the different arrays you can load look at this pdf from CME: https://www.cmegroup.com/clearing/files/Loading_a_Risk_Array_File.pdf.

Hope this helps.

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

For anyone who may be interested, I have found a few more useful command-line utilities to use with PC-SPAN.

SpanPosConv.exe allows you to convert a CSV file with your portfolio to a XML file that PC-SPAN can import (PC-SPAN imports your positions from either POS or XML files). It can be downloaded from the CME Clearing House site CME Clearing House (you will have to log in with the info they gave you when you purchased PC-SPAN).

It comes with a sample CSV file to show you the format that your CSV file should be in, but they don't have much for instructions. A batch file to run this would look like this:

c:\Span4\SpanPosConv\SpanPosConv /i c:\Span4\SpanPosConv\RJO.csv /o c:\Span4\Data\RJO.xml /t np

The first path is where I unzipped the SpanPosConv utility; the path after "/i" is the input file; the path after "/o" the output file.


The RiskReporter.exe utility can create several useful CSV files that give you more detailed information about your positions and there is no need to download, it's already installed with PC-SPAN. All that is required is that you create a Risk.XML file (can be created by the spanit.exe utility as described at the end of post #6).

A batch file would look like this:

C:\Span4\Bin\RiskReporter.exe C:\Span4\Data\Risk_RJO.xml /PbReq_CSV /Pos_CSV /ScanRiskContr_CSV C:\Span4\Data\MySpanCalc.log

Path to RiskReporter.exe; Path of my Risk file; Reports I want; Path of MySpanCalc.log (not sure what it is, but you need this)

These are three of the more useful reports:
/PbReq_CSV - 'PB Requirements' report that has your portfolio margin requirements
/ScanRiskContr_CSV - 'Scan Risk Contributions' report that has the net positions, current value and margin requirements per position
/Pos_CSV - 'Positions' report that has your net positions, their current price and value

Hope this makes sense.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #13 (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
This is my first take at some VBA in a spreadsheet to help automate some of the tasks ... it's not pretty, but does some of the basic functions I was looking for (download risk arrays, unzip, rename, run batch file). It would be easy to tweak to have it open the Summary.csv and edit, etc. if you are comfortable with VBA. If not, let me know and I'll see what I can modify to have it do what you are looking for.

Thank you very much for this.

One thing is that I have to set the batch file location every time I use it. Where would I put the path in VBA? Attached is the VBA.

Attached Thumbnails
Click image for larger version

Name:	vba.jpg
Views:	277
Size:	190.8 KB
ID:	128442  
Started this thread Reply With Quote
 
(login for full post details)
  #14 (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 was able to get SpanPosConv.exe to work. Here is how the spreadsheet would look for CL options. I put my symbol format for the firm. You can put whatever you want there.

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #15 (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
Thank you very much for this.

One thing is that I have to set the batch file location every time I use it. Where would I put the path in VBA? Attached is the VBA.

Initially I had this set up so that when you run the Set_Batch sub it would allow you to set the path/name for the batch file and save the value in cell B2. Then when running the download_risk_files sub it would look to cell B2 for the path/name value.

If you want your path/name to be set in VBA in the download_risk_files sub, just change the following line:

batchfile = Range("Sheet1!B2").Value

to read

batchfile = "C:\Span4\SPAN RiskRep RJO.bat" (just insert the path and name of your batch)

By the way, thanks ... I took your idea of keeping the risk array file name the same so I wouldn't have to change my spanit script. That made it easier for me to have my main spreadsheet do everything from downloading the risk arrays, unzipping them, renaming them, load my positions, create the risk reports and import key data into my spreadsheet. The only piece that I haven't yet come up is the code to create the position CSV file ... on the to-do list. I really like being able to do all of this through the spreadsheet rather than the PC-SPAN program, just easier for me I guess.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #16 (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 run the Set_Batch sub and it works but the next day when I run the download_risk_files sub it doesn't work unless I run the Set_Batch sub again before I run the download_risk_files sub.

The path wasn't being kept in cell B2.

Putting the path in the sub worked. Thanks.

Instead of putting the path in the sub would it have also worked to just enter the path in cell B2?

Also I find I have to delete the cme.s.pa2 & nyb.s.pa2 files from the previous day for it to work. Should I have to be doing that? Any way to have the sub do that?

I greatly appreciate all of your help. I find that I am saving 3 minutes a day using the sub. 3 minutes times the 260 times a year I run it is 13 hours a year. I have used SPAN for 8 years so that is 104 hours or 4.3 days I won't be wasting in the next 8 years!

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

As long as the spreadsheet was saved after running the Set_Batch sub, the path should have been retained in B2 (it was kind of hidden behind the button). Alternatively, you could simply type the path/name of batch file in B2 and save it and that would work just as well as the VBA.

I revamped my download_risk_files sub a bit and it takes care if the issue of having to delete the risk arrays. The following sub will just do the download risk arrays, unzip, delete old risk arrays, and then rename risk arrays:

Sub download_risk_files()
On Error GoTo GetOut
Dim RetVal
Dim batchfile As String
Dim path As String
dateit = Range("Risk!C1").Value 'Date in YYYYMMDD format
namecme = "cme." & dateit & ".s.pa2.zip"
namenyb = "nyb." & dateit & ".s.pa2.zip"
path = "C:\Span4\Data"

SaveWebFile "ftp://ftp.cmegroup.com/pub/span/data/cme/" & namecme, path & "\" & namecme
SaveWebFile "ftp://ftp.cmegroup.com/pub/span/data/nyb/" & namenyb, path & "\" & namenyb

Call UnZip(path & "\", path & "\" & namecme)
Call UnZip(path & "\", path & "\" & namenyb)

Kill path & "\cme.s.pa2"
Kill path & "\nyb.s.pa2"
Name path & "\" & "cme." & dateit & ".s.pa2" As path & "\cme.s.pa2"
Name path & "\" & "nyb." & dateit & ".s.pa2" As path & "\nyb.s.pa2"

GetOut:
End Sub


The "Kill" lines do the dirty work there. Then I use seperate subs for runnning my batch files and assign buttons to each one:

Sub RunPosRJO()
batchfile = "C:\Span4\SpanPosConv\SpanPosConvRJO.bat"
RetVal = Shell(batchfile, 1)
End Sub

Sub RunRiskRJO()
batchfile = "C:\Span4\SPAN Risk RJO.bat"
RetVal = Shell(batchfile, 1)
End Sub

Sub RunReportsRJO()
batchfile = "C:\Span4\SPAN RiskRep RJO.bat"
RetVal = Shell(batchfile, 1)
End Sub


I could probably run all of my batches in the same sub, but I usually keep them seperate when developing and testing the VBA ... perhaps I will combine them down the road.

Have you been using the RiskReporter.exe at all? If you do and are looking for a way to get data from the CSV files that it creates into another spreadsheet I can pass along my VBA to point you in the right direction.

It's no problem at all. I love finding ways to get VBA to do the dirty work, and it's the least I could do with all of the great info I get from you on the Selling Options thread.

Reply With Quote
The following 2 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #18 (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 added these two lines

Kill path & "\" & namecme
Kill path & "\" & namenyb


right underneath the Call UnZip lines to delete the unzipped files.

I also added my sub to format the csv file for use by Access and Excel (I need column B text to columns). But the sub doesn't wait until the batch is done before it runs my sub. I suspect I need a Do While line. How should that look?


Quoting 
Sub download_risk_files()
On Error GoTo GetOut
Dim RetVal
Dim batchfile As String
Dim path As String
dateit = Range("Sheet1!B3").Value
namecme = "cme." & dateit & ".s.pa2.zip"
namenyb = "nyb." & dateit & ".s.pa2.zip"
path = "C:\Span4\Data"

SaveWebFile "ftp://ftp.cmegroup.com/pub/span/data/cme/" & namecme, path & "\" & namecme
SaveWebFile "ftp://ftp.cmegroup.com/pub/span/data/nyb/" & namenyb, path & "\" & namenyb

Call UnZip(path & "\", path & "\" & namecme)
Call UnZip(path & "\", path & "\" & namenyb)

Kill path & "\" & namecme
Kill path & "\" & namenyb

Kill path & "\cme.s.pa2"
Kill path & "\nyb.s.pa2"
Name path & "\" & "cme." & dateit & ".s.pa2" As path & "\cme.s.pa2"
Name path & "\" & "nyb." & dateit & ".s.pa2" As path & "\nyb.s.pa2"

batchfile = "C:\Span4\Bin\SPAN-Batch.bat"
RetVal = Shell(batchfile, 1)

Call FormatSPANMargins
GetOut:
End Sub


Quoting 
Sub FormatSPANMargins()
Workbooks.Open Filename:="C:\Span4\Data\test.csv"
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
Columns("A:A").EntireColumn.AutoFit
Range("B1").Select
ActiveWorkbook.SaveAs Filename:="C:\Span4\Data\SPANMargins.xls", FileFormat _
:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
End Sub


Started this thread Reply With Quote
 
(login for full post details)
  #19 (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 suspect I need a Do While line. How should that look?

Truthfully, I wasn't sure exactly how to do this. After looking around a bit it looks rather complex. Once the shell command is used to call the batch file that process is no longer under the control of VBA and that's why the code continues. Separate functions and subs are needed to tell VBA to pause.

I attached some code that I was going to try myself, but I won't be able to test until this weekend. It looks like it might be easy to copy-paste and modify his code.

The simple fix is to just not call your FormatSPANMargins sub from within the download_risk_files sub (delete the "Call FormatSPANMargins" line). Run download_risk_files, wait for the cmd window to close, and then run FormatSPANMargins.

I'll let you know if I get the VBA pause for batch files to work.

Attached Files
Register to download File Type: txt VBA Run Batch- wait until done before continue.txt (3.4 KB, 36 views)
Reply With Quote
 
(login for full post details)
  #20 (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'll just not call my format sub. Not that big of a deal.

Started this thread Reply With Quote
 
(login for full post details)
  #21 (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 hate to bother you but my sub does everything down to doing the batchfile and for one second the DOS window flashes on my screen but it doesn't run.

But if I click on the batch file in Windows Explorer it works OK. So it's not the batchfile.

I tried running the batchfile as a separate sub and it still doesn't run. It just flashes for one second.

Sub RunSPANBatchfile()
batchfile = "C:\Span4\Bin\SPANBatch.bat"
RetVal = Shell(batchfile, 1)
End Sub


I am using Excel 2010 with VBA 7.0.

Started this thread Reply With Quote
 
(login for full post details)
  #22 (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
But if I click on the batch file in Windows Explorer it works OK. So it's not the batchfile.

I tried running the batchfile as a separate sub and it still doesn't run. It just flashes for one second.

I had a similar problem with one of my batch files. I think it is related to the path (I can't find any definitive proof). Most of my batch files were located in the C:\Span4 folder, but I had 2 in the sub-folder C:\Span4\SpanPosConv. The name of the Batch had a space in it and it just flashed the cmd window when I tried to run it in VBA as "C:\Span4\SpanPosConv\SpanPosConv RJO.bat". My other batch files in the C:\Span4 folder had spaces in their name and worked fine when called through VBA.

I removed the space in the name of the batch and was able to call it as "C:\Span4\SpanPosConv\SpanPosConvRJO.bat". I also tried moving the batch to the C:\Span4 folder and leaving the name with the space and that also worked calling "C:\Span4\SpanPosConv RJO.bat".

You mentioned that your batch works fine when you click on it, so it should not be an error in the batch. The name of your batch does not have a space, but it is in a sub folder. I have no idea why it would have issues with the path, but I would recommend moving the batch to the C:\Span4 folder and modify your sub:

Sub RunSPANBatchfile()
Dim RetVal
Dim batchfile as string
batchfile = "C:\Span4\SPANBatch.bat"
RetVal = Shell(batchfile, 1)
End Sub


Hopefully this will take care of the issue.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #23 (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'll just not call my format sub. Not that big of a deal.

I was able to get this to work by editing only a few lines of that text that was in the file I upload. Now I can run one sub that can call multiple batch files, wait for each to complete before running the next, and then run additional code. I can walk you through that if you are interested.

Reply With Quote
 
(login for full post details)
  #24 (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 a similar problem with one of my batch files. I think it is related to the path (I can't find any definitive proof). Most of my batch files were located in the C:\Span4 folder, but I had 2 in the sub-folder C:\Span4\SpanPosConv. The name of the Batch had a space in it and it just flashed the cmd window when I tried to run it in VBA as "C:\Span4\SpanPosConv\SpanPosConv RJO.bat". My other batch files in the C:\Span4 folder had spaces in their name and worked fine when called through VBA.

I removed the space in the name of the batch and was able to call it as "C:\Span4\SpanPosConv\SpanPosConvRJO.bat". I also tried moving the batch to the C:\Span4 folder and leaving the name with the space and that also worked calling "C:\Span4\SpanPosConv RJO.bat".

You mentioned that your batch works fine when you click on it, so it should not be an error in the batch. The name of your batch does not have a space, but it is in a sub folder. I have no idea why it would have issues with the path, but I would recommend moving the batch to the C:\Span4 folder and modify your sub:

Sub RunSPANBatchfile()
Dim RetVal
Dim batchfile as string
batchfile = "C:\Span4\SPANBatch.bat"
RetVal = Shell(batchfile, 1)
End Sub


Hopefully this will take care of the issue.

I moved the batchfile to SPAN4. And then copied and pasted your sub. I now get the error that SPANData.dll is not on my computer. I moved the SPANData.dll from the Bin folder to the SPAN4 folder and I still get the same error message.

I have used too much time on trying to get this to work. I need to move on. Your download risk files sub has been a big help thanks.

Started this thread Reply With Quote
 
(login for full post details)
  #25 (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 was able to get this to work by editing only a few lines of that text that was in the file I upload. Now I can run one sub that can call multiple batch files, wait for each to complete before running the next, and then run additional code. I can walk you through that if you are interested.

I may be crazy to try but can you easily do that?

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


ron99 View Post
I have used too much time on trying to get this to work. I need to move on.

I'm sorry that it didn't help. I know the dll file needs to be in the same folder as the spanit.exe. The error makes me think that there might be something in the batch file or span script that might be throwing it off (file name or path of batch that has changed). If you decide later to give it another go, I'll be happy to try to help you troubleshoot.

Reply With Quote
 
(login for full post details)
  #27 (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 may be crazy to try but can you easily do that?

It's pretty painless.
1- copy all the code from the text file uploaded and paste it above all of your other code in your module.
(all of the "Option Explicit", "Private Declare Function" and "Private Const" lines need to be on top, above all the subs ... if you have any other "Private Declare Functions" they'll have to be moved above the subs as well)

2- edit the "Sub Run_bat()" as needed. It is currently set to run 2 batch files and then call another sub "copy_data_RJO" in my spreadsheet ... just change the name/path of the batch files:
"C:\Span4\SPAN Risk RJO.bat" and "C:\Span4\SPAN RiskRep RJO.bat" and insert code to call other subs as needed.

Attached Files
Register to download File Type: txt Code.txt (3.3 KB, 30 views)
Reply With Quote
 
(login for full post details)
  #28 (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'm sorry that it didn't help. I know the dll file needs to be in the same folder as the spanit.exe. The error makes me think that there might be something in the batch file or span script that might be throwing it off (file name or path of batch that has changed). If you decide later to give it another go, I'll be happy to try to help you troubleshoot.

I got it to work. I changed my SPANBatch from

spanit C:\Span4\Bin\SPANScript.txt

to

C:\Span4\Bin\spanit C:\Span4\Bin\SPANScript.txt

Looks like it needed the full path for spanit.

You mentioning the path made me think of this.

Started this thread Reply With Quote
 
(login for full post details)
  #29 (permalink)
 spj77 
Qld, Australia
 
Experience: Beginner
Platform: Excel/Hoadley/PC-SPAN
Broker: OX
Trading: Options
 
Posts: 28 since Mar 2012
Thanks: 6 given, 44 received

Hi,

You guys will be fully fledged programmers soon!

Do you happen to know an easy way to get the margins on a list of OX symbols such as the following list out of SPAN:

ESZ31400P
ESF41300P
CLF4115C
CLF475P
CLG4120C
NGZ34.15C
NGZ33.25P
NGG45.5C
NGH45.7C
GCG41700C
KCH41600C
SF41060P
SH41600C
ECZ31.405C

I am thinking that simply listing them into a portfolio csv file and loading them up with SpanPosConv (net position of 1 each) and calculating the perf bond req will calculate the margins for each as part of a spread within the portfolio etc - But i am hoping to get just the straight margin for each as if it were traded independently of anything else, like if it was the only position in the portfolio....

Cheers
Scott

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

Scott, if you put the symbol in the firm name then you will get the margin for each. That is what I do. See post #14.

SPAN will group the results by firm name with the same acct ID. So everything on the spreadsheet will the same firm name and acct ID will be calculated together to give you margin for all lines of the spreadsheet with that firm name and acct ID.

If you want margin for each contract then put your symbol in the firm name and anything in acct ID.

If you want margin for a spread, Put the same firm name and acct ID for each leg. SPAN will then combine them.

Tip: The spreadsheet must be closed in order to run the SPANPosConv batch. Also blank lines in spreadsheet are ignored.


This is how it would look if you put them into the SPAN program.

Started this thread Reply With Quote
The following 3 users say Thank You to ron99 for this post:
 
(login for full post details)
  #31 (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
Looks like it needed the full path for spanit.

That'll do it. If no path is given it will default to the folder the batch file is in. I went back through my batch files and placed the full path in all of them just to be safe.

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


spj77 View Post
But i am hoping to get just the straight margin for each as if it were traded independently of anything else, like if it was the only position in the portfolio.t

Another route you could look at would be to use the RiskReported.exe as mentioned in post #12.

It'll give you a CSV file that looks like the attached pic (activeValue= last value of position; scanContr= margin for that position) :

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #33 (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
Another route you could look at would be to use the RiskReported.exe as mentioned in post #12.

It'll give you a CSV file that looks like the attached pic (activeValue= last value of position; scanContr= margin for that position) :

I don't understand. I get $61 IM for each OG contract.

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #34 (permalink)
 spj77 
Qld, Australia
 
Experience: Beginner
Platform: Excel/Hoadley/PC-SPAN
Broker: OX
Trading: Options
 
Posts: 28 since Mar 2012
Thanks: 6 given, 44 received

Wish the CME would hurry up and send me a user id and password so i can download SpanPosCov.exe...So frustrating, want to try the ideas you guys have given!

Reply With Quote
 
(login for full post details)
  #35 (permalink)
 spj77 
Qld, Australia
 
Experience: Beginner
Platform: Excel/Hoadley/PC-SPAN
Broker: OX
Trading: Options
 
Posts: 28 since Mar 2012
Thanks: 6 given, 44 received

Hi Ron,
So if in PCSPAN each option is its own portfolio, would you use the risk reporter tool to extract them all out into one excel/csv file, or do you have another method?
Cheers
Scott



ron99 View Post
Scott, if you put the symbol in the firm name then you will get the margin for each. That is what I do. See post #14.

SPAN will group the results by firm name with the same acct ID. So everything on the spreadsheet will the same firm name and acct ID will be calculated together to give you margin for all lines of the spreadsheet with that firm name and acct ID.

If you want margin for each contract then put your symbol in the firm name and anything in acct ID.

If you want margin for a spread, Put the same firm name and acct ID for each leg. SPAN will then combine them.

Tip: The spreadsheet must be closed in order to run the SPANPosConv batch. Also blank lines in spreadsheet are ignored.


This is how it would look if you put them into the SPAN program.


Reply With Quote
 
(login for full post details)
  #36 (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 get $61 IM for each OG contract.

I did misspeak (thanks for catching that) ... it is the scan risk contribution that is listed, not the margin. The main thing is that it does not add in the Short Option Minimum (SOM). I loaded it up in PC-SPAN with 1 GCZ3 C1660 I get $61 for risk and $71 for IM. Digging through PC-SPAN results it looks like the SOM for GC added $55 to the $7 risk array (rounded to $61), plus the option value of $10 for the $71 total.

Unfortunately, the ScanRiskContr report doesn't reflect the SOM. That is done in the PBReq report, but in there the SOM is grouped by commodity so it won't be too helpful for multiple positions in the same market. Your method of using the spanit.exe with the position name in the firm column may be the best solution for calculating individual position margin that will include the SOM. Thanks for the idea.

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


spj77 View Post
Wish the CME would hurry up and send me a user id and password so i can download SpanPosCov.exe...So frustrating, want to try the ideas you guys have given!

CME give good customer service?

Started this thread Reply With Quote
 
(login for full post details)
  #38 (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

The procedure would be to create the CSV spreasheet like post #12 with your contracts.

Run SPAN Position Converter Utility to create the .pos file.

Run the SPAN Script batch (spanit) created in post #6.

The output of SPAN Script would be a spreadsheet that looks like this (Column M is the IM)



Or if you run my FormatSPANMargins sub it would look like this (Column L is the IM) I delete column A and do a Text to Column procedure on the resulting Column A to extract the symbols from the rest of the info.



Column H is the current option value in dollar form. You just multiply this by -1 to get a positive number.
Column J is the MM.

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #39 (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 did misspeak (thanks for catching that) ... it is the scan risk contribution that is listed, not the margin. The main thing is that it does not add in the Short Option Minimum (SOM). I loaded it up in PC-SPAN with 1 GCZ3 C1660 I get $61 for risk and $71 for IM. Digging through PC-SPAN results it looks like the SOM for GC added $55 to the $7 risk array (rounded to $61), plus the option value of $10 for the $71 total.

Unfortunately, the ScanRiskContr report doesn't reflect the SOM. That is done in the PBReq report, but in there the SOM is grouped by commodity so it won't be too helpful for multiple positions in the same market. Your method of using the spanit.exe with the position name in the firm column may be the best solution for calculating individual position margin that will include the SOM. Thanks for the idea.

The SPAN Risk under Initial Requirements is the IM. $61 in this case. That matches statements I get from the firms I trade at.

The Risk Initial amount on the statements is what I am looking to match. The Initial Margin number on some statements includes the value of the option. But that number is not used to determine margin calls and we don't use that to determine ROI.

The $55 is the MM.

Started this thread Reply With Quote
 
(login for full post details)
  #40 (permalink)
 spj77 
Qld, Australia
 
Experience: Beginner
Platform: Excel/Hoadley/PC-SPAN
Broker: OX
Trading: Options
 
Posts: 28 since Mar 2012
Thanks: 6 given, 44 received

Hi,

I finally received the login credentials from the CME and have started to try and automate the PC-SPAN experience like you guys.

But i am hitting the second hurdle. I have copied your guys coding from earlier posts and the 'DownloadRiskArrayFiles" sub works fine; places the unzipped data files in the 'span4\data' folder ready for loading into SPAN. All good there.

I created a "SpanScriptLoadRiskArrayFiles.txt" file and saved it in Span4\Bin folder. It contains two lines:
Load C:\Span4\Data\cme.s.pa2
Load C:\Span4\Data\nyb.s.pa2

I created a "LoadRiskArrayFiles.bat" file and saved it in Span4\Bin folder. It contains:
C:\Span4\Bin\spanit.exe C:\Span4\Bin\SpanScriptLoadRiskArrayFiles.txt


When i run the macro, it opens up a dos window which contains "Batch Interface for PC-SPAN" and then says it is loading the files. The window then closes. The problem is when i then open PC-SPAN to see if files have been loaded it is empty in there.......... The data files themselves are fine as i loaded them manually in PC-SPAN and they populate as you would expect (and then exited without saving).

Any ideas what i am missing / doing wrong? I can't find any setting in the program where i need to enable batch processing...


My Code is attached:
-------------------------------------------------

Sub DownloadRiskArrayFiles()

On Error GoTo GetOut
Dim RetVal
Dim batchfile As String
Dim path As String
dateit = Range("SpanMacro!B2").Value 'Date in YYYYMMDD format
namecme = "cme." & dateit & ".s.pa2.zip"
namenyb = "nyb." & dateit & ".s.pa2.zip"
path = "C:\Span4\Data"

SaveWebFile "ftp://ftp.cmegroup.com/pub/span/data/cme/" & namecme, path & "\" & namecme
SaveWebFile "ftp://ftp.cmegroup.com/pub/span/data/nyb/" & namenyb, path & "\" & namenyb

Call UnZip(path & "\", path & "\" & namecme)
Call UnZip(path & "\", path & "\" & namenyb)

Kill path & "\" & namecme
Kill path & "\" & namenyb

Kill path & "\cme.s.pa2"
Kill path & "\nyb.s.pa2"
Name path & "\" & "cme." & dateit & ".s.pa2" As path & "\cme.s.pa2"
Name path & "\" & "nyb." & dateit & ".s.pa2" As path & "\nyb.s.pa2"

GetOut:
End Sub

-----------------------------------------------

Sub LoadRiskArrayFiles()

batchfile = "C:\Span4\Bin\LoadRiskArrayFiles.bat"
RetVal = Shell(batchfile, 1)

End Sub

-----------------------------------------------


Cheers
Scott

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

Scott, the spanit program does not load the arrays into the program for you to use PC-SPAN. You have to load them manually if you want to use the PC-SPAN program.

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #42 (permalink)
 spj77 
Qld, Australia
 
Experience: Beginner
Platform: Excel/Hoadley/PC-SPAN
Broker: OX
Trading: Options
 
Posts: 28 since Mar 2012
Thanks: 6 given, 44 received

Oh, now i see why in the spanscript file you need to:
- load datafiles
- load position files
Calc
- send summary output csv file
all in the one batch, as the data is not actually stored anywhere you need it to return the csv file output before the batch file completes.....makes perfect sense now i know that.

I was trying to seperate them into multiple tasks in different batch files thinking the data is stored in the program for the next step

Thanks!
Scott

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


spj77 View Post
Oh, now i see why in the spanscript file you need to:
- load datafiles
- load position files
Calc
- send summary output csv file
all in the one batch, as the data is not actually stored anywhere you need it to return the csv file output before the batch file completes.....makes perfect sense now i know that.

I was trying to seperate them into multiple tasks in different batch files thinking the data is stored in the program for the next step

Thanks!
Scott

With spanit you are using the program without having to open it. Thus saving a bunch of time.

Here is the manual for spanit.
https://www.cmegroup.com/confluence/display/pubspan/Script+Language+for+PC-SPAN+v.4.05+and+SPAN+Risk+Manager

Here is the manual for all of PC-SPAN
https://www.cmegroup.com/confluence/display/pubspan/SPAN+Overview

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #44 (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

Warning.

The example spreadsheet in this CME Span Pos Converter pdf manual
https://www.cmegroup.com/clearing/risk-management/files/spanposconvutil.pdf

for ES shows using cc code (column J) ES. But that is not correct. You need to use cc code SP.

I emailed CME about this. We'll see if they do anything.

Started this thread Reply With Quote
 
(login for full post details)
  #45 (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've been working on a spreadsheet that can extract the risk, value and delta of an option from the CME risk arrays for some time. After ron99 found some discrepancies with a previous post about risk and margin it forced me to review the methods I was using, but it helped me to eventually come up with what I think is some better code. I found that the short option minimum and initial-to-maintenance ratio (two items I was missing) were in the risk arrays, so you have everything you need to calculate the SPAN initial/maintenance and the Total initial/maintenance margins (option value added to SPAN margins).

Just enter the option, the underlying, call or put, strike and net positions and the spreadsheet will extract/calculate the price of the option, the position value, option delta, position risk, short option minimum, and the SPAN and Total margins. I tested this out on a few portfolios to make sure that it was coming up the same numbers as PC-SPAN. I know ron99 said that his firms use the SPAN initial margin, but the firms I trade at use the Total initial margin, so I decided to included both the SPAN and Total margins in the spreadsheet.

I think an advantage that this may have is that it's a little simpler to get the numbers ... all that is needed is the spreadsheet and the risk arrays (no PC-SPAN or batch files).

Don't get me wrong, it has its limitations. It is only coded to work with 12 of the markets I trade, but could be expanded fairly easy. I think I can get it to do simple, two-legged spreads eventually, but for now it will calculate individual option positions. Another aspect it does not handle right now are the credits for offsetting positions, so if you are looking for entire portfolio margin calculation stick with PC-SPAN.

If anyone happens to take it for a spin, let me know if you find any issues ... I'm sure they are in there.

Happy Thanksgiving!

***A few additional notes if you give it a try, the option contract, underlying contract and strikes should stay in the format as shown in the spreadsheet. Short positions should be entered as negative numbers in the net column (e.g. -2). Also, the spreadsheet will be looking for the CME risk arrays labelled as cme.s.pa2 and nyb.s.pa2 in the C:\Span4\Data folder.

Attached Files
Register to download File Type: zip Margin without PC-SPAN.zip (33.5 KB, 109 views)
Reply With Quote
The following 4 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #46 (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, that is great! The options I compared all matched PC-SPAN for margin.

I found out that you have to use capitalized letters for the symbols.

How do you use the Single Position button?

Can other commodities be added? Like HE (Hogs), CT, CO, DA (milk), HO, RB, SI.

Started this thread Reply With Quote
 
(login for full post details)
  #47 (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 found out that you have to use capitalized letters for the symbols.

Thanks for finding that. I suppose I could fix that by having the code make your text input upper case before running the search. I'll put it on the to-do list.


ron99 View Post
How do you use the Single Position button?

When you click on the Single Position button an input box will appear asking you to select an option. Click anywhere in the row of an option you want to look at and click OK. It runs the code for just that row/option. It should come in handy if you have just a couple of options to check our or if you add one to your list and don't want to run them all. I will eventually get that into my main spreadsheet so as I am looking at potential trades I can run the numbers for just the strikes that I may want to sell.


ron99 View Post
Can other commodities be added? Like HE (Hogs), CT, CO, DA (milk), HO, RB, SI.

Yes, it just takes a little reverse-engineering. I run a test portfolio in PC-SPAN so I can find the symbols and values and then dig through the risk arrays manually to make sure I get the search strings right. I can add these symbols to the to-do list if you'd like.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #48 (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

Yes I would like those symbols added if you could.

I am going to add the download disk array macro to this spreadsheet. So then all can be done from one spreadsheet.

There wouldn't be a problem adding that on this spreadsheet?

Started this thread Reply With Quote
 
(login for full post details)
  #49 (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
There wouldn't be a problem adding that on this spreadsheet?

No, their shouldn't be any issues ... just check the cell references in the code to make sure they are looking in the right place and it should work fine. I'll look to add the download/unzip/rename function on the next version.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #50 (permalink)
 kevinkdog   is a Vendor
 
 
Posts: 3,002 since Jul 2012
Thanks: 1,598 given, 5,963 received


Dudetooth View Post
I've been working on a spreadsheet that can extract the risk, value and delta of an option from the CME risk arrays for some time. After ron99 found some discrepancies with a previous post about risk and margin it forced me to review the methods I was using, but it helped me to eventually come up with what I think is some better code. I found that the short option minimum and initial-to-maintenance ratio (two items I was missing) were in the risk arrays, so you have everything you need to calculate the SPAN initial/maintenance and the Total initial/maintenance margins (option value added to SPAN margins).

Just enter the option, the underlying, call or put, strike and net positions and the spreadsheet will extract/calculate the price of the option, the position value, option delta, position risk, short option minimum, and the SPAN and Total margins. I tested this out on a few portfolios to make sure that it was coming up the same numbers as PC-SPAN. I know ron99 said that his firms use the SPAN initial margin, but the firms I trade at use the Total initial margin, so I decided to included both the SPAN and Total margins in the spreadsheet.

I think an advantage that this may have is that it's a little simpler to get the numbers ... all that is needed is the spreadsheet and the risk arrays (no PC-SPAN or batch files).

Don't get me wrong, it has its limitations. It is only coded to work with 12 of the markets I trade, but could be expanded fairly easy. I think I can get it to do simple, two-legged spreads eventually, but for now it will calculate individual option positions. Another aspect it does not handle right now are the credits for offsetting positions, so if you are looking for entire portfolio margin calculation stick with PC-SPAN.

If anyone happens to take it for a spin, let me know if you find any issues ... I'm sure they are in there.

Happy Thanksgiving!

***A few additional notes if you give it a try, the option contract, underlying contract and strikes should stay in the format as shown in the spreadsheet. Short positions should be entered as negative numbers in the net column (e.g. -2). Also, the spreadsheet will be looking for the CME risk arrays labelled as cme.s.pa2 and nyb.s.pa2 in the C:\Span4\Data folder.


Dude -
This looks awesome! Can you save a version in Excel 97 format? I am still way behind the times...

Follow me on Twitter Reply With Quote
 
(login for full post details)
  #51 (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 can add these symbols to the to-do list if you'd like.

I have mostly figured out how to add them myself. I added DA, CT & CC (Cocoa). I see that the added ones need to be in alphabetical order.

I see v1 is the factor to change price to dollars.

What is imm2?
What is stk1 used for?
What is d1?

But then I ran into a problem. I mistakenly tried to get a misspelled symbol to calculate. The program was stuck in a loop. I hit Esc twice and told it to end process. Now it won't work for any contract.

Edit: I went back to my saved original file and have things working again.

Started this thread Reply With Quote
 
(login for full post details)
  #52 (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

Am I correct that expiration date or DTE is not available in the disk arrays?

Started this thread Reply With Quote
 
(login for full post details)
  #53 (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 am having problems adding SI, HO to the macro.

Got LN (Hogs) to work.

I'm guessing my stk1 is wrong or my v1 is wrong.

On the Single Position button the popup box says Select Row so I was trying to select the whole row. I changed the macro so it says "Select Cell in Option Row".

Started this thread Reply With Quote
 
(login for full post details)
  #54 (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
Can you save a version in Excel 97 format? I am still way behind the times...

This one is 97/2003 compatible.

Attached Files
Register to download File Type: zip Margin without PC-SPAN-97.zip (26.2 KB, 24 views)
Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #55 (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
What is imm2?

When I found the line in the risk arrays that held the short option minimum I noticed that there were a few that didn't seem to work until I multiplied them by 2 (for currencies) and 5 (for grains) ... perhaps related to the size of the mini-contracts.


ron99 View Post
What is stk1 used for?

That is the strike of the option.


ron99 View Post
What is d1?

I found a similar issue with the delta that I found with SOM, but just with the currencies. Multiplying by 2 created the correct delta. Again, no idea why, but it works.

Reply With Quote
 
(login for full post details)
  #56 (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
Am I correct that expiration date or DTE is not available in the disk arrays?

I'm not sure. I did a quick look, but didn't see anything. Have you ever seen the DTE displayed in any of the reports that PC-SPAN creates? If it's in a report we should be able to extract it.

****Good news ... I think I found it. There are lines in the risk array that appear to that the date that the option expires, so we should be in luck.

Reply With Quote
 
(login for full post details)
  #57 (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 am having problems adding SI, HO to the macro ... I'm guessing my stk1 is wrong or my v1 is wrong.

It is possible. Not sure when I'll get a chance to work the code for those. You can try stepping through the code in the VBA editor (F8 key) to see where it gets hung up. If it just that the search is not grabbing anything then it might be something like the strike.


ron99 View Post
On the Single Position button the popup box says Select Row so I was trying to select the whole row. I changed the macro so it says "Select Cell in Option Row".

I can see where that would be confusing. It is easy to understand when I am writing it ... doesn't always translate properly to others. I'll change that in the next build.

Reply With Quote
 
(login for full post details)
  #58 (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'm not sure. I did a quick look, but didn't see anything. Have you ever seen the DTE displayed in any of the reports that PC-SPAN creates? If it's in a report we should be able to extract it.

****Good news ... I think I found it. There are lines in the risk array that appear to that the date that the option expires, so we should be in luck.

I was thinking if I had the expiration date then I could then have your spreadsheet calculate monthly ROI when it does the margin.

Started this thread Reply With Quote
 
(login for full post details)
  #59 (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
That is the strike of the option.

For NG you have this line
stk1 = stk1 * 1000

Other ones have 10 or 100 instead of 1000. Or some don't have that line at all. What determines which multiplier you use?

Started this thread Reply With Quote
 
(login for full post details)
  #60 (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

Here's the code for silver to add to the macro for the spreadsheet in post #45.

ElseIf con1 = "SI" Then
stk1 = stk1 * 100
str1 = "CMXSO SI OOF" & cp1
str1 = str1 & "201" & yr1 & umo1 & " 201" & yr2 & omo1
strSearch = str1 & " " & Application.WorksheetFunction.Text(stk1, "0000000")
strSearch2 = "3 CX-SI"
strSearch3 = "4 CX-SI"
imm2 = 1
v1 = 0.5


Hogs

ElseIf con1 = "LN" Then
stk1 = stk1 * 10
str1 = "CMELN LN OOF" & cp1
str1 = str1 & "201" & yr1 & umo1 & " 201" & yr2 & omo1
strSearch = str1 & " " & Application.WorksheetFunction.Text(stk1, "0000000")
strSearch2 = "3 LN"
strSearch3 = "4 LN"
imm2 = 1
v1 = 0.4


Cocoa

If con1 = "CC" Then
stk1 = stk1 * 100
str1 = "NYBCC CC OOF" & cp1
str1 = str1 & "201" & yr1 & umo1 & " 201" & yr2 & omo1
strSearch = str1 & " " & Application.WorksheetFunction.Text(stk1, "0000000")
strSearch2 = "3 CC"
strSearch3 = "4 CC"
imm2 = 1
v1 = 10


Cocoa needs to be first in front of CL. I added Else in front of the If to start the CL code.

Cotton

ElseIf con1 = "CT" Then
stk1 = stk1 * 100
str1 = "NYBCT CT OOF" & cp1
str1 = str1 & "201" & yr1 & umo1 & " 201" & yr2 & omo1
strSearch = str1 & " " & Application.WorksheetFunction.Text(stk1, "0000000")
strSearch2 = "3 CT"
strSearch3 = "4 CT"
imm2 = 1
v1 = 5


Heating Oil

ElseIf con1 = "HO" Then
stk1 = stk1 * 10000
str1 = "NYMOH HO OOF" & cp1
str1 = str1 & "201" & yr1 & umo1 & " 201" & yr2 & omo1
strSearch = str1 & " " & Application.WorksheetFunction.Text(stk1, "0000000")
strSearch2 = "3 NY-HO"
strSearch3 = "4 NY-HO"
imm2 = 1
v1 = 4.2


RB

ElseIf con1 = "RB" Then
stk1 = stk1 * 10000
str1 = "NYMOB RB OOF" & cp1
str1 = str1 & "201" & yr1 & umo1 & " 201" & yr2 & omo1
strSearch = str1 & " " & Application.WorksheetFunction.Text(stk1, "0000000")
strSearch2 = "3 NY-RB"
strSearch3 = "4 NY-RB"
imm2 = 1
v1 = 4.2


These need to be added in alphabetical order.

Started this thread Reply With Quote
The following 2 users say Thank You to ron99 for this post:
 
(login for full post details)
  #61 (permalink)
 kevinkdog   is a Vendor
 
 
Posts: 3,002 since Jul 2012
Thanks: 1,598 given, 5,963 received


Dudetooth View Post
This one is 97/2003 compatible.

Thanks. I'm guessing there is no easy way with an old version of Excel to automatically go get and unzip the required SPAN files?

Follow me on Twitter Reply With Quote
 
(login for full post details)
  #62 (permalink)
 spj77 
Qld, Australia
 
Experience: Beginner
Platform: Excel/Hoadley/PC-SPAN
Broker: OX
Trading: Options
 
Posts: 28 since Mar 2012
Thanks: 6 given, 44 received

This works fine for me in XL 2003:


Sub DownloadRiskArrayFiles()

On Error GoTo GetOut
Dim RetVal
Dim batchfile As String
Dim path As String
dateit = Range("SpanMacro!B2").Value 'Date in YYYYMMDD format
namecme = "cme." & dateit & ".s.pa2.zip"
namenyb = "nyb." & dateit & ".s.pa2.zip"
path = "C:\Span4\Data"

SaveWebFile "ftp://ftp.cmegroup.com/pub/span/data/cme/" & namecme, path & "\" & namecme
SaveWebFile "ftp://ftp.cmegroup.com/pub/span/data/nyb/" & namenyb, path & "\" & namenyb

Call UnZip(path & "\", path & "\" & namecme)
Call UnZip(path & "\", path & "\" & namenyb)

Kill path & "\" & namecme
Kill path & "\" & namenyb

Kill path & "\cme.s.pa2"
Kill path & "\nyb.s.pa2"
Name path & "\" & "cme." & dateit & ".s.pa2" As path & "\cme.s.pa2"
Name path & "\" & "nyb." & dateit & ".s.pa2" As path & "\nyb.s.pa2"

GetOut:
End Sub

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


spj77 View Post
This works fine for me in XL 2003:


Sub DownloadRiskArrayFiles()

On Error GoTo GetOut
Dim RetVal
Dim batchfile As String
Dim path As String
dateit = Range("SpanMacro!B2").Value 'Date in YYYYMMDD format
namecme = "cme." & dateit & ".s.pa2.zip"
namenyb = "nyb." & dateit & ".s.pa2.zip"
path = "C:\Span4\Data"

SaveWebFile "ftp://ftp.cmegroup.com/pub/span/data/cme/" & namecme, path & "\" & namecme
SaveWebFile "ftp://ftp.cmegroup.com/pub/span/data/nyb/" & namenyb, path & "\" & namenyb

Call UnZip(path & "\", path & "\" & namecme)
Call UnZip(path & "\", path & "\" & namenyb)

Kill path & "\" & namecme
Kill path & "\" & namenyb

Kill path & "\cme.s.pa2"
Kill path & "\nyb.s.pa2"
Name path & "\" & "cme." & dateit & ".s.pa2" As path & "\cme.s.pa2"
Name path & "\" & "nyb." & dateit & ".s.pa2" As path & "\nyb.s.pa2"

GetOut:
End Sub

Thanks
I will give this a try.

Follow me on Twitter Reply With Quote
 
(login for full post details)
  #64 (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

For new traders in this thread, when I get a chance I will make a post and put in the Quick Summary a step by step list of what you have to do to get Dudetooth's system running. It will give you a fast, free! calculator of SPAN margins.

You will need some batch files and the spreadsheet.

Started this thread Reply With Quote
 
(login for full post details)
  #65 (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
For NG you have this line
stk1 = stk1 * 1000

Other ones have 10 or 100 instead of 1000. Or some don't have that line at all. What determines which multiplier you use?

I glean option and futures quotes from Barchart's website, so all of those calculations are made to convert an option strike as it appears on Barchart's site to be compatible with what is seen in the risk arrays.

Reply With Quote
 
(login for full post details)
  #66 (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
Here's the code for silver to add to the macro for the spreadsheet in post #45.

Thanks for the code. I will get this into the next build for sure. I will also look to have DTE calculated and the download ability. I am also thinking I may have the simple spread calculation in it as well (and a 97 version for kevinkdog).

Is there anything else worth looking to add?

Reply With Quote
 
(login for full post details)
  #67 (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 the code. I will get this into the next build for sure. I will also look to have DTE calculated and the download ability. I am also thinking I may have the simple spread calculation in it as well (and a 97 version for kevinkdog).

Is there anything else worth looking to add?

Nothing I can think of right now.

You have been a HUGE help to many of us. Thank you.

Here is how I added my macro buttons. I was going to add them above your stuff but I didn't want to have to redo all the cell references in the macros. This is OK. But if you have a better idea that would be good.

If you don't want all of them on there that is OK. The Download Risk Arrays is the one that is needed with the date cell. I can add mine where needed later.



I left a row below them for titles of columns when I do ROI calculations and other things.

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #68 (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 an update with some recommendations / upgrades built in.

1) Download file (there is an Excel 97/2003 version available) and unzip.

2) Open spreadsheet and follow the instructions on the Setup tab.

New features:
-Setup tab with instructions and ability to change default path
-Ability to download CME and NYB risk arrays (download, unzip, rename)
-DTE based on the date of the downloaded risk arrays
-7 new commodities programmed
-Spreads: simple 2-legged intra-commodity spreads can be calculated

I did some quick tests and everything seems to be working ... please let me know if you find any bugs.

Thanks to ron99 for helping with the new commodity programming.

****Updated: Minor fixes ... already.
I've got the SaveWebFile and Unzip macros in there (missed them first time around) and 97 version has TrailingMinusNumbers issue taken care of. Thanks ron99 and kevinkdog for the troubleshooting I missed.

Attached Files
Register to download File Type: zip Margin Without PC-SPANv2.zip (44.1 KB, 46 views)
Register to download File Type: zip Margin Without PC-SPAN-97v2.zip (39.8 KB, 28 views)
Reply With Quote
The following 5 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #69 (permalink)
 kevinkdog   is a Vendor
 
 
Posts: 3,002 since Jul 2012
Thanks: 1,598 given, 5,963 received

I tried the 97 version, and it seems to work well!

Thanks Dudetooth!


I have a handful of other instruments I want to add, and if/when I am successful I will post the code here (probably by Saturday)...

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

This is a great tool, @Dudetooth ! Thanks for putting it together.


I was running some Lean Hog calcs, and the price seems to be wrong for some of them. Everything else looks OK. Can you check this? I don;t know if it is an error in the SPAN file, but the OEX margin calculator seems to give the right price.

Thanks



Follow me on Twitter Reply With Quote
 
(login for full post details)
  #71 (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


kevinkdog View Post
This is a great tool, @Dudetooth ! Thanks for putting it together.


I was running some Lean Hog calcs, and the price seems to be wrong for some of them. Everything else looks OK. Can you check this? I don;t know if it is an error in the SPAN file, but the OEX margin calculator seems to give the right price.

Thanks



At 87 the price goes to over 1.000 and to 4 digits from less than 1.000 and only 3 digits.

EDIT I noticed that on the Temp worksheet cell A12 has this statement
=IF(U5>1000,5,U5)

That is where the 5 is coming from. I don't know why that is there. Cell U5 does have the correct price.

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #72 (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
I was running some Lean Hog calcs, and the price seems to be wrong for some of them.

You're welcome ... glad it is a help.

Got the issue:
1- Go to the Temp page, cell A12
2- Replace the formula with this one: =IF(U5>9999990,5,U5)

SPAN is odd that when an option is cabinet: they give it a value of 9999999 instead of 0. This is just how the spreadsheet takes care of that. Before Lean Hogs were added the formula =IF(U5>1000,5,U5) would have handled it, but this will work better. I have only tested this on cabinet positions in ES and EC (should work in other currencies) ... not really sure if there are other markets that have cabinet positions.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #73 (permalink)
 kevinkdog   is a Vendor
 
 
Posts: 3,002 since Jul 2012
Thanks: 1,598 given, 5,963 received


Dudetooth View Post
You're welcome ... glad it is a help.

Got the issue:
1- Go to the Temp page, cell A12
2- Replace the formula with this one: =IF(U5>9999990,5,U5)

SPAN is odd that when an option is cabinet: they give it a value of 9999999 instead of 0. This is just how the spreadsheet takes care of that. Before Lean Hogs were added the formula =IF(U5>1000,5,U5) would have handled it, but this will work better. I have only tested this on cabinet positions in ES and EC (should work in other currencies) ... not really sure if there are other markets that have cabinet positions.

Thanks! What is a cabinet position? Does that mean a zero price?

Follow me on Twitter Reply With Quote
 
(login for full post details)
  #74 (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


kevinkdog View Post
Thanks! What is a cabinet position? Does that mean a zero price?

Cabinet is zero at OX but it is half a tick at most other firms. LH, LC, ES have cabinet positions. Probably others.

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #75 (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
Thanks! What is a cabinet position? Does that mean a zero price?

From CME's site:
Cabinet Price
Nominal price for liquidating deep-out-of-the-money options contracts. Defined as the lowest possible tradable price for this option, and is determined within the Clearing System. Trades on options done at a price equal to zero are considered cabinet trades.


On statements I get from RJO they will list positions as cabinet when they get to a point where they are practically worthless. Not sure if they can still be traded at that point.


Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #76 (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

It is possible to trade at cabinet. I have done it.

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #77 (permalink)
 enderqa 
Chicago, IL
 
Experience: Beginner
Platform: RTrader
Trading: CL
 
Posts: 15 since Apr 2013
Thanks: 9 given, 1 received

Dudetooth,

Thanks for developing this software. Can you describe for me your algorithm for finding the Margin in the cme settlement's file, (such as: cme.20131129.s.pa2.zip). I don't use Excel, and I want to write my own Python program that can extract the information myself. (I'll post it here after I'm done so others can use it.)

I know that the file itself, when unzipped is a normal text file. However there are a number of fields per line. From reading the thread it seems that you pull the maintenance margin from the file and then calculate the initial margin using the options price information. Is that correct?

Thanks in advance.

Reply With Quote
 
(login for full post details)
  #78 (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
Can you describe for me your algorithm for finding the Margin in the cme settlement's file, (such as: cme.20131129.s.pa2.zip) ... From reading the thread it seems that you pull the maintenance margin from the file and then calculate the initial margin using the options price information. Is that correct?

I'd gather that you are looking for a step-by-step on how this is done within the spreadsheet. That will take me a little bit to jot down in a narrative form to show you where the data is within risk arrays and explain the process. If you give me some time, I'll see what I can come up with.

Even though you don’t use Excel, if you can look through the spreadsheet and its VBA code some of the process may make sense with your ability to program in Python.

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


enderqa View Post
From reading the thread it seems that you pull the maintenance margin from the file and then calculate the initial margin using the options price information. Is that correct?

He takes the IM & MM and adds the option price to get Total IM & Total MM.

Started this thread Reply With Quote
 
(login for full post details)
  #80 (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 just noticed that PC-SPAN uses the settlement date and not the expiration or last trade date.

For example, LHG4 options expiration or last trade date is 2/14/14. Settlement date is 2/19/14.

So the DTE in our spreadsheet uses the 2/19 not the 2/14.

I also just noticed that QST lists 2/14 as the expiration date but OX lists 2/19. Futures last trading day is 2/14.

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

@Dudetooth -

I was able to add some new instruments: BP, AD, CD. I will post the code once I verify the results tonight.


I tried, but failed, with the following. Could you add them when you get a chance?

Soybean Meal
Soybean Oil
Copper
30 Year US Bonds
10 Year Treasury Notes
Eurodollars


I have found some good trades in those instruments.


THANKS

Follow me on Twitter Reply With Quote
 
(login for full post details)
  #82 (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'd gather that you are looking for a step-by-step on how this is done within the spreadsheet. That will take me a little bit to jot down in a narrative form to show you where the data is within risk arrays and explain the process. If you give me some time, I'll see what I can come up with.

Even though you don’t use Excel, if you can look through the spreadsheet and its VBA code some of the process may make sense with your ability to program in Python.

Wouldn't it be easier to just post your VBA code? Understanding your steps aren't that difficult if you know a little about programming and can inspect the code. I can post it, but don't want to without Dudetooth's permission.

Reply With Quote
 
(login for full post details)
  #83 (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 took a quick look over at CME ... most other products we have in this spreadsheet seem to have the same date for last trade and settlement, even LC. I checked a couple on ICE and the date in the SPAN is what they list as the last trade date. It almost looks like LH is odd man out.

Reply With Quote
 
(login for full post details)
  #84 (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
I was able to add some new instruments: BP, AD, CD. I will post the code once I verify the results tonight.

Great ... looking forward to it.



kevinkdog View Post
I tried, but failed, with the following. Could you add them when you get a chance?

I added it to the "to-do" list for the next update.

Reply With Quote
 
(login for full post details)
  #85 (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
Wouldn't it be easier to just post your VBA code? Understanding your steps aren't that difficult if you know a little about programming and can inspect the code. I can post it, but don't want to without Dudetooth's permission.

That's fine by me, but it seems to me that it would be rather difficult to understand the process without seeing the corresponding data in the spreadsheet.

Reply With Quote
 
(login for full post details)
  #86 (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
That's fine by me, but it seems to me that it would be rather difficult to understand the process without seeing the corresponding data in the spreadsheet.

Yeah, you're probably right. The other option is to download a trial copy of excel and look at the code that way. Assuming he has a windows box to load it on. If he has no windows computers, then that's a non-starter.

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

I have been playing around with currencies in the SPAN spreadsheet. Here is what I have found:

1. In the spreadsheet, the delta calculations for Euro (6E) and Yen (6J) are double what they actually are. This can be corrected easily in the code (by changing code to d1=1). @Dudetooth - can you verify this?

2. For settlement dates, the Options Express Trade Calculator gives the options expiration date as 3 days before the SPAN settlement date. This is true for all currencies I looked at (BP, EC, CD, AD, JY). So, if you are used to calculating ROI based on OEX date, the SPAN spreadsheet ROI will be lower.

For ROI calculations, I believe you want to use options expiration date, not settlement date. Can someone confirm my thinking?


3. I have added the following currencies: BP, AD, CD. Code is below:

If con1 = "A6" Or con1 = "6A" Or con1 = "AD" Then '6A Globex, A6 Barchart
stk1 = stk1 * 1000
str1 = "CMEAD AD OOF" & cp1
str1 = str1 & "201" & yr1 & umo1 & " 201" & yr2 & omo1
strSearch = str1 & " " & Application.WorksheetFunction.Text(stk1, "0000000")
strSearch2 = "3 AD"
strSearch3 = "4 AD"
strSearch4 = "B CMEAD OOF201" & yr1 & umo1 & " 201" & yr2 & omo1
imm2 = 2
v1 = 1#
d1 = 1
'CMEBP BP; CMEC1 C1; CMEAD AD
ElseIf con1 = "B6" Or con1 = "6B" Or con1 = "BP" Then '6B Globex, B6 Barchart
stk1 = stk1 * 1000
str1 = "CMEBP BP OOF" & cp1
str1 = str1 & "201" & yr1 & umo1 & " 201" & yr2 & omo1
strSearch = str1 & " " & Application.WorksheetFunction.Text(stk1, "0000000")
strSearch2 = "3 BP"
strSearch3 = "4 BP"
strSearch4 = "B CMEBP OOF201" & yr1 & umo1 & " 201" & yr2 & omo1
imm2 = 2
v1 = 0.625
d1 = 1
'CMEBP BP; CMEC1 C1; CMEAD AD
ElseIf con1 = "C6" Or con1 = "6C" Or con1 = "CD" Then '6C Globex, C6 Barchart
stk1 = stk1 * 1000
str1 = "CMEC1 C1 OOF" & cp1
str1 = str1 & "201" & yr1 & umo1 & " 201" & yr2 & omo1
strSearch = str1 & " " & Application.WorksheetFunction.Text(stk1, "0000000")
strSearch2 = "3 CD"
strSearch3 = "4 CD"
strSearch4 = "B CMEC1 OOF201" & yr1 & umo1 & " 201" & yr2 & omo1
imm2 = 2
v1 = 1#
d1 = 1

Follow me on Twitter Reply With Quote
 
(login for full post details)
  #88 (permalink)
 enderqa 
Chicago, IL
 
Experience: Beginner
Platform: RTrader
Trading: CL
 
Posts: 15 since Apr 2013
Thanks: 9 given, 1 received

@Dudetooth,

Maybe we can start small. Here are four lines from the CME settlement's file


Quoting 
354561:81NYMLO CL OOFC201403 201403 000920000622-00657+01282-00048-00002-01304+01982-00808-00575+00000000000470N
354562:82NYMLO CL OOFC201403 201403 000920001887+02719-01618-01108+02406+02458-01270+06196+002043440000470++06225+C
354563:81NYMLO CL OOFP201403 201403 000920000622-00657+00232-01002+01052-00254+00118+01292+01525-00000000000242N
354564:82NYMLO CL OOFP201403 201403 000920000213-00431+01532+02042-00744-00661+01848-03784-002043440000242++03775-C

I believe these are Crude Oil options. This is because "NYMLO" and "CL" are symbols CME uses for Crude Oil. Here is what I can surmise from the fields. The fist number, e.g., "354561", is NOT in the file, it is the line number. After the colon ":" the actual file begins.
  1. The first feild is 81NYMLO or 82NYMLO. I don't know the difference between the 81 and 82. But I see it in a number of lines.
  2. The "CL" field probably lists the underlying future
  3. The OOFC201403/OOFP201403 lists "options on futures" and then "C" or "P" for call or put. Then the year and month of the underlying future.
  4. The next date "201403" is probably the expiration of the option, in this case the same as the future.
  5. Then it looks like there is a series of numbers I don't understand. There is a "00092" which I think may the the option's strike price. But then it's all Greek to me.

If you could help me understand the long string of numbers, and let me know how you converted that into the Initial Margin (IM) that would be great. I don't need to calculate the Delta. You have some other fields: price, value, DTE, Risk, etc. I dont' need those either at the moment. Like I said, I want to start small and just get the IMs. Then I can look to see how easy or hard it is too add other things.

Lastly, I did download Dudetooth's Excel Macro (xlsm) file. I can load it in LibreOffice but I can't see the underlying "guts" of the program. (I do have a Window's box, but I don't feel like trying to setup Excel just to do one thing).

As for posting the source code. I would recommend you use pastebin: Pastebin.com - #1 paste tool since 2002!. You post the code there. The Web site give you a URL, and then post the URL/link here in the forum. This way the forum isn't cluttered with code.

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

enderqa, Here is what is in the file.

https://www.cmegroup.com/confluence/display/pubspan/Type+8+Records+-+Standard

Started this thread Reply With Quote
 
(login for full post details)
  #90 (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



FYI, when I open a .pa2 file in a text editor, it appears to be the "expanded" version of the file layout. Here is the file structure for the expanded .pa2 file:

https://www.cmegroup.com/confluence/display/pubspan/Type+8+-+Expanded

The first data record is "81". If you scroll down, you'll see where record type "82" begins.

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

@Dudetooth -

Is there a way to enhance the error tracking for cases where the option is listed but has no open interest? Here is an example - the 3rd one in the list has no open interest (the first 2 have some open interest):

HOF4 HOF4 P 2.49 -1
HOF4 HOF4 P 2.5 -1
HOF4 HOF4 P 2.51 -1



The problem is once the macro stops, if you try running "portfolio" again, no results are given. You actually have to exit the spreadsheet, and start over. I'm guessing the first time through it is writing something "bad" to the temp sheet, and then when you re-run, no results are obtained.

Thanks!

Follow me on Twitter Reply With Quote
 
(login for full post details)
  #92 (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


kevinkdog View Post
@Dudetooth -

Is there a way to enhance the error tracking for cases where the option is listed but has no open interest? Here is an example - the 3rd one in the list has no open interest (the first 2 have some open interest):

HOF4 HOF4 P 2.49 -1
HOF4 HOF4 P 2.5 -1
HOF4 HOF4 P 2.51 -1



The problem is once the macro stops, if you try running "portfolio" again, no results are given. You actually have to exit the spreadsheet, and start over. I'm guessing the first time through it is writing something "bad" to the temp sheet, and then when you re-run, no results are obtained.

Thanks!

I was having same problem. Dudetooth sent me this correction that works OK.

To fix the issue you are having with the portfolio sub:
1) Go into the 'PortfolioRisk' sub and delete/hide the 'On Error GoTo GetOut' and the 'GetOut:' lines.
2) Go into the 'CalcRisk' sub and add 'Worksheets(tab1).Select' right under the 'GetOut:' line.

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #93 (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
I have been playing around with currencies in the SPAN spreadsheet. Here is what I have found:

1. In the spreadsheet, the delta calculations for Euro (6E) and Yen (6J) are double what they actually are. This can be corrected easily in the code (by changing code to d1=1). @Dudetooth - can you verify this?

2. For settlement dates, the Options Express Trade Calculator gives the options expiration date as 3 days before the SPAN settlement date. This is true for all currencies I looked at (BP, EC, CD, AD, JY). So, if you are used to calculating ROI based on OEX date, the SPAN spreadsheet ROI will be lower.

For ROI calculations, I believe you want to use options expiration date, not settlement date. Can someone confirm my thinking?


3. I have added the following currencies: BP, AD, CD. Code is below:

Kevin,

Thanks for the code for the other currencies ... they'll make it to the next build.

I'm not seeing the issue with delta that you were seeing. I haven't added the news currencies to the code yet, so I just calculated a couple EC and JY options with the current code to see what the differences were (I did not change the d1 variable either).

SPAN's results:


Spreadsheet's results:


Sorry, I'm not seeing the discrepancies.

With the settlement dates I was looking at the Feb and Mar options for EC and JY.

From OX Options Quotes:
Euro FX February 2014 Options (66 days to expiration) Expiration Date: 2/7/2014
Euro FX March 2014 Options (94 days to expiration) Expiration Date: 3/7/2014

From CME's Product Calendar:


The Spreadsheet is reflecting the settlement date from the risk arrays. I looked at product calendars for CL, NG, GC, EC, JY, LC, LH on CME and KC and SB on ICE, and the only options I saw that had a last trade date that was different than the settlement was LH, so as far as I know only LH ROI would be affected (there may be others I didn't check yet).

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

Is there a way to enhance the error tracking for cases where the option is listed but has no open interest? Here is an example - the 3rd one in the list has no open interest (the first 2 have some open interest):

HOF4 HOF4 P 2.49 -1
HOF4 HOF4 P 2.5 -1
HOF4 HOF4 P 2.51 -1



The problem is once the macro stops, if you try running "portfolio" again, no results are given. You actually have to exit the spreadsheet, and start over. I'm guessing the first time through it is writing something "bad" to the temp sheet, and then when you re-run, no results are obtained.

Thanks!

I think you would need a different risk array.
S=*Settlement*cycle*–Settle*prices*for*all*products*with*OI*
C= Complete*cycle*–All*settle*price*for*all*products

So you'd want to download the cme.20131203.c.pa2.zip file ... code would need to change from s.pa2 to c.pa2, but that would be fairly easy. That file is about 3x the size of the s.pa2 (~150mb worth of text unzipped) ... not sure how much that will slow things down.

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

@Dudetooth -

Try this one, based on 12/2/2013 settlement data:




this shows delta>100, which can't be.

Follow me on Twitter Reply With Quote
 
(login for full post details)
  #96 (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
@Dudetooth -

Try this one, based on 12/2/2013 settlement data:




this shows delta>100, which can't be.

I know that in-the-money options should be no greater than 100, but PC-SPAN says the same thing as the spreadsheet:


Just saying that I don't think it is the spreadsheet miscalculating ... at least not in this case.

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


Dudetooth View Post
I know that in-the-money options should be no greater than 100, but PC-SPAN says the same thing as the spreadsheet:


Just saying that I don't think it is the spreadsheet miscalculating ... at least not in this case.



Very interesting! I would say that PC SPAN is wrong then, since delta can never be greater than 1.

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


Dudetooth View Post
Kevin,

Thanks for the code for the other currencies ... they'll make it to the next build.

I'm not seeing the issue with delta that you were seeing. I haven't added the news currencies to the code yet, so I just calculated a couple EC and JY options with the current code to see what the differences were (I did not change the d1 variable either).

SPAN's results:


Spreadsheet's results:


Sorry, I'm not seeing the discrepancies.

With the settlement dates I was looking at the Feb and Mar options for EC and JY.

From OX Options Quotes:
Euro FX February 2014 Options (66 days to expiration) Expiration Date: 2/7/2014
Euro FX March 2014 Options (94 days to expiration) Expiration Date: 3/7/2014

From CME's Product Calendar:


The Spreadsheet is reflecting the settlement date from the risk arrays. I looked at product calendars for CL, NG, GC, EC, JY, LC, LH on CME and KC and SB on ICE, and the only options I saw that had a last trade date that was different than the settlement was LH, so as far as I know only LH ROI would be affected (there may be others I didn't check yet).


Yes, you are correct. I had the incorrect date in Cell A1 - that date did not match the risk array date. My bad!

Follow me on Twitter Reply With Quote
 
(login for full post details)
  #99 (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
I'm not sure what is going on then. Here is an example. The DTE in the spreadsheet shows 3 days difference. As of yesterday, the spreadsheet shows DTE=70, when it was actually 67 days to expiration.

The spreadsheet calculated by subtracting the date entered in A1 (the date of the risk file to download) from the settlement date in the risk file. So if you had Friday's date in A1 and did your calculation on Monday the spreadsheet would show 70 days where OX would be adjusting to Monday's date and giving you 67 days.

Perhaps that is what is going on.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #100 (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 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.

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