How to link across different data products within LSEG data.
LSEG (formerly Refinitiv/Thomson) Data Map
LSEG (formerly Refinitiv/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, they have been actively acquiring outside data products. As a result of these recent acquisitions, LSEG can be a relatively complicated vendor for researchers to maneuver.
WRDS carries the following data products from LSEG:
- 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
- LSEG ESG: company-level ESG information based on publicly-reported data
- SDC: historical M&A and new issues data
- Worldscope: global fundamental data
- WRDS-LSEG DealScan: global commercial loan data
As each data product provides unique empirical data and tends to have different identification systems. For instance, in Datastream's equity pricing database, 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 LSEG umbrella.
Security Master and Map Tables
LSEG provides a suite of "helper" datasets that enable tracking a security over time and mapping it across various data vendors. The common identifier that links across many different LSEG 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 LSEG team also developed a SQL view, vw_securityMasterX
, that pre-joins the information from SecMstrX
and GSecMstrX
.
Similarly, LSEG 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: ResearchApps/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 LSEG umbrella.
Jupyter Notebook: ResearchApps/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: ResearchApps/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 the Database Schema for Refinitiv Quantitative Analytics Core Tables documentation.
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: