MutQuery: US Survivor-Bias-Free Mutual Fund Database Access
MutQuery is used to extract data from the CRSP US Survivor-Bias-Free Mutual Funds Database. Access is designed for Fund data, cross-referencing securities with funds, and for holdings company information. A subsequent release of Sift will also allow for getting to fund information through a security. Locate the MutQuery toolbar below the main CRSPSift toolbar.
The menu bar for MutQuery is like that of most other Sift tools. The default date range is set for the most recent three months up through the end date of the database. Specify a date range by entering any 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
19940613
all data from the beginning of 1994 until June 15, 1994
19961231
(empty)
data only on the date December 31, 1996
Security Selection
From the Choose Selection Type drop-down menu in the Mutual Fund Selection area, specify the key, such as CRSP FUNDNO or CRSP COMPANY KEY, for use in your query. In the next version of Sift, the key type that you select will limit your data item selection to those items that are relevant to the key.
Next, identify the issue or issues of interest. If you know an identifier, you can enter it directly. If you do not know an identifier, click the Find… button to open the Search window. Searches for data in the Mutual Funds Database are limited to CRSP FUNDNO, CRSP PORTNO, and CRSP COMPANY KEY for use as primary key selections.
Use the search form to locate a particular fund, portfolio, or company in the database. Results of your search criteria appear in a table, initially sorted by key – FUNDNO for funds and portfolios, and CRSP COMPANY KEY for securities. 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 entity that you want, click its row in the table, and then click the Select button. The key of that entity 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 Mutual Fund Selection area. To use an input file, create a plain text file with an entry for each issue, one per row, using the active key of your choice. Select the key type that from the Active Key menu that corresponds to that used in your input file and click Input on File. Click the Browse… button to locate the file you created.
Data Items
Data can be selected as either individual items going through the Data Items tab, or as preselected groups of logically categorized data by using the Data Groups tab.
Select/Edit Data Items
Clicking on the Select/Edit Data Items tab launches the interface for selecting individual items in MutQuery.
The MutQuery 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., “fee”) to find items of interest. Browsing and searching result in data items appearing in the Search Results area of the window.
Note: A future version of the Mutual Funds database will allow for context when selecting items. Depending on the access key that is selected – FUNDNO, PORTNO, or COMPANY KEY, only those categories of data items that are applicable to the key will be available for selection.
Important to note for this release, COMPANY KEY access is limited to the Company Info Category located under Holdings. Through PORTNO, access is available for Holdings detail data. FUNDNO allows acces to all mutual funds items, summary data, time series, and Portno mappings.
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.
Select each item you want in your query, and 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 quer y. Click cancel to exit the MutQuer y 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.
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 clusters of related event and time series data items.
Available groups include:
Mutual Fund Data
Contact Information – Management Company contact information
Dividends – Distribution type and amount, reinvestment NAV history
Front Load – Load amount, percentage, relevant date ranges
Fund Fee – Fees and ratios history
Fund Header – Descriptors and flags, Manager, Advisor
Fund Header History – History of Descriptors and flags, Manager, Advisor
Fund Style – Flags and classification codes
Master Items – Identifier codes, date ranges for daily and monthly data
Rear Load – Load type, dollar amount, withdrawal fees data
Annual and Quarterly Summary – Snapshots of fund performance-related data, Fund makeup by percentage
Daily and Monthly Time Series – Daily NAVs and returns, Monthly NAVs, TNA, returns
CRSP Portno Map – Identifier mapping of portfolios associated with a fund with begin and end dates
Portfolio Data
Holdings Detail – Listing of fund holdings, their ranks within the fund, market value, shares held, % of fund TNA
Company Data
Company Information - Security identifiers, non- equity coupon rates and maturity dates
Output Options
You can save the output of a MutQuery 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.
Helpful Hint: Many queries on the mutual funds database can result in large files. Consider output format when extracting data. The screen default is appropriate for individual funds. Excel 2007 will be acceptable for many queries, but any query that is extracting a large universe of funds over a long date range will quickly exceed Excel row limits. SAS is CRSP’s recommended output format for large query results.
Screen output appears in the output area at the bottom of the MutQuery 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 query 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 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.
Notes about supported Formats
Many queries on the mutual funds database result in large files. Consider output format when extracting data.
The screen default is appropriate for individual funds.
Pipe-deliimited ASCII text is suitable for importing into databases or software packages not directly supported by CRSPSift. This option generates a single output file.
Excel 97-2003 (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 will be acceptable for many queries, but any query that is extracting a large universe of funds over a long date range will quickly exceed Excel row limits and will truncate the results. SAS is CRSP’s recommended output format for large query results.
Viewing Output Files
With the exception of the Pipe Delimited option, if you select a non-screen output format, MutQuery will generate one or more output files, one for each unique data type layout. When the query completes execution, the hopper displays links to the files that were 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
MutQuery is used to extract data from the CRSP US Survivor-Bias-Free Mutual Funds Database. Access is designed for Fund data, cross-referencing securities with funds, and for holdings company information. A subsequent release of Sift will also allow for getting to fund information through a security. Locate the MutQuery toolbar below the main CRSPSift toolbar.
The menu bar for MutQuery is like that of most other Sift tools. The default date range is set for the most recent three months up through the end date of the database. Specify a date range by entering any one of the following formats: YYYYMMDD, YYYYMM, or YYYY. The following table provides examples of how to use the various formats.
Security Selection
From the Choose Selection Type drop-down menu in the Mutual Fund Selection area, specify the key, such as CRSP FUNDNO or CRSP COMPANY KEY, for use in your query. In the next version of Sift, the key type that you select will limit your data item selection to those items that are relevant to the key.
Next, identify the issue or issues of interest. If you know an identifier, you can enter it directly. If you do not know an identifier, click the Find… button to open the Search window. Searches for data in the Mutual Funds Database are limited to CRSP FUNDNO, CRSP PORTNO, and CRSP COMPANY KEY for use as primary key selections.
Use the search form to locate a particular fund, portfolio, or company in the database. Results of your search criteria appear in a table, initially sorted by key – FUNDNO for funds and portfolios, and CRSP COMPANY KEY for securities. 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 entity that you want, click its row in the table, and then click the Select button. The key of that entity 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 Mutual Fund Selection area. To use an input file, create a plain text file with an entry for each issue, one per row, using the active key of your choice. Select the key type that from the Active Key menu that corresponds to that used in your input file and click Input on File. Click the Browse… button to locate the file you created.
Data Items
Data can be selected as either individual items going through the Data Items tab, or as preselected groups of logically categorized data by using the Data Groups tab.
Select/Edit Data Items
Clicking on the Select/Edit Data Items tab launches the interface for selecting individual items in MutQuery.
The MutQuery 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., “fee”) to find items of interest. Browsing and searching result in data items appearing in the Search Results area of the window.
Note: A future version of the Mutual Funds database will allow for context when selecting items. Depending on the access key that is selected – FUNDNO, PORTNO, or COMPANY KEY, only those categories of data items that are applicable to the key will be available for selection.
Important to note for this release, COMPANY KEY access is limited to the Company Info Category located under Holdings. Through PORTNO, access is available for Holdings detail data. FUNDNO allows acces to all mutual funds items, summary data, time series, and Portno mappings.
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.
Select each item you want in your query, and 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 quer y. Click cancel to exit the MutQuer y 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.
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 clusters of related event and time series data items.
Available groups include:
Mutual Fund Data
Portfolio Data
Company Data
Output Options
You can save the output of a MutQuery 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.
Helpful Hint: Many queries on the mutual funds database can result in large files. Consider output format when extracting data. The screen default is appropriate for individual funds. Excel 2007 will be acceptable for many queries, but any query that is extracting a large universe of funds over a long date range will quickly exceed Excel row limits. SAS is CRSP’s recommended output format for large query results.
Screen output appears in the output area at the bottom of the MutQuery 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 query 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 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.
Notes about supported Formats
Many queries on the mutual funds database result in large files. Consider output format when extracting data.
The screen default is appropriate for individual funds.
Pipe-deliimited ASCII text is suitable for importing into databases or software packages not directly supported by CRSPSift. This option generates a single output file.
Excel 97-2003 (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 will be acceptable for many queries, but any query that is extracting a large universe of funds over a long date range will quickly exceed Excel row limits and will truncate the results. SAS is CRSP’s recommended output format for large query results.
Viewing Output Files
With the exception of the Pipe Delimited option, if you select a non-screen output format, MutQuery will generate one or more output files, one for each unique data type layout. When the query completes execution, the hopper displays links to the files that were 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.