Readme 2.0- Please read all of it

ADDING DIFFERENT CONTRACTS

1.)First thing you're going to want to do is head over to the cftc.gov and find their COT reports.Once you've decided on the type and years of data you want, download
them as a .csv file.

2.)Assuming you're using excel 2016, head over to the data tab and under "Get & Transform Data" choose "from text/csv" (See step 6)

3.)When the window pops up go to edit.

4.)If you want to edit the data to fit the format provided by the other tables then you're going to have to click on "Choose Columns"

5.)[The following example uses the legacy report][For the other reports see step 4 after selecting the query on the weekly sheet and compare to the "Variable names"
 links on the CFTCs website]
--Unselect any column that has to do with "Old","Other","Concentration","Traders","Contract Units","CFTC Market Code in Initials"
--"CFTC Region Code","CFTC Commodity Code","As of Date in Form YYMMDD" and anything that ends in "Quotes".

-Move the date column to the far left and contract code to the far right

6.)The cftc only has data before 2016 all put together in one csv file, so if you want the most recent years you'll have to download those years indivdually
 	-Fret not though,simply append the queries together.

7.)I recommend that you add the query to a new sheet.

8.) You can use the dropdown menus on the created table to filter for the contracts and years you want to add.

9.)If you want to keep access to the formulas and graphs for the contracts you want; Click on one of worksheets that contain the charts and copy and paste them 
back onto the worksheet that they reference and sort from oldest to newest by date.

10.)click on the worksheet tab and select move or copy and click the check that says "create a copy"

11.)select the RAW DATA (not anything that needs to be calculated) from the now copied worksheet and delete it.

12.)Go back to the worksheet that has the contract you want already filtered and copy and paste it back to the new worksheet in place of the old data.(May require resizing
of the query)

13. For the calculated columns I have converted many of the formulas to values to reduce the size of the file and speed up its open time but the values near
the bottom should still contain formulas so extend those formulas over their respective columns to recalculate.

	-Originally I typed in all of the defined names and cell references by hand but only recently discovered that copying an existing sheet scopes all formulas to
	the created sheet. So if the formula says something like "EUR_DR_N3Y" but you are on a Gold tab then don't worry about it.

14.Rename the tab and table

15.Once you have renamed the tab copy that name and then right click the graphs and select data

15.edit EACH series so that it is equivalent to =Tabname!xxxx if that doesnt work then ="Tabname"!xxxx  (you shouldn't have to edit the xxx part)

16.Create a new sheet to contain those charts and move them.

17.Now go to the weekly tab and click in side the table.The query tab should pop up.Go to edit.Double click "Filtered Rows" in the applied steps pane on the right.

18. Add clause and mirror the entries above. On the right select the contract name you want to add.
 
19.Delete the sheet that contains the data for all contracts

20.Go to the Weekly tab and enter the designer mode on the developer tab.Double click the button underneath the table to view the Copy/paste code.

21.Scroll to the bottom and copy one the preceding blocks of code.

22.Edit the line that looks like this====>[If Worksheets("Weekly").Cells(i, 55).Value = "1170E1" Then] so that .Value="" contains the contract code of the contract
 you have added. Then edit the rest of the code exclududing "weekly" so that it contains the sheet name you have created.
===========================================================================================================================================================


Now you are finished just some things to consider.
--------------------------------------------------------------------------------------
On the Disaggregated and Legacy reports the indices go beyond a 0-100 range(when max and min are calculated the current week is not included in the range).
This was a personal decision and only matters at extremes and will not affect subsequent index values but will affect values on the movement index.

-If you do not like this then go to the formulas tab and select defined names. You will notice that most formulas are made in reference to another so find the original
(generally it should be either the one that refers to OI or dealer/commercial net positions but may not always be the case).

-Find the ones that don't have a count function in them and edit it so that it looks something like the ones in the Traders in Financial Futures reports
 If you want to increase the range that these formulas refer to then alter these values.

=OFFSET(Sugar[@[Producer Net]],-157,0,157,1)<---Original

=OFFSET(Sugar[@[Producer Net]],0,0,-157,1)<-----New

Always remember to refresh the weekly query.If you miss it you may have to go through "some" of the above steps again to get everything in sync

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I like to keep a record of all COT data in a microsoft access database in case there is another contract I want to add.
Simply import the csv files.(Change Contract Code to "Short Text" and the date to match what is actually in the csv doc as access gets it wrong) and delete
the fields you don't want.
Create queries that show data by Contract Code to adjust for changes in the name or exchange.

You can use excel to keep this databse updated by creating a web query that links to the most recent cot data on the cftc's website.
Use access to create a linked table to this excel file and simply copy it and paste(special function on ribbon to paste append) to the database each week.

You can now query this database from excel if you want to add more contracts.

==============================================================================================================================================================
Final note. 

If you have experience in excel and know of a better way to more quickly edit,visualize or analyze the data (for example with power pivot) then please recreate this 
and upload for the rest)




