COT Excel Tool - desegregated data | Forex Factory

Options

Go to
Permalinks
Print Thread

Similar Threads

MT4 to Excel through Excel RTD (Finally!) 612 replies

COT Data Excel linked to MT4 Indicator 1 reply

Is there a tool for transferring MT4 trade history into excel? 13 replies

Spread tool in excel 0 replies

COT data to Excel. 4 replies

Attachments: COT Excel Tool - desegregated data
Exit Attachments

COT Excel Tool - desegregated data

  • #1
  • First Post: Edited Mar 21, 2012 6:14am Mar 11, 2011 12:28pm | Edited Mar 21, 2012 6:14am
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.
http://i40.tinypic.com/54srwp.png

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.
Attached File(s)
File Type: zip Commitment of Traders (Deseg data v 2.0002a).zip   895 KB | 5,893 downloads | Uploaded Mar 21, 2012 6:13am
  • #2
  • Mar 12, 2011 7:03am Mar 12, 2011 7:03am
Hordane:
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
In trading, there is no bullshit. You either make money or you don't.
  • #3
  • Mar 12, 2011 7:57am Mar 12, 2011 7:57am
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
Enter Signature
  • #4
  • Mar 12, 2011 9:05am Mar 12, 2011 9:05am
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.

regards Ingmar.
  • #5
  • Mar 13, 2011 10:20am Mar 13, 2011 10:20am
Quoting CHFJunkie
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
I'm not sure if/when they will discontinue the old format. Several legacy software formats still use it so I doubt it will be any time soon. They are currently in commit period for the new report styling and whether to completely eliminate the old format.

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.
  • #6
  • Mar 13, 2011 10:22am Mar 13, 2011 10:22am
Quoting mrgreen
Hordane:
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...
Updated the tool, new file on first post.
  • #7
  • Edited 1:24pm Mar 13, 2011 12:58pm | Edited 1:24pm
Quoting Hordane
Updated the tool, new file on first post.
Thank You Hordane, We do have the Date reflecting 3/8/11 on the data - but not on the Charts:
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.
Attached Images
In trading, there is no bullshit. You either make money or you don't.
  • #8
  • Mar 14, 2011 10:30am Mar 14, 2011 10:30am
Quoting mrgreen
Thank You Hordane, We do have the Date reflecting 3/8/11 on the data - but not on the Charts:
I changed how charts were drawn and pulled. Added a macro that will automatically update all the chart when importing the new weekly data.
  • #9
  • Mar 15, 2011 4:20am Mar 15, 2011 4:20am
Dear Hordane,

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?
Enter Signature
  • #10
  • Apr 12, 2011 1:49pm Apr 12, 2011 1:49pm
Hordane,
Thank you for your time and effort



Quoting Hordane
Updated the tool, new file on first post.
  • #11
  • Apr 12, 2011 2:02pm Apr 12, 2011 2:02pm
Quoting Hordane
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...

Great Tool! Much appreciated.
Markets are not efficient, rather they are effective - Jones
  • #12
  • Apr 12, 2011 3:48pm Apr 12, 2011 3:48pm
Have you given thought to plotting open interest on the graf, chart
to see if open interest is following commercial contract or specs
  • #13
  • Apr 27, 2011 7:10pm Apr 27, 2011 7:10pm
Hordane

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
  • #14
  • Aug 17, 2011 6:06am Aug 17, 2011 6:06am
Quoting Hordane
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...
but I have problem to download your file

can you help me please ?

cheers
argon
  • #15
  • Feb 21, 2012 10:02pm Feb 21, 2012 10:02pm
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.
  • #16
  • Mar 19, 2012 3:13pm Mar 19, 2012 3:13pm
I've updated the file.
  • #17
  • Mar 21, 2012 3:15am Mar 21, 2012 3:15am
Thank you, will check it out!
  • #18
  • Edited 6:12am Mar 21, 2012 5:58am | Edited 6:12am
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.
  • #19
  • Mar 21, 2012 12:43pm Mar 21, 2012 12:43pm
Quoting Hordane
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.
Top job Hordane, thanks a lot.

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?...
cheers man.
  • #20
  • Mar 21, 2012 4:34pm Mar 21, 2012 4:34pm
Quoting kubio
Top job Hordane, thanks a lot.

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?...
cheers man.
There will be a hole in the data and i'm working to fix that as well but its on the idle burner so to speak.

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.
0 traders viewing now