Forex Factory (https://www.forexfactory.com/forum.php)
-   Trading Discussion (https://www.forexfactory.com/forumdisplay.php?f=11)
-   -   COT Excel Tool - desegregated data (https://www.forexfactory.com/showthread.php?t=284089)

Hordane Mar 11, 2011 1:28pm | Post# 1

COT Excel Tool - desegregated data
 
1 Attachment(s)
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.
Commitment of Traders (Deseg data v 2.0002a).zip

mrgreen Mar 12, 2011 8:03am | Post# 2

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

CHFJunkie Mar 12, 2011 8:57am | Post# 3

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

ingmarforex Mar 12, 2011 10:05am | Post# 4

Nice
 
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.

Hordane Mar 13, 2011 11:20am | Post# 5

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.

Hordane Mar 13, 2011 11:22am | Post# 6

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.

mrgreen Mar 13, 2011 1:58pm | Post# 7

2 Attachment(s)
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.
Name:  Pic021 2011-03-13, 12_51_45.jpg
Views: 9658
Size:  32 KB
Name:  Pic022 2011-03-13, 12_52_58.jpg
Views: 9452
Size:  10 KB

Hordane Mar 14, 2011 11:30am | Post# 8

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.

CHFJunkie Mar 15, 2011 5:20am | Post# 9

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?

toddanderson Apr 12, 2011 2:49pm | Post# 10

Thank you
 
Hordane,
Thank you for your time and effort



Updated the tool, new file on first post.

mzvega Apr 12, 2011 3:02pm | Post# 11

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.

toddanderson Apr 12, 2011 4:48pm | Post# 12

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

Stockpile Apr 27, 2011 8:10pm | Post# 13

nice job
 
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

argon Aug 17, 2011 7:06am | Post# 14

thanks for great tool
 
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

uncleti88 Feb 21, 2012 11:02pm | Post# 15

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.

Hordane Mar 19, 2012 4:13pm | Post# 16

I've updated the file.

uncleti88 Mar 21, 2012 4:15am | Post# 17

Thank you, will check it out!

Hordane Mar 21, 2012 6:58am | Post# 18

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.

kubio Mar 21, 2012 1:43pm | Post# 19

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.

Hordane Mar 21, 2012 5:34pm | Post# 20

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.


© Forex Factory