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 doubt that a 0.25% increase will affect the markets much. I doubt that the Fed will do anything at the Jan meeting. So the March meeting will be the next time the interest rate may be changed.
The energy stocks are what's pulling down the ES. I don't see that changing anytime soon.
Since 2004, ES on the quarterly future expiration day has been down from 8:30 am ET to closing, 77% of the time. 33 out of 43 times.
But the Dec expiration has been up the last 3 times and up 4 out of the last 5 times. So I didn't trade that today. But so far today it looks like it will be down.
Attached is a spreadsheet with the data. @MGBRoadster made the spreadsheet. I removed 2008 data because that was not normal times.
I know this is a lot to ask but do you have a breakdown on how you do your back testing? The process, algorithms, examples, etc. I get a lot of value from your information but would like to run some tests on my own and help out with a second set of eyes. Unfortunately the automation you're using is just very difficult to figure out on my own.
The first thing you will need is this folder on your computer.
C:\Span4\Data
All spreadsheets and files need to be in this file. It must have this name exactly as shown.
This Excel spreadsheet is 2010 version. It needs to be Macro enabled. When you first open it it will say Security Warning Macros have been disabled. Click on the box that says Enable Content.
The Excel sheet was done by @Dudetooth. It is a fantastic spreadsheet. Amazing work.
The excel attached is his spreadsheet with a few modifications that I have made to it. The PC-SPAN thread has his version of it at the end of the thread.
On the Setup sheet the first thing to do is to go to column D and put an "x" in this column for all commodities you will ever use. This is only for CME products. The ICE products listed will be included. This is so that the CME file size can be reduced dramatically and things will run much faster. Right now only the commodities listed can be run.
There are over 2,000 symbols at the CME. Just about all of us will us less than 20. This will reduce the lines in the file from almost 470,000 to < 100,000.
You will need to download a disk array file (CME & ICE) for each trading day you want to include in your research.
Right now you can put in the date (YYYYMMDD format) in cell J2 on the Setup sheet and then click "Download Risk" right below where you entered the date (cell J5). But you can only do that for the dates that the CME has in the current file.
Some folders at the top and then files below them.
We are downloading the files that looks like this cme.20151202.s.pa2.zip The "s" is the important part. That is the end of the day file.
As you can see the files right now start with 20151130 and go to the current one. Those are the ones you can download using the date and the Download Risk button on the Setup sheet. They will automatically be reduced in size holding only the commodities you checked in column D.
To get files before 20151130 you will need to open the folder with the year you want, 2013 or 2014 or 2015. You will then need to click on the file for each date you want, and unzip it to your C:\Span4\Data folder.
After you are done downloading and unzipping the CME files you can reduce the size of them. On the Setup sheet in cell A25 is a button "Reduce All". This will remove all unneeded lines from the CME files. This will take a while if you have downloaded a lot of dates so don't click this until you won't need the computer for a while.
If you think you will ever need 2013 files I suggest you get all of them now before the end of the year because in the past the CME has removed old years from this ftp page when a new year started. You can just put the zipped files in a separate folder and if you need them you can unzip them into the Data folder.
If you need ICE files they are in the folder named "nyb". They don't need to be reduced in size.
How to use the spreadsheet will be in a following post. Probably on Saturday.
Here my version of the Excel spreadsheet. XLS-Span Ron I thought I had attached it to the prior post but forum software didn't allow it to be attached.
I had to save it in Excel 97-2003 format because the forum software wouldn't let me upload a spreadsheet that was macro-enabled.
If you have a newer version of Excel then immediately after opening it and enabling macros, click Save As and use Save as type: Excel Macro-Enabled Workbook.
This post will explain how to use the tracker sheet and my ES sheet.
On the Tracker sheet in cell G1 is the date that will be used if you click Track Select (cell C1) or Track Spread (cell D1). If you have the risk file for last Friday downloaded and in your C:\Span4\Data folder then put 20151218 in cell G1.
On the Tracker sheet I have listed in row 3 a naked short option ESh6p1650. The Net column has a -1 meaning one short contract. Positive numbers in the net column are for long options.
The number 1 in the S1 column is for grouping strategies. This naked option is in group 1 without any other options.
In row 4 I have the same short option and in row 5 I have two long ESh6p1350 options.
The number 2 in the S1 column for rows 4 & 5 tells the program to combine them together as a spread.
Click on Clear Hist Data in cell N2 to clear the HistData sheet.
If you select cells A3, A4 & A5 and then click Track Select (cell C1) you should get this.
Row 3 shows $1,420 IM for the naked option.
Row 4 shows $637 IM for the spread.
The HistData sheet shows the raw data for what you just calculated. I added columns AI to AP to the original excel so that more calculations could be done to the data.
(Note I have had trouble with getting results on the HistPivot sheet if cell AI1 is blank. That usually happens when I change contracts on the Tracker sheet. If the data on the HistPivot sheet didn't update then check to see if there is a number in cell AI1. If not the put a number in this cell and then on the HistPivot sheet right click on any data that is there now and the left click on Refresh.)
To run historical research on an option or spread
Go to Tracker sheet
Put the starting date (format YYYYMMDD) in cell G1. Use one trading day before you are adding the position. So if you want the position to start on 11/30/15 then put 11/27/15 in cell G1. The reason for this is that when you are looking to add a position today the IM you will have is the IM from the prior trading day.
In cell H1 put the ending date for the research.
Click on Clear Hist Data in cell N2 to clear the HistData sheet.
Left click cells A3 and then hold the left click and drag down to cells A4 & A5 so that all three are selected.
Click Track Historic in cell L1.
You can now watch the program go through each day calculating all of the data. All of this data will be on the HistData sheet.
Go to the HistPivot sheet. It is actually a Pivot Table. It should look like this.
Column A has the dates. Row 2 has the numbers you put in column S1 on the Tracker sheet. Columns B-K have the data for the option that has "1" in column S1 on the Tracker sheet. Columns L-U have the data for the spread that has "2" in column S1 on the Tracker sheet.
The first 6 columns (B-G for number 1) for each S1 number have raw data. The next 4 columns (H-K for number 1) have calculated data. This calculated data is not quite correct if you are using the first date for the day prior to the day you are adding the position. That is why it is best to move it over to the ES sheet.
To move this data to my ES sheet that shows some additional info you would copy the date column from HistPivot and paste it to the first column in each block on the ES sheet. You would then copy the raw data (for number 1 in this example cells C4 to G19) and paste it to cell B5 using Paste Values. Copy raw data in cells L4 to Q19 for number 2 to cell S5.
Put the positions in the green cells in rows 2 & 3 columns C thru F on the ES sheet. The column F that has the number of positions is important so that the MROI can know how many contracts are in the position to correctly calculate fees.
In cell A1 of the ES sheet you should enter your total round turn cost per contract.
In cell B1 you should enter the multiplier for your IM. If you are using IMx3 then the number 3 should be in this cell.
the ES sheet should look like this
The blue cell E5 is the IM used for that block to do the calculations.
The blue cell G6 is the premium used for that block. This is the settlement. If you want you change this to what you actually entered a trade at you can do that here.
There are some hidden columns to make calculating easier. Normally you do not need to see what is there.
Column L or Percent of Beginning is current Premium in dollars divided by beginning premium.
Column M or Pos P/L is profit or loss in dollars since starting the position. It does not include costs.
Column N or Draw Down is Pos P/L divided by (beginning IM times the number in cell B1). A negative number means that the position has lost money since it was started. Positive is a profit. Again these are gross not net numbers. Costs aren't included. I have the lowest number in this column turn to a green background fill.
Column O or Acct Bal for IM is (Current IM + (Pos P/L*-1)) / (Beginning IM * number in cell B1). This percent, if this was the only position you had on and you fully invested in this position, would be how much of your account was now being used by IM. When this number goes over 110% you are on margin call. (You don't go on margin call until the maintenance margin is gone. Maintenance margin is 10% less than IM). I have the highest number in this column turn to a green background fill.
The blocks on the ES sheet can be copied and pasted to other areas of this sheet or to other sheets. If you need more lines per block then insert more lines and then copy down all cells from the last line of the block into the new added lines. This should keep the cell highlighting correct.
Thank you very much sir. I do have a follow-up. I ran you process but modified the dates to a different range, 11/4 - 12/21 but the pivot table didn't seem to like it. Below is the screenshot
For whatever reason on after 11/20 the dated entries jump to 12/18. Is there a step that I'm missing? The histroical data tab seems to be missing those dates as well.
Do you have the data files in your C:\Span4\Data folder for the missing dates?
You should be getting this.
Does you data folder look like this?
Another way to check is to put, for example, 20151123 in cell G1, select the option in column A and click Track Select. Do you get a result on the Tracker sheet?