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)
I am not sure whether vba just skips when there is error because i do not get the prompt that the file exists, it just gors strUght to m5 unable to download
Can you help answer these questions from other members on NexusFi?
It looks as though Office 64-bit has issues with some functions. The Lib "urlmon" may need to have "PtrSafe" added to the beginning and the Long variables as LongPtr to safely run in 64-bit Office. I don't currently have the 64-bit version of Office, so I am not sure if this will solve the issue.
Try going into the Download module at the top under "Option Explicit" and replacing:
Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long
with this:
Private Declare PtrSafe Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As LongPtr, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As LongPtr, _
ByVal lpfnCB As LongPtr) As LongPtr
When I run, another part of the code sprung an error
Got a mismatch error here.
Private Function SaveWebFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then SaveWebFile = True
End Function
It looks like there are a limited number of areas where you may encounter errors running VBA in Office 64-bit according to the article above. It would seem as though if you change the declarations that you should be OK.
I'm very impressed by your XLS-Span tool, you did amazing work; thank you so much for taking the time do build this for us.
I have a question related to spread margins. Is this something you calculate in the tool (I couldn't figure out how you got the numbers)? Or is it something you get from the CME data? I couldn't find good references on how to calculate it online and was hoping for your guidance on that.
Thanks again for the excellent tool; and thanks in advance for your help.
Can you think of a way to extract a few variables for each option that would allow us to calculate spread margins across options?
Your documentation says the following about calculating spread margins: [Position IM for all legs= Net * (Initial to Maintenance Ratio * Max(Risk Scenarios 1-16, Short Option Minimum)); Initial to Maintenance Ratio extracted from PA2].
If I understand correctly, the formula in the VBA code is:
'*********** Short option minimum added together for spreads
dicSprd.Item("PosSOM") = dicSprd.Item("PosSOM") + clsOpt.PosShortMin
'*********** Write all risk scenarios to array and find max scenario/SOM to define total risk
If iArrPos = totOpt Then
vArrayS = dicSprd.items()
Risk2 = Application.WorksheetFunction.Max(vArrayS)
If Risk2 = 0 Then Risk2 = 1
dicFut(i)("SprdRisk") = Risk2
End If
End If
To make this work I think we would have to extract the initial maintenance ratio, R1-16 and the PosSOM for each option. With that information we should be able to calculate the spread IM in Excel fairly easily. Would you agree?
I guess I'm not tracking what you are looking to do. It seems as though you are asking how to get XLS-SPAN to calculate spread IM ... It already does that (extracting all the needed info for each option from the pa2 files). Am I misunderstand?
I'm looking to back test different strategies more quickly. So far I've exported complete ES put data from XLS-Span to a database. I'm now querying the database to back test different strategies.
The problem is I can only get option level data (the standard output from the "Scan" capability of XLS-Span). To allow me to quickly test spread strategies, I would need to load enough information in the database to be able to independently calculate the spread margin.
So that's why I'm wondering if it's possible to export the data necessary to calculate spread margins at the option level. That way I can combine any group of options and get the spread IM requirement with database queries instead of relying on XLS-Span to do the calculation.
