What Is the Commitments of Traders (COT) Report?

The Commitment of Traders report is a weekly publication that shows the net long and short positions of various assets (forex, commodities, etc.) of major players in the market. Traders often use the report to help them determine whether they should take short or long positions in their trades. Commitment of Traders report (COT) is published every Friday, around 6:30 PM London time.

If you are monitoring just one product in the report, then it’s easy to access the online version of the report and check the numbers, or copy/paste details into excel spreadsheet every week. 

But, if you are monitoring many items in the report, then the copy and pasting can be very time consuming, and report can be prone to human error. Using excel, you can extract and organize the data on your spreadsheet, so this would become a fully automated COT tool. Below I will guide you step by step how to make such a report for yourself. 

More information about how to use COT report can be found at BabyPips learning school

Or in the YouTube video here

Extracting the report from the web into Excel directly

COT report updated every week, but URL address always stays the same. This will allow us to obtain data directly from the web into the spreadsheet, and this data will try to update every time you open the report or when you click “Refresh” button on the spreadsheet. Any changes in the report, with immediate effect, will be updated on excel as well.
First thing first, and before we step into formulas, we need to extract the data from the web. To do this, please follow these steps:

• Copy URL https://www.cftc.gov/dea/newcot/deacom.txt
• Open a new Excel workbook.
• Go to the Power Query Tab and click on the icon “From Web”. The icon is located on the left of the Data Toolbar.

• In the pop-up window, paste the url into the text box that appear on top of the window and click OK.

• The report is now displayed in within the pop up window.

• Click “Load To” and choose “Existing worksheet:” and click “Load”

• To have this report refreshing every time you open the spreadsheet, click    “DATA” –> “Connections” –> “Properties” –> Tick the box “Refresh data when opening file”

Save the spreadsheet; the first step creating COT tool is done; you now have a live data spreadsheet available. The report will refresh the next time you open the file and pull the latest figure from the web.

COT Historical data sheet

You can now proceed to second step and download historical data from the URL’s

• 1995-2016 – https://www.cftc.gov/files/dea/history/deacom_xls_1995_2016.zip
• 2017 – https://www.cftc.gov/files/dea/history/dea_com_xls_2017.zip
• 2018 – https://www.cftc.gov/files/dea/history/dea_com_xls_2018.zip
• 2019 – https://www.cftc.gov/files/dea/history/dea_com_xls_2019.zip
• 2020 – https://www.cftc.gov/files/dea/history/dea_com_xls_2020.zip

Now create a new tab in your spreadsheet for each currency or commodity pair and create a table frame where your data will be posted. Something like this:

And now copy paste historical data from the spreadsheets you have downloaded previously, starting from most recent (2020) to the oldest. Keep the row 4 empty, because this will be used for live data.

Information you need will be in the columns in the following order:

Your spreadsheet	Historical data spreadsheet
Date	Column C
Non-Commercial Long	Column I
Non-Commercial Short	Column J
Commercial Long	Column L
Commercial Short	Column M

Fill in rest of the cells with the following formulas:

Total	% Long	% Short	Net Positions
=B4+C4	=B4/D4	=C4/D4	=B4-C4

After you finish copying and paste with the data from the historical spreadsheets, your table should look like this:

The final step here is to have formulas in place for live data feed. Row 4 should contain following formulas:

Long	Short
=VLOOKUP($A$1,’COT live Data’!$A:$AB,9,0)	=VLOOKUP($A$1,’COT live Data’!$A:$AB,10,0)

Create COT tool / table using excel formulas

Next step is to create a tool using all this data you have compiled so far. Open a new tab on your spreadsheet and call it COT Tool.

• Create table with the following details:

• Now post all formulas as below:

Non-Commercial (Hedge Funds, Banks & Institutions)
Long	Short	Total	% Long	% Short	Net Positions
=LOOKUP($K$2,EURUSD!$A:$A,EURUSD!B:B)	=LOOKUP($K$2,EURUSD!$A:$A,EURUSD!C:C)	=SUM(C6+D6)	=C6/E6	=D6/E6	=C6-D6

Commercial (Corporations Hedging)
Long	Short	Total	% Long	% Short	Net Positions	Difference	Diff. Prev. Week	Change	% Change
=LOOKUP($K$2,EURUSD!$A:$A,EURUSD!H:H)	=LOOKUP($K$2,EURUSD!$A:$A,EURUSD!I:I)	=SUM(I6+J6)	=I6/K6	=J6/K6	=I6-J6	=H6-N6	=+INDEX(EURUSD!A:N,5,14)	=O6-P6	=Q6/O6

Use cell K2 for date of the report. To make sure you have right date for the report release, you can make a drop down list using data from any of the historical data sheets.

• Enter on cell K2
• Go to Data tab on the Ribbon, then click Data Validation.

• On the Settings tab, in the Allow box, click List.
• Click in the Source box and choose range from historical data sheet (for example EURUSD) data column. I limit range to 20 columns, therefore choose column A, rows 4 to 24.

• Click OK and your drop down list is ready.

We have completed data for EURUSD. Now you need to repeat the same steps for all other FX pairs you are trading. I know it takes some time, but once it’s done, you have an automated tool for COT sentiment.

All you need to do now is once a week to add a new row into each pair tab and copy-paste previous week data from row 4 to row 5. It needs to be done before the new report is released, because after this, row four will update information with most recent data.

To save some time, you can download worksheet I have used to write this guide for COT tool, from here.

Forex trading – Easy money, or hard work?