IndQuery is used to access index header and time-series data.
Locate the IndQuery toolbar below the main CRSPSift toolbar.
First select whether your query will use the Daily or Monthly database from the Database menu.
Specify a date range for your query. Enter dates using one of the following formats: YYYYMMDD, 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
1994
19940615
all data from the beginning of 1994 until June 15, 1994
19961231
(empty)
data only on the date December 31, 1996
Index Selection
In the Choose Indno Type drop-down menu, specify whether your query will access a Single Series (an individual index) or a Group of Portfolios. Note that Single Series is for use only with a Stock and Index Database. If you know the INDNO of an index, you can type it directly in the Enter INDNO box. Otherwise, click the Find… button to open the Find INDNO window. If your query will access an individual daily index, make sure you have selected Single Series from the INDNO Type menu.
The Find INDNO window will appear as shown below.
From the Available Index Groups menu, select the index group that contains the index you want. Click the table row containing your target index, and then click the Select button. That index’s INDNO will now appear in the Enter INDNO box.
If you are using the Daily database, and you selected Group of Portfolios from the INDNO Type menu, the Find INDNO window will instead look like this:
Simply click to choose the index group you want, and then click the Select button. The index group’s INDNO will appear in the Enter INDNO box.
You may also choose to access all indexes in the database, or those that you specify in an input file. To search all indexes, click All Indexes in the Index Selection area. To use an input file, create a text file and enter the INDNO of each index your query will use, one per line. For example, to specify that your query will access the CRSP NYSE Value-Weighted Market Index (INDNO 1000000) and the CRSP NYSE American Value-Weighted Market Index (INDNO 1000020), create and save the following two line text file:
1000000
1000020
Click Input File in the Index Selection area, and then click the Browse… button to locate the input file you created.
Header Identification
In the Header tab, choose what header information you want at the top of your query report. Options include standard index header information, header information with date ranges in YYYYMMDD format, or no header information.
Time Series Data
In the Time Series tab, you can select the time series data items that are relevant to your query. You can select individual data items, choose pre-defined groups of items, or supply a list of data items in an input file.
Individual Items
Select an item from the Available Items list at left, and then click the right arrow to move it to the Selected Items list.
Pre-Defined Item Groups
Alternatively, you can select pre-defined groups of data items.
Portfolio Group Options
The Portfolio Group Options is for use only with a Stock and Index Database. Its tab is enabled only if you have selected Group of Portfolios from the INDNO Type menu. Here you can select portfolio range and rebalancing options for your query.
Output Options
You can save the output of an IndQuery in one of several formats. Click the Output tab to show the Output Options panel.
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 IndQuery 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 to a text file or to clear it away. Clicking the History tab shows you the results 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 entire session. You can select a new index, or different data or options related to the same index. 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 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 select a location to save the file.
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
If you select a non-screen output format, IndQuery 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 output 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, however, 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
IndQuery is used to access index header and time-series data.
Locate the IndQuery toolbar below the main CRSPSift toolbar.
First select whether your query will use the Daily or Monthly database from the Database menu.
Specify a date range for your query. Enter dates using one of the following formats: YYYYMMDD, YYYYMM, or YYYY. The following table shows some examples of how to use the various formats.
Index Selection
In the Choose Indno Type drop-down menu, specify whether your query will access a Single Series (an individual index) or a Group of Portfolios. Note that Single Series is for use only with a Stock and Index Database. If you know the INDNO of an index, you can type it directly in the Enter INDNO box. Otherwise, click the Find… button to open the Find INDNO window. If your query will access an individual daily index, make sure you have selected Single Series from the INDNO Type menu.
The Find INDNO window will appear as shown below.
From the Available Index Groups menu, select the index group that contains the index you want. Click the table row containing your target index, and then click the Select button. That index’s INDNO will now appear in the Enter INDNO box.
If you are using the Daily database, and you selected Group of Portfolios from the INDNO Type menu, the Find INDNO window will instead look like this:
Simply click to choose the index group you want, and then click the Select button. The index group’s INDNO will appear in the Enter INDNO box.
You may also choose to access all indexes in the database, or those that you specify in an input file. To search all indexes, click All Indexes in the Index Selection area. To use an input file, create a text file and enter the INDNO of each index your query will use, one per line. For example, to specify that your query will access the CRSP NYSE Value-Weighted Market Index (INDNO 1000000) and the CRSP NYSE American Value-Weighted Market Index (INDNO 1000020), create and save the following two line text file:
1000000
1000020
Click Input File in the Index Selection area, and then click the Browse… button to locate the input file you created.
Header Identification
In the Header tab, choose what header information you want at the top of your query report. Options include standard index header information, header information with date ranges in YYYYMMDD format, or no header information.
Time Series Data
In the Time Series tab, you can select the time series data items that are relevant to your query. You can select individual data items, choose pre-defined groups of items, or supply a list of data items in an input file.
Individual Items
Select an item from the Available Items list at left, and then click the right arrow to move it to the Selected Items list.
Pre-Defined Item Groups
Alternatively, you can select pre-defined groups of data items.
Portfolio Group Options
The Portfolio Group Options is for use only with a Stock and Index Database. Its tab is enabled only if you have selected Group of Portfolios from the INDNO Type menu. Here you can select portfolio range and rebalancing options for your query.
Output Options
You can save the output of an IndQuery in one of several formats. Click the Output tab to show the Output Options panel.
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 IndQuery 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 to a text file or to clear it away. Clicking the History tab shows you the results 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 entire session. You can select a new index, or different data or options related to the same index. 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 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 select a location to save the file.
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
If you select a non-screen output format, IndQuery 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 output 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, however, 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.