Tip #2000-9: Getting around Reuters TS1 history date limitations
The Reuters Tools History Wizard has a listbox to choose dates for
retrieval. The problem is that I want to get data prior
to the earliest listed date. I know it exists! How
do I get it?
It's a two step process:
- Use the Wizard to create the formula with any Start and
End date.
- Edit the resulting formula manually to the dates you want.
To illustrate what we mean, let's bring in a year's worth of data
on the US Consumer Price Index into Excel (Reuters Instrument
Code: aUSCPI, Fields: Time Stamp (-600) and Cvalue (492)). We
used the Wizard and selected a Start Date of Jan 1, 1980 and
an End Date of Dec 31, 1980. Show me the History Wizard Box.
The data is pulled in as an array formula; i.e., a single formula
that refers to a range of values.
|
{=REUTER|TS1!'/RI=aUSCPI /PE=M /ST=19800101 /EN=19801231 /FI=-600,492'}
|
Show me the data
To bring in data prior to January, 1980, simply change the area
of the formula that shows the Start Date: /ST=19800101.
Let's change the starting date to January 1, 1978.
|
{=REUTER|TS1!'/RI=aUSCPI /PE=M /ST=19780101 /EN=19801231 /FI=-600,492'}
|
There is a trick to this: you need to enter your change
by using Ctl-Shift-Enter. If you don't, you'll get an error
message telling you that you can't change part of an array.
Using the Ctl-Shift-Enter keyboard combination changes
all the cells in the array at the same time.
Show me the sheet now
|
