Home > Business Databases > Writing multiple requests to a single sheet with Datastream Request Table

Writing multiple requests to a single sheet with Datastream Request Table

Thomson Reuters logoOnce you have understood the basics of the Datastream Request Table for performing bulk financial or economic data enquiries, a common question is raised about the output. By default, each line in the request table will write to a separate, new sheet in the current workbook. The field labelled “Data Destination” allows one to set the first cell of the results explicitly, for example “Sheet1!$A$1” for Sheet 1, Cell A1. If this field is blank, the next new sheet is created, in the series Sheet1, Sheet2, Sheet3… always in the top-left cell A1. This is the default destination.

Default destination

Sometimes we want to put all of the results into the same destination sheet. For example, if looking up the share price on the date and days prior to a company’s initial public offering, for a list of companies. The start and end date will be different for each company, so, if using the default destinations, a request table will contain something like:

Each row: Update: Y, Request Type: TS, Format: RCT, Datatype: UP, Freq: Daily.

Series Lookup Start Date End Date Data Destination
AA. 20/06/2014 23/06/2014  Sheet1!$A$1
SAGA 22/05/2014 26/05/2014  Sheet2!$A$1
SSPG 09/07/2014 12/07/2014  Sheet3!$A$1
PETS 11/03/2014 15/03/2014  Sheet4!$A$1
TSB 19/06/2014 23/06/2014  Sheet5!$A$1

The Data Destination field will fill in automatically if left blank. Running this request table will look like this:

Request table default destinations

Using a Datastream Request Table and leaving the Data Destination fields blank will the results of each line into a separate new sheet.

Custom destination

The key to making the request table write all the output to the same sheet is to know how many rows or columns to leave. If this is the same for every line, you can use Excel short cuts to save typing in each destination by hand. In this example, each request takes two rows, so the sequence should be something like this.

  1. Create a new sheet, re-name it “All”.
  2. For the first line, edit the Data Destination to “All!$A$1”.
  3. For the second line, edit the Data Destination to “All!$A$3” — gives two lines for the previous response (since 1+2=3).
  4. Select both of the two edited cells. When the mouse cursor is at the bottom-right and turns into a black plus symbol (+), drag down to copy the sequence values.
  5. Now the destination row numbers are 1, 3, 5, 7, 9, run the table again.
Request table custom destinations

Using Excel to auto-fill a sequence, cell references in the same sheet can be given as a destination for each request line. This assumes the same number of rows/columns are used for each request line.

Series Lookup Start Date End Date Data Destination
AA. 20/06/2014 23/06/2014  All!$A$1
SAGA 22/05/2014 26/05/2014  All!$A$3
SSPG 09/07/2014 12/07/2014  All!$A$5
PETS 11/03/2014 15/03/2014  All!$A$7
TSB 19/06/2014 23/06/2014  All!$A$9

Related Excel tips

  • Cell references to different sheets in the same workbook have the format “SheetName ExclamationMark CellReference” (without spaces).
  • Dollar signs in cell references mean that the reference is “locked”, i.e. it will not change if you copy and paste the contents of that cell to another location.
Advertisements
Categories: Business Databases Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s