S&P's Capital IQ database carries a wide range of data. This document discusses: 1) how to link across different datasets within the Capital IQ umbrella; and 2) how to link Capital IQ data to other databases.
Link within Capital IQ Ecosystem
The CIQ database on the WRDS platform contains several data packages, including:
- Transaction
- Key Development
- Transcript
- People Intelligence
- Capital Structure
- Rating
The common identifier across all these datasets is CIQ's unique ID: companyid. Researchers can look up company specific information using the companyid within the CIQ data ecosystem. For example, the table ciqcompany contains the company name, address, industry--as well as various other fields--all organized around the companyid.
Link Capital IQ and External Databases
Capital IQ is one of the most commonly used databases. Therefore, it is crucial for researchers to be able to link the data from CIQ to other databases, such as pricing from CRSP. To streamline this task, WRDS provides several linking tables between the companyid and other company identifiers. These linking tables all reside under the CIQ data path, and they are:
- WRDS_CIK
- WRDS_CIQSYMBOL
- WRDS_CUSIP
- WRDS_GVKEY
- WRDS_ISIN
- WRDS_TICKER
For instance, according to records in the WRDS_GVKEY table, companyid = 18921 is linked to GVKEY = 001581, AT&T Corp. With this link in place, users can then easily connect CIQ data to Compustat fundamental data, and even to CRSP pricing data through the CCM linking table provided by CRSP. Users can apply similar logic to link from CIQ data to other databases using CUSIP, CIK, and other identifiers.
Using the WRDS_CIQSYMBOL Table
The WRDS_CIQSYMBOL table is designed to help users retrieve the Capital IQ's companyid associated with ticker symbols, CUSIPs, ISINs, or GVKEYs and vice versa. WRDS created this table to supplement the capabilities of the WRDS Capital IQ ID web query.
The Capital IQ ID web query results contain multiple security IDs for a given companyid. The WRDS_CIQSYMBOL table helps researchers distinguish between each of these multiple securities. The table integrates CIQ's companyid, GVKEY, Ticker, CUSIP, and ISIN with very basic security-level and company-level variables that researchers can use to differentiate each security. For example, user can pull multiple ticker symbols for a given companyid, and then figure out which ticker is primary, whether that ticker is active, and in which stock exchange it is traded on. Likewise, one can retrieve associated CUSIP numbers or ISINs for a given CIQ companyid or GVKEY using this table, as well as ID–GVKEY mapping.
Example Code for Querying the WRDS_CIQSYMBOL Table
The following code demonstrates how to retrieve the list of active primary stock tickers for Amazon, Inc., companyid = 18749.
The Jupyter Notebook is written in SAS, please make sure to select "SAS" in the kernel list.
Jupyter Notebook: LinkwithinCIQ.ipynb Download
Query Results
The first block of SQL query returns the following output:
Reviewing the query results, researchers can determine "AMZN" is the ticker for Amazon on the Nasdaq exchange, and can therefore ignore the other tickers, such as "AMZNCL," or "AMZO34."
If researchers want more detailed information about the above securities, they can run the second SQL query listed in the Jupyter Notebook above. The objectid in the above table matches with either tradingitemid in ciq.ciqtradingitem or securityid in ciq.ciqsecurity. Otherwise it will be based on the object type. View the results below:
