TsQuery is designed to generate output from a combination of database frequencies, and varying types of data into different formats and layouts that serve a range of purposes. Click the New Query button on the CRSPSift toolbar. Select the Data Environment you wish to use and highlight the TsQuery icon. Optionally give your query a name in the Query Name field, and then click the OK button. You may now fill out the screens required to define your query: entities, data items, date information, and report layout.
Entities
In the Entities screen, you select which securities, indexes, or portfolios your query will use. To add an entity, select an entity type from the drop-down menu and click the New button.
List, the default, is meant for entering a single security, or a small number of securities, manually.
List All calls the full universe of securities in the database selected.
List File accesses securities specified in a user-created input file.
Portfolio All allows the user to create an equal- or valueweighted portfolio from the full universe.
Portfolio File accepts a user-created input file of securities in a portfolio.
List
Once the Entity Type Selection is made, a series of options become available. Under List, a menu of identifier keys is available from the drop down menu. Next, identify the issue/issues of interest. If you know the identifier, you may type it in, or you may click on Find... to do a search.
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/companies you want, click its row in the table, and then click the Select button. To select more than one company, hold down the Ctrl key while making selections. The PERMNO of that company will appear in the identifier field.
Date Options
There are two options on the Entities screen for restricting query output by date: Date Range and Event Date. The Date Options allow you to target date ranges or single dates specific to an entity.
Date Options selected on the Entities screen are further tied to the Date Range option on the Date tab. If Date Range is selected on the Entites tab, a Fixed Date Range must be specified on the Date tab. Likewise, an Event Date must be paired with a Relative Date Range on the Date tab.
Date Range
If given, a Date Range must include both beginning and ending dates. Dates may be in YYYYMMDD, YYYYMM, or YYYY format. For formats that do not specify months or days, the beginning date in the range will start with the first period within the specified range. The ending date will be the last period in the range.
For example, the output of a query with Entity Date Range 200605 - 200703 would include daily data from May 1, 2006 through March 31, 2007, or monthly data from May 31, 2006 through March 31, 2007.
The valid query output is the union of the security’s trading history, the Date Range specified on the Entities tab, and the Fixed Date Range given on the Date tab. The Entity Date Range must fall within the Fixed Date Range. Note that the Entity Date Range must also exceed the frequency duration of the calendar selected for your output. For example, if your calendar is set to report annually, the Date Range must span more than 12 months.
Event Date
The Entity Event Date specifies a single date in YYYYMMDD. An Entity Event Date must be paired with a Relative Date Range on the Date tab. The Relative Date Range controls the number of calendar periods before and after the Event Date to report in output. Event Date does not work with indexes or portfolios.
For example, suppose you enter an Entity Event Date of 19991231. Next, in the Date tab, you select the Daily calendar and give a Relative Date Range of 2 days before and 2 days after the Entity Event Date. The query output will include data for trading days 19911229 though 19920104, including the event date.
For more information about calendars, Fixed Date Ranges, and Relative Date Ranges, refer to the section on the Date tab below.
Entity Filters
Several entity filters are available in TsQuery, allowing you to refine your selection of securities by such criteria as stock exchange, share type, and SIC code. These options are available for list and portfolio entity types but will not work with indexes.
Exchange
The Exchange Filter allows you to filter the trading history of issues on the basis of stock exchange. This option is available only when using the List and Portfolio Entity Types.
Share Type
The Share Type Filter allows you to restrict the output on the basis of share type for individual securities. The selection is based on the CRSP Share Type Code variable.
Select at least one item from both Group A and Group B. Hold the Control button while clicking to select multiple items from a group.
NASDAQ Market Tiers
When the NMSIND option is used, only NASDAQ issue ranges are restricted. The filter has no effect on data from issues trading on other exchanges.
SIC
SIC issue range restriction is applicable to List and Portfolio Entity Types. Each number represents a single SIC Code. You can filter the data to output a range of SIC values or an individual SIC value
For example, 1000-2000,3725 would extract all securities with SIC Codes between 1000 and 2000, and all with and SIC code of 3725.
Portfolio Assignment
Portfolio Assignments are only available for use with a Stock and Index Database. A Stock and Index Database subscriber has access to nine sets of daily and eight sets of monthly portfolios. Each entity in the CRSP universe may be a member of a number of CRSP portfolios, which are based on combinations of market capitalization and exchange membership or group membership.
The Port Assign Filter allows you to restrict your query to a subset of the CRSP universe, composed of your choice of portfolio sets and the portfolios within them.
To filter by portfolio assignment, select a Database Type, Daily or Monthly, and then select a portfolio set from the list of portfolio sets. Next enter a list or range of portfolios from the set. For example, to choose portfolios 1, 3, 4, 5, and 10, enter “1,3-5,10” in the Portfolio List field. Refer to Chapter 4 of the Stock and Index Data Description Guide for a list of all portfolios and sets of portfolios.
Note: Be sure to match the database type of the items you select on the Data Items tab to the database type of the portfolio you filter by here. For example, if you filter by portfolios in the Monthly NYSE Cap Assignment, select items only from the Monthly Stock & Index group on the Data Items tab.
Groups
Currently the only group defined is the S&P 500 Constituents. Selecting this filter limits output to those issues listed in the S&P 500 during the time 9 Chapter 3: Introduction CRSPSift User Guide period covered by the query. Use of Groups is limited to Stock and Index Database subscribers.
Filter Tuning
Each entity filter can be further refined by selecting one of three tuning flags. The flags are:
Keep what is valid - Keep only the trading history of an issue where the selected filter applies.
Keep none if ever invalid - Discard all data on an issue if the selected filter fails to match at any point in the time period of the query.
Keep all if ever valid - If the selected filter matches an issue at any point during the time period of the query, all data on that issue is kept.
The Entity Hopper
After setting appropriate options, click the Add button to add this entity to the hopper in the lower area of the screen.
Clicking an entity’s row in the hopper displays the options and values you have set for it.
Using this method, add as many entities as your query requires. If you wish to change the order in which entities appear in the hopper, click on a row and use the arrow buttons to move it up or down.
To go back and edit an entity click its row in the hopper, and then click the Edit button.
When you have finished editing the entity, click the Save button to save your changes. To delete an entity from the hopper, select its row and click the Remove button. The Remove All button deletes all entities that you have added.
DATA ITEMS
In the Data Items screen you can select the CRSP data items relevant to your query.
Searching and Navigating Data Items
Data items are grouped in the Navigate tab according to the database to which they belong: Daily Stock, Monthly Stock, Compustat (available to CCM Database subscribers), and Favorites. Click the plus sign next to a database to open a categorized list of data items. When you select a category, a list of the data items from that category populates the Search Results area in the central area of the screen.
In addition to navigating items by category, you can search the available data items. To find items this way, click the Search tab.
Search is limited to the database selected in the Search By menu. Additional criteria can be supplied as well, such as whether results must be an exact match for the search term, begin or end with the term, or simply contain the term.
Both mnemonic codes and full item names are searched and returned for each item. Click the column names—Item ID, Variant, Data Item Name, or Header—to change the order in which items are sorted.
Data Item Properties
Further details about an individual item are available on the far right of the screen.
After selecting a data item, if you want to keep its default settings, click the Add button to add that item to the hopper below. Otherwise, click the value of a setting you wish to change, modify the settings, and then click Add. Values that appear in bold type are editable. Changing default settings affects the item report formatting.
Data Length
CRSP recommends keeping the Data Length field consistent with the Fixed Width so that the item is not truncated. Data Length is based on the values in the underlying data items.
Data Type
CRSP recommends thatData Type not be edited. What appears in the field is what CRSP has identified to be appropriate for the underlying data item.
Fixed Width
The allotted number of characters that an item will be given in the output file. Fixed Width must be greater than or equal to the Data Length for the requested item. Note that expanding Fixed Width does not increase a data item’s numeric precision.
Header
If column headers are used, more descriptive names may replace the default itemids.
Item Lag
Used to either lead or lag a data item. A positive number of periods will lag the data by the number of periods designated in the output calendar, while a negative number will lead the data item. For example, given a December 2006 date in an output file, prices requested with a two month lag will return the October 2006 values, and prices requested with a two month lead will return prices for February 2007. 11 Chapter 3: Introduction CRSPSift User Guide
INDNO/SUBNO
Data items related to an index or portfolio require more specific information before they can be added to the item hopper - a particular INDNO value for an index, or a particular SUBNO value for a portfolio. When these items are selected, red text instructing you how to refine your selection appears above the Data Item Settings area.
Click on the INDNO or SUBNO to open the list of available indexes or portfolios to which the item may be related.
Repeat this process until all the data items of interest to you appear in the hopper at the bottom of the screen. As in the Entities screen, use the arrow buttons to the right of the hopper to change the order in which data items appear.
To remove a single item, select its row and click the single document button. To remove all items from the hopper, click the multiple document button.
Click this to edit an item’s attributes. Remove the selected item from your query. Remove all items from the Query Items list. 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.
To modify the settings for a data item in the hopper, select its row and click the Edit button. Change the data item settings to your liking, and then click the Save Changes button. When you change an item’s settings from the default, its entry in the hopper displays a Yes in the “Customized?” column. To cancel changes, click the Cancel button. 13 Chapter 3: Introduction CRSPSift User Guide
Global Item Options
When selecting Compustat data items using the new CCM databases, several global option settings are available. The default currency conversion option is to report data items in the currency in which they are reported. By clicking on the drop down menu, you may change this option to convert all items to US Dollars.
You can also choose to include footnotes and/or data codes for the Compustat items in your query that have them. The Footnote and Data Code items can also be extracted automatically for all items that have them by choosing Yes for the settings. This can also be done manually by selecting the individual data codes or footnotes for select data items in the Data Item Settings.
Note: Global options must be selected before choosing data items in order for them to be applied.
It is important to note that the Global Item Options apply only to Compustat data accessed from the CRSP/ Compustat Merged Database.
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.
Date
The Date screen allows you to control what dates will be used in the output for the data items selected for each entity.
Calendar Options
You can define your own set of dates for a query, or you can select one of several predefined calendars. To use a predefined calendar, click the “Calendar Name” radio button and select a calendar—Daily, Weekly, Monthly, Quarterly, or Annually—from the menu.
You may want to create a custom calendar with dates for output that are not available in one of the standard Calendar Name options. Click on the Calendar File radio button to select a user-generated calendar.
Refer to the Calendar File Wizard chapter in this guide for information on creating a custom calendar file.
Date Range
Date usage is linked to any date information entered in the Entities screen. If an event date is assigned to an issue, either in the Entities screen, or included in the list entity input file, Relative Date Range should be selected.
A relative date range specifies a window of time, in days, based on the event date chosen in the Entities screen. It is tied to the calendar selected for the output. Take, for example, an event date of October 18, 2005, with the Daily calendar selected for output. We would like to limit the results of our query to information from the five trading days preceding, but not including, the event date. To accomplish this, we first set the date range type to Relative. Then, in the Relative Event Date Range controls, we set the first parameter to five trading days before and the second parameter to one trading day before.
The query will now return information only from October 13, 2005 to October 17, 2005. Similarly, depending on your needs, you could set the range to a window of days either surrounding or after a selected event date.
Helpful hint:
When using relative dates, include Date, caldt, as an output item in your query. Without caldt, all that returns is an item’s position relative to the event date, with the event date always equal to zero.
For all queries that do not employ event dates, the default Fixed Date Range should be selected in the Date screen.
If a date range is specified in the Entities tab, Fixed Date Range acts as an umbrella to the beginning and ending dates selected, and must cover all dates requested. For example, if you used the following list entity input file, the Fixed Date Range set in the Date screen would need to span 19751230-19961231.
12490 19751230 19861001 IBM 10107 19900930 19961231 Microsoft
Optional Parameters
The Report On menu allows you to select either a Calendar Basis or Fiscal Basis for reporting. This option applies only to Compustat data items, controlling whether to report based on a security’s fiscal year or the December calendar year. Queries accessing only CRSP data report on the December calendar basis.
The Display field allows you to select specific dates or a range that falls within a fixed date range that will be displayed in the output.
Layout
On the Layout screen you specify the file type, save location, and physical layout of a query’s output.
Output Options
TsQuery can save report output in a variety of file formats. By default TsQuery saves reports as Formatted Text files with no field delimiter, which is intended to be human-readable.
Choosing a Field Delimiter
If you plan to import query output into another program for further processing, select a field delimiter from the Field Delimiter menu. A field delimiter is a marker that tells the importing program where to split a data record into its constituent data fields. Available options are Pipe (|), Comma (,), and Space ( ). You can enter your own preferred field delimiter by selecting Custom and typing a value in the text field next to the menu.
Supported File Formats
In addition to Formatted Text, TsQuery can save query output directly to any of the following file formats:
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)
Basic Formatting
In the default Formatted Text format, X, Y, and Z correspond to the Data Item, Date, and Entity information added to the query. The preview pane of the Layout screen shows an animated preview of how your report will appear, allowing you to visualize it without first having to execute the query. The preview is best used to visualize the appearance of Formatted Text (.txt) output. The appearance of output in other formats, such as Excel or Matlab, will vary according to the target application. Previewing saves time and enhances the readability of your output. If, for instance, your query uses only a few entities, you may wish to place Entity on the report’s X axis. If it uses many, you may prefer to place it along the Y or Z axis to limit the output’s physical width.
The nature of your query will determine the proper report layout. Use the controls to the left of the preview pane to test various scenarios. To see how a report will look given particular settings, modify the layout controls, and then click the Preview button. The contents of the pane will reorganize to reflect your settings, and the axes will briefly flash with color to identify themselves: X is purple, Y is green, and Z is red. It may be helpful to think of X as “rows,” Y as “columns,” and Z as “page”.
When you have found a suitable layout, click the Browse... button next to the Location and File Name field. Navigate to where you want to save your report, give the file a descriptive name, and then click the Save button. When you execute the query, CRSPSift will save the report in this file.
Advanced Layout Settings
Click the Advanced... button to access the following advanced layout settings.
Report Name
A text description that will appear at the top of the report. (Formatted Text output only)
Calendar Format
Controls formatting of the dates appearing in the output when date headers are chosen. Options include:
Format
Example
YYYYMMDD (default)
20071231
YYMMDD
071231
MM/DD/YY
12/31/07
MM/DD/YYYY
12/31/2007
DD-mmm-YYYY
31-Dec-2007
Cal-Based
2007.4 (for the quarterly calendar)
Row Delimiter
Controls the number of rows between output lines. The first integer is the number of blank lines between rows when the Z-axis value changes and impacts Z-flag options 1 and 3. The second integer is the number of blank lines between all data rows. The default is 0,0.
Character Delimiter
A character string placed before and after all character string fields—Company Name, for example—in report records. The default is no character string delimiter. For example, a value of * would cause the character string field Company Name to be surrounded by asterisks in output.
Partial Period Data
Includes partial-period data in the output. If not selected, ts_print will not include the last month of data for a company that stopped trading mid-month, because only months with end-of-month data are normally included. This option applies to monthly data.
No Fill
The No Fill option, enabled by default, causes rows outside an issue’s date range or the user’s date specification not to print to the output file. No Fill is applicable under the following conditions:
Item is chosen for the X-axis
Date is the Y-axis
Entity is the Z-axis
Z-Axis Data Flag is set to 1 or 3
Fixed Date Range is selected on the Date tab.
No Fill does not work with relative dates.
Compact
Compresses output by removing all spaces and trailing decimal zeros in numbers. Compact is ideal for producing output to be loaded into another program.
Delisting Returns
Default Missing: Outputs the default value (-88.0) for missing delisting returns for entities that have delisted during the selected dates. You must have a Returns data item selected in the Data Items tab in order to include Delisting Returns in your output.
Custom Missing: Outputs user-specified missing delisting return codes. You may assign missing values for a range of delisting codes for select beginning and ending exchanges. To do this, create a text input file containing the following fields in the following order: begin delist code, end delist code, begin exchange code, end exchange code, alternate delisting return value, alternate delisting return without dividends value.
Note that in this example, the first row would assign a -0.50 value to missing delisting returns for securities with delisting codes 200-299 that initially traded on NYSE and ended up trading on NASDAQ, and -0.55 for missing delisting returns without dividends. If your query includes a security with a missing delisting return that was not included in your input file, the default missing delisting return, -55.0, would be used instead.
Direct Edit
To use this feature, click the Direct Edit tab. Click the Edit button above the text area there, and then type or paste your query code, overwriting the template query code. When you are finished, click Save. If the syntax of your query is correct, CRSPSift automatically populates the appropriate fields in the other screens to reflect your query code.
Executing a Query
After executing a query, click the View Output button on the CRSPSift toolbar.
The File Download window opens, prompting you to open the output file in the target application or to save it in a different location.
Click Open to open the output file in the target application (Excel 2007, in the example shown below).
Subscription Information
TsQuery is designed to generate output from a combination of database frequencies, and varying types of data into different formats and layouts that serve a range of purposes. Click the New Query button on the CRSPSift toolbar. Select the Data Environment you wish to use and highlight the TsQuery icon. Optionally give your query a name in the Query Name field, and then click the OK button. You may now fill out the screens required to define your query: entities, data items, date information, and report layout.
Entities
List
Once the Entity Type Selection is made, a series of options become available. Under List, a menu of identifier keys is available from the drop down menu. Next, identify the issue/issues of interest. If you know the identifier, you may type it in, or you may click on Find... to do a search.
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/companies you want, click its row in the table, and then click the Select button. To select more than one company, hold down the Ctrl key while making selections. The PERMNO of that company will appear in the identifier field.
Date Options
There are two options on the Entities screen for restricting query output by date: Date Range and Event Date. The Date Options allow you to target date ranges or single dates specific to an entity.
Date Options selected on the Entities screen are further tied to the Date Range option on the Date tab. If Date Range is selected on the Entites tab, a Fixed Date Range must be specified on the Date tab. Likewise, an Event Date must be paired with a Relative Date Range on the Date tab.
Date Range
If given, a Date Range must include both beginning and ending dates. Dates may be in YYYYMMDD, YYYYMM, or YYYY format. For formats that do not specify months or days, the beginning date in the range will start with the first period within the specified range. The ending date will be the last period in the range.
For example, the output of a query with Entity Date Range 200605 - 200703 would include daily data from May 1, 2006 through March 31, 2007, or monthly data from May 31, 2006 through March 31, 2007.
The valid query output is the union of the security’s trading history, the Date Range specified on the Entities tab, and the Fixed Date Range given on the Date tab. The Entity Date Range must fall within the Fixed Date Range. Note that the Entity Date Range must also exceed the frequency duration of the calendar selected for your output. For example, if your calendar is set to report annually, the Date Range must span more than 12 months.
Event Date
The Entity Event Date specifies a single date in YYYYMMDD. An Entity Event Date must be paired with a Relative Date Range on the Date tab. The Relative Date Range controls the number of calendar periods before and after the Event Date to report in output. Event Date does not work with indexes or portfolios.
For example, suppose you enter an Entity Event Date of 19991231. Next, in the Date tab, you select the Daily calendar and give a Relative Date Range of 2 days before and 2 days after the Entity Event Date. The query output will include data for trading days 19911229 though 19920104, including the event date.
For more information about calendars, Fixed Date Ranges, and Relative Date Ranges, refer to the section on the Date tab below.
Entity Filters
Several entity filters are available in TsQuery, allowing you to refine your selection of securities by such criteria as stock exchange, share type, and SIC code. These options are available for list and portfolio entity types but will not work with indexes.
Exchange
The Exchange Filter allows you to filter the trading history of issues on the basis of stock exchange. This option is available only when using the List and Portfolio Entity Types.
Share Type
The Share Type Filter allows you to restrict the output on the basis of share type for individual securities. The selection is based on the CRSP Share Type Code variable.
Select at least one item from both Group A and Group B. Hold the Control button while clicking to select multiple items from a group.
NASDAQ Market Tiers
When the NMSIND option is used, only NASDAQ issue ranges are restricted. The filter has no effect on data from issues trading on other exchanges.
SIC
SIC issue range restriction is applicable to List and Portfolio Entity Types. Each number represents a single SIC Code. You can filter the data to output a range of SIC values or an individual SIC value
For example, 1000-2000,3725 would extract all securities with SIC Codes between 1000 and 2000, and all with and SIC code of 3725.
Portfolio Assignment
Portfolio Assignments are only available for use with a Stock and Index Database. A Stock and Index Database subscriber has access to nine sets of daily and eight sets of monthly portfolios. Each entity in the CRSP universe may be a member of a number of CRSP portfolios, which are based on combinations of market capitalization and exchange membership or group membership.
The Port Assign Filter allows you to restrict your query to a subset of the CRSP universe, composed of your choice of portfolio sets and the portfolios within them.
To filter by portfolio assignment, select a Database Type, Daily or Monthly, and then select a portfolio set from the list of portfolio sets. Next enter a list or range of portfolios from the set. For example, to choose portfolios 1, 3, 4, 5, and 10, enter “1,3-5,10” in the Portfolio List field. Refer to Chapter 4 of the Stock and Index Data Description Guide for a list of all portfolios and sets of portfolios.
Note: Be sure to match the database type of the items you select on the Data Items tab to the database type of the portfolio you filter by here. For example, if you filter by portfolios in the Monthly NYSE Cap Assignment, select items only from the Monthly Stock & Index group on the Data Items tab.
Groups
Currently the only group defined is the S&P 500 Constituents. Selecting this filter limits output to those issues listed in the S&P 500 during the time 9 Chapter 3: Introduction CRSPSift User Guide period covered by the query. Use of Groups is limited to Stock and Index Database subscribers.
Filter Tuning
Each entity filter can be further refined by selecting one of three tuning flags. The flags are:
Keep what is valid - Keep only the trading history of an issue where the selected filter applies.
Keep none if ever invalid - Discard all data on an issue if the selected filter fails to match at any point in the time period of the query.
Keep all if ever valid - If the selected filter matches an issue at any point during the time period of the query, all data on that issue is kept.
The Entity Hopper
After setting appropriate options, click the Add button to add this entity to the hopper in the lower area of the screen.
Clicking an entity’s row in the hopper displays the options and values you have set for it.
Using this method, add as many entities as your query requires. If you wish to change the order in which entities appear in the hopper, click on a row and use the arrow buttons to move it up or down.
To go back and edit an entity click its row in the hopper, and then click the Edit button.
When you have finished editing the entity, click the Save button to save your changes. To delete an entity from the hopper, select its row and click the Remove button. The Remove All button deletes all entities that you have added.
DATA ITEMS
In the Data Items screen you can select the CRSP data items relevant to your query.
Searching and Navigating Data Items
Data items are grouped in the Navigate tab according to the database to which they belong: Daily Stock, Monthly Stock, Compustat (available to CCM Database subscribers), and Favorites. Click the plus sign next to a database to open a categorized list of data items. When you select a category, a list of the data items from that category populates the Search Results area in the central area of the screen.
In addition to navigating items by category, you can search the available data items. To find items this way, click the Search tab.
Search is limited to the database selected in the Search By menu. Additional criteria can be supplied as well, such as whether results must be an exact match for the search term, begin or end with the term, or simply contain the term.
Both mnemonic codes and full item names are searched and returned for each item. Click the column names—Item ID, Variant, Data Item Name, or Header—to change the order in which items are sorted.
Data Item Properties
Further details about an individual item are available on the far right of the screen.
After selecting a data item, if you want to keep its default settings, click the Add button to add that item to the hopper below. Otherwise, click the value of a setting you wish to change, modify the settings, and then click Add. Values that appear in bold type are editable. Changing default settings affects the item report formatting.
Data Length
CRSP recommends keeping the Data Length field consistent with the Fixed Width so that the item is not truncated. Data Length is based on the values in the underlying data items.
Data Type
CRSP recommends thatData Type not be edited. What appears in the field is what CRSP has identified to be appropriate for the underlying data item.
Fixed Width
The allotted number of characters that an item will be given in the output file. Fixed Width must be greater than or equal to the Data Length for the requested item. Note that expanding Fixed Width does not increase a data item’s numeric precision.
Header
If column headers are used, more descriptive names may replace the default itemids.
Item Lag
Used to either lead or lag a data item. A positive number of periods will lag the data by the number of periods designated in the output calendar, while a negative number will lead the data item. For example, given a December 2006 date in an output file, prices requested with a two month lag will return the October 2006 values, and prices requested with a two month lead will return prices for February 2007. 11 Chapter 3: Introduction CRSPSift User Guide
INDNO/SUBNO
Data items related to an index or portfolio require more specific information before they can be added to the item hopper - a particular INDNO value for an index, or a particular SUBNO value for a portfolio. When these items are selected, red text instructing you how to refine your selection appears above the Data Item Settings area.
Click on the INDNO or SUBNO to open the list of available indexes or portfolios to which the item may be related.
Repeat this process until all the data items of interest to you appear in the hopper at the bottom of the screen. As in the Entities screen, use the arrow buttons to the right of the hopper to change the order in which data items appear.
To remove a single item, select its row and click the single document button. To remove all items from the hopper, click the multiple document button.
Click this to edit an item’s attributes. Remove the selected item from your query. Remove all items from the Query Items list. 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.
To modify the settings for a data item in the hopper, select its row and click the Edit button. Change the data item settings to your liking, and then click the Save Changes button. When you change an item’s settings from the default, its entry in the hopper displays a Yes in the “Customized?” column. To cancel changes, click the Cancel button. 13 Chapter 3: Introduction CRSPSift User Guide
Global Item Options
When selecting Compustat data items using the new CCM databases, several global option settings are available. The default currency conversion option is to report data items in the currency in which they are reported. By clicking on the drop down menu, you may change this option to convert all items to US Dollars.
You can also choose to include footnotes and/or data codes for the Compustat items in your query that have them. The Footnote and Data Code items can also be extracted automatically for all items that have them by choosing Yes for the settings. This can also be done manually by selecting the individual data codes or footnotes for select data items in the Data Item Settings.
Note: Global options must be selected before choosing data items in order for them to be applied.
It is important to note that the Global Item Options apply only to Compustat data accessed from the CRSP/ Compustat Merged Database.
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.
Date
The Date screen allows you to control what dates will be used in the output for the data items selected for each entity.
Calendar Options
You can define your own set of dates for a query, or you can select one of several predefined calendars. To use a predefined calendar, click the “Calendar Name” radio button and select a calendar—Daily, Weekly, Monthly, Quarterly, or Annually—from the menu.
You may want to create a custom calendar with dates for output that are not available in one of the standard Calendar Name options. Click on the Calendar File radio button to select a user-generated calendar.
Refer to the Calendar File Wizard chapter in this guide for information on creating a custom calendar file.
Date Range
Date usage is linked to any date information entered in the Entities screen. If an event date is assigned to an issue, either in the Entities screen, or included in the list entity input file, Relative Date Range should be selected.
A relative date range specifies a window of time, in days, based on the event date chosen in the Entities screen. It is tied to the calendar selected for the output. Take, for example, an event date of October 18, 2005, with the Daily calendar selected for output. We would like to limit the results of our query to information from the five trading days preceding, but not including, the event date. To accomplish this, we first set the date range type to Relative. Then, in the Relative Event Date Range controls, we set the first parameter to five trading days before and the second parameter to one trading day before.
The query will now return information only from October 13, 2005 to October 17, 2005. Similarly, depending on your needs, you could set the range to a window of days either surrounding or after a selected event date.
Helpful hint:
When using relative dates, include Date, caldt, as an output item in your query. Without caldt, all that returns is an item’s position relative to the event date, with the event date always equal to zero.
For all queries that do not employ event dates, the default Fixed Date Range should be selected in the Date screen.
If a date range is specified in the Entities tab, Fixed Date Range acts as an umbrella to the beginning and ending dates selected, and must cover all dates requested. For example, if you used the following list entity input file, the Fixed Date Range set in the Date screen would need to span 19751230-19961231.
12490 19751230 19861001 IBM
10107 19900930 19961231 Microsoft
Optional Parameters
The Report On menu allows you to select either a Calendar Basis or Fiscal Basis for reporting. This option applies only to Compustat data items, controlling whether to report based on a security’s fiscal year or the December calendar year. Queries accessing only CRSP data report on the December calendar basis.
The Display field allows you to select specific dates or a range that falls within a fixed date range that will be displayed in the output.
Layout
On the Layout screen you specify the file type, save location, and physical layout of a query’s output.
Output Options
TsQuery can save report output in a variety of file formats. By default TsQuery saves reports as Formatted Text files with no field delimiter, which is intended to be human-readable.
Choosing a Field Delimiter
If you plan to import query output into another program for further processing, select a field delimiter from the Field Delimiter menu. A field delimiter is a marker that tells the importing program where to split a data record into its constituent data fields. Available options are Pipe (|), Comma (,), and Space ( ). You can enter your own preferred field delimiter by selecting Custom and typing a value in the text field next to the menu.
Supported File Formats
In addition to Formatted Text, TsQuery can save query output directly to any of the following 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.
Basic Formatting
In the default Formatted Text format, X, Y, and Z correspond to the Data Item, Date, and Entity information added to the query. The preview pane of the Layout screen shows an animated preview of how your report will appear, allowing you to visualize it without first having to execute the query. The preview is best used to visualize the appearance of Formatted Text (.txt) output. The appearance of output in other formats, such as Excel or Matlab, will vary according to the target application. Previewing saves time and enhances the readability of your output. If, for instance, your query uses only a few entities, you may wish to place Entity on the report’s X axis. If it uses many, you may prefer to place it along the Y or Z axis to limit the output’s physical width.
The nature of your query will determine the proper report layout. Use the controls to the left of the preview pane to test various scenarios. To see how a report will look given particular settings, modify the layout controls, and then click the Preview button. The contents of the pane will reorganize to reflect your settings, and the axes will briefly flash with color to identify themselves: X is purple, Y is green, and Z is red. It may be helpful to think of X as “rows,” Y as “columns,” and Z as “page”.
When you have found a suitable layout, click the Browse... button next to the Location and File Name field. Navigate to where you want to save your report, give the file a descriptive name, and then click the Save button. When you execute the query, CRSPSift will save the report in this file.
Advanced Layout Settings
Click the Advanced... button to access the following advanced layout settings.
Report Name
A text description that will appear at the top of the report. (Formatted Text output only)
Calendar Format
Controls formatting of the dates appearing in the output when date headers are chosen. Options include:
Row Delimiter
Controls the number of rows between output lines. The first integer is the number of blank lines between rows when the Z-axis value changes and impacts Z-flag options 1 and 3. The second integer is the number of blank lines between all data rows. The default is 0,0.
Character Delimiter
A character string placed before and after all character string fields—Company Name, for example—in report records. The default is no character string delimiter. For example, a value of * would cause the character string field Company Name to be surrounded by asterisks in output.
Partial Period Data
Includes partial-period data in the output. If not selected, ts_print will not include the last month of data for a company that stopped trading mid-month, because only months with end-of-month data are normally included. This option applies to monthly data.
No Fill
The No Fill option, enabled by default, causes rows outside an issue’s date range or the user’s date specification not to print to the output file. No Fill is applicable under the following conditions:
No Fill does not work with relative dates.
Compact
Compresses output by removing all spaces and trailing decimal zeros in numbers. Compact is ideal for producing output to be loaded into another program.
Delisting Returns
Default Missing: Outputs the default value (-88.0) for missing delisting returns for entities that have delisted during the selected dates. You must have a Returns data item selected in the Data Items tab in order to include Delisting Returns in your output.
Custom Missing: Outputs user-specified missing delisting return codes. You may assign missing values for a range of delisting codes for select beginning and ending exchanges. To do this, create a text input file containing the following fields in the following order: begin delist code, end delist code, begin exchange code, end exchange code, alternate delisting return value, alternate delisting return without dividends value.
For example:
200 299 1 3 -0.50 -0.55
500 570 3 3 -0.40 -0.45
571 600 3 3 -0.30 -0.35
Note that in this example, the first row would assign a -0.50 value to missing delisting returns for securities with delisting codes 200-299 that initially traded on NYSE and ended up trading on NASDAQ, and -0.55 for missing delisting returns without dividends. If your query includes a security with a missing delisting return that was not included in your input file, the default missing delisting return, -55.0, would be used instead.
Direct Edit
To use this feature, click the Direct Edit tab. Click the Edit button above the text area there, and then type or paste your query code, overwriting the template query code. When you are finished, click Save. If the syntax of your query is correct, CRSPSift automatically populates the appropriate fields in the other screens to reflect your query code.
Executing a Query
After executing a query, click the View Output button on the CRSPSift toolbar.
The File Download window opens, prompting you to open the output file in the target application or to save it in a different location.
Click Open to open the output file in the target application (Excel 2007, in the example shown below).