Accessing S&P Capital IQ Pro (Excel version)
Note: This post refers to the new S&P Capital IQ Pro Excel add-in,available at the Eddie Davies Finance Zone from October 2023. Please use a differentFAQ post if you have installed the legacy Capital IQ Excel add-in from the Software Center and have not yet upgraded.
Do you want to use add columns to an existing data set? Or use the identifier converter tool? If you are using a PC at the Eddie Davies Finance Zone with the Capital IQ Pro Excel add-in (Office plug-in) installed, you may log in to the add-in with the same username and password as the Capital IQ Pro website. (You must register for your own Capital IQ accountusing your University email address. It can take half an hour after registration before your account works in Excel.) See also our guide How do I access the Finance Zone remotely?
You may be asked if you want to enable extra keyboard shortcuts (we always disable these).
Resetting the Excel add-in
The Capital IQ Pro Excel add-in does not come with any tools to reset the Excel add-in. If it breaks, try logging out of the add-in, closing all Excel workbooks and opening it again. In more extreme cases, a technical usercan try to reload the COM Add-in in the Excel Options menu, from location:
C:\Program Files (x86)\SP Global Market Intelligence\SP Capital IQ Office\SNL.Clients.Office.Shim.dll
If you have problems, please contact the Library and report which PC you are using (there should be a label on the monitor, eg A007). You may need to use another PC with Capital IQ in the meantime.
Company identifier codes in Capital IQ Pro Excel
The Excel version of Capital IQ Pro accepts a wider range of company identifier code types than the web version, though you have to be aware of the different prefixes. The following code types can all be used as the identifier in a Capital IQ formula:
Variable name | Variable Code | Prefix | Example with Apple Inc |
Capital IQ Company ID |
| IQ24937 | |
Company Ticker |
| Exchange | NasdaqGS:AAPL |
SEC CIK |
| CIK_ | CIK_0000320193 |
Issue ISIN |
| I_ | I_US0378331005 |
Compustat GV Key |
| GV_ | GV_001690 |
Issue CUSIP [9-digit] |
| CSP_ | CSP_037833100 |
SEDOL (read only) | N/A | S_ | S_B0YQ5W0 |
Example: =CIQ("CSP_037833100", "IQ_ISIN") will convert CUSIP to ISIN for Apple Inc.
Notes:
- The native Capital IQ (CIQ) Company ID is called Excel Company ID in the web version.
- When requesting GVKEY, ISIN, CUSIP and CIK, you have to strip the prefix from the codes returned from Capital IQ to use themwith other databases. There may be more than one code returned per company, separated by commas.
- Conversely, if you already have GKVEY, ISIN, CUSIP, CIK or SEDOL codes, you must add the appropriate prefix before Capital IQ can read them. This applies to the Excel formulas and also if you create a watchlist in the web version.
- CUSIP codes must be 9-digits long, with leading zeros where appropriate.
- If you use the CIQ Company ID in a WRDS query (with S&P Capital IQ Transcripts), you have to strip the "IQ" prefix, reshape them to a single row, separated by commas and no spaces.
- You can use SEDOL codes in a Capital IQ Excel formula to identify a company. You cannot retrieve the SEDOL code for a company.
- To do a sanity check that you have a valid prefixed ID, try a simple query first like
=CIQ("GV_001690", "IQ_COMPANY_NAME")to get the company name. Consider alsoIQ_COUNTRY_NAME,IQ_COUNTRY_ISO_CODE,IQ_YEAR_FOUNDED,IQ_ULT_PARENTandIQ_ULT_PARENT_CIQIDfor the country name/code, year founded, ultimate parent name/code, respectively.
See also:
