Tip #2000-8: Grab a single historical price
I need to compare today's price of a market average with its price
of 10 days ago. How can I display only the price I want in a
single cell, without having to display the close for the previous
9 days?
Historical data is retrieved as an array formula. Array formulas
are formulas that work with ranges of data and are enclosed in curly
brackets { }. However, if you nest the historical array formula
within Excel's INDEX function, you can display one of the array
values as a single entry in
a cell.
To illustrate what we mean, we used the Reuters Tools History Wizard
to bring 10 days of historical closing prices on the FTSE Index
into an Excel worksheet. Show me the
sheet. Reuters data users, download
a sample sheet.
The data that appears is displayed in reverse chronological order,
with the most recent price (yesterday's close) displayed first.
The closing price from ten days ago is displayed last. The array
formula in each of the cells is
|
{=REUTER|TS1!'/RI=.FTSE /PE=DAILY /EV=10 FI=LAST /EN=EARLIEST'}
|
To make only the final value display in a single cell, without
the previous nine values displaying, you need to nest this formula
within Excel's INDEX function.
The INDEX function returns a single value from a range of values
selected by row and column number indexes. The function needs
3 arguments: the range of values, the row number, and the column
number. These arguments are separated by commas. (The column
argument is an offset value, indicating the number of columns
to the right or left of the column that contains the range of
values. For example, zero indicates the same column, 1 indicates
one column to the right, and -1 indicates one column to the
left.)
The easiest way to nest the History array formula within the index
function is to select any cell containing the History formula
and copy it to where you want it to appear as a single cell,
in our case D4. Notice that the value displayed in the cell
is the first value in the array. To make the last value of the
array display in the cell, you need to change the formula to:
|
=INDEX(REUTER|TS1!'/RI=.FTSE /PE=DAILY /EV=10 /FI=LAST
/EN=EARLIEST',10,0)
|
The INDEX function determines which value of the array will be displayed.
We substituted the entire Reuters History array formula
|
(REUTER|TS1!'/RI=.FTSE /PE=DAILY /EV=10 /FI=LAST /EN=EARLIEST')
|
as the first argument in the INDEX function. It returns a range
of ten values for the FTSE Index over the previous ten days.
The second argument is 10, the value in the 10th row of the range
of values returned by the TS1 array formula.
Because our array of values is contained in a single column, the last
argument (0) is optional. In the accompanying illustration,
we entered the formula in cell D4. To confirm that our formula
is returning the correct value, we compared it to the value
appearing in cell G13 (the value of the FTSE 10 ten days ago).
Show me You can now erase the values
in cells G3 to G13.
Cell C4 contains the live data formula
Show me
In order to calculate the percent change for the prior ten days,
you need to divide the current price (C4) by the price ten days
ago (D4) and subtract 1. In our case, we entered the formula
in cell E4, and clicked the percent style icon on the formatting
toolbar to display the result as a percent value, as shown in
the accompanying illustration. Show me
If you were to add other Market Indices or other instruments to
this worksheet, you wouldn't need to retype the formula into
each cell. All you have to do is copy the range of cells to
the row below and use Excel's Replace feature to change the
RIC. For example, to add the same data for the Dow Jones Industrial
Average to the row below in our worksheet, copy the data in
cells B4 - E4 to cell B5-E5. While these cells are still highlighted,
select Replace... from the Edit menu. Type
.FTSE in the Find What: edit box and .DJI
in the Replace with: edit box, and click Replace
All. (Be sure that Find entire cells only
is NOT checked.) The copied data will change to display real-time,
TS1, and calculated data for the Dow Jones Industrials instead
of the FTSE 100 Index. Show me
|
