COT Report Analysis - a thread on market sentiment

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)

Following the logic in the last Tutorial, you should be able to figure out the rest of the graphs yourself.

Here’s a little help though.


Oh, almost forgot. Displaying the COT Index is a little tricky, so I shall give you a little help there.

Once you select the data you want to see on the chart, you’ll probably notice that something amiss.


That does not look good, does it? The reason for this appearance is the the chart is centered around zero without fixed limits. Here’s the way to solve the problem.


Right click on the Horizontal Lines, then select “Format Axis


Apply the following settings. That should do the trick!

Well, that is all for now. Wish you luck with your database!

Hey guys.
Wednesday results.

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

Well this one was EVEN.
USD and AUD on top today.
And there goes NZD down. (figures…I guess now is a good time huh?)

00 GMT


Mike

Hi BB,

as today I will finish the tutorial and can start working with the analysis, the question about the database and especially on your database are coming into my mind.

Here are the questions I have:

  1. With Philip, we were working on the Currency Cross COT Index. The question is if you also worked it out how to manage it in your database to make charts based on that. It would be a great tools to use the COT Report on the crosses too, and not only on US pairs. To see more information on the issue, please check the Briese book on page 131 and page 132. I am though quite sure that you already worked it out or at least know what I am talking about.

  2. The other thing is refreshing your database. I wanted to know what method do you find the best to refresh your database? Do you download the compressed COT report every week and then for example you just take the one column of silver for the given week and enter it into your database? Or do you write just the numbers out? What method does best work for you?

  3. How did you handle your trades? If you analysed the COT report all alone, it had to be a huge work to analyse all markets. So I guess you either focused yourself on specific commodities or you analysed every week different products. For this reason I think a team can reach better results, because if we have enough dedicated people, then we will be able to analyse the whole market on a weekly basis and be up to date with all signals at the same time.

Have a nice afternoon everyone and keep working with the tutorial,

FE

[B]Question #1[/B]: To be honest, I had no intention to create COT Cross Indexes but since you asked, I’ll give it a try.

[I]“The futures markets for cross-rates—currency pairs that do not include the dollar—are not well enough established to provide a usable COT data base. So I simply combine the COT data for two futures contracts to create the COT Cross Indexes. To get the correct combination, multiply the quote currency times −1 before adding.”[/I]

Let’s take [B]EUR/JPY[/B] for instance.

[U]Euro FX[/U] - Commercial longs: [I]54,556[/I] | Commercial shorts: [I]210,178[/I]
[U]Japanese Yen[/U] - Commercial longs: [I]192,234[/I] | Commercial shorts: [I]47,395[/I]

[U]Cross Index[/U] - Commercial longs: [I]54,556+(-192,234)=(-137,678)[/I] | Commercial shorts: [I]210,178+(-47,395)=(162,783)[/I]

Well, that does it. I’m not sure about -137,678 though. Maybe you have to take it’s absolute value. From these numbers, you are able to calculate the important indicators. I’ll look into the matter more, once I have some spare time.

[B]Question #2[/B]: I simply write the numbers out until I find a programmer who is able to automatize the process. I Ctrl+C - Ctrl+V last week’s row, re-type the [I]Report Date[/I] and the Long / Short positions. The calculations are automatic.

[B]Question #3[/B]: It actually takes about 10 seconds to know whether that particular instrument deserves attention during next week or not. That’s what COT Index extremes are for! If there are other confirmations (Movement Index, OI low/high, Willco), even better.

[B]Update report -> Look for extremes -> Analyze chart using TA.[/B]

Thanks for a great tutorial BB, I will be uploading my database according to it.

I hope some of you guys have taken advantage on the trades I shared on the weekends; GBPNZD is close to getting to its first target of 140 pips.

The S&P is working great too; I’m up eight points for a profit of $225 and we are only in the second day of trading. Those who did enter the trade I recommend moving your stops to break-even just in case price moves back below the 200 ma. As we have seen from previous example this remains the likely scenario, especially with the Fed meetings coming up.

I’m also long USDJPY and USDCHF for a small profit, a good week so far. I think I only made one or two losing trades since September #onfire.

Hi Philip,

this is also my very best week since trading. I have very many open trades, the USD trades you mentioned. I entered earlier S&P 1893 to 1921 once, and now I am also in from 1929 and SL is above breakeven. I shorted JPY vs. all others except EUR. I also entered CAD longs, but GBP only vs. JPY. I think GBP will fall and I do not go long with it. I am more happy to trade CAD these days.

Good luck,
FE

Hi BB,

What did you say about creating the excel? Something like this?:

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

It was the toughest part of the tutorial! Took quite some time. If you have not posted the exact steps or the charts pictures to compare it… then I do not know.

After some sweating it looks like yours, only in different colours.

So I can start doing it now. I cannot wait for the weekend :slight_smile:

About crosses: I think your calculation looks good, I do not think we have to take the absolute value. I mean the net position can be negative without any problem. However it does look like a hell of a lot of work. There are 28 major currency pairs and 21 are crosses. That is a lot of calculation. It can though help quite a lot I believe, but still a lot of work.

You did not say how many markets do you follow with COT? Do you do all the analysis on the weekend?

And you showed us where to insert the little part of the excel where we calculate COT Index, Movement Index, Willco and OI. Now the question is: we actually do not need to upper row every week. Do you move this chart every week one row down? I just try to understand all steps to make it fast. I was thinking that this data copying on the weekends takes long if I do not choose the right strategy.

Ok, now I will rest. The tutorial was great but made me tired. Now I take a break before it goes “live”.

FE

Hey guys.
Thursday results.

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

Comms took this one. Barely. +1

Monday: M +1
Tuesday:C +12
Wednes: EVEN
Thursday:C +1

00 GMT


Mike

Hi Mike,

wow, your table shows the shift change in CAD sentiment after the more bullish central bank bias! They just killed everyone.

Guys,

as you see I still do not post much. I am ready with the tutorial, but I could not start the table of content yet. Still, I am not lazy and have good news. Check out the thread name :slight_smile:

Have a nice day everyone,
FE

Well, it was easy for me because I was specialized in IT when I was in high school (^_^) Anyway, sorry for not being more specific, but at least you figured out by yourself so you can re-create it if needed.

Crosses: If my calculations were correct, then it should not be a big deal to create a database. All you need is to copy and paste the latest figures for the base / quote currency, then calculate the Cross Indexes. I might do it for a pair on the weekend and see how it goes.

I currently have 11 instruments in my database. I won’t add more until I make sure which commodities are responsive to the COT report. I already shared with you guys my Silver study. I usually do my analysis on the weekend. It takes no more than an hour.

Yes, I simply add a new row each week. At the end of each year, it is alright the get rid of 1 year of data, since you won’t need that anymore. If you copy last week’s row, you only have to type in the date, and the long/short positions, since the calculations are automatic. It takes about 15-20 seconds to type all the numbers.

Hey BB!

I’m having trouble with Wilco


I have double checked if there was any error formula wise but couldn’t find anything


202nd is the last row as you can see from the attached image above , and corresponding to column N there’s commercials net position/OI which I’ve circled. I hope this is enough info for you to be able see what I’m wrong doing here. I’m almost done with ‘gold’ only though.

Seems like you are using 101 week as a variable, so your Willco formula should look like this (for the row ‘202’)

=100*((N202-MIN(N101:N202))/(MAX(N101:N202)-MIN(N101:N202)))

I see what is the problem here. Your database is upside down. You are calculating the Willco for 1/7/2014 from the weeks that followed the date. In other words, you are calculating from future numbers.

You have to sort your database, so the report dates are from the oldest to the newest.


Yep you’re right BB the IT expert. Thank you again!

Hi guys,

a very quick write up from my point of view. I only write here “products” which does worth to mention:

[B]CAD[/B]: the bullish Central Bank is definitely something to mention. It suprised everyone. I closed one of my two short CAD trades right away (NZD/CAD long) and have them only vs. USD short. On the other side I am long vs. JPY, CHF and GBP.

[B]GBP[/B]: the opposite of the CAD, I think the Eurozone will pull it down.

[B]S&P[/B] and [B]FTSE[/B]: both were moving up continuously giving some great opportunities to enter! We talked about it a week ago and the ones who entered made nice profit on it (I do not know who did, I know only about Philip and myself)

As we were talking on the COT about possible setups, here is how our watchlist is doing:

[B]Gold[/B]: was moving the last weeks up but turned down again. I do no see any good reason why it moved up, I do not buy it until a COT signal comes.

[B]Silver[/B]: basically shows a big difference to gold as it does not want to move to any direction at all.

[B]Copper[/B]: I am watching the action every day. Neither the support zone or the channel break is in danger. We will see what BB has to say on the weekend about it. It might take some time to see an upside break.

[B]Gasoline[/B]: I do not even have a chart on it, I wonder how BBs trade is doing. Probably he will mention it on the weekend.

These were the main happenings and our COT watchlist commodities for the post week.

FE

Hi guys,

this is off the topic but all of you together has many years of experience and I thought I ask it. I read today quite a lot about the [I]4 week rule, developed by Richard Donchain[/I].

Now, as it seems, it is quite obvious and easy to use and produces also good results. The question is obvious? Does some have experience with it and if yes, what kind?

Thanks,
FE

FE, it’s an oldie system, almost as old as me, basically a breakout in the trend direction.

Fails on a fakeout, but sure to catch the big move.

This was the system where the tutor said he would never consider buying below the current market price or selling above current because you are guaranteed to be in the market when you are wrong.

BTW post something to get off that post number :slight_smile:

Finished my COT report. Unfortunately I have no signal to report. I’m staying on the sidelines for next week. I actually advise everyone to do the same, it is a direction-defining week ahead. So no reason to be involved until we see what happens.