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:
- Key Development
- People Intelligence
- Capital Structure
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
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:
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.
Proc sql; create table tickers as select distinct companyID, companyname, symbolvalue as Ticker, exchangeid, exchangename, startdate, enddate, objectid from ciq.wrds_ciqsymbol where companyId in (18749) and symboltypecat ='ticker' and primaryflag_trd = 1 and activeflag = 1; quit;
Results of the Query
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 a query like the sample code below:
Proc sql; create table tickers_w_detail as select distinct a.*, b.securityid, c.securityname, c.securitysubtypeid, d.securitysubtypename from tickers as a left join ciq.ciqtradingitem as b on a.objectid = b.tradingitemid left join ciq.ciqsecurity as c on b.securityid = c.securityid left join ciq.ciqsecuritysubtype as d on c.securitysubtypeid = d.securitysubtypeid; quit;
objectid in the above table matches with either
ciq.ciqsecurity. Otherwise it will be based on the
object type. View the results below: