Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
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 for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
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.
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:
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).
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
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.
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 = "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.
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!
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"
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.
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?
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.