Commitments of Traders COT Report Forex Analysis Excel
The Commitment of Traders Excel Analysis Tutorial
The Commitment of Traders report, also known as the COT report, is a weekly sentiment indicator that tracks and provides Forex traders with important information on the positioning of currency pairs.
Most importantly, the COT report lets Forex traders know the positions of big players in the markets like hedge funds (leveraged funds). The COT report is issued by the Commodities Futures Trading Commission (CFTC) and it’s used in many profitable trading strategies.
This article will discuss exactly how to use the Commitment of traders report also known as the COT report in Forex and actually how to read the cot report itself. It'll walk you through a professional tutorial for COT analysis when Forex trading so make sure to make some notes. You might also have seen the COT charts, you'll be able to fully understand how they work and make them yourself throughout the tutorial.
Using the COT report analysis is by far one of the best Forex strategies out there to implement just make sure to use it with a fundamental outlook and you're good to go.
One of the best reasons to use the cot report is because it's the only tool that shows transparent fx positions. Transparent fx commitment of traders positions means all market participants that trade under CFTC regulation need to declare their trades including fx trades and their direction.
The commitment of Traders Report - COT Report Analysis in Forex Trading
What you'll learn in this article:
How to read the Commitment of Traders Report using spreadsheets
How to use the COT report in forex trading with a downloadable pdf and excel file
Best COT trading strategy
Using the Commitment of Traders Report Analysis from Logikfx.com
Learn more about trading with market sentiment and fundamentals
How to make your own forex COT report indicator
What is the Commitment of Traders Report (COT Report)?
The Commodity Futures Trading Commission (CFTC) Commitment of traders report (COT report) is a unique underutilised and misunderstood tool used to trade the positioning of traders across markets. It's the best Forex trading strategy to apply. Forex traders specifically use this tool when trading the Forex market. By law traders in the US need to declare their positions and they’re broken down into long or short positions with an overall open interest.
So, basically we know as Forex traders what institutions are doing and how to position ourselves.
Understanding how to read the Commitment of Traders report (COT report) in Forex and using the cot report in your trading is essential when thinking of trade ideas but more importantly timing your trade ideas!
Reports are released every Friday at 3:30ET pm. So, make sure on a weekly basis you’re keeping an eye on the Commitment of Traders report (COT report) positions because it can make or break your strategy. It’s important because it tracks institutional money like hedge funds who are in the markets to make money just like yourself.
There are also other participants in the Commitment of Traders report (COT report) like businesses who use the futures market to hedge exposure to exchange rate fluctuations or raw material prices which are volatile. Overall, it allows traders to gauge the position and sentiment of the market at a specific time which can help filter out good trades and bad trades.
4 Market Participants in the New Commitment of Traders COT report:
Dealer/Intermediary - These include large banks (U.S. and non-U.S.) and dealers in securities, swaps. They design and sell various financial assets to clients. They are not speculators in the markets so forex traders shouldn’t follow their positioning.
Asset Manager/Institutional - These are institutional investors, including pension funds, endowments, insurance companies, mutual funds and those portfolio/investment managers whose clients are predominantly institutional.
Leveraged Funds (Hedge Funds) - These are typically hedge funds and various types of money managers, including registered commodity trading advisors (CTAs); registered commodity pool operators (CPOs) or unregistered funds identified by CFTC. The traders may be engaged in managing and conducting proprietary futures trading and trading on behalf of speculative clients. This is who we’re interested in as forex traders.
Other Reportable - The traders in this category mostly are using markets to hedge business risk, whether that risk is related to foreign exchange, equities or interest rates. This category includes corporate treasuries, central banks, smaller banks, mortgage originators, credit unions and any other reportable traders not assigned to the other three categories. These are hedgers who are not in the market to make money but minimize their own risk of business.
Now that traders know the definitions and what to watch in the COT report. You can decide how to use this information to gauge the sentiment in the forex market. Below is the short format of the new COT report.
Canadian Dollar COT Report Forex:
The short format of the COT report is quite ugly and difficult to understand what’s happening. That’s why we’re going to go through how to create a COT indicator yourself. Don’t worry if you don’t have the time, we have made a COT Report Template for you to use.
Make sure to read the whole post to understand the COT strategy and step by step guide.
Commitment of Traders Report (COT Report Forex) Trading Strategy
Now, that you have an understanding of what the ugly short format of the COT report looks like and the participants involved, we can now move onto how to create a COT indicator for your forex trading strategy. You have 2 options, watch the YouTube video below, or keep scrolling to see the written guide!
Commitment of Traders COT Report Video Tutorial:
This 15 minute video on the COT Report Analysis and step by step guide shows you exactly how to make the COT indicator and start using it for free within your trading.
If you're interested in a COT Report Indicator template or COT Report analysis template we have one ready made below to get you started! You'll still need to understand how to make the indicator but it gives you a head start against others.
How to make a Commitment of Traders COT Report Indicator
The above image is the final outcome of what you'll be able to produce after reading this article. If you want to skip the whole process and get access to the article checkout the COT analysis indicator here at logikfx
Want the excel version and do it yourself every week? The COT spreadsheet is the way to go. We have a code "COT99" to use on the COT report analysis spreadsheet to get up to 99% off the template to get you started.
Firstly, I'd read the step by step guide to understand how to read the COT report before you use the template. Then once you understand it you may not even need it!
Step 1 – Getting the COT report data
The most crucial step is making sure you collect the correct COT report data. Therefore, to make sure you’re getting data for the new COT report and not the legacy data follow these steps carefully. What you want to do is navigate on your browser to the CFTC Commitment of Traders page.
It should look something like this:
Once you’ve made your way onto this page, there’s lots of various links which can help you understand the definitions of the COT report such as the explanatory notes. Don’t worry too much about that right now, we are purely focused on getting the COT report data and making sure it’s right.
What you want to do now is scroll down the page and click on the button that says, “Historical Compressed”.
Once you’re onto the historical compressed page there’s lots of links to various different COT reports. What we want to do is to find the one with traders in it. So, if you scroll down on the page, you’ll find a section called “Traders in Financial Futures; Futures Only Report”. It should look something like this:
Double-check you have the right one because some titles are very similar!
As you can see the data goes back as far as 2006, this is because before 2006 the COT report data was categorized as “Commercial”, “Non-commercial” and “Non-reportable”. Whereas, in the new COT report it’s more detailed and it’s categorized into “Dealer”, “Leveraged Funds”, “Asset Managers” and “Other reportable” positions.
To make sure you collect all the data what you want to do is collect the excel data from 2006-2016 first, then afterwards you will need to download the excel files for 2017,18,19 and 20 to have a fully combined sheet. I’ve provided an image to help you out with the COT report order of downloads.
After you’ve downloaded all the files and combined the data into one file. It should look something like this:
You’ll see various heading in the first row which includes the market participants and then the data below row 1 is all the data specific for those headings. What we’re going to do is focus on the currency “Australian Dollar” as our example in explaining the step by step guide to the COT report. We’ll also be focusing on the “Leveraged Funds” because these are the market participants in the COT report who are trading the market to make money as speculators.
Good, you’ve got the data, but it looks a little bit overwhelming. The next step will help you out as it goes through the filtering process which is very easy as we’re only interested in a couple columns.
Step 2 – Filtering the COT report data
Now that we’ve collected all the data, we need to filter it to make the COT repot indicator. The two columns which are interesting for us and essential for the strategy indicator is:
“Pct_Of_OI_Lev_Money_Long_All” and “Pct_Of_OI_Lev_Money_Long_All” are the two columns we’re interested in.
These should be in or near the columns “AW” and “AX” on you excel sheet.
So, if you go ahead and hide/delete everything except for: “Market_and_Exchange_Names”, “Report_Date_as_MM_DD_YYYY”, “Pct_Of_OI_Lev_Money_Long_All” and “Pct_Of_OI_Lev_Money_Long_All”.
You’ll get something that looks like this which is a lot easier to read.
Now, as I said earlier, we want to filter the data from the cot report and for now focus on the Australian Dollar. How do we do this? Firstly, you want to click on the cell A1 which is the “Market and exchange names” navigate to the “Data” tab at the top and click filter. It should now look something like the image below.
Now a little arrow should appear for all the headings in the cot report excel sheet. This is how you filter data. What we want to do now is find and only select the Australian Dollar in the market and exchange name column (A1). Click the little arrow and un-tick the select all, this now means you’re filtering everything out because you un-ticked it.
Now, scroll down or search for the Australian Dollar in the filter. Once you’ve found it you want to only tick the Australian Dollar. This means you’ve now filtered everything except the Australian Dollar, great stuff.
Once you’ve filtered the data you want to copy all of the data you’ve just filtered and paste it into a new cot report excel sheet. This allows us to separate the main bulk of the cot report data and solely focus on the Australian Dollar. It also means we don’t accidently delete data in the main section if we want to analyze more forex currencies.
You should now have the same data but on a different sheet, I’ve named it “AUD COT Analysis (Filtered)” in the image below.
The next step and the most important step for creating your COT report indicator is calculating what we call the “flip”. The “Flip” is just the difference between the percent of open interest long and percent of open interest short so columns C and D above.
Step 3 – Making the indicator
Now that we have all the cot report data filtered, we want to create the indicator using the “flip”. To do this in the column E we’re going to create a new heading called “Flip”. In the cell below the header we’re going to write the formula “=C2-D2”. This is the formula for the flip. We then drag that formula down to the rest of the data to fill it out.
Now that we’ve calculated the flip for each date, you’ll notice it’s just a bunch of negative or positive numbers. These numbers are important. When the numbers are above 0 it means the hedge, funds are buying the asset whereas when the flip is below 0 the hedge funds are selling the asset.
The final step is to use this flip data and compare it against the asset we want to trade to see if we can create any conclusions or evidence.
What you want to do now is get price data for “AUDUSD” because as Forex traders we want to use the COT report data and trade the currency pair AUDUSD because AUD is the base currency. We need to match this price data against the COT data.
Step 4 – Collecting price data
What we want to do now is to finalize our COT Report Strategy Indicator is to compare the Flip data against price data of the asset your analyzing. In this case we chose Australian Dollar so we’re going to search for Australia Dollar price data to match up against the flip.
To do this we’re going to google Australian Dollar historical price data by investing.com and filter out the same dates from 2006. Double check the excel sheet dates column to make sure you collect the full range of price data. Before you download the data, you want to filter the time frame to “Weekly” on the left of the image below. This will help the data line-up with the cot report weekly schedule.
After you download that data, paste the “price” data of Australian dollar into the next column of the AUD COT sheet. Just like below.
Now, we have all the variables lined up for us to compare COT report data against the AUDUSD forex pair. The next step is to make it visual!
You’re now going to select all the data in Columns B, E and F which is the date the flip and the AUD price data.
Navigate to the “insert” tab at the top and click on the little arrow next to the bar chart
option. From there you want to click “More Column Charts” as seen in the image below.
You should get the following popup.
Here you want to click on the “Combo” chart type in the bottom left and on the “Flip” series name you want to tick the box where it says Secondary Axis. This will plot the flip data on the right vertical axis and AUD price data will stay on the left.
Now the chart is very digestible and we’re able to compare the COT report data against the AUDUSD currency pair.
I’ve given the data a title of AUD COT report vs. AUD/USD. This helps me understand what the chart is about. I’ve then included red lines to show where the flip goes below 0 and green lines to show where the flip goes above 0.
We’ve now completed our COT report indicator strategy! What we can tell from the data is that when the flip goes from positive to negative as seen in the red lines the price data also seems to follow the same direction (seen in the red arrow). When the flip goes from a negative number to positive (green line) we see the AUDUSD exchange rate follow (green arrow). This creates very high probability confirmations on trade ideas that you generate through fundamental analysis.
The cot report flip has been a great confirmation signal on various trade ideas we’ve generated so it will be invaluable for you as a forex trader moving forwards. You can see the data and compare it for yourself. Always make sure to use fundamentals in your trade idea as this is the idea generation used in hedge funds.
If for example your fundamental analysis is signalling a long. You want the COT indicator to go from negative to positive and vice versa for short ideas. This can save you from making poor trading decisions when the market is against you. It also helps you better assess whether you’re making a correct trading decision or not. Make sure to implement this in your trade analysis, if you’re still not sure on how to do this, we made the COT Report Strategy Indicator spreadsheet available for you here.
Some of you may also be interested in our fundamental analysis approach to the markets as it’s a key driver for the COT analysis discussed above.
If you want to learn professional fundamental analysis, we’re currently running an online free webinar covering the fundamental forex strategy, book your spot here.
Keeping the COT report up to date
Now that you’ve gathered all the data, which is up to date for the COT report, we need to remember that new data is released every week. That means to keep on top of our trading you need to make sure to update your spreadsheet on a weekly basis. The schedule for updating the COT indicator manually is every weekend, as long as you update it on Saturday or Sunday you should be fine to continue your analysis.
All our the Logikfx Academy members get full access to our COT indicator which is updated automatically on a weekly basis on top of all the other benefits. But we’re showing you exactly how to update it for yourself if you want to do it for free! Currently, your spreadsheet should look something like this with everything completed.
There are two main things we need to update which are the COT report data itself and the price data of the asset you’re analyzing. So, following our example we need to keep the COT report data up to date and then make sure the price data lines up too.
The first step to updating the COT report indicator is to head back towards our “historical compressed” page mentioned in the earlier steps. On this page we want to click the most recent date which from today is “2020”, this will gather all the data from 2020.
Once you download the excel file it should look something like this:
You may need to download software to extract the data such as Winrar or 7zip. After you open up the file it will look like the other COT report sheets we’ve been modifying.
What we want to do is gather only the most recent data. For example, the date today is 27/06/2020 so we want this week’s data to paste into our excel sheet. To do this we want to highlight all the data by press “CTRL+A” on your keyboard and then navigate towards the “Data” tab on the top.
Your data should highlight in grey when you select it all. Once you’re in the data tab you want to click the filter button as seen in the image above in red. This will create filters for each of the headings, so you’ll now see a small downwards triangle next to each header.
Since we’ve already collected most the data for the COT report we only want the most recent data to keep it up to date. To do that we head towards column C which is the “Report_Date_as_MM_DD_YYYY” cell. We then click the small triangle to start filtering the dates we want.
After you’ve clicked the small triangle it should look something like this:
What we want to do is un-tick the all the “(Select All)” box. This will deselect all the data. Since todays date is the 27th of June 2020, we want to only get data that is in June this week. To do that we will click the small “+” icon next to June, it should look something like this below:
As you can see from the image there are different numbers: 02, 09, 16 and 23. These are the days of each of the week. The most recent date for this example was 23/06/2020 so we’ll click 23. What this will do is only get the data from the spreadsheet within the dates 23/06/2020.
After you’ve selected 23, you can now click “OK”. Your data in the excel sheet should now change to something like this.
As you can see all the data has now been filtered for the most recent date. We now want to copy and paste this data into our spreadsheet that included all the COT report data from 2006.
So, we’re going to copy and paste all the filtered values we just did into our old spreadsheet with all the old data. To do that we’re going to highlight all the data in our filtered sheet with the dates 23/06/2020 like in the image above and paste it into our old sheet with data from 2006.
Remember we don’t want to highlight the first row because the old sheet already has the headers.
We now paste that at the bottom of our 2006 data as seen below:
In the red box we now have our new updated weekly data in the 2006-2020 data. We will do this step every week to update the COT report indicator constantly. There’s still one step we need to do to make sure the data here will line up properly. As you can see because I’m from the UK the date is not in the format I want, if you’re in the US it’s okay. But I want the date format to be DD/MM/YYYY therefore we need to highlight column C and click the short date option like below.
This will help filter the COT report date in the same format as our indicator.
The next step is to now filter the headers out that we don’t need. If you remember from previous steps we only need the 4 headers below: “Market_and_Exchange_Names”, “Report_Date_as_MM_DD_YYYY”, “Pct_Of_OI_Lev_Money_Long_All” and “Pct_Of_OI_Lev_Money_Long_All”.
So, to do that we go to the date column in the filtered sheet now and filter the date 23/06/2020.
This is similar to our original step of getting this week’s COT report data, but this time round we’re doing it on the filtered sheet. Now, for our example we want to get the most recent data for Australian Dollar specifically. You may remember to do this we’re now going to filter the “Market and exchange names” column to Australian Dollar like below.
We now have the COT report data for Australian Dollar which is up to date for 23/06/2020 as of writing this post the dates will change so just see how many weeks of the data you need to filter out to make sure it’s chronological.
Now, we should only have one line of data for this week as seen below:
We can now copy and paste this data into our COT indicator sheet!
Now that it’s in the AUDUSD COT data sheet all we need to do is drag the formula for the flip down to this row and get this week’s AUDUSD price to paste into the columns to the right of it. You can just google AUDUSD price today and paste the most recent value into the price cell.
It should now be all up to date!
Now, we just need to update the chart. So firstly, click your COT chart that we made earlier and the following columns should highlight.
You can see there’s a colored box around columns B, E and F. You want to drag each of them down to the most recent data, so it’s included in the chart.
Now the chart includes the most recent data and you’ve just updated the COT indicator, congratulations!
I understand this can be a lot to take in at first but once you start practicing how to update the COT report and how to read the COT report data, you’ll be whizzing through this every week.
Alternatively, we actually have an online “COT indicator” tool which all of our Logikfx Academy students get access to for life. If that’s something you’re interested in I’d watch our free webinar to see if the academy is for you.
I’ve summarized the COT report post into some key points below.
COT Report Forex Key Points:
Shift in flips from either positive to negative or vice versa are the best times to time your trade provided your initial fundamental idea is forecast in that direction too.
If the flip has been positive for a long period of time and you have a long bias fundamentally you may be too late to the trade, so reducing your risk on those positions or waiting for a better time is always a smart play. This applies vice versa.
You never want to be against the market. If the flip of leveraged funds is long and you’re seeking to short. You want to wait for the flip to go negative or else your idea will be against the market and likely get stopped out. This stops you getting in too early.
These hedge funds will use the COT analysis in their own strategy to increase or decrease exposure dependent on “how much” the leveraged fund market agrees with their current portfolio.
These funds are all conducting macro analysis (fundamentals) which you can learn in our webinar.
Save yourself some time and download a COt Report complete template of everything above here!
