Joining COT data with Price Data from Yahoo!

This is how to build a COT database with Access. If you are not familiar with Access then you have a little bit of learning to do but trial and error works fine and Access is actually easy to use once you get the hang of it.

You will need 3 files,

One is https://www.quandl.com/data/YAHOO/INDEX_GSPC-S-P-500-Index

The other 2 of them are at http://www.cftc.gov/MarketReports/CommitmentsofTraders/HistoricalCompressed/index.htm:

The exact location of the COT files is in the next screenshot:

cot

You need to Open Access and create 3 tables.
They have to be named exactly the same as above.
You need to right click each to import the data from the 3 files mentioned.

Then you build 3 queries and edit the SQL View for each and paste in the following:

“COT_Format” is:

SELECT COT.Market_and_Exchange_Names, COT.Report_Date_as_MM_DD_YYYY, COT.NonComm_Positions_Long_All – COT.NonComm_Positions_Short_All AS NonCommNet, COT.Comm_Positions_Long_All – COT.Comm_Positions_Short_All AS CommNet
FROM COT
WHERE (((COT.Market_and_Exchange_Names)=”E-MINI S&P 500 STOCK INDEX – CHICAGO MERCANTILE EXCHANGE”));

“Historical_Format” is:

SELECT [0714].Market_and_Exchange_Names, [0714].Report_Date_as_MM_DD_YYYY, [0714].NonComm_Positions_Long_All – [0714].NonComm_Positions_Short_All AS NonComm_Net, [0714].Comm_Positions_Long_All – [0714].Comm_Positions_Short_All AS Comm_Net
FROM 0714
WHERE ((([0714].Market_and_Exchange_Names)=”E-MINI S&P 500 STOCK INDEX – CHICAGO MERCANTILE EXCHANGE”));

“Join” is:

SELECT COT.*, ES.Close
FROM (select * from COT_Format
union all
Select * from Historical_Format

) AS COT INNER JOIN ES ON COT.Report_Date_as_MM_DD_YYYY = ES.Date
WHERE (((COT.Market_and_Exchange_Names)=”E-MINI S&P 500 STOCK INDEX – CHICAGO MERCANTILE EXCHANGE”));

The output is as follows from the Join Query. In theory if you keep all the files in the same place then all you need to do is download the 2015 COT data every weekend and the Yahoo file.

(alternatively for the Yahoo data you could just open the ES table and manually insert the closing price of the previous Tuesday.)

cot_output

You can then copy paste to excel and you get:

cot_output_excel

Advertisements

One thought on “Joining COT data with Price Data from Yahoo!

  1. Excellent work , well explained as well thank you .
    As a suggestion for anyone wanting to get a understanding of Excel or Access , a very informative way is the educational videos at Lynda.com .
    You can get a 7 day free trial or just pay $25 for a months access , you will save a lot of time and fast forward your learning curve .

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s