NexusFi: Find Your Edge


Home Menu

 





PC-SPAN


Discussion in Options

Updated
      Top Posters
    1. looks_one Dudetooth with 218 posts (213 thanks)
    2. looks_two ron99 with 116 posts (55 thanks)
    3. looks_3 CafeGrande with 30 posts (4 thanks)
    4. looks_4 BlueRoo with 24 posts (5 thanks)
      Best Posters
    1. looks_one Dudetooth with 1 thanks per post
    2. looks_two SMCJB with 1 thanks per post
    3. looks_3 ron99 with 0.5 thanks per post
    4. looks_4 BlueRoo with 0.2 thanks per post
    1. trending_up 188,886 views
    2. thumb_up 317 thanks given
    3. group 75 followers
    1. forum 610 posts
    2. attach_file 159 attachments




 
Search this Thread

PC-SPAN

  #451 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
Posts: 266 since Sep 2012
Thanks Given: 30
Thanks Received: 274

Ok, I'm following you now.

SOM and initial to maintenance ratio are both constants, or at least they change very rarely. For ES SOM is $35 and initial to maintenance ratio is 110%.

You would just need the 16 risk scenarios for each option in your database. You could add lines in the WriteResults routine to get R1-R16 to show up on the spreadsheet and it should work out the way you are looking to use it.

Reply With Quote
Thanked by:

Can you help answer these questions
from other members on NexusFi?
ZombieSqueeze
Platforms and Indicators
Better Renko Gaps
The Elite Circle
NexusFi Journal Challenge - April 2024
Feedback and Announcements
Futures True Range Report
The Elite Circle
New Micros: Ultra 10-Year & Ultra T-Bond -- Live Now
Treasury Notes and Bonds
 
  #452 (permalink)
TFOpts
Los Angeles, CA
 
Posts: 64 since May 2017
Thanks Given: 49
Thanks Received: 136

I was able to extract the necessary data and match what XLS-Span is calculating as a spread margin.

If others are interested, the formula for the SPAN initial margin on spreads is:

IMSpread = Init Maint Ratio * MAX(Short Minimum * # of short positions, Weighted R1 through R16)

Where,
  • Initi Maint Ratio is typically 1.1
  • Short Minimum is now $37 but can change
  • Weighted R# is R# scenario result multiplied by -number of positions (so if you sell 2 options and you therefore have -2 positions, you multiply by 2, not -2) for each option and then added together for each R#. Do this for each R# from 1 to 16 and take the maximum of those results or the Short Minimum value * the number of short positions.

An example on the weighted R. Say you are selling 1 option A and buying 2 option B. So number of positions for A is -1 and number of positions for B is +2. R1 value for A is 100 and for B it's 25. Weighted R1 is 100 * -(-1) + 25 * -(2) = 50.

For those interested in the coding changes I made to extract this data, here they are:
In the WriteResults subroutine, define the following variables:
Dim colSOM As Integer, colMaintRatio As Integer, colR1 As Integer, colR2 As Integer, colR3 As Integer, colR4 As Integer
Dim colR5 As Integer, colR6 As Integer, colR7 As Integer, colR8 As Integer, colR9 As Integer, colR10 As Integer
Dim colR11 As Integer, colR12 As Integer, colR13 As Integer, colR14 As Integer, colR15 As Integer, colR16 As Integer
Then add the following to find the columns in which to populate the results. Note that you'll need to include column headers in the appropriate sheet that match the names below. For example, if you want R1, you need to add a column header in the "Scan" tab that says "Risk Scen 1".
'*********** Find column headers for intermediate spread IM, skip if not found
colSOM = getColumn("Short Min")
colMaintRatio = getColumn("Init Maint Ratio")
colR1 = getColumn("Risk Scen 1")
colR2 = getColumn("Risk Scen 2")
colR3 = getColumn("Risk Scen 3")
colR4 = getColumn("Risk Scen 4")
colR5 = getColumn("Risk Scen 5")
colR6 = getColumn("Risk Scen 6")
colR7 = getColumn("Risk Scen 7")
colR8 = getColumn("Risk Scen 8")
colR9 = getColumn("Risk Scen 9")
colR10 = getColumn("Risk Scen 10")
colR11 = getColumn("Risk Scen 11")
colR12 = getColumn("Risk Scen 12")
colR13 = getColumn("Risk Scen 13")
colR14 = getColumn("Risk Scen 14")
colR15 = getColumn("Risk Scen 15")
colR16 = getColumn("Risk Scen 16")
And finally write the results.
'*********** Write spread IM intermediate values
If colSOM > 0 Then Cells(iFillRow, colSOM) = clsOpt.PosShortMin: Cells(iFillRow, colSOM).NumberFormat = "#0.0#"
If colMaintRatio > 0 Then Cells(iFillRow, colMaintRatio) = clsOpt.InitialRatio: Cells(iFillRow, colMaintRatio).NumberFormat = "#0.0#"
If colR1 > 0 Then Cells(iFillRow, colR1) = clsOpt.r1: Cells(iFillRow, colR1).NumberFormat = "#0.0#"
If colR2 > 0 Then Cells(iFillRow, colR2) = clsOpt.R2: Cells(iFillRow, colR2).NumberFormat = "#0.0#"
If colR3 > 0 Then Cells(iFillRow, colR3) = clsOpt.R3: Cells(iFillRow, colR3).NumberFormat = "#0.0#"
If colR4 > 0 Then Cells(iFillRow, colR4) = clsOpt.R4: Cells(iFillRow, colR4).NumberFormat = "#0.0#"
If colR5 > 0 Then Cells(iFillRow, colR5) = clsOpt.R5: Cells(iFillRow, colR5).NumberFormat = "#0.0#"
If colR6 > 0 Then Cells(iFillRow, colR6) = clsOpt.R6: Cells(iFillRow, colR6).NumberFormat = "#0.0#"
If colR7 > 0 Then Cells(iFillRow, colR7) = clsOpt.R7: Cells(iFillRow, colR7).NumberFormat = "#0.0#"
If colR8 > 0 Then Cells(iFillRow, colR8) = clsOpt.R8: Cells(iFillRow, colR8).NumberFormat = "#0.0#"
If colR9 > 0 Then Cells(iFillRow, colR9) = clsOpt.R9: Cells(iFillRow, colR9).NumberFormat = "#0.0#"
If colR10 > 0 Then Cells(iFillRow, colR10) = clsOpt.R10: Cells(iFillRow, colR10).NumberFormat = "#0.0#"
If colR11 > 0 Then Cells(iFillRow, colR11) = clsOpt.R11: Cells(iFillRow, colR11).NumberFormat = "#0.0#"
If colR12 > 0 Then Cells(iFillRow, colR12) = clsOpt.R12: Cells(iFillRow, colR12).NumberFormat = "#0.0#"
If colR13 > 0 Then Cells(iFillRow, colR13) = clsOpt.R13: Cells(iFillRow, colR13).NumberFormat = "#0.0#"
If colR14 > 0 Then Cells(iFillRow, colR14) = clsOpt.R14: Cells(iFillRow, colR14).NumberFormat = "#0.0#"
If colR15 > 0 Then Cells(iFillRow, colR15) = clsOpt.R15: Cells(iFillRow, colR15).NumberFormat = "#0.0#"
If colR16 > 0 Then Cells(iFillRow, colR16) = clsOpt.R16: Cells(iFillRow, colR16).NumberFormat = "#0.0#"
Thanks.

Reply With Quote
Thanked by:
  #453 (permalink)
 ron99 
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
Posts: 3,081 since Jul 2011
Thanks Given: 980
Thanks Received: 5,785


TFOpts, could you attach your XLS-SPAN Excel file with the changes you made? Give it another name other than what Dudetooth gave to his.

This looks like it could be valuable. Thanks

Started this thread Reply With Quote
  #454 (permalink)
TFOpts
Los Angeles, CA
 
Posts: 64 since May 2017
Thanks Given: 49
Thanks Received: 136


ron99 View Post
TFOpts, could you attach your XLS-SPAN Excel file with the changes you made? Give it another name other than what Dudetooth gave to his.

This looks like it could be valuable. Thanks

See attached. My programming skills are not nearly as advanced as Dudetooth but I tried to stay true to the XLS-SPAN architecture. The new fields start in column AC of the "Scan" tab.

Attached Files
Elite Membership required to download: XLS-SPAN (05a)_SprdIM.zip
Reply With Quote
Thanked by:
  #455 (permalink)
TFOpts
Los Angeles, CA
 
Posts: 64 since May 2017
Thanks Given: 49
Thanks Received: 136


ron99 View Post
I don't think your Excel file is there. I doubt it could only be 22 bytes. I get nothing when I try to download it.

@ron99. My mistake, thanks for pointing this out; the attachment has been updated.

Reply With Quote
  #456 (permalink)
TFOpts
Los Angeles, CA
 
Posts: 64 since May 2017
Thanks Given: 49
Thanks Received: 136

@Dudetooth,

Do you know if there is volume data in the CME output used in XLS-SPAN? I'm looking at higher DTE strategies (140+) but I'm not sure how viable these strategies are if there is very little activity in longer term options.

Reply With Quote
  #457 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
Posts: 266 since Sep 2012
Thanks Given: 30
Thanks Received: 274

Negative, pa2 files have no volume info ... Sorry. I'm not sure where you'd find that historically. You may just have to look at current volume for high DTE options to see if it is viable.

Reply With Quote
Thanked by:
  #458 (permalink)
 ItalianBmT 
Java/IN
 
Experience: Beginner
Platform: TOS
Trading: gold
Posts: 13 since Nov 2013
Thanks Given: 2
Thanks Received: 0


Dudetooth View Post
You mat need to change:
Private Function SaveWebFile to Private PtrSafe Function SaveWebFile

or perhaps:
Dim lngRetVal As Long to Dim lngRetVal As LongPtr

I'm afraid I won't be of much help with actually troubleshooting the code, but this may help:
https://msdn.microsoft.com/library/gg264421.aspx

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.

Hope this helps.

Thanks Dudetooth. But unfortunately this doesn't solve the issue even when adding PtrSafe. Thanks anyway and I shan't disturb you further. Hopefully others who are using 64 bit version could chime in if they experience similar instance on their machine?

Thanks.

Reply With Quote
  #459 (permalink)
TFOpts
Los Angeles, CA
 
Posts: 64 since May 2017
Thanks Given: 49
Thanks Received: 136

Additional data from CME was provided for the first trading day of each month from 5/1/08 to 4/1/09. The hope was to understand how different strategies would have behaved during a recession.

The files provided by CME are attached. 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.

There's an active project to add SPAN historical data to Datamine (CME's database service) with an estimated completion date of late 2017.

Note: this is a cross-post with this: . It is more appropriate in this thread.

Attached Files
Elite Membership required to download: ccl.20090202.s.zip
Reply With Quote
Thanked by:
  #460 (permalink)
 ron99 
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
Posts: 3,081 since Jul 2011
Thanks Given: 980
Thanks Received: 5,785



TFOpts View Post
Additional data from CME was provided for the first trading day of each month from 5/1/08 to 4/1/09. The hope was to understand how different strategies would have behaved during a recession.

The files provided by CME are attached. 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.

There's an active project to add SPAN historical data to Datamine (CME's database service) with an estimated completion date of late 2017.

Note: this is a cross-post with this: . It is more appropriate in this thread.

I tested these files with CME's PC-SPAN program. They work with that program.

These files are for CME grains, equity, meats, dairy, & currencies. They do not contain NYMEX (energies) or COMEX (metals) products.

Started this thread Reply With Quote




Last Updated on September 23, 2021


© 2024 NexusFi™, s.a., All Rights Reserved.
Av Ricardo J. Alfaro, Century Tower, Panama City, Panama, Ph: +507 833-9432 (Panama and Intl), +1 888-312-3001 (USA and Canada)
All information is for educational use only and is not investment advice. There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
About Us - Contact Us - Site Rules, Acceptable Use, and Terms and Conditions - Privacy Policy - Downloads - Top
no new posts