Dudetooth 

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?

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

spj77 

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.

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.

ron99 

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.

ron99 

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?

Dudetooth 

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.

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.

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)...

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