COT Excel Tool - desegregated data
I'm going to post this tool I updated for those interested in the COT data. As you may or may not know, CTFC now desegregates Swap dealers from COT data. Swaps have become so large that they actually skew what is occurring, not all data services use the new desegregated data but the old data format with the swaps included.
The tool is simple, it requires Excel 2003 or greater. I rewrote most of this on 2007 and can't test on 2003. The data is preloaded for the Aussie, Cable, Loonie, Fiber, Yen, Franc, Dollar Index. Instructions on how to add the new week's data is included on the first page. Charts are in Yellow tabs, raw data in red.
The Tool only works with the new desegregated data and comes preloaded with deseg'd data back to 06 for the currencies listed. The tool does not work with the Legacy format and I don't plan on making one that will work with legacy format.
The benefit of this tool is I designed it to give you the desegregated data charts along with the COT Index charts. The data range for the chart can be quickly and easily changed by pressing the button. You can also lock the range. This is beneficial because you lock the range, expand data time, mark the extremes, and then you can look at lesser data window for when extremes occur.
Base work is off an abandoned project from 06', I have updated, rewrote most of the code and expanded the functions.
I would love to hear some feedback if this was helpful or other useful features to add on later versions. The raw data is unlocked, you can add any other charts, pivot tables, or other reports you want.
I think the instructions for operating are straight forward, if you need any help let me know.
Here's an example of the data with the OI added.
Update 3/13/11 (2.0001a): I updated the issue with date not properly copying. Was a minor bug in macro rewrite, sorry about that.
Update 3/14/11 (2.0001b): With the change of charting, they did not update to encompass the new data. Added update macro to fix that.
Update 3/19/12 (2.0002a): Updated the file with all the year's data. Fixed a few macro bugs and added 2,5,10 year treasury notes and charts, including a combined chart between commercials and speculators on treasuries. Added picture example of data.
Update 3/21/12 (2.002a): Please redownload the file. Fixed a minor issue with treasury import and you must have the new file for it to work correctly.
***I will keep this post updated with any changes and the latest file.
Commitment of Traders (Deseg data v 2.0002a).zip
Thank you for posting. I have wanted something like this for sometime now and really appreciate it.
I executed the "Click Here to Run COT Update" after downloading the COT data from http://www.cftc.gov/dea/newcot/FinFutWk.txt and naming COT110308.csv
The Date did not update to 03/8/2011 and appears as below in each worksheet.
Dollar Index rows 7,8,9,10
3/1/2011 10299 -276 79 79
3/1/2011 10575 174 80 80
2/22/2011 10401 2264 79 79
2/15/2011 8137 -85 72 72
Import Tab Row 2,3,4
CANADIAN DOLLAR - CHICAGO MERCANTILE EXCHANGE 110308 3/8/2011 90741 CME
SWISS FRANC - CHICAGO MERCANTILE EXCHANGE 110308 3/8/2011 92741 CME
BRITISH POUND STERLING - CHICAGO MERCANTILE EXCHANGE 110308 3/8/2011 96742 CME
Using Excel 2007
thanks for the tool.
really appreciate it.
do u know when they are going to discontue issuing the old format?
also I appreciate it if u describe in details the diff categories in the new format
Verry nice job you have done i have a question if it is possible?
If you can incorparate the volatility in it aswell otherwise pm if you want i am working on some stuff aswell.
For financials, the new Commercial category contains: 1) the dealer intermediary category are your commercial hedgers and market makers; 2) the Asset Managers/Institutional Traders, from what I can tell, these are the commercials engaged in trading the currency the same as speculators trade (example, 59.39% of these traders are long EU as of last week, where 59.92% of speculators are long Eu too). For this tool currently, Dealer Intermediary and Asset managers are combined for total Commercials (see my thoughts below).
Speculators are divided into leveraged funds (your hedge funds that are using leverage to trade) and other reportable (all other schedule 40 required reporting). These are the speculators trying to make money from the market.
Non reportable are the retail traders (us) and has not changed from the previous format.
With the new detailed data we can see both the hedgers and where they are but we also get to see where non-commercial hedgers are placing trades for the currency. I'm working on another version that will help show any relationship between the commercial traders and speculators.
Here are the snaps. The 'Select Data Source/Edit - and changing to cell $A$7 does resolve.
EDIT: - If select the Change Period button - then OK - it updates. adn the Select Data window reflects new date - and the Chart updates.
Thanks for sharing your hard work with us.
I do appreciate it.
and to sum it up,
from the new format, what can be categorized as commercials ,non commercials aka large speculators? espc. if we compare cot indx readings from both data? which categories will give same cot indx readings or at at least with minimal deviation?
Thank you for your time and effort
Great Tool! Much appreciated.
Plotting open interest
Have you given thought to plotting open interest on the graf, chart
to see if open interest is following commercial contract or specs
On the "Start Here" page, unless it's obvious how does one update more then one individual week using the CSV format from CFTC.
I see how it works using the hyperlink for the current week as long as you stay current.
Are there individual weeks in CSV format available for download with CFTC, I only see the entire years data in CVS?
The historical download deletes exsiting data so that's not an option.
Also I've noticed the layout for disaggregated commodity's differs from the financials. The 3 main groups producer/money positions/other reportable
appear to be single cells as opposed to the financials.
So I'm assuming I'll have to change the macros.
Thanks in advance
and I appreciate you making this public
thanks for great tool
can you help me please ?
Just found out bout this thread, I am using COT legacy reports and have been incorporating the desegregated report as well, will take a look at the file you have posted.
I've updated the file.
Thank you, will check it out!
I believe the new file resolves the issues in earlier post. The tool version has been changed to be much more friendly and better instructions. Please post here or pm me if you have issues.
I added instructions for adding your own currency to the update. If you cannot figure out what those mean please let me know.
Also updated the file to fix import issue with treasury. Please redownload the file if you downloaded the file before this post.
can we presume that this file takes the data right from the source and is most accurate from all (datawise)? asking, because there is slight difference from site to site, i.e. on timingcharts.com the commercial positions are slightly higher as a number compared to this spreadsheet while OI is 100% match (???)...
also, if we say miss to update it once or twice or whatever, will it be there a ''whole'' in the data when finally update it?...
The data comes directly from CFTC where everyone else gets their data. However, mine is going to be intentionally slightly different than what you see most do because of how deseg data works. I took out the swap dealers positions since it inflated/skewed what the real traders in each category was doing.
Swap dealer: An individual who acts as the counterparty in a swap agreement for a fee called a spread. Swap dealers are the market makers for the swap market. The spread represents the difference between the wholesale price for trades and the retail price. Because swap arrangements aren't actively traded, swap dealers allow brokers to standardize swap contracts to some extent.
Basically, these are the spread dealers for all brokers and so their positions don't really matter all that much in determining what the traders are holding, the vol there is enough to soften or spike the data. My charts, imho, are the "true" volume traded and I believe the CFTC backs that position with the categorization in trader types.
If you wanted to include the swap dealers in your data to get the same charts as everyone else then you just have to change the input macro slightly to add that classification into the appropriate category.
© Forex Factory