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.
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.
The following user says Thank You to Dudetooth for this post:
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.
I'll try to troubleshoot your issue and give you a few pointers as well:
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.
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).
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.
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:
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)
.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))
6) Save ... it should work now.
If that doesn't work, or if you have any issues with the fix let me know.
The following 2 users say Thank You to Dudetooth for this post:
Favorite Futures: Selling options on commodities futures
Posts: 8 since Nov 2015
Thanks: 9 given,
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?
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.
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.
The following user says Thank You to Dudetooth for this post:
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.