Extract data from COT using Excel

The COT (known as Commitments of Traders) report contains the sale and purchase of various assets (forex, commodities, etc) of major players in the market. It is refreshed every week on the website. If you are just monitoring one product in the report, extracting the data from the report is pretty simple and straight forward, just copy and paste multiple times and it shouldn’t take you more than 10 minutes. But if you are monitoring a number of items in the report, then the

copy and pasting, though simple, can be very time consuming. In this case, I would consider using Excel to pull the data on a weekly basis from the web. Then by using Excel formulas, extract and organize them into a database which allows you to have a qucik analysis of the information on a weekly basis. If that is something you are considering, then read on.

Extracting the Report from the web into Excel directly

This same report is updated on a weekly basis without any change to the url. This provide us with the opportunity to pull this report weekly by using Web Query, one of the relateively unknown function in Excel. Here are the steps to create a Web Query.

1. Copy the url. For our example, the url is http://www.cftc.gov/dea/futures/financial_lf.htm

2. Open a new Excel workbook.

3. Go to the Data tab and click on the icon “From Web”. The icon is located on the left of the Data Toolbar.

4. In the pop-up window, paste the url into the text box that appear on top of the window.

5. Click on the GO button next to the text box. The report is now displayed in within the pop up window.

6. Click on the small little yellow arrow on the top left hand corner of the preview window.

7. Click on the Import button found at the bottom right hand corner of the window.

8. Another pop-up window appears. Select A1 to present the report starting from cell A1. Click OK and the report is presented in the worksheet.

9. Save the file, just in case Excel freezes.

10. With the Web Query setup, refreshing the report is simply right click within the report and choose Refesh (last item) from the pop-up menu.

If you wish to have the report update on open, right click on the report and select “Data Range Properties”. In the pop-up window, check the box “Refresh on open” and click OK. The report will refresh the next time you open the file and pull the latest figure from the web. For Excel 2010 and above, you need to enable the feature for refresh once on re-open. The setting is then set permanently. If you are using Excel 2007, you have to click “Enable content” every time you open the file.

After setting up the Web Query, we can now proceed to create formulas to pick up the products (i.e. forex) we want for our report.