COT Report Analysis - a thread on market sentiment

Hi guys,

rookie,
ok, thanks, it does make sense how you answered.

Based on what we have learned lately, I also try to plot a possible trade idea. As we (Philip, rookie and me) did not find many trading opportunities based on COT for currencies, I look at another segment.

Nikkei 225 had gained during the whole Asian Session. JPY was beat up in all pairs sinse market open (I actually went short on JPY looking at the gaps and all trades are positive). JPY could not close these gaps. All these brings me to the conclusion to look for short-term long S&P 500 trades today.

Have a nice week and good trading guys!

FE

Hi guys,

as Copper looks like the only interesting Commodity from our COT findings, I found the following article interesting, but it is only for members. If someone (who is obviously registered) could make a print screen and post it here, it would be great.

Chinese Stimulus Fuels Hope for Copper Prices - WSJ - WSJ

BTW, does membership cost money there?

Thanks,
FE

Good morning everybody!

Today, I’m going to show you how I made my database in a few, easy-to-follow steps. You don’t have to be an expert in Microsoft Excel, but some basic knowledge is required.

Template

My template is probably the most important element of my database. Developing it was a pain in the ass, but well worth the time.


I want you to pay attention not to the numbers, but the way the columns are organized. You might experiment with different layouts, but as you soon will see, there is a reason behind my version.

I encourage you to use the same layout as me, at least until you get comfortable using the Excel.

Importing Data

This point, where the element of speed lies. I probably spend about a day to figure out the way to export data from CFTC’s website into my database.

Step 1: Commitments of Traders - CFTC

Step 2: Click on “Historical Compressed”


Step 3: Scroll down until you find either “Futures Only Reports” or “Futures-and-Options Combined Reports”

I prefer to use the latter.


Step 4: Download the Excel format of the years you wish to include in your own database.

Make sure you finished with these steps before moving forward.

Hey guys.
0720.


This is where things gets a little complicated.

Extract the files you downloaded from the CFTC’s website.


It should look like this. This is where the magic comes in. To make sense from all the numbers, you have to know what you are looking for. Let’s assume that we are searching for Silver.

Click on the small Icon that the arrow points at to open a blank sheet.


Go back to the “XLS” sheet.

Press Ctrl+F when you are in the Excel, then type “Silver”. Press “Find Next”.


Voila, we cut through all the c!#p! Hang in there buddy, we are just getting started!

Now, zoom out until you can see the whole year’s data.


Ctrl+C it (you won’t need all the columns, if you copy it to column “T”, you should be fine), then paste it into “Sheet 1”.


What you are staring at is 1 year of data of Silver.

It might seem to be a little complicated, but once you get the hang of it, you should be able to obtain 1 year of data in less than 2 minutes.

Now, open your Template.


Copy

Column A from XLS
to Column A in your Template


Column C from XLS
to Column B in Template


Column H from XLS
to Column C in Template 


Column I + J from XLS
to Column D and E in Template


Column K from XLS
to Column G in Template


Column L + M from XLS
to column H + I in Template 


Column P + Q from XLS
to Column K + L in Template


Hi Balazs,

thanks a lot for your tutorial. It is very valuable, I think I can say it in the name of all of us.

I did the tutorial just like you said. I even chose Silver so I have everywhere the same data as you. We can move further on.

As I do not know if you shared your template earlier, I do share mine what I just made. This way we all have the same, it looks like a perfect copy of your work. WOW. Guys, I cannot upload xls file. If someone has an idea and I share the file.

I have one question until this point:

For currencies the report does look like then for Commodities. For currencies we have commercial, non-commercial and non-reportable categories (like your sheet says) but for commodities we have 1 more group as there is Processor/User, Swap Dealers, Managed Money and Other Reportables. As I see in the excel, there is no difference in commodities and currencies. Still does it not influence the accurate results?

I am waiting for the further steps,

FE

You are probably looking at the Financial Futures. Open up the Futures and Options Combined reports then search for “Yen” for example. You should see the same columns as with Silver for example.


Hi rookie, Mike, Philip, Peter, flows and Balazs,

Lagoonboy raised a very valid question in one of his posts that made me thinking yesterday.

This question was when he asked if he should read the whole thread to get to know the COT Report. Well, as we are getting more and more attention, people start reading our thread in the middle of it and it is almost impossible to read the whole thread and catch up.

For this reason I thought I would take a good example from some other threads here and make a content for our thread. This would mean that I change a bit the very first post in the thread where I inclued a table of content. This is easy to update always as the thread moves forward. Well, now we have 1 700 posts and it is already a big work to read all these posts and choose which were the crucial posts for the COT report. Still, it is easier to do now than when we have 3 000 - 5 000 posts. If we are done, then we can always decide “live” if I should add another post to the table of content later on, that is no problem.

Why do I need you help? If you all help me, we can check about 300 posts/person. This does not take more time than 15-20min/person. If I have to do it alone I will get crazy. If you all contribute it will be easy to do.

What are the important points to make such a table?

  1. You all have to keep in mind to find the important posts, but really the important ones. A table of content for the thread should not have 100 - 150 important posts. I was thinking to have most of 15 posts like that. If in the future we will add extra to it then it will be longer anyway.

  2. Which posts do we need? I think to understand the COT report the following posts were important:

  • my discussion with Peter in the beginning about the COT report. I have to check if there is something I can pick out

  • the Briese and the Williams book description. These books give a summary of the COT report and how it works. Now here the tough part is to post all separated book parts. I though the person who will click through this part of the thread could send me the exact post number where the books are discussed. I will link to the very first post and write in commas all the other post numbers.

  • I started the COT Index discussion project with Philip. He made 1-2 very good explanation how to make the different COT Index steps to set up the indicator. He also does his analysis on COT Index so it would be good to refer to.

  • we definitely have to cover some part of Balazs’s analysis and tutorial. His analysis show for other people what is the goal to reach and where we want to come. His tutorial is also essential to show people how they can make their own database.

Besides these three points I am sure there are at least 5-10 posts which are important. I just remember these three topics that were discussed for a longer period of time in the thread and would be crucial for newcomers.

Okay guys, that is how I see it and how I think we can make it for newcomers clear what COT is about. At this point if someone comes to the thread we have to explain one by one for all people what we are doing here and what is COT report. It does not make much sense to do it like this way.

I ask all 5 of you to give this post a like if you would help me and click through about 300 posts to send find the important posts which you would send me in a private message (do not spam the thread with important post numbers), I can check all those posts. I definitely have to check them as maybe more people would link to similar posts. If I do not get a like, I consider it that you might not have read this post, I send then a private message and if you tell me you are not keen of doing this work, it is ok, then I distribute it between all others. It would be however nice if at least we have 4 out of 6 who is there to make this work. I find it for 3 or less people it too much work. I am sure I can already count on two people (I do not write names) so the question is if everyone participates. [I]I also have to emphasize, as everyone has a lot of things to do, this work does not have a timeline. If you wish to to this with me and some others, just give the like. If you only have time on the weekend, it is ok. If in two weeks, it is also ok. When you do it and give me the post numbers I just add your links to the first page table of content.[/I]

In my first thinking this is how I would distribute the thread:

[B]0-600 posts: Peter and me[/B]: original discussions between us about the books and basic COT information. [I]Also we would do this part as we are basically the two people there from the beginning.[/I]
[B]601-1499: Mike, rookie and Philip[/B]: the books were discussed I think in this part and we made some further development. The COT Index indicator was discussed here which is very important.[I] It is good for you guys to do this part because it is somewhere at that point I think when all 3 of you started working hard on the thread[/I] (although rookie started I think earlier, maybe Mike too)
[B]1501-present: Balazs[/B], as he did some great and complete analysis for COT “products” and he does an important tutorial. [I]Also Balazs would get the last part because he just joined.[/I]

Of course in the above mentioned disdtribution there are not all imporant issues mentioned but I tried to give some first thoughts.

Keep in mind that this kind of work is not only made for others but for ourselves. How often do I read in the thread that we ask the same questions? It is because with the increasing number of posts some crucial posts are lost is impossible to find them.

I am waiting for your answers.

Okay, for those who have gotten this far, it’s time to calculate the Net Positions.


For column F (Circled), type “=D3-E3”. Same goes for each Net Position category. Column J -> =“H3-I3”. Assuming that you have the exact same template as I have.

Once you have the Net Positions typed, simply drag down the formula until the end of the year.


After you completed the step for the three category, calculate the Commercial Net / OI. The formula for column N is J3/C3. We won’t need this calculation directly, but it is necessary, if you want to have the CP/OI Index (Willco)


Drag down the column to have the calculation for the whole year.

Done? Great, you just have your first year in your database! Give yourself a pat on the shoulder :slight_smile:

Now repeat the steps until you have your 4 years of data.

Hi Balazs,

the excel is okay, it works. I just wanted to know how come in one excel these different instruments are all combined although the original COT report does look different for them. At least I work with the Chicago Marcentile Exchange for currencies as it is suggested on the babypips website. And it looks different than the commodities.

As I said, in the excel you provided it is ok, just wanted to understand the thinking behid it.

Keep the tutorial coming when you have the time!

FE

EDIT: during the writing of this post you already made the next step. I start working with it right now.

Think of it as the courtesy of CFTC. Imagine if you had to download the historical data for each instrument one-by-one. They simply put together every instrument being traded on the exchanges and compressed the data so you get the whole package at once.

Hi BB,

ok, thanks I am ready with what you said. You even discussed the basic excel formulas so it should not be difficult to get these points. There is one difference though, for this reason I only made 1 year of data and not 4. In your chart the dates start from the top. I downloaded 2014, but on the top I have the most recent data. This might not be important because we do not care much about the dates but if I put more years in it then it will be a mix. I only know how can I turn a column upside down but do not know how can I do it with the whole excel sheet so all formulas and data will stay on its place and data remains therefore accurate.

Thanks,
FE

PS: maybe a pause would be good so the others also have the time to catch up. Soon if we get to the point where you discuss all different charts then we do not have to ask you all different analysis, only metals. Wow I am already happy for the time when soon we will analyse that way all markets!

No, no, you are actually pointing out a critical point with the report dates. I have 4 years of data because for my COT Index, I use 3 years look back period. I wanted to show you guys how to sort in the correct order the report dates once you have the data period you want.

Ok I will read from post 1199-1499. Now everyone knows which segment I’m reading so we wont do the same work.

One other thing FE, once we give you our recommendations and one of them is put through. Make sure to tell us to rename these posts with the title you gave them in the first page. There were many times when I read a headline, thought it was interesting but couldn’t find the info the headline suggested.

I’ll get 601 - 901 , good idea FE by the way. Oftentimes I do feel the need to re read some of the older posts. And without proper content it was such a hassle so I started taking screenshots.

[B]BB[/B] thank you for showing the whole process step by step. We really do appreciate that! think I’ll get to work on my database. And we’re going to start having some in depth historical data analysis :wink:

By now, your database should look like this:


At this point, I usually make the data visually more pleasing.

Press Ctrl+A, then Center and Align the text to the middle.


You can also experiment with different Font styles and sizes. I prefer Calibri, because it displays the numbers nice and clean.

After you finished, let’s separate the columns from each other.

Highlight column B and C, right click with the mouse then click on “Format Cells”.


Navigate to the “Border” tab, and edit it the way you want to.

Repeat these steps until your database looks like mine.


Before we continue I’d like to address a critical point. You might noticed that the report dates are messed up. What you have to do is highlight your table except for row 1 and 2. Move your mouse to “Sort & Filter” and select “Custom Sort”.


Unfortunately, I cannot attach more pictures so you’ll have to do without them.

Once you are in the Sort tab, apply the following parameters.

Sort by Column B
Sort On Values
Order Oldest to Newest

There, you have it. The data is organized. Now, you can separate the years with a thick line.

As you can see for yourself, it is no biggie to have years of organized COT data.

What we did until now, is paved the way for the various COT related calculations.

What I want you to do now, is to scroll down to the end of 2013, and make a table similar to mine.


This is where stuff gets really complicated. As you can see, we are about to calculate the COT Index for the Commercial and Non-Commercial entities.

Step 1: Calculate the Current Net Position for Non-Commercials and Commercials. I already showed how to do that.


I advise against Ctrl+C - Ctrl+V here. If you copy the Net Positions from Column F and J, the value won’t calculate itself the next time you update the database.

Step 2: Once you finished, navigate under “Maximum Net”. We are going to find the highest Net Position value in the last X years.

Since I’m working with 3 years, let us stick with it.

Type “=MAX(F3:F160)” -> Highest NP in the last 3 years. Drag the formula down.


Step 3: “=MIN(F3:F160)” -> Lowes NP in the last 3 years.


Step 4: Now, for the COT Index. The formula is 100*(Current Net - Min Net)/(Max Net - Min Net)

Click in the cell under COT Index.

Type “=100*(Q160-P160)/(O160-P160)”. You should get 37 as the value. That means that on 2014.01.07. the COT Index was 37%.

Simply drag down the formula to get the Index readings for the rest of the year.


Simple, isn’t it? :wink:

Do the same for the Commercial entities.

Hi Guys, I know you are all very busy with BB’s excellent work and FE’s note task, so just as a little interlude I’ll post something as a little relaxer.

We talked recently about the fall in the S&P, we suggested a few signals that gave us the heads up on that including the USD index and the CRB index. We also noted the widening gap in the Silver and Gold ratio.

Recently we discussed the VIX index as a heads up on a bottom, but there yet another index which has gone unmentioned ( lol, is that groaning I hear?)

Anyways, this is a Risk Sentiment Index, often used by S&P traders. It is not A on A and has a logic in economics so there is no argument about validity.

Suppose you are an investor in the stock market. Like all good portfolios your risk is spread.

You will choose the higher capitalized companies, namely those in the S&P for their lesser risk. You will also want to be exposed to growth potential, more risk, you don’t really have the appetite for emerging markets, you want to keep your investment at home.

You would likely choose some of the companies in the Russell 2000, the small cap (small capitalized) index.

Now if Investor Risk appetite is diminishing, if you become fearful, for any reason, then which side of your portfolio do you liquidate first?

Here is this index in action in July and Sep, it gave a clear warning of the 2 pullbacks in the S&P, and unlike the others it is very precise in timing.

The red line is the S&P, you can see that July 24, the beginning of the first pull back the Russell 2000 had already fallen, likewise on Sep 18.

If it gave a clear heads up at the top, then I wonder will it give a heads up at the bottom.

Russell 2000 Rebound Gains Support From Dollar: Chart of the Day - Bloomberg