COT Report Analysis - a thread on market sentiment

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

Hey FE.

Well I went through posts # 615 to #1474. Here are the important ones. I’ll asterisk the ones pertaining to the Williams Book you wrote from. There’s a lot of posts here, but you must pick the ones you think are really important.
615*/659*/707*/834*/ 875/880*/ 901/916/931*/ 933/934/935/948/977/986/989/1012/1013/1043/1044/1097/1098/1173/1197/1207*/1216/1222/1282/1384/1392/1409/1413.
And BB starts on 1474.
Most have to do with COT. Some have to do with intermarket stuff.

Hope this helps you out.
(Man…what a ride down memory lane)

Mike

Hey guys.
Monday’s results.

GBP: +5 -0 2///+2 -0 1
EUR: +2 -0 5///+1 -0 2
AUD: +2 -1 4///+1 -1 3
NZD: +1 -0 6///+1 -0 4
CHF: +0 -1 6///+0 -0 3
USD: +0 -1 6///+0 -1 2
CAD: +0 -3 4///+0 -2 3
JPY : +0 -3 4///+0 -1 2

Majors took it. Barely. +1

The only interesting thing to me is how many 'even’s. Indecisions.


Mike

Morning guys,

I do not lie, I was happy this time not to see 15 new posts out here. As Peter said, we have some tasks to do. I really hope everyone takes the time to do BB’s tutorial. I continue it soon, I find that the most important priority. Besides that I will try to do the table of content, Mike and rookie already sent their results (thanks for that). And of course I also try to keep up with the markets. So it is not bad not to have very many new posts :slight_smile:

I also do not want to write too much, only share with you an article just published on babypips about Group trading vs. Alone trading. During the serious work we do it is refreshing to read something like that (at the same time Peter and Mike still made some nice posts during the many tasks to do!):

Having a Forex Trading Team vs. Trading Alone

Have a nice day and keep up to develop those COT skills!

FE

It’s time to calculate the Movement Index.

Formula: Current COT Index - COT Index X weeks ago. I use six, as Steve Briese advised.


The next step is to calculate Commercial Positions / OI (How much in % longs and shorts does Commercial entities own of the overall OI).


A simple % calculation.

Most of you guys were not familiar with the following index. It’s called Williams Commercial Index (Willco). I like to call it CP/OI Index.

Forumla: Stochastic (Net Position / OI, vara)


These are the calculations I use for my analysis. It is really no rocket science once you get the hang of it.

In the last part of the tutorial, I’m going to show you how you can plot the values on different charts.

Hi Balazs,

I am stuck at Tutorial VI. Need some help there. I am at the point where I want to do the table you mention at the first picture. The problem is I cannot read the words in your excel sheet. All the letters are too small and I do not see it.

Besides that I have a bit problem with the sorting: oldest to newest. In English this looks obvious, I do it on another language where the translation is not that obvious. Should the 2011 year data be on the top and 2014 for the bottom? If I go to the end of 2013 and 2011 data is on the top then we analyse on 2014 data.

I know it is difficult to make there maybe other print screens but if I cannot see then it is hard to move furhter on.

Thanks,
FE



The order should be from 2011 to 2014.



Well guys, that was yesterday, today we had the largest rise in stocks across the board for over 12 months.

Moral of the story is keep an eye on the small caps for a guide to risk appetite, like I said above it’s timing is very precise.

Hey guys.
Tuesday results.

CAD: +7 -0 0///+5 -0 0
NZD: +5 -1 0///+4 -0 1
USD: +3 -1 3///+0 -1 2
AUD: +3 -2 2///+3 -0 2
JPY : +3 -2 2///+0 -2 1
GBP: +2 -5 0///+0 -3 0
EUR: +0 -6 1///+0 -3 0
CHF: +0 -6 1///+0 -3 0

Comms took this one pretty good. +12


Mike

I wonder whether we could use the COT to find possible tops and bottoms. The Russel 2000 is included in the report. I might look into it later.

Yeah, but I’d suggest if it is the traded instrument then great, it has it’s own vix also.

It’s real power is measuring shifts in stock investor risk sentiment, the reality is as suggested, the greater risk stocks are sold off first in a decreasing risk appetite environment, when it was seen that the reality is that the US economy is not about to implode, that there was little prospect of “a perfect storm” then risk appetite quickly increased, “buy the stock bargains with the greater risk - quickly before everyone else gets in.”

Had the small caps been still falling last week then it is most likely the S&P would be at best consolidating, probably still falling.

Now back to CRB watching, it could herald the next major shift.

[B]Morning guys![/B]

I decided to let the tutorial rest for a while. At least until somebody finish the latest volume.

I’m trying to make it a habit of mine, to make a study once or twice a week related to the COT report. It is a great exercise, and gives you a general idea of how responsive different markets to the report.

In my last study, I examined the [B]relationship between OI readings and price[/B]. We came to the conclusion, that market[I] bottoms[/I] (highs) usually came hand-in-hand with [I]low[/I] (high) OI readings.

[B]Today, I’m taking the study one step further to see how Willco fared in 15 years. My test subject is once again, Silver.[/B]

[I]Here is the chart of XAGU/USD from 2000 to 2014[/I]

I calculate Willco with 1,5 years (80 weeks) variable, so that is what I’m going to use here too. If anyone wants to experiment with different variables, be my guest.

I have marked the instances with green line when Willco stepped over the 90% line. As you can see for yourself, almost 100% of the time Commercials became extremely bullish, the price of Silver skyrocketed.

Interestingly enough, they are just as precise with short positions.

Let us add the Net Positions and mark only those instances when both indicators flashed an extreme condition. Longs are right on the money, that is obvious. Despite the fact that silver was in an uptrend for a prolonged period, Commercials were picking tops pretty good as well.

[B]Conclusion[/B]: [B]Commercials rock![/B] The 2 indicators combined together might warn us about major trend changes/continuations.

Off-topic: [I]It would be nice if some of us used pictures as illustrations. I really liked Peter’s Russel 2000 and S&P 500 comparison. I believe that the matter deserves more attention.[/I]

[B]Edit: I used new pictures because in the last 3, I accidentally used Silver chart from 1998 to 2014 instead of 2000 to 2014. Sorry about that.[/B]

I have no idea how the last pic got here. I cant remove it -_-

Hi BB,

I made the whole tutorial. Wow, it took some time, but it is nothing compared to the time you had to develop this system and excel sheet. It is a very nice work!

I do not want to be picky, but there is a small typing mistake. Please correct it. Anyone being fit with excel would see it easily, but if someone did not work much with it, well it can cause some small problems. Here is what to check: post 1720, Step3 in the formula an “F” is missing.

In post 1733 the first attachment does not work.

Doing all the steps you wrote, I came to the conclusion that I have to buy silver soon. :slight_smile: What a surprise.

I have decided to try this analysis on the weekend. This means I will not do the analysis until now, I will just check rookies writeup on currencies, I want to make the next step. I will only analyse 1 commodity, but I believe doing this the first time will keep me busy. I think I will do US Crude oil. After doing the work a couple of times I do think that the process will speed up.

And thanks for the larger sprint screens, showing how to make the second template. This last Willco writing was also cool. Now I am ready for the last part of the tutorial.

FE

Guys,

I think you have seen that I do not post too much in the last days. It is not because I am not there. I am just very busy with many forex tasks at the same time. I do not start new conversations until I am done with the current works. There might be a bit of slow down in the posts as I see, but we all use our time for gaining knowledge and learning and we will have better analysis in the future. I think this invested time and work with have a huge payout.

Everyone should keep up working hard and the nice write-ups from Peter and Mike keep the thread live until everyone is done with the different work to do.

FE

I assume that you have completed the previous parts of the tutorial, so we can move onto displaying the data using Excel charts.

It is going to be a walk in the park compared what you’ve been through :slight_smile:

Net Positions

The best way to illustrate the action of the different entities in the last X years is using a simple line chart.


Insert the chart. Once the black field appears, right click on it, then “Select Data”.


Depending on the years you wish to display, select the desired interval’s Commercial and Non-Commercial Net Position fields. (You might wish to include Nonreportables, although they are going to be a flat line compared to Hedgers & Funds.


On the day of this writing, to display 3 years of data, we have to move back to 2011.10.11.


To see the date for each value, select the interval from column B. (Black circle)
For Legend Entries, edit Series 1 & 2 to Commercial and Non-Commercial.


Once done, yours should look like this. (probably with different colors)