Can someone tell me why numerous options have gaps in the strike prices listed in PC SPAN? For example, SBM4 Call Options. Above 21, there are only 2 options that show a SPAN entry and SPAN margin. The highest entry is 22 and it has a Delta of 5.82% (as of 3/14). I would think with the way Sugar has been moving there would be a lot of more activity on the Call side. Is it because there is no Open Interest?
On the put side, Span entries seem to correspond to only those strike prices that have Open Interest. Does SPAN only provide data for instruments that have Open Interest?
How do you compute an expected ROI in such cases? Don't you sometimes sell options where you make up the entire OI? I sold 40 EW4H4 1420 Puts and I still make up all the OI. I don't remember how I came up with an expected ROI, but I know I wouldn't have entered it without something to go on. In cases where there is no OI in option/strike combinations near where you want to trade, how do you do the math?
I'm not sure which version you have, and I have been making some minor tweaks on mine since the last release, so I have attached the code I've been using that works in my spreadsheet.
One thing that I noticed is that you have removed any reference to Col A on the Historic tab. Starting at A6 I have the available dates that I have SPAN files for in my C:\Span4\Data folder (skipping weekends, holidays). The code was designed to simply gather those dates from Col A to copy over to Col F.
Hope this helps.
Please register on futures.io to view futures trading content such as post attachment(s), image(s), and screenshot(s).
Yes it helps. As I download risk files my list of previous downloaded dates update. I have not coded it so that the user is informed whether or not the selected date range is within that of the downloaded history. I have started to code a data archive report that would present the user with a date range (with any missing dates within) for the downloaded risk files. I see now that F3 needs to have a date for the changedate code to work. I am working on the idea that:
1. The user needs to know the date range of the risk files downloaded to their nominated directory.
2. That a current list of downloaded dates for say the last 30 days is updated as each download occurrs.
3. That selection of any date range utilises the previous knowledge to inform the user if the selected date range is not represented by the risk files downloaded.
Where am I going with this...
1. I am using the XLS SPAN file within a "Shell" file I have been using to do option analysis.
2. I am interested in time series analysis and charting option prices, volatility and delta for 90 DTE to Expiry.
-Once the dates are in Col A things have been working fine for me. The date in cell C5 is the last date in Col A, giving me the range of dates available. The issue would seem to be getting the initial listing of SPAN files in your C:\Span4\Data folder.
-One possible solution would be to have a macro that could query your C:\Span4\Data folder to grab the name of each SPAN file to populate the dates in Col A. Each time you download a SPAN file the code can add that date to the end of the list.
-The code is currently set so that if there isn't an exact match for the date you entered it will grab the next closest date. That way you don't have to look at a calendar to ensure you aren't trying a weekend.
Function that collects the date range for saved risk files
I have this code in my shell file. When I choose to set a date range for generate historic data, the getRiskFileDates returns the date range for saved risk files.
Set p = Workbooks("OptionAnalysis.xls").Sheets("Parameters")
.LookIn = "M:\Trading\Data\CME SPAN\RiskArrays\"
.SearchSubFolders = False
.Filename = "cme*.pa2"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
numberOfFiles = .FoundFiles.Count
For i = 1 To .FoundFiles.Count
Filename = .FoundFiles(i)
Length = Len(.FoundFiles(i))
dateInFileName = Mid(.FoundFiles(i), 41, 8) '.Filename
If i = 1 Then
startDate = convertDateCodeToDateProper(dateInFileName)
ElseIf i = numberOfFiles Then
endDate = convertDateCodeToDateProper(dateInFileName)
getRiskFileDates = startDate & " - " & endDate
MsgBox "There were no files found."
Selecting track spread allows the user to select the two legs of a spread through input boxes and then calculates the combined result. If there are several spreads in tracker it seems that this manual selection needs to be done for each one for the results of each spread to be updated. Here is some simple code that could automate this process by a single click.
The issue is that the calMargin code in XLS SPAN consistently seems to change the spread number of the last spread in Col F.
May I suggest that some type of automation as follows is added to the file and the issue of the renumbering of the last spread is addressed.
Private Sub cbTrackAllSpreads_Click()
Set t = Workbooks("OptionAnalysis.xls").Sheets("Tracker")
tRow = 3
Do Until t.Range("A" & tRow) = ""
If t.Range("F" & tRow) <> "" Then
If t.Range("F" & tRow) = t.Range("F" & tRow + 1) Then
putrow = tRow
callrow = tRow + 1
Range("A" & putrow & ",A" & callrow).Select
Call CalcMargin(3, 0)
tRow = tRow + 1
tRow = tRow + 1