Linking Within Refinitiv (Thomson Reuters)

How to link across different data products within the Refinitiv (Thomson Reuters) system

Refinitiv (Thomson Reuters) Data Map

Refinitiv (formerly known as Thomson Reuters) is a comprehensive data vendor that carries a wide range of data, including but not limited to pricing, fundamental, and institutional data. In addition, Refinitiv has been actively acquiring outside data products. As a result of these recent aquisitions, Refinitiv can be a relatively complicated database for researchers to maneuver.

The Refinitiv database on WRDS platform carries the following data products:

  • Datastream: global pricing data
  • Insiders: insider activity data reported through SEC forms 3, 4, 5, and 144
  • Institutional Holdings - S34: holdings data collected from SEC form 13f
  • Lipper Hedge Fund (TASS): fund performance data
  • Mutual Fund Holdings - S12: mutual fund characteristics, holdings, and performance data
  • Ownership: global institutional ownership data
  • Refinitiv ESG: company-level ESG information based on publicly-reported data
  • SDC: historical M&A and new issues data
  • Worldscope: global fundamental data
  • WRDS-Reuters DealScan: global commercial loan data

As each data product provides unique empirical data, the products tend to have different identification systems. For instance, in Datastream's equity pricing data piece, Datastream Code (DsCode) is used as a permanent identifier to track a security, while in WorldScope, the Worldscope Permanent ID (item6105) is used to track down a company. Therefore, to facilitate empirical research, it is crucial to have an accurate and efficient way of mapping across all the data products under the Refinitv umbrella.

Top of Section

Security Master and Map Tables

Refinitiv provides a suite of "helper" datasets that enable tracking down a security over time and mapping it across various data vendors. The common identifier that links across all different Refinitiv data products is the SecCode. The files are located on WRDS server at: /wrds/tfn/sasddata/common/. Researchers can review the Database Schema for Refinitiv Quantitative Analytics Core Tables for an in-depth description on how to understand these support tables. We will discuss briefly how to use two of the main support tables below.

SQL View

SecMstrX and GSecMstrX are security master tables for the North American and the Global universe respectively. These tables are structured in a similar manner. In addition, the Refinitiv team also developed a SQL view, vw_securityMasterX, that pre-joins the information from SecMstrX and GSecMstrX.

Similarly, Refinitiv pre-joined the SecMapX and GSecMapX into SQL view vw_securityMappingX for easy access.

For example, the code snippet below shows how to look up all US companies that contain the phrase "APPLE " in their company name string.

Please make sure to select "SAS" in the kernel list when trying to run the code in Jupyter Notebook environment.

Jupyter Notebook: LinkwithinRefinitiv_part1.ipynb Download

The results return close to 60 entities that meet the conditions above. A quick glance will help us find the record that is associated with the blue chip giant, Apple.

SecCode Id typ Sedol PrevSedol Sedol2 PrevSedol2 Cusip PrevCusip Isin Name Country
6027 AAPL 1 204625 3783310 US0378331005 APPLE
INC COM
USA

With the SecCode information, we can then easily look up all identifiers for Apple used by different data products under the Refinitiv umbrella.

Jupyter Notebook: LinkwithinRefinitiv_part2.ipynb Download

The output below reports all the IDs used by various vendors (VenCode) for Apple. In this output, the VenType variable indicates the data vendor. For example, VenType = 33 corresponds to the Datastream data product. We can then find the Datastream identifier value for Apple to be VenCode = 72990.

SecCode VenType typ VenCode Rank Exchange StartDate EndDate
6027 1 1 43030 1 1 19600101 20790606
6027 2 1 28 1 1 19810820 20790606
6027 4 1 751 1 1 19600101 20790606
6027 5 1 1690 1 1 19600101 20790606
6027 6 1 1300 1 0 19600101 20790606
6027 7 1 180 1 0 19600101 20790606
6027 8 1 26435 1 0 19600101 20790606
6027 9 1 100000319 1 0 19600101 20790606
6027 10 1 29833 1 0 19600101 20790606
6027 14 1 72164 1 1 19801212 20751231
6027 15 1 7199 1 1 19600101 20790606
6027 16 1 72990 1 1 19600101 20790606
6027 17 1 1690 1 0 19600101 20790606
6027 18 1 1690 1 0 19600101 20790606
6027 19 1 311 1 1 19600101 20790606
6027 20 1 18911 1 1 20021129 20220325
6027 21 1 20769 1 1 19600101 20790606
6027 23 1 782 1 1 20220824 20220824
6027 24 1 782 1 1 19950131 20220731
6027 25 1 29833 1 0 19600101 20790606
6027 26 1 549 1 1 19600101 20790606
6027 27 1 18911 1 1 19600101 20790606
6027 28 1 3694 1 1 19890703 20790606
6027 29 1 809 1 0 19600101 20790606
6027 30 1 10797159 1 0 20001109 20790606
6027 33 1 72990 1 1 19801212 20790606
6027 34 1 315 1 1 19821130 20220824
6027 35 1 6751 1 0 19600101 20790606
6027 36 1 20000357 1 1 19900131 20790605
6027 37 1 7069 1 1 19600101 20790606
6027 39 1 6027 1 0 19600101 20790606
6027 40 1 186191 1 1 20040121 20790605
6027 41 1 1133 1 0 19980101 20781231
6027 42 1 28 1 1 19810820 20790606
6027 43 1 6751 1 0 19600101 20790606
6027 44 1 35109 1 1 20191230 20210227
6027 44 1 49025 2 1 20191206 20200221
6027 44 1 7059 3 1 19990401 20130621
6027 46 1 19775 1 1 19801212 20790606
6027 47 1 113 1 1 20110118 20790606
6027 48 1 67 1 1 19820730 20790606
6027 51 1 10220 1 1 19820104 20790605
6027 52 1 1300 1 0 19600101 20790606
6027 256 1 1690 1 0 19600101 20790606

In addition, the two-stage process above can be consolidated into one proc SQL statement:

Jupyter Notebook: LinkwithinRefinitiv_part3.ipynb Download

The output from the code reports Apple's ID, VenCode , in the data product Datastream (VenType = 33).

Id Sedol Name SecCode VenType typ VenCode Rank Exchange StartDate EndDate
AAPL 204625 APPLE
INC COM
6027 33 1 72990 1 1 19801212 20790606

For a more detailed discussion of variable definitions in this table, review theDatabase Schema for Refinitiv Quantitative Analytics Core Tables documentation.

Top of Section

Security Master and Map Queries

Lastly, for researchers who want quick access to the content without coding, the two main tables vw_securityMasterX and vw_securityMappingX are available on the WRDS website in a web query format:

Top of Section

Top