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 straigh 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 organise 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.
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.
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.