NexusFi: Find Your Edge


Home Menu

 





PC-SPAN


Discussion in Options

Updated
      Top Posters
    1. looks_one Dudetooth with 218 posts (213 thanks)
    2. looks_two ron99 with 116 posts (55 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 188,759 views
    2. thumb_up 317 thanks given
    3. group 75 followers
    1. forum 610 posts
    2. attach_file 159 attachments




 
Search this Thread

PC-SPAN

  #11 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
Posts: 266 since Sep 2012
Thanks Given: 30
Thanks Received: 274


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
Thanked by:

Can you help answer these questions
from other members on NexusFi?
ZombieSqueeze
Platforms and Indicators
The space time continuum and the dynamics of a financial …
Emini and Emicro Index
NT7 Indicator Script Troubleshooting - Camarilla Pivots
NinjaTrader
Better Renko Gaps
The Elite Circle
Are there any eval firms that allow you to sink to your …
Traders Hideout
 
Best Threads (Most Thanked)
in the last 7 days on NexusFi
Get funded firms 2023/2024 - Any recommendations or word …
65 thanks
Funded Trader platforms
41 thanks
Battlestations: Show us your trading desks!
29 thanks
NexusFi site changelog and issues/problem reporting
23 thanks
The Program
19 thanks

  #12 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
Posts: 266 since Sep 2012
Thanks Given: 30
Thanks Received: 274

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
Thanked by:
  #13 (permalink)
 ron99 
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
Posts: 3,081 since Jul 2011
Thanks Given: 980
Thanks Received: 5,785



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:	579
Size:	190.8 KB
ID:	128442  
Started this thread Reply With Quote
  #14 (permalink)
 ron99 
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
Posts: 3,081 since Jul 2011
Thanks Given: 980
Thanks Received: 5,785

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
Thanked by:
  #15 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
Posts: 266 since Sep 2012
Thanks Given: 30
Thanks Received: 274


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
Thanked by:
  #16 (permalink)
 ron99 
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
Posts: 3,081 since Jul 2011
Thanks Given: 980
Thanks Received: 5,785

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

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
Thanked by:
  #18 (permalink)
 ron99 
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
Posts: 3,081 since Jul 2011
Thanks Given: 980
Thanks Received: 5,785

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


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
Elite Membership required to download: VBA Run Batch- wait until done before continue.txt
Reply With Quote
  #20 (permalink)
 ron99 
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
Posts: 3,081 since Jul 2011
Thanks Given: 980
Thanks Received: 5,785


I'll just not call my format sub. Not that big of a deal.

Started this thread Reply With Quote





Last Updated on September 23, 2021


© 2024 NexusFi™, s.a., All Rights Reserved.
Av Ricardo J. Alfaro, Century Tower, Panama City, Panama, Ph: +507 833-9432 (Panama and Intl), +1 888-312-3001 (USA and Canada)
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.
About Us - Contact Us - Site Rules, Acceptable Use, and Terms and Conditions - Privacy Policy - Downloads - Top
no new posts