Skip to Main Content

Knowledge Base

Datastream Formula Builder to Download Data

We recommend using the Datastream Formula Builder to download data from Datastream.

Note: You must request access first. Learn more about Datastream access.

Step 1: Connect to Datastream

  1. Connect to the Datastream For Office (DFO) Excel Plugin (see Datastream access help).
  2. In Excel, select Refinitiv > Datastream > Datastream Formula.
  3. In the spreadsheet, place your cursor where you want the data to populate.

Step 2: Select the Securities/Entities

If You Have a List of Security/Entity Identifiers

Available Identifiers

  • ISINs are recommended when merging with other sources.
  • The main identifier used in Datastream is the Mnemonic.
  • Modified 9-digit CUSIPs are in the 'local code' field. U.S. CUSIPs have a "U" at the start and Canadian ones have a "Q" (e.g. U345370860).
  • RICs (Reuters' Instrument Codes) are used in many LSEG datasets, are based on stock tickers, but are not always reliable after a company goes private or is acquired.
  • SEDOL (UK & Ireland) and T1 Codes (to merge with legacy Thomson ONE data).

Ask Us for assistance creating historically accurate lists of identifiers.

To Use a List of Identifiers

  1. In the spreadsheet, paste the identifiers in a single column; one identifier per row.
  2. Place your cursor where you want the data to populate.
  3. In the ribbon menu, select Datastream Formula.
  4. Click the Select Series from Sheet icon to the right of the Series/Lists text box.
  5. Use your cursor to select all your identifiers and press Select in the pop up. You should see the correct cell range in the Series/Lists text box.
  6. Below the Series/Lists text box, in the drop-down menu next to Symbology:
    • Select Datastream if you're using ISIN, SEDOL, CUSIP, or Mnenomic identifiers.
    • Select RIC if you're using the RIC or T1 Code identifiers.

If You Need to Make a List

Please Ask Us for assistance creating a historically accurate list, especially if you're merging with other data sources.

You can create a list in Datastream by doing the following:

  1. Click the Find Series icon to the right of the Series/Lists text box. The Datastream Navigator will open in a new window.
  2. Use the search bar and the left panel to refine the search (e.g. to limit to active U.S. Equities).
  3. Hover over or click on an item in the results list. More information will be in a section under the results.
  4. Check the box next to the securities/entites.
  5. In the upper right click MY SELECTIONS. Click Return all selections.

Step 3: Select the Datatypes/Variables

To avoid issues downloading, you need to know if the datatypes you want are time-series or static.

  • Time-series data runs over a period of time, like a firms' stock price.
  • Static data is typically data from a fixed point in time, such as the date of an IPO. It can also be information about a specific entity (like a company's headquarters) that is 'header' data and overwritten with the most current value.

To tell if a datatype is time-series or static, select the Find Datatype icon to the right of the Datatypes/Expressions text box to open the Datatype Navigator. In the results, look at the Type column. You can also filter the results by Type in the menu on the left.

For Static OR Time-Series Data
  1. Select the Find Datatype icon to the right of the Datatypes/Expressions text box.
  2. Use the left panel to refine your search and/or search by keyword at the top.
  3. Click an item in the results list to see a section under the results with more information.
  4. Check the box next to the datatypes you want.
  5. In the upper right click MY SELECTIONS. Click Return all selections.

For Time-Series AND Static Data at the Same Time
  1. Add the time-series data:
    1. Select the Find Datatype icon to the right of the Datatypes/Expressions text box.
    2. Use the left panel to refine your search.
    3. Click an item in the results list to see a section under the results with more information.
    4. Check the box next to the datatypes you want.
    5. In the upper right click MY SELECTIONS. Click Return all selections.
  2. Add the static data:
    1. Select Display Custom Header.
    2. In the new pop-out window, click Datatypes to launch the Datastream Navigator.
    3. Search for the datatype, such as ‘ISIN’ or ‘international securities identification number.’
    4. Click on the blue datatype Symbol.
    5. Select the plus sign (+) to add the datatype to the Selector box.
    6. Select OK.

Step 4: Adjust the Dates & Frequency

If you included time-series data, beneath the Datatypes/Expressions section, edit the From, To, and Frequency boxes to the right of the Start-End box.

If you only need static data, beneath the Datatypes/Expressions section, change the Start-End drop-down to As Of. Then edit the Latest Value drop-down if you need to pick a specific date.

Step 5: Format the Results

  1. Pick how the data will be formatted in the Options box. Select all that apply (e.g. you may want to check the box next to Display Headings or Display Currency.
    • Note, if you need to change the default currency, click the icon near the upper right.
  2. Click Insert in the lower right corner.

Helpful Tips

To edit the formula once you've inserted the data:

  1. Place your cursor in the cell you initially entered the formula (it should have a little red triangle in the upper right corner).
  2. In the ribbon menu, select the Datastream Formula icon again.
  3. You'll see all the options you initially selected and can change them as needed.



Answered By: Alice Kalinowski
Oct 20, 2023

Related Library Tips

    Resource Use

    Most resources are only available to current Stanford students, faculty, and staff.

    Researchers are responsible for using these resources appropriately. See the eResources Usage policy.

    Accessibility Support

    Ask Us for accessibility support with library resources.

    Ask Us