In this write-up, we are going to use INDEX and MATCH to
extract the data from the **COT report**. At first glance,
most
Excel users would be quick to conclude that it is impossible
to pick the numbers any details from the report without the
use of a macro (aka VBA). The currency description is in one
of the rows and the data related to the currency is located
in another low below the description. Using VLOOKUP will not
be able pick up the data because the currency description is
not on the same row as the data. For those who do not know
macros, most would have resorted to the primitive and yet
simple way of eyeballing at the data, copy and paste
the data to another worksheet. This maual way of
extracting data is tedious and prone to error. What I would
have done is to use the MATCH formula to hunt for the
currency description and then use the INDEX formula to pick
up the data for the respective currencies.

MATCH is similar to VLOOKUP. The difference is MATCH formula
returns the position of the currency description in the
range. If we start from row 1, MATCH formula effectively
returns the row number of the currency description. d
instead of the value. VLOOKUP cannot be used in this case
because the data we wanted is not exactly on the cell
itself. It is a few rows below the currency description.
Using the MATCH
formula which returns the row number for the description, we
can move down a few more rows to identify the location of
the data. Let me explain further with the first currency,
CANADIAN DOLLARS, in the COT report. A copy of the COT
report can be downloaded
**here**.

In
the workbook, the COT report can be found in the COT
worksheet. Create a new worksheet. In A2, type in the full
description for the currency "CANADIAN DOLLAR - CHICAGO
MERCANTILE EXCHANGE
(CONTRACTS OF CAD 100,000)". To find the row the description
is located, we enter the following MATCH formula
=MATCH(A2,COT!$A$1:$A$200,0). The formula will return 7 as
the position where the description is found. Since we start
the range at row 1, 7 can also be intepreted as the row
number. But I do not want to copy the long string of text
into A1 as it contains irrelvant information. So what can I
do? I can add an asterix after A2 so that the MATCH formula
can do a partial match for me. The formula should be entered
as follow =MATCH(A2**&"*"**,COT!$A$1:$A$200,0).
With the change in formula, I can use the shorter
description "CANADIAN DOLLAR" with the formula.

The
COT report imported into Excel is organised in such a way
that the data we wanted is always 3 rows below the currency
description. We can find that row simply by adding 3 at the
end of our MATCH formula =MATCH(A2**&"*"**,COT!$A$1:$A$200,0)+3.
With the row identified, we can insert the MATCH formula
into an INDEX formula which is a great formula to return the
value displayed in the cell. The formula is
=INDEX(COT!$A$1:$A$200,**MATCH(A2&"*",COT!$A$1:$A$200,0)+3**).

Using the same formula but different currency descriptions in column A, you can extract the respective data into column B.

•

## New! Comments

Have your say about what you just read! Leave me a comment in the box below.