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)
Trading: Primarily Energy but also a little Equities, Fixed Income, Metals and Crypto.
Frequency: Many times daily
Duration: Never
Posts: 5,049 since Dec 2013
Thanks Given: 4,384
Thanks Received: 10,206
I've been having the error consistently for several weeks, doesn't seem to effect anything. I wouldn't worry except that I'm finally about to start automating the dozens of portfolio's I'm running and their reports and am worried that the error message will cause the code execution to break. I might try contacting CME but in my past experience their customer service is not good.
Can you help answer these questions from other members on NexusFi?
Try running your "c:\SPAN4\RptModule\spanReport.hta C:\SPAN4\Risk.xml" as a separate batch file and have it placed in the "C:\Span4\RptModule" or any other subfolder within "C:\Span4". It appears as though the spanReport.hta is looking to find the Reports folder in a particular location.
One the error it states: "..//reports/rptBatch.log" which looks sort of like the old DOS command when you would type in "CD.." backing you out one folder level. When I did this it ran the reports and did not generate an error. Even tried placing it in the "C:\Span4\Data" and it worked as well.
I'm using your spreadsheet to do some backtesting - I'm looking up prior option premiums for a certain strike in the past at different dates. It works well by typing in the prior date in the past in A1, and then downloading the data, and then copying down the premium and IM. Then I'd repeat with another date.
I was wondering if there is anyway to put in multiple dates, and have it download and calculate the margin and premium at a certain strike for different dates?
If you can hang on a few more days, I'm ironing out the last few kinks in a new spreadsheet that includes the ability to track an option history. I'm hoping to post it by this weekend at the latest.
Here's a few of the features:
-Added Scanner and Historic tools
-Internalized calculations to eliminate the need of a Temp tab
-Extracted more data from pa2 files, reducing need for data to present in the code and making it easier to add new commodities
-Increased the speed of the code by utilizing arrays to minimize the number of times the pa2 files need to be scanned
-Added the ability to select SPAN or Total IM
-Added futures price, historic volatility and implied volatility to option data extracted
Special thanks to Ron, Kevin and Jay for ideas and troubleshooting to help me make this better.
Beta updates:
-Fixed ROI sort on scanner to include column G
-Fixed contract sort on scanner to include column G
-Fixed contract sort on scanner to sort by futures then option then strike
-Fixed issue with SOM compounding on tracker that was calculating incorrect margin
-Added sort ROI / contract on tracker
-Added erase all results on tracker
-Cell A3 is now selected on historic after changing dates / before running calculations
-Fixed blank rows causing error in calculation
-Changed ROI to 365/DTE/12*net premium/(IM*IM Multiplier)
-Changed instructions on hist tracker added a helpful hints form
-pa2 files are now saved with date in name unless Setup A18 is changed, then back to old way
-Added clear tracker results if no option data in columns A-D
-Cabinet settlement price displayed as 0.5
-Fixed grain price adjustment
-Added more date tools for historic
-Fixed error with option at expiration causing error in calculations
Just clicking the "Thanks" button wasn't adequate to express just how extraordinarily good your Excel SPAN margin calculator sheet is now!! I'm pretty much out of superlatives - I think "Wow" gets close though!
Thank you very much @Dudetooth!! It is appreciated more than you could know!
A minor addition seems to be the source of the problem.
In the Functions Module delete:
'Written: October 07, 2007
'Author: Leith Ross
'Summary: Add Minimize, and Maximize/Restore buttons to a VBA UserForm
Private Const GWL_STYLE As Long = -16
Public Const MIN_BOX As Long = &H20000
Public Const MAX_BOX As Long = &H10000
Const SC_CLOSE As Long = &HF060
Const SC_MAXIMIZE As Long = &HF030
Const SC_MINIMIZE As Long = &HF020
Const SC_RESTORE As Long = &HF120
Private Declare Function GetWindowLong _
Lib "user32.dll" _
Alias "GetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong _
Lib "user32.dll" _
Alias "SetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
'Redraw the Icons on the Window's Title Bar
Private Declare Function DrawMenuBar _
Lib "user32.dll" _
(ByVal hwnd As Long) As Long
'Returns the Window Handle of the Window accepting input
Private Declare Function GetForegroundWindow _
Lib "user32.dll" () As Long
Public Sub AddToForm(ByVal Box_Type As Long)
Dim BitMask As Long
Dim Window_Handle As Long
Dim WindowStyle As Long
Dim Ret As Long
If Box_Type = MIN_BOX Or Box_Type = MAX_BOX Then
Window_Handle = GetForegroundWindow()
WindowStyle = GetWindowLong(Window_Handle, GWL_STYLE)
BitMask = WindowStyle Or Box_Type
Ret = SetWindowLong(Window_Handle, GWL_STYLE, BitMask)
Ret = DrawMenuBar(Window_Handle)
End If
End Sub
View the code of the Help form and delete:
Private Sub UserForm_Activate()
AddToForm MIN_BOX
End Sub
Thanks again! Very impressive. I think adding the volatility measures can help everyone become a better risk manager, if not trader.
One question: Does the PC-SPAN documentation tell you how many trading days are used for the historical volatility (HV) calculation, and does the day count vary across exchanges?
