StkQuery is used to extract CRSP stock header, event, and time-series data items. Designed primarily to access data on an individual security, it can also access a list of securities supplied in an input file, or all securities in the database.
Locate the StkQuery toolbar below the main CRSPSift toolbar.
In the Database field, select which CRSP database your query will use, either Daily or Monthly. Next, specify a date range. Enter dates using one of the following formats: YYYYMMDD, YYYYMM, or YYYY. The following table provides 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
1994
19940615
all data from the beginning of 1994 until June 15, 1994
19961231
(empty)
data only on the date December 31, 1996
By default, StkQuery returns data from the last three months of the CRSP database being accessed.
Security Selection
From the Choose Selection Type drop-down menu in the Security Selection area, specify the key, such as PERMNO or Active Ticker symbol, your query will use.
Next, identify the issue or issues of interest. If you know an identifier, you can enter it directly. For example, the ticker symbol of Apple Computer is AAPL. Given that knowledge, you can set the key to Active Ticker and type AAPL as the identifier.
If you do not know an identifier, click the Find… button to open the Company Search window.
Use the search form to locate a particular company in the CRSP database. Companies matching your search criteria appear in a table, initially sorted by PERMNO. If your search yields many matches, click the column labels to resort 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 PERMNO of that company will appear in the identifier field.
You may also choose to access all issues in the database, or issues you specify in an input file. To search all issues, click All Issues in the Security Selection area. To use an input file, first create a plain text file (using the Microsoft Windows Notepad accessory, for instance) with an entry for each issue, one per row, using the active key of your choice.
For example, to search for Apple and Microsoft by PERMNO using an input file, create and save the following two line text file:
14593
10107
Next, select PERMNO from the Active Key menu. Click Input File in the Security Selection area, and then click the Browse... button to locate the file you created.
Header Identification
Click the Header tab if it is not the active tab. In this screen, select the stock header information that you want in your report and how it should appear.
Data Items
Use the Data Items tab to control what individual event and time series data items your query will use.
Select/Edit Data Items
Clicking this button launches the interface for selecting individual event and time series items in StkQuery.
The StkQuery Data Items window is divided into several areas.
Items are divided into logical groups in the Navigate/ Search area.
Clicking the name of a group causes its data items to appear in the Search Results area. Or, rather than browsing by category, click the Search tab. There you can enter a descriptive keyword (e.g., “NASDAQ”) to find items of interest. Browsing and searching both cause the data items found to appear in the Search Results area of the window.
When you highlight an item in the Search Results area, its attributes and a short text description appear in the window areas to the right.
Global Options
StkQuery allows users to modify output data items by changing default parameters for certain types of items. Defaults can be modified for all items selected in a query by defining parameters in the Global Options table on the Data Items screen.
Parameter
Default
Definition
AdjDate
Default is to adjust values through the latest date of the database.
The adjustment date is the anchor date on which all data are presented as they were reported and are adjusted back in time by selected or default factors from that point.
AdjTyp
Default is to apply all factors
The adjustment type value represents the factors that are applied to price adjustments. Choices are 1 = Apply All Factors or 2 = Apply Stock Splits and Dividends.
AdjTypes
Default is to apply stock splits and dividends
The adjustment types values represents the factors that are applied to shares and volume adjustments. Choices are 1 = Apply All Factors or 2 = Apply Stock Splits and Dividends
BaseAmount
Default is 100
The base amount is the amount to be reported on a specified base. For use with Time Series items Total Return Index Level and Price Index Level.
BaseDate
If specifying a base amount, a base date must be provided for the date to apply the amount. Values before and after the specified date will be indexed to the amount.
GapRule
Default is to set values to missing upon exceeding the specified gap
Gap rule defines when and if to introduce missing values when a specified number of periods passed with no new data. Choices are 0 = Continue data after the gap and 1 = set missing values after the gap
GapWindow
Default is 10
The gap window defines the maximum number of dates allowed to pass between two dates where the previous value of an item remains valid. For example, with the default of 10, a price from up to ten trading dates ago is eligible for use in the returns calculation if there are no more current prices. Once that 10th day has passed, using the default gap rule, the price is considered missing and will no longer be available for use in the returns calculation.
RightsRule
Default is to use Share History
The rights rule is used to apply share factors from rights distributions. Choices are 0 = Use shares history, 1 = Recreate shares history.
ValidExch
Default is all exchanges
Valid exchange identifies the exchanges of interest to be used. Choices can be modified by clicking on the text box and checking/unchecking available exchanges. Valid exchanges is used in conjunction with the calculated returns items, cret and cretx.
Fields that can be edited are parameters in bold, black text associated with adjusted values. These are set to default values and may be modified.
Select each item you want in your query, adjust its parameters if necessary, and then click the Add to Query button to add it to the Query Items area below the Search Results.
Items in the Query Items area appear in the order that they will appear in your query’s output. To adjust the position of an item, select it and click the blue up and down arrows to move it up or down the list. The other buttons are:
Click the OK button to confirm the addition of the selected items to your query. Click cancel to exit the StkQuery Data Items window without making any changes.
Import Data Item File...
Clicking this button allows you to import a list of data items into your query from a text file. The file contains a single column of item mnemonics item IDs.
Export to File...
Use this option to output the Selected Data Items list to a text file containing each item’s mnemonic itemid. This text file may then be imported into other queries using the Import Data Item File... button.
Data Groups
The Data Groups tab allows you to select combinations of related event or time series data items.
Available groups include:
Event Data
Adjusted Delistings
Adjusted Distributions
Adjusted Shares
Delisting History
Distribution History
Group Inclusion
NASDAQ History
Name History
Name History - Short
Portfolio History
Raw Shares History
Shares History
Time Series Data
Adjusted Market Summary
Adjustted Price, Shares
Market Summary
Price and Index Levels
Price and Returns
Price and Shares
PORTFOLIOS
The Portfolios screen is applicable for Stock and Index Databases only. It provides a list of predefined portfolios. Choose one or more of them to view the performance of an issue or issues over time, using the selected portfolios as benchmarks. The data frequency — daily or monthly — will determine the list of available portfolios.
Output Options
You can save the output of an StkQuery in one of several formats. Click the Output tab to show the Output Options panel.
Output To Screen
The default option is text-based output, formatted to fit the screen width specified in the Width control. 80 characters is the default output width.
Screen output appears in the output area at the bottom of the StkQuery 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 StkQuery session was launched. 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 new security, or different data or options related to the same security. Each time you click Execute the new output is saved in the History tab. By clicking Save History, you can save all the output to one text file.
Output To File
You can also save query output in a number of external file formats. When you choose a non-screen output format, the Output File Name field becomes active. In order to successfully execute the query, you must select a location to store the output file. Click the Browse button to do so.
Supported File Formats
Pipe(|) Delimited, For Import - pipe-deliimited ASCII text, suitable for importing into databases or software packages not directly supported by CRSPSift. This option generates a single output file.
Excel 2007 Workbook (*.xlsx) - Excel version 2007 NOTE: This Excel version has a row limit of 1,048,576 total rows. If a query generates more than that number of rows, the output will be truncated at the row limit.
MAT-files (*.mat) - Matlab version 7
SAS Dataset (*.sas7bdat) - SAS version 7
Stata File (*.dta)
SPSS File (*.sav)
Viewing Output Files
With the exception of the Pipe Delimited option, if you select a non-screen output format, StkQuery 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 output files generated. Each file will have the name specified in the Output File Name field, with the addition of an extension identifying the type of data it contains.
Clicking one of the output files links opens the File Download dialog box.
Click Open to open the output file in the appropriate application (Excel 2007, in the example shown below).
Alternatively, you can click the View Output button, located in the CRSPSift toolbar, to view the output files generated by your query.
If a query generated only one file, the target application will open the file directly, as shown above. If the query resulted in multiple output files instead of opening the files, CRSPSift will open a Windows Explorer window showing the folder where you chose to save output.
Output File Suffixes
The following table lists output file suffixes and the type of data they indicate is contained in a file.
Subscription Information
StkQuery is used to extract CRSP stock header, event, and time-series data items. Designed primarily to access data on an individual security, it can also access a list of securities supplied in an input file, or all securities in the database.
Locate the StkQuery toolbar below the main CRSPSift toolbar.
In the Database field, select which CRSP database your query will use, either Daily or Monthly. Next, specify a date range. Enter dates using one of the following formats: YYYYMMDD, YYYYMM, or YYYY. The following table provides examples of how to use the various formats.
By default, StkQuery returns data from the last three months of the CRSP database being accessed.
Security Selection
From the Choose Selection Type drop-down menu in the Security Selection area, specify the key, such as PERMNO or Active Ticker symbol, your query will use.
Next, identify the issue or issues of interest. If you know an identifier, you can enter it directly. For example, the ticker symbol of Apple Computer is AAPL. Given that knowledge, you can set the key to Active Ticker and type AAPL as the identifier.
If you do not know an identifier, click the Find… button to open the Company Search window.
Use the search form to locate a particular company in the CRSP database. Companies matching your search criteria appear in a table, initially sorted by PERMNO. If your search yields many matches, click the column labels to resort 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 PERMNO of that company will appear in the identifier field.
You may also choose to access all issues in the database, or issues you specify in an input file. To search all issues, click All Issues in the Security Selection area. To use an input file, first create a plain text file (using the Microsoft Windows Notepad accessory, for instance) with an entry for each issue, one per row, using the active key of your choice.
For example, to search for Apple and Microsoft by PERMNO using an input file, create and save the following two line text file:
14593
10107
Next, select PERMNO from the Active Key menu. Click Input File in the Security Selection area, and then click the Browse... button to locate the file you created.
Header Identification
Click the Header tab if it is not the active tab. In this screen, select the stock header information that you want in your report and how it should appear.
Data Items
Use the Data Items tab to control what individual event and time series data items your query will use.
Select/Edit Data Items
Clicking this button launches the interface for selecting individual event and time series items in StkQuery.
The StkQuery Data Items window is divided into several areas.
Items are divided into logical groups in the Navigate/ Search area.
Clicking the name of a group causes its data items to appear in the Search Results area. Or, rather than browsing by category, click the Search tab. There you can enter a descriptive keyword (e.g., “NASDAQ”) to find items of interest. Browsing and searching both cause the data items found to appear in the Search Results area of the window.
When you highlight an item in the Search Results area, its attributes and a short text description appear in the window areas to the right.
Global Options
StkQuery allows users to modify output data items by changing default parameters for certain types of items. Defaults can be modified for all items selected in a query by defining parameters in the Global Options table on the Data Items screen.
Fields that can be edited are parameters in bold, black text associated with adjusted values. These are set to default values and may be modified.
Select each item you want in your query, adjust its parameters if necessary, and then click the Add to Query button to add it to the Query Items area below the Search Results.
Items in the Query Items area appear in the order that they will appear in your query’s output. To adjust the position of an item, select it and click the blue up and down arrows to move it up or down the list. The other buttons are:
Click the OK button to confirm the addition of the selected items to your query. Click cancel to exit the StkQuery Data Items window without making any changes.
Import Data Item File...
Clicking this button allows you to import a list of data items into your query from a text file. The file contains a single column of item mnemonics item IDs.
Export to File...
Use this option to output the Selected Data Items list to a text file containing each item’s mnemonic itemid. This text file may then be imported into other queries using the Import Data Item File... button.
Data Groups
The Data Groups tab allows you to select combinations of related event or time series data items.
Available groups include:
Event Data
Time Series Data
PORTFOLIOS
The Portfolios screen is applicable for Stock and Index Databases only. It provides a list of predefined portfolios. Choose one or more of them to view the performance of an issue or issues over time, using the selected portfolios as benchmarks. The data frequency — daily or monthly — will determine the list of available portfolios.
Output Options
You can save the output of an StkQuery in one of several formats. Click the Output tab to show the Output Options panel.
Output To Screen
The default option is text-based output, formatted to fit the screen width specified in the Width control. 80 characters is the default output width.
Screen output appears in the output area at the bottom of the StkQuery 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 StkQuery session was launched. 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 new security, or different data or options related to the same security. Each time you click Execute the new output is saved in the History tab. By clicking Save History, you can save all the output to one text file.
Output To File
You can also save query output in a number of external file formats. When you choose a non-screen output format, the Output File Name field becomes active. In order to successfully execute the query, you must select a location to store the output file. Click the Browse button to do so.
Supported File Formats
NOTE: This Excel version has a row limit of 1,048,576 total rows. If a query generates more than that number of rows, the output will be truncated at the row limit.
Viewing Output Files
With the exception of the Pipe Delimited option, if you select a non-screen output format, StkQuery 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 output files generated. Each file will have the name specified in the Output File Name field, with the addition of an extension identifying the type of data it contains.
Clicking one of the output files links opens the File Download dialog box.
Click Open to open the output file in the appropriate application (Excel 2007, in the example shown below).
Alternatively, you can click the View Output button, located in the CRSPSift toolbar, to view the output files generated by your query.
If a query generated only one file, the target application will open the file directly, as shown above. If the query resulted in multiple output files instead of opening the files, CRSPSift will open a Windows Explorer window showing the folder where you chose to save output.
Output File Suffixes
The following table lists output file suffixes and the type of data they indicate is contained in a file.