PC-SPAN

May 25th, 2017, 06:53 PM

TFOpts

A review of the compatibilty with XLS-SPAN is still ongoing. One thing Ron found was that by changing the name from ccl to cme he was able to scan in the data into XLS-SPAN but was not able to use the tracking functionality of the tool.

Small code fix from what I can tell in order to get Track to work with the older PA2 files.

1) Go to Class Modules COption

2) Find:

Public Property Let OptContract(Value As String)
pOptContract = Value
If pCSVPA2 = "CSV" Then
pType8Opt = pOptContract & pOptType & pStrike '"50,201" & Right(pOptContract, 1) & conv2num(Mid(pOptContract, Len(pOptContract) - 1, 1)) & "00," & pOptType & "," & pStrike
Else
pType8Opt = pType8O & pOptType & "201" & Right(pFutContract, 1) & conv2num(Mid(pFutContract, Len(pFutContract) - 1, 1)) _
& " 201" & Right(pOptContract, 1) & conv2num(Mid(pOptContract, Len(pOptContract) - 1, 1)) & " " & pStrike
End If
End Property

3) And replace with:

Public Property Let OptContract(Value As String)
pOptContract = Value
Dim tempdate As String
If pCSVPA2 = "CSV" Then
pType8Opt = pOptContract & pOptType & pStrike '"50,201" & Right(pOptContract, 1) & conv2num(Mid(pOptContract, Len(pOptContract) - 1, 1)) & "00," & pOptType & "," & pStrike
Else
If Left(pDatePA2, 3) & Right(pFutContract, 1) >= Left(pDatePA2, 4) Then
tempdate = Left(pDatePA2, 3) & Right(pFutContract, 1)
Else
tempdate = Left(pDatePA2, 2) & Mid(pDatePA2, 3, 1) + 1 & Right(pFutContract, 1)
End If
pType8Opt = pType8O & pOptType & tempdate & conv2num(Mid(pFutContract, Len(pFutContract) - 1, 1)) _
& " " & tempdate & conv2num(Mid(pOptContract, Len(pOptContract) - 1, 1)) & " " & pStrike
End If
End Property

I'll continue to troubleshoot any other issues that come from these older PA2 files.

May 25th, 2017, 08:08 PM

How will XLS-SPAN know the difference between ESH8 for 2008 and 2018?

May 25th, 2017, 08:45 PM

ron99

How will XLS-SPAN know the difference between ESH8 for 2008 and 2018?

I looks at the year of the PA2 file. With the 20080501.s.pa2 it takes the digit for the decade (0) and combines it with the single digit for the year of the contract (8). That year (08) should be >= the year of the PA2 file or it adds to the decade. That should work when we get 2009 PA2 files with 2010 contract data.

May 26th, 2017, 11:57 AM

@Dudetooth when I make the change above, Track quits working for me even for 2017 contracts. When I change it back to the way it was before the change, it works again for 2017 contracts but not 2008 contracts.

I copied from the post above and pasted it on top of old part. Do I need to compile VBS project? Or just save changes? Are the indents necessary?

May 26th, 2017, 02:05 PM

@ron99,

You should just have to hit save, in either the VBA editor or in Excel itself.

I did a quick check with 20170524's PA2 and it was working for me.

I'm not sure if something gets messed up when copying and pasting the code to the forum, but I uploaded it just to be safe and so you could compare.

May 26th, 2017, 02:27 PM

Dudetooth

@ron99,

You should just have to hit save, in either the VBA editor or in Excel itself.

I did a quick check with 20170524's PA2 and it was working for me.

I'm not sure if something gets messed up when copying and pasting the code to the forum, but I uploaded it just to be safe and so you could compare.

I downloaded this version and then copied and pasted the macro and everything works OK.

I found the problem. When copy and pasting to forum it lost some spaces between quotes.

Your post with macro changes was missing the spaces.

June 7th, 2017, 11:10 PM

@Dudetooth,

Do you know if the SPAN files include margin requirements on the underlying futures (instead of just on options)? If yes, would it required extensive modifications to XLS-SPAN to extract this information?

June 8th, 2017, 02:01 PM

TFOpts

@Dudetooth,

Do you know if the SPAN files include margin requirements on the underlying futures (instead of just on options)? If yes, would it required extensive modifications to XLS-SPAN to extract this information?

XLS-SPAN does give margin for futures. Just put a "F" in the C/P column and nothing in strike column.

June 8th, 2017, 02:37 PM

TFOpts

@Dudetooth,

Do you know if the SPAN files include margin requirements on the underlying futures (instead of just on options)? If yes, would it required extensive modifications to XLS-SPAN to extract this information?

Just to add to ron99's comment, the futures contact have the same 16 risk scenarios that the options do, so futures can be added to a spread with options to calculate margin.

June 11th, 2017, 12:27 PM

@Dudetooth
Sorry, I forgot how to get simple futures price, f.e. CLQ7

PC-SPAN

Discussion in Options

PC-SPAN