Sidebar

Main Content

Datastream Time Series Using Excel

Questions? Email Lippincott Library

Use Excel when requesting time series data from Datastream. At the Datastream terminals in the library, click on the Excel for Datastream Users icon. Then click on the Datastream-AFO button at the top of the Excel screen. From that dropdown menu, select Time Series.

A Time Series Request box will open.
To run a Time Series Request, the following types of information must be selected: Code (or Mnemonic), Datatype (or Function), Start Date and End Date, and a Frequency. It is also possible to select the format of the Excel sheet.

Datastream uses a unique set of codes (or mnemonics). These mnemonics must be used when requesting data in Datastream. To find the codes, click on the search button

QUICK SEARCH

Click on Quick Search at the top of the screen to highlight. From the left hand sidebar, select a Subject category such as Equities, Economics or Interest Rates, etc. It is possible to search for a code by name, sedol number, IMF code or other unique identifiers.

To search for an equity by name, select Name from the drop down menu for FIND (towards the top of the screen). Then choose either Starts With or Contains (if only part of the name is known) from the next drop down box. Type the company name in the search box. Click on SEARCH.

A company may be listed several times if it is traded on more than one exchange. Datastream adds identifying tags to a base code (usually the ticker symbol for equities) which identify the Market or the Exchange (@ for Nasdaq).

Basic information is provided including Name, Mnemonic, Market, The Base Date of coverage by Datastream, Currency, and Status (active, suspended, etc). Mouse over the More tag to get additional information such as the Stock Exchange or other identifiers.

To select a code, double click it. The code is automatically placed in the Time Series Request . It is possible to add multiple codes by looking them up and double clicking. A comma is automatically placed between codes. If the codes are known, they can be directly typed into the Times Series Requestseparated by commas. (For large groups of codes, see Lists).

POWER SEARCH

It is also possible to search for codes using multiple criteria. Click on the Search button.

Double click on Power Search. Select a Subject category from the left hand sidebar, such as Economics. Select a Search Criteria, such as Market, from the dropdown menu for FIND (towards the top of the screen).

A list of codes, which corresponds to the Criteria selected (such as Market) will appear on the screen below the initial search box. Using the search box located at the top of this list, type in all or part of the name and click on SEARCH. Double click on the requested item to add that item to the search.

Select additional criteria using the FIND drop down menu. If two or more criteria come from the same category, such as Market, Datastream will automatically add them to the search using OR, such as Market (United States OR United Kingdom) AND Classification (Balance of Payments).

Continue to add criteria. When finished, click on SEARCH. Any Codes matching the Criteria will appear on the screen. Click on the Mnemonics to move them into the Times Series Request box.

A date range and frequency must be selected to execute a time-series request.

DATE RANGE

Dates may be selected several different way.

• 1. To select a Start Date and/or End Date, MM/DD/YY is the most common format. To include today as the End Date for the series, leave the End Date blank.
• 2. Another way to format date ranges is by selecting a length of time for the series. For 10 years of data, type -10Y in the Start Date box.
• 3. A beginning year and ending year can also be specified by typing a start year, such as 1990 for a Start Date and a different year, such as 1999 for an End Date. The year must have 4 digits, YYYY.

FREQUENCY

The Frequency determines the interval between data observations. In Datastream the options are Daily, Weekly, Monthly, Quarterly or Annual. Use the Frequency drop down menu to select the frequency.

DATATYPES

Datatypes indicate the type of date requested for the time series. Datatypes vary by Subject category. Also, There are different types of datatypes; times series datatypes or static request (requests for data on a specific date) datatypes. Each Subject category has a Default Datatype , such as Adjusted Price for Equities. For any request, one or more Datatypes may be requested.

Datatypes are added to the Time Series Request box below the Codes. Click on the Datatype search button.

Datatypes are available from many different sources, depending on the Subject category. (Some sources may be listed, such as Worldscope, but are not available through the academic account). Select a TYPE (Time Series or Static Request) and SOURCE to retrieve a list of dataypes.

Select a Subject category from the left sidebar such as Equities. Choose the TYPE and SOURCE. Scroll through the list to see the Datatypes and click to select. Or, type a datatype in the search box, such as Market Value. Click on the Mnemonic to add it to the Time Series Request box. Like Series/Codes, several Datatypes may be selected (Price, Dividend Yield and Market Value for an Equity).

FUNCTIONS

If a particular datatype or analytic is not provided by Datastream from the Dataypes, it is possible to select from a list of Expressions (or Functions). It is also possible to create a new Expression. Functions (or Expressions), such as Historical Volitility, are entered in the Datatype search box. Datatypes and Functions can be requested in the same Times Series Request.

Expression Picker

To find a pre-formatted Expression, use the Expression Picker.

Search for a Function such as Historical Beta. Double click to select and move to the Time Series Request box.

Expression Wizard

To create a new function, click on the Datastream AFO button at the top of the Excel sheet. Use Tools and then select Expression Wizard to select appropriate Series, Numbers, Operators and Functions.

Constituent List

Datastream provides preformatted lists of codes for the components of different groupings such as indices, or all equities from a given country. Using a Constituent List makes it possible to set up one search request for a large number of codes with out having to key in each one.

To find a Constituent List, open Datastream in Excel and select Time-Series or Static Request. Click on the Code button.

Select Constituent List from the left sidebar. If you know the name of the list, such as the S&P 500, use Name contains 500 (it is not always clear how the name has been abbreviated by Datastream so no results could appear if a search was done using Name starts with).

Several types of information are provided including the List Name, Mnemonic (most Constituent List Mnemonics begin with the letter L), Market, Type and Size (number of companies in the list).

Click on the > next to the List Name for links to related items in Datastream such as a list of Equities or any related Indices.

A Power search can also be done to search for Lists by Market or other Criteria.

The List mnemonic could now be used in a Datastream query. Information would appear for each company in the list.

Local Lists

At other times, a user may want to create a unique list which can be saved and updates as needed. To create a Local List, open Excel and click on the Datastream-AFO tab located at the top of the Excel spreadsheet. Click on Tools. There are two ways to create a list.

A list of Mnemonics may already be included in a spreadsheet. Open the spreadsheet containing the Mnemonics and highlight the cells containing the codes. Then click on Tools, Create from a List. The range of cells will be listed in the List box. Name the List in the appropriate box (include the .LLT extension). Click Save.

At other times the Mnemonics are not known and need to be looked up in code search. From the Datastream-AFO tab, click on Tools and then click on List Wizard. Select Create a New List. Click Next. Select the Data Category, such as Equities. Use Find to look up the codes.

For example, type Merck and click on the the Code Search button. Select the correct code by clicking on the Mnemonic. The code will then appear in the List Wizard. Continue to look up codes. When finished, type a description in the List Description box. Click on Next. Type in a name for the list.

To retrieve Local Lists, open Excel and click on Datastream AFO. Select Time Series Request. Click on the List Picker button.

Highlight the List Name. The constituents of the list will display in the right column on the screen. Click on Okay. The List Mnemonic will appear in the Time Series Request box. Continue to fill in the Request box and submit the search.