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,781 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

  #401 (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

Because of the CME's screwy idea of putting the files for December 31st in the following year's folder for 2014 & 2015 (2013 is OK), XLS-SPAN will not download those files.

Started this thread Reply With Quote

Can you help answer these questions
from other members on NexusFi?
The space time continuum and the dynamics of a financial …
Emini and Emicro Index
Exit Strategy
NinjaTrader
Futures True Range Report
The Elite Circle
Deepmoney LLM
Elite Quantitative GenAI/LLM
My NT8 Volume Profile Split by Asian/Euro/Open
NinjaTrader
 
Best Threads (Most Thanked)
in the last 7 days on NexusFi
Get funded firms 2023/2024 - Any recommendations or word …
59 thanks
Funded Trader platforms
37 thanks
NexusFi site changelog and issues/problem reporting
22 thanks
GFIs1 1 DAX trade per day journal
22 thanks
The Program
19 thanks
  #402 (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


ron99 View Post
Because of the CME's screwy idea of putting the files for December 31st in the following year's folder for 2014 & 2015 (2013 is OK), XLS-SPAN will not download those files.

Try this:

Go into the Download module and find the Sub downloadRiskFile(targetDate)
Look down until you see the section 'download risk inside that sub

Replace:
Else:
If SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & namecme, path & "\" & namecme) = True Or _
SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & Left(dateit, 4) & "/" & namecme, path & "\" & namecme) = True Or _
SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & Left(dateit, 4) & "/" & Left(dateit, 6) & "/" & namecme, path & "\" & namecme) = True Then


with:
Else:
Dim dateyear As Double
dateyear = Left(dateit, 4)
If SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & namecme, path & "\" & namecme) = True Or _
SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & dateyear & "/" & namecme, path & "\" & namecme) = True Or _
SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & dateyear + 1 & "/" & namecme, path & "\" & namecme) = True Or _
SaveWebFile("ftp://ftp.cmegroup.com/pub/span/data/cme/" & dateyear & "/" & Left(dateit, 6) & "/" & namecme, path & "\" & namecme) = True Then

Reply With Quote
Thanked by:
  #403 (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


I have found a bug in the latest XLS-SPAN.

If I have an option or spread on Track and click on Select or Spread to get the data for the date in cell A1 and then I click on H-Track to do a Historic Track of the range of dates from the date in A1 to the date in D1, the results on HistData and Hist Pivot do not include the date in cell A1. They start with the next date.

If you are able to fix this, besides posting a fixed XLS-SPAN spreadsheet, please let us know what changes were made to the macros so we can make those changes rather than having to make all of our modifications to your fixed spreadsheet.

Started this thread Reply With Quote
  #404 (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


ron99 View Post
I have found a bug in the latest XLS-SPAN.

If I have an option or spread on Track and click on Select or Spread to get the data for the date in cell A1 and then I click on H-Track to do a Historic Track of the range of dates from the date in A1 to the date in D1, the results on HistData and Hist Pivot do not include the date in cell A1. They start with the next date.

Sorry for the late response.

It's actually not a bug, it was intentional coding. It was designed to prevent days with missing risk array files from being populated in the Hist_Data (i.e. weekends would show up as 3 consecutive days with the same data). It is comparing the DTE before and after it runs the TrackSelect code for that day. If the DTE is the same it is considering the risk arrays to be missing and prevents a rewrite of the data. If the DTE is different then it is new data and it is copied to Hist_Data.

To get all of your dates when running H-Track:
1) Fill in your option or spread on the Track tab and have your start date in A1 and end date in D1 ... just as normal.
2) If you are tracking a spread add a matching number in S1 column for all legs of that spread.
3) Highlight all options you want to historically track (it can be a mix of spreads an single options).
4) Then instead of clicking Select or Spread to get the first day's data, just click H-Track and the first date will be included in the results. H-Track was designed to run without having to prime the pump with the first day's data.

Hope this helps.

Reply With Quote
Thanked by:
  #405 (permalink)
keong
Singapore + Singapore
 
Posts: 13 since Nov 2015
Thanks Given: 10
Thanks Received: 2

Hi Dudetooth,

Fantastic program. I'm in awe of your programming skills and your generosity in sharing this with the forum.

I have a question if you don't mind.

I managed to get the XLS-SPAN (05a) to work.

The Download and Run macro buttons work beautifully as does the Select macro.

1. In the Track worksheet, I input today's date into cells A1 and D1, before running the Select macro, which populates the necessary data, other than the record-keeping columns.

How to do I populate the record-keeping columns (e.g. Entry Date, Entry DTE, etc.) with my existing portfolio (with positions being entered at different dates) and still get the select button to work. Everytime I key directly into the white cells (e.g. column I) before the Entry Date, the program bugs out on me with a "Run-time Error 6: Oveflow". I'm guessing it's because I can only key into the green cells, but I can't seem to find where I can enter these record-keeping data.

I ran the Entry Values macro, and it populates with today's start date. However, it doesn't seem to allow me to enter earlier dates. Is it because I need to download the risk arrays starting from my entry dates until todate?

Question is how can I populate the entry data for my existing portfolio (positions opened at different dates) and get the Select and Entry values macros to work?

2. Secondly, the All macro doesn't seem to work for me. I get a "Compile error: File or sub function not defined:"

Sorry for asking what must seem to be a noob question, but I've spent the past few hours trying to get this to work with no success.


Many thanks!

Cheers,
K

Reply With Quote
  #406 (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

@keong,

I'll try to troubleshoot your issue and give you a few pointers as well:


keong View Post
In the Track worksheet, I input today's date into cells A1 and D1 ...

A1 is the start date and D1 the end date if you are doing multiple dates, like Historic (H-Track or H-Scan) or multiple downloads. If you are just doing a single day Track, Scan or Download you only need a value in A1.


keong View Post
How to do I populate the record-keeping columns (e.g. Entry Date, Entry DTE, etc.) with my existing portfolio (with positions being entered at different dates) and still get the select button to work. Everytime I key directly into the white cells (e.g. column I) before the Entry Date, the program bugs out on me with a "Run-time Error 6: Oveflow". I'm guessing it's because I can only key into the green cells, but I can't seem to find where I can enter these record-keeping data.

The routine Entry Values is using the current date (A1) to calculate and the record that as the first day's data (any column header with Entry in the name). So to set up your portfolio correctly you will need the date you entered the trade in A1, highlight a cell in the row of the option position(s) you entered that day and then click Entry Values. It will crunch the numbers and transfer data to the Entry columns. Typically you only change values in green cells ... you shouldn't have to change anything in the white cells unless the actual price you got for your option is different than the closing price (recorded as Entry Price).


keong View Post
I ran the Entry Values macro, and it populates with today's start date. However, it doesn't seem to allow me to enter earlier dates. Is it because I need to download the risk arrays starting from my entry dates until todate?

Yes. If you put the oldest date you need in A1 and the most recent date in D1 and click Download it will download all of the risk files in that date range.


keong View Post
2. Secondly, the All macro doesn't seem to work for me. I get a "Compile error: File or sub function not defined:"

I had a bit of a coding error there when I adapted that part of the code into this spreadsheet ... you're the first to find this issue. I'm not sure how familiar you are with VBA, so I'll try to walk you through the fix.
1) Press Alt+F11 to open the Marco Editor
2) In the left panel click + next to Modules to see all of them
3) Double-click on the Track module ... that modules code should appear in the main panel to the right
4) Scroll down to find a module with the first line that reads "Sub TrackAll()"
5) Replace all of that code for that module (down until you see "End Sub") with this code:

Sub TrackAll()
Dim rAll As Range, lastrow As Integer
If ActiveSheet.Name = "Track" Then
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Set rAll = Range("A3:A" & lastrow)
rAll.Select

With InfoForm
.Caption = "Track All Options"
.tbInfo.Value = "Tracking All ..."
.Top = Int(((Application.Height / 2) + Application.Top) - (.Height / 2))
.Left = Int(((Application.Width / 2) + Application.Left) - (.Width / 2))
.Show vbModeless
End With
DoEvents

Call TrackSelect

Range("A1").Select

Else: Sheets("Track").Select
End If
End Sub


6) Save ... it should work now.

If that doesn't work, or if you have any issues with the fix let me know.

Reply With Quote
Thanked by:
  #407 (permalink)
keong
Singapore + Singapore
 
Posts: 13 since Nov 2015
Thanks Given: 10
Thanks Received: 2

Hi Dudetooth,

Works like a charm now.

I noticed that the TrackAll windows doesn't close automatically after running the macro, so I added in an additional line into the TrackAllNow sub.

A bit of cosmetics but if I can save an extra click, that's one less click that I have to do...

Cheers and thanks so much for your kind help,
K

Sub trackAllNow(control As IRibbonControl)
With InfoForm
.Caption = "Track All Options"
.tbInfo.Value = "All options in 'Open' accounts being tracked ..."
.Top = Int(((Application.Height / 2) + Application.Top) - (.Height / 2))
.Left = Int(((Application.Width / 2) + Application.Left) - (.Width / 2))
.Show vbModeless
End With
DoEvents

Call TrackAll

With InfoForm
.Caption = "Track All Options"
.tbInfo.Value = "Tracking all 'Open' options complete."
.Top = Int(((Application.Height / 2) + Application.Top) - (.Height / 2))
.Left = Int(((Application.Width / 2) + Application.Left) - (.Width / 2))
.Show vbModeless
End With
DoEvents
Application.OnTime Now + TimeValue("00:00:02"), "killInfoForm"
End Sub

Reply With Quote
Thanked by:
  #408 (permalink)
keong
Singapore + Singapore
 
Posts: 13 since Nov 2015
Thanks Given: 10
Thanks Received: 2

Hi Dudetooth,

Now that I've managed to get the xls working (with your help), I ran the Track worksheet for 2 option positions, both singly and as a spread.

A. The IMs for the short position and the spread is correct (compared with the Zaner platform), however, I find that the long leg seems a little off. For e.g., on 20170113,

-1 OEW3J7 P1890 has an IM of 1102
-1 OEW3J7 P1890 +2 OEW3J7 P1675 has an IM of $297

However, the following long positions have IMs which differ slightly from Zaner platform.

+2 OEW3J7 P1675 has an IM of $205, compared to $200 on the Zaner platform.
+2 OESH7 P2025 has an IM of $570, compared to $0 on Zaner.

So now, I'm a little puzzled as to which is correct? Any thoughts?


B. Secondly, how do we enter in the Entry price (if different from the closing price) and have the Track worksheet calculate the Current ROI% and P/L? Will the macro automatically compute or do we have to manually enter in the formulas ourselves?

Looking forward to hearing from you.


Cheers,
K

Reply With Quote
  #409 (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


keong View Post
However, the following long positions have IMs which differ slightly from Zaner platform.

+2 OEW3J7 P1675 has an IM of $205, compared to $200 on the Zaner platform.
+2 OESH7 P2025 has an IM of $570, compared to $0 on Zaner.

So now, I'm a little puzzled as to which is correct? Any thoughts?

IM for long options is kind of misleading, since there isn't any really. From CME:

For long option value (money you pay) we create a credit in the PC Span calculation and for short option value (money you collect) we create a debit. This way you are getting credit against the margin (span risk) calculation for the money that you have already paid out. Long option value is always greater than the span risk that is calculated because we understand that you cannot lose more than what you have paid for a long option. For a short option we create a debit because we want to make sure that you are keeping the premium for that option in the account until you unwind the position or it expires. In other words, a short option will also have additional margin that has to be posted that is not covered by the premium collected.

Normally, you will see that the IM calculated for a long option will be nearly identical to the value of the option.


keong View Post
B. Secondly, how do we enter in the Entry price (if different from the closing price) and have the Track worksheet calculate the Current ROI% and P/L? Will the macro automatically compute or do we have to manually enter in the formulas ourselves?

If you use the Entry Values macro to populate the Entry price and it is different that what you actually bought/sold the option, just make the change manually in the Entry Value column to reflect your price. From then on, when you use the Track Select/Spread/All it will calculate profit/loss based on that value.

Reply With Quote
Thanked by:
  #410 (permalink)
illinoisrei
Seattle, WA
 
Posts: 4 since Dec 2016
Thanks Given: 2
Thanks Received: 0


Dudetooth,

I echo the praise of many before - amazing work on this tool and some mad VBA skills!!

I've been using Windows Task Scheduler to run a batch file that loads the risk file at the end of the day along with a position file (.pos) covering multiple accounts, exports the results in .xls and sends it out in an email.

This process is automatic but requires PC-SPAN to run.

It looks like it may be possible to open the .pos files in Excel as .xml and perhaps move the position info into your tool and get the summary data that way, sans PC-SPAN.

Does that sound feasible?

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