CCMQuery: CRSP/Compustat Merged Database Access

CCMQuery provides access to Compustat data for all securities in the Compustat universe as well as linking information for those securities also in the CRSP Universe.

Click the New Query button on the CRSPSift toolbar. Select a data environment that includes a CRSP/Compustat database. Highlight the CCMQuery icon, enter a descriptive name, and then click the OK button.

The CCMQuery Toolbar

First specify a date range for your query. Enter dates using one of the following formats: YYYYMM, or YYYY. The following table shows some examples of how to use the various formats.

Start End Result
199609 199612 All data from the beginning of September through December of 1996
1990 (empty) All data in the year 1990

Next, from the Date Display menu, choose either Fiscal Period (the default) or Calendar.

Company Selection

Entities in the CCM Database are accessed by: ‚

  • GVKEY - company identifier ‚
  • GVKEY.IID - security identifier
  • GVKEYX - index

Securities linked with CRSP can be accessed by CRSP identifiers also (PERMNO and variations, PERMCO).

Each GVKEY has associated with it a primary issue. When using GVKEY with no IID, CCMQuery will automatically attach the IID of the primary isssue, most often .01.

From the drop-down menu in the Company Selection area, specify the key type you wish to use.

Next, identify the issue or companies of interest. If you know an identifier, enter it directly. For example, the GVKEY of Apple Computer is 1690. Given that knowledge, you can set the key to GVKEY and enter 1690 as the identifier.

If you do not know an identifier, click the Find… button to open the Company Lookup window.

Use the search form to locate a particular company in the CCM database. Companies matching your search criteria appear in a table, initially sorted by GVKEY, then by IID. If your search yields many matches, click the column labels to re-sort the table in the order most useful to you. When you have found the company you want, click its row in the table, and then click the Select button. The GVKEY of that company will appear in the identifier field.

Queries can be run against the full universe by selecting All Issues, or by creating a list of keys and selecting Input File. To use an input file, create a text file with an entry for each company, one per row, using the active key you selected. For example, to search for Apple and Microsoft by GVKEY using an input file, select GVKEY from the Active Key menu.

Next create and save the following two line text file in the format GVKEY.IID:

1690.01
12141.01

For company and financial statement data, if the IID is not explicitly provided, the primary issue default will be provided. If security-level data is requested, both GVKEY and IID are necessary. Accessing security data using an input file without IID will result in an error message and the query will return no data.

Once your input file is properly created, Click Input File in the Company Selection area.

Data Items and Groups

Compustat data may be accessed either by selecting and combining individual data items in a query, or by selecting pre-defined groups of data items.

Data Items

Click on Select/Edit Data Items to open a screen that offers two categories: Compustat data items and Favorites.

Expand the Compustat data items category (CCM-All Available) to see categories of Compustat data items.

Select Subgroups within the categories and view items within each of the subgroups. When you select a subgroup, a list of its items appears in the Search Results area. Further details about the selected item are shown in the area to the right of the Search Results. Highlight items to be added to your query and click the Add to Query button. Added items appear in the Query Items section of the screen.

Keysets

Keysets are CRSP-created groups of Compustat secondary keys that are used to further define Compustat data items. CRSP has identified a default keyset for each data item. Sift allows you to override the default keysets, or select multiple keysets in Sift.

On the Data Items screen, clicking on the Keyset box opens a table of all existing keysets for the items with statistics about each. The default keyset is highlighted upon opening the box and is typically STD, or Standard. The Keyset number is provided, the number of GVKEYs, the number of data points, the date range populated by the data, and a description of the Keyset.

Choose different or additional keysets and click the Select button to close the window.

Keyset Display

Compustat data items can be qualified by a set of secondary keys. For example, the data item SALE has secondary keys for industry format, data format, population source, and consolidation level. A different value of company sales can be available for different combinations of these keys, such as a combination that represents the originally reported sales or the final restated sales from a later filing.

CRSP calls these collections of keys and values a keyset and assigns a numeric code and mnemonic tag to each unique collection. Each of these represents different output series. When multiple keysets are available the user can specify both the item and keyset to identify the series of interest, or use the default preset combination most commonly used.

The Keyset Display drop-down menu in CCMQuery’s Output tab affects how a data item’s keyset is represented in query output.

Options include CRSP Mnemonic Keyset Tag, Keyset Number, and Compustat Expanded Keyset. The following examples illustrate these options using Microsoft Excel output.

CRSP Mnemonic Keyset Tag

Output represents the item’s keyset using a mnemonic tag assigned by CRSP.

KYGVKEY KEYSET_TAG FYYYY FYRA SALE
1690 STD 2005 9 13931
1690 STD 2006 9 19315
1690 STD 2007 9 24006

Keyset Number

Output represents the item’s keyset using a keyset number assigned by CRSP.

KYGVKEY KEYSET FYYYY FYRA SALE
1690 1 2005 9 13931
1690 1 2006 9 19315
1690 1 2007 9 24006

Compustat Expanded Keyset

Each component of the item’s keyset is broken out into its own column in output.

KYGVKEY CONSOL DATAFMT INDFMT POPSRC FYYYY FYRA SALE
1690 C STD INDL D 2005 9 13931
1690 C STD INDL D 2006 9 19315
1690 C STD INDL D 2007 9 24006

Keyset Available Data

Reference Codes (Default key)

Reference code data can be used in two ways: to return a list of unknown codes, or to find the meaning of a specific code.

Description Base Item Data Items
Accounting Standard ACCTSTD ACCTSTDCD
ACCTSTDDESC
Acquisition Method ACQMETH ACQMETHCD
ACQMETHDESC
Balance Sheet Presentation BSPR BSPRCD
BSPRDESC
Comparability Status COMPST COMPSTCD
COMPSTDESC
Constituent CONTYPE
CONVAL
CONTYPECD
CONVALCD
CONVALDESC
Country FIC, LOC, EXCNTRY ISOCNTRYCD
ISOCNTRYCDDESC
Data Code *_DC DATCDCD
DATCDDESC
Data Format DATAFMT DATAFMTCD
DATAFMTDESC
Exchange Rate Type EXRATTPD EXRATTPDCD
EXRATTPDDESC
Footnote *_FN*
POPSRC
FND_FNCD
FND_POPSRC
FND_FNDESC
Footnote *_FN* FNCD
FNDESC
Internal Control Auditor Opinion AUOPIC AUOPICCD
AUOPICDESC
Industry Format INDFMT INDFMTCD
INDFMTDESC
Industry Presentation IPCD IPCDCD
IPCDDESC
Issue Status Alert STALT ISALRTCD
ISALRTDESC
Issue Type TPCI TPCICD
TPCIDESC
Index INDEXTYPE
INDEXVAL
IDXTYPECD
IDXVALCD
IDXVALDESC
Level of Consolidation CONSOL CONSOLCD
CONSOLDESC
Market Holiday ISOCNTRYCD ISOCD
HCAL_DATADATE
Major Index INDEXID IDXIDCD
IDXCAT
IDXIDDESC
Note Subtype NOTETYPECD
SUBTYPE
SUB_NOTETYPECD
SUBTYPECD
SUBTYPEDESC
Note Type NOTETYPE NOTETYPECD
NOTETYPEDESC
Officer SOX Certification CEOSO, CFOSO OSOCD
OSODESC
Oil & Gas Method OGM OGMCD
OGMDESC
Officer Title OFCD OFCDCD
OFCDDESC
Research Company Reason for Deletion DLRSN DLRSNCD
DLRSNDESC
Status Alert STALT STALTCD
STALTDESC
State / Province STATE, INCORP STATECD
STATEDESC

Reference Numbers

Reference Numbers are numeric codes assigned to Compustat data. Like Reference Codes, Reference Number data can be used in two ways: to return a list of unknown numeric codes, or to find the meaning of a specific numeric code.

Description Base Item Items
Auditor Opinion AUOP AUOPCD
AUOPDESC
Auditor AU AUCD
AUDESC
Cash Flow Format SCF SCFCD
SCFDESC
Source Document SRC SRCCD
SRCDESC
Source Document (Quarterly) SRCQ SRCQCD
SRCQDESC
S&P Economic Sector SPCSEC SPSECCD
SPSECDESC
Stock Exchange EXCHG EXCHGCD
EXCHGDESC
Fortune Industry FORI FORICD
FORISTAT
FORIDESC
GICS GGROUP, GIND, GSECTOR, GSUBIND GICCD
GICSTAT
GICDESC
S&P Industry Index SPII SPIICD
SPIISTAT
SPIIDESC
Income Statement Model ISMOD ISMODCD
ISMODDESC
Inventory Valuation INVVAL INVVALCD
INVVALDESC
NAICS NAICS NAICSCD
NAICSTAT
NAICSDESC
Price Status PRCSTD PRCSTDCD
PRCSTDDESC
SIC SIC, SICH XPFSICCD
SICSTAT
SICDESC
S&P Industry Sector SPIND SPINDCD
SPINDDESC
S&P Major Index SPMI SPMICD
SPMISTAT
SPMIDESC
Stock Ownership STKO STKOCD
STKODESC
Update UPD UPDCD
UPDDESC

Currency Data

Currency data items include information about a country’s currency as well as a history of daily and monthly exchange rates.

Note on Exchange Rate Data:

Exchange rates are listed “from” a common currency, “to” the currency in question. Currently, “GBP” (Pounds Sterling) is used as the common “from” currency.

Currency Data
itm_name description
ISOCURCD ISO Currency Code
ISOCURBD Currency Birth Date
ISOCURDD Currency Death Date
ISOCURLNK Currency Link Code
ISOCURTR Currency Tier Number
ISOCURNM Currency Name
Daily Exchange Rate
itm_name Description
EXRATD Daily Exchange Rate
Monthly Exchange Rate
itm_name Description
EXRATM Monthly Exchange Rate
Monthly Exchange Rate Averages
itm_name Description
EXRAT1M Monthly Exchange Rate, 1 Month Average
EXRAT2M Monthly Exchange Rate, 2 Month Average
EXRAT3M Monthly Exchange Rate, 3 Month Average
EXRAT4M Monthly Exchange Rate, 4 Month Average
EXRAT5M Monthly Exchange Rate, 5 Month Average
EXRAT6M Monthly Exchange Rate, 6 Month Average
EXRAT7M Monthly Exchange Rate, 7 Month Average
EXRAT8M Monthly Exchange Rate, 8 Month Average
EXRAT9M Monthly Exchange Rate, 9 Month Average
EXRAT10M Monthly Exchange Rate, 10 Month Average
EXRAT11M Monthly Exchange Rate, 11 Month Average
EXRAT12M Monthly Exchange Rate, 12 Month Average
EXRAT13M Monthly Exchange Rate, 13 Month Average
EXRAT14M Monthly Exchange Rate, 14 Month Average
EXRAT15M Monthly Exchange Rate, 15 Month Average
EXRAT16M Monthly Exchange Rate, 16 Month Average
EXRAT17M Monthly Exchange Rate, 17 Month Average
EXRAT18M Monthly Exchange Rate, 18 Month Average

Country Economic Indicator Data

Economic Indicator Data

itm_name description
AUTO Sale of Passenger Cars
BOND10YR Government Bonds – 10 Year (Canada Only)
BOND20YR Government Bonds – 20 Year (U.S. Only)
BOND30YR Government Bonds – 30 Year (U.S. and Canada)
CABGDP1 Current Account Balance (Annual)
CABGDP2 Current Account Balance (Quarterly)
CPI Consumer Price Index
CPI1 Consumer Price Index Inflation Rate (Index Value – Annual)
CPI3 Consumer Price Index Inflation Rate (Index Value – Monthly)
CPIR Consumer Price Index Inflation Rate (Percent)
EMPLOY Employment – Nonfarm
EMPLOYT1 Employment – Total (Annual)
EMPLOYT2 Employment – Total (Quarterly)
FEDFUNDS Federal Funds Rate
GDP Gross Domestic Product
GDPN1 Nominal Gross Domestic Product (Annual)
GDPN2 Nominal Gross Domestic Product (Quarterly)
GDPR1 Real Gross Domestic Product (Annual)
GDPR2 Real Gross Domestic Product (Quarterly)
HOUSE Housing Starts
IP1 Industrial Production Growth Rate (Index Value – Annual)
IP3 Industrial Production Growth Rate (Index Value – Quarterly)
IPGR Industrial Production Growth Rate (Percent)
IPPI Industrial Product Price Index – Canada
LIBOR1M London Interbank Offering Rate – 1 Month
LIBOR2M London Interbank Offering Rate – 2 Month
LTGDR Interest Rate on Long Term Government Debt
M1 Money Supply
M2 Money Supply
MBROAD1 Broad Money Supply (Annual)
MBROAD3 Broad Money Supply (Monthly)
NOTE10YR Government Notes – 10 Year
NOTE2YR Government Notes – 2 Year
NOTE3YR Government Notes – 3 Year
NOTE5YR Government Notes – 5 Year
NOTE7YR Government Notes – 7 Year
POPT Population
PPI Producer Price Index
PRIME Prime Interest Rate
RAWMAT Raw Material Price Index
RTLSALES Retail Sales
STGDR Interest Rate on Short Term Government Debt
TBILL12M Treasury Bill – 12 Month
TBILL3M Treasury Bill – 3 Month
TBILL6M Treasury Bill – 6 Month
TXCR Corporate Income Tax Rate
UNEMP Unemployment Rate
UNEMP1 Unemployment Rate (Annual)
UNEMP2 Unemployment Rate (Quarterly)
WPI1 Wholesale Price Index Inflation Rate (Index Value – Annual)
WPI3 Wholesale Price Index Inflation Rate (Index Value – Monthly)
WPIR Wholesale Price Index Inflation Rate (Percent)

Favorites

If you find that you commonly use a certain subset of items, at any time, you may highlight them and click the Add to Favorites button. Favorites is a user-created category designed to house these commonly accessed data items. It allows storage of Daily and Monthly CRSP stock items as well as Compustat data items.

To control the order in which your report will display data items, use the up and down arrows to the right of the Query Items list. Click an item and then click the up or down arrow to reorganize the list. To remove an item or group of items, select them from the list and click the X button, located above the arrow buttons.

Select Global Item Options to include Footnotes or Data Code items for those items that have them, and to change the default from providing output in its Reported Currency to converting it to US Dollars.

When items and options are selected, click on OK to return to the main CCMQuery screen. Selected items appear in the Screen.

Note: Global options must be set prior to selecting data items in order for them to be applied.

Importing Data Items From A File

Instead of choosing data items using the item selection screen, you can import a set of data items from a text file. To do so, click “Import Data Item File…” The file should contain one data item per line, in the format itemid.<keyset>. Keyset number is optional; the item’s default keyset will be used if it is omitted, as for the data item filedate in the following example.

ofid.0
ofcd.0
ofnm.0
filedate
sale.5

Imported items will be added below any items already present in the Selected Data Items list.

Exporting Data Items To A File

Click “Export To File…” to export items in the Selected Data Items list to a text file in the format described above.

Groups

Click on the Groups tab to view choices of pre-defined groups of Compustat data.

Groups provide a convenient way to select multiple related data items at once. For example, there are five items related to Filing Dates data. These items are found under the Filing Dates sub-category of the Additional Descriptions category in the data items selection screen. There you can add each of the five items to your query, one by one. That method is best if you want to modify properties (such as keyset or output header) of some but not all items.

Alternatively, you could add all five items at once by selecting Filing Dates under the Groups tab. This is an efficient way of selecting multiple items if you intend to apply the same properties to all items in the group.

Output To Screen

The default option is text-based, formatted to fit the screen width specified in the Width control. 80 characters is the default width. Screen output appears in the hopper at the bottom of the CCMQuery window.

By default, the output of the last query execution is shown in the Current tab. The Save Output and Clear Output buttons at the top of the output area allow you to save output to a text file or to clear it away. Clicking the History tab shows you the output of every query execution since the session was opened.

When you switch to the query history view, the buttons above the output area change to Save History and Clear History. Save History allows you to capture the output of an entire session. You can select a different key (GVKEY or PERMNO, for example), or different data or options related to the same key. Each time you click Execute the new output is saved in the history. Then, by clicking Save History, you can save all the output to one text file.

Output To File

You can also save data in a number of external file formats. When you choose a non-screen format, the Output File Name field becomes active and is required.

Click the Browse button to select a location to save the file.

Supported File Formats

Pipe(|) Delimited, For Import - pipe-delimited ASCII text, suitable for importing into databases or software packages not directly supported by CRSPSift. This option generates a single output file.

Excel 97-2003 Workbook (*.xls) - Excel version 8.

Note: Excel version 8 has a row limit of 65536 total rows. If a query generates more than that number of rows, the output will be truncated at the row limit.

  • ‚Excel 2007 Workbook (*.xlsx) - Excel XML
  • MAT-files (*.mat) - Matlab version 7
  • SAS Dataset (*.sas7bdat) - SAS version 7
  • Stata File (*.dta)
  • SPSS File (*.sav)

Viewing Output Files

If you select a non-screen output format, CCMQuery will generate one or more output files, one for each unique output of the query. When the query completes execution, the output area displays links to the generated output files. Each file will have the name specified in the Output File Name field, with the addition of an extension identifying the type of output it contains (for example, “fd” for Filing Dates data, or “adjf” for Adjustment Factors data).

Clicking one of the output files links opens the File Download dialog box.

Click Open to open the application and output file to which the data items were written.

Output File Suffixes

The following table lists output file suffixes and the type of data they indicate is contained in a file.

Data Category Data Group Name Output File Suffix
Additional Descriptions Adjustment Factors adjf
Additional Descriptions Company History hcom
Additional Descriptions Company History - CST hcst
Additional Descriptions Company Industry Pres ipcd
Additional Descriptions Company Officer Titles off
Additional Descriptions Filing Dates fd
Additional Descriptions GICS History hgic
Annual Data Index Per Desc - Annl xades
Audit Data Audit Data - Annl aaud
Audit Data Audit Data - Qtr iaud
CCM - Common Subsets Company Summary cos
CCM - Common Subsets Period Summary - Annl pa
CCM - Common Subsets Period Summary - Qtr pq
Company Market Data Fortune 500 Data fo
Company Market Data Market Data - Annl amkt
Company Market Data Market Data - Qtr imkt
Description and Links CCM Header and Ranges mstr
Description and Links Company Description com
Description and Links Company Security List secl
Description and Links Index Header in
Description and Links Index Header - pre GICS spind
Description and Links Link History link
Description and Links Link Used ulink
Description and Links Linked Used Ranges lnkrng
Operating Segment Segment Currency test_sr
Operating Segment Segment Customer test_sc
Operating Segment Segment Detail test_sd
Operating Segment Segment Geographic Area test_sg
Operating Segment Segment Item test_sm
Operating Segment Segment NAICS test_sy
Operating Segment Segment Product test_sp
Operating Segment Segment Source test_ss
Quarterly Data Index Per Desc - Qtr test_xqdes
Security Data Security - Constituents test_hidx
Security Data Security - Dividend FN test_divfn
Security Data Security - Header test_sec
Security Data Security - Header Hist test_hsec
Security Data Security - S&P test_sind
Security Data Security - S&P Constit test_idx
Security Data Security - Split Ev FN test_sptfn
Security Data Security - Split Events test_spt