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 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.
Subscription Information
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.
Next, from the Date Display menu, choose either Fiscal Period (the default) or Calendar.
Company Selection
Entities in the CCM Database are accessed by:
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.
Keyset Number
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.
ACCTSTDDESC
ACQMETHDESC
BSPRDESC
COMPSTDESC
CONVAL
CONVALCD
CONVALDESC
ISOCNTRYCDDESC
DATCDDESC
DATAFMTDESC
EXRATTPDDESC
POPSRC
FND_POPSRC
FND_FNDESC
FNDESC
AUOPICDESC
INDFMTDESC
IPCDDESC
ISALRTDESC
TPCIDESC
INDEXVAL
IDXVALCD
IDXVALDESC
CONSOLDESC
HCAL_DATADATE
IDXCAT
IDXIDDESC
SUBTYPE
SUBTYPECD
SUBTYPEDESC
NOTETYPEDESC
OSODESC
OGMDESC
OFCDDESC
DLRSNDESC
STALTDESC
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.
AUOPDESC
AUDESC
SCFDESC
SRCDESC
SRCQDESC
SPSECDESC
EXCHGDESC
FORISTAT
FORIDESC
GICSTAT
GICDESC
SPIISTAT
SPIIDESC
ISMODDESC
INVVALDESC
NAICSTAT
NAICSDESC
PRCSTDDESC
SICSTAT
SICDESC
SPINDDESC
SPMISTAT
SPMIDESC
STKODESC
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
Daily Exchange Rate
Monthly Exchange Rate
Monthly Exchange Rate Averages
Country Economic Indicator Data
Economic Indicator Data
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.
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.