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.
|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.
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:
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.
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 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.
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.
Output represents the item’s keyset using a keyset number assigned by CRSP.
Compustat Expanded Keyset
Each component of the item’s keyset is broken out into its own column in output.
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|
|Balance Sheet Presentation||BSPR||
|Country||FIC, LOC, EXCNTRY||
|Exchange Rate Type||EXRATTPD||
|Internal Control Auditor Opinion||AUOPIC||
|Issue Status Alert||STALT||
|Level of Consolidation||CONSOL||
|Officer SOX Certification||CEOSO, CFOSO||
|Oil & Gas Method||OGM||
|Research Company Reason for Deletion||DLRSN||
|State / Province||STATE, INCORP||
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.
|Cash Flow Format||SCF||
|Source Document (Quarterly)||SRCQ||
|S&P Economic Sector||SPCSEC||
|GICS||GGROUP, GIND, GSECTOR, GSUBIND||
|S&P Industry Index||SPII||
|Income Statement Model||ISMOD||
|S&P Industry Sector||SPIND||
|S&P Major Index||SPMI||
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.
|ISOCURCD||ISO Currency Code|
|ISOCURBD||Currency Birth Date|
|ISOCURDD||Currency Death Date|
|ISOCURLNK||Currency Link Code|
|ISOCURTR||Currency Tier Number|
Daily Exchange Rate
|EXRATD||Daily Exchange Rate|
Monthly Exchange Rate
|EXRATM||Monthly Exchange Rate|
Monthly Exchange Rate Averages
|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
|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)|
|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|
|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|
|PPI||Producer Price Index|
|PRIME||Prime Interest Rate|
|RAWMAT||Raw Material Price Index|
|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|
|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)|
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.
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.
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 version 2007
- 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|