CRSPSift’s TrsQuery tool is used to access CRSP Daily and Monthly US Treasuries Data. Individual treasury issues and a variety of treasury series are available.
Query Date Range Specification
Locate the TrsQuery toolbar below the main CRSPSift toolbar.
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, TrsQuery returns data from the last three months of the CRSP database included in your query’s Environment. For more information on Environments, see Chapter 8: Environments.
Treasury Selection
Selecting A Key Type
Select what type of key your query will use from the Choose Key Type drop-down menu in the Treasury Selection area. Available keys are:
TREASNO - (default) CRSP’s Unique US Treasury Number Identifier can be used to access individual issues. TREASNOs are 6-digit numbers between 200001 and 206341.
CUSIP - Treasury CUSIP. The Committee on Uniform Security Identification Procedures began assigning CUSIP identification numbers in 1968. Issues that matured prior to 1968 are assigned the value OXX. The earliest maturity in the file with a CUSIP is February 15, 1969.
CRSPID - The CRSP Issue Identification Number in the format YYYYMMDD. TCCCCE, where:
YYYY
Maturity Year
MM
Maturity Month
DD
Maturity Day
T
Type of Issue (TYPE)
CCCC
Integer part of Coupon Rate (COUPRT * 100)
E
Uniqueness Number (UNIQ)
For example, 19850515.504250 identifies a 4¼% callable bond which matures May 15, 1985.
TREASNOX - CRSP’s Unique US Treasury Index Number Identifier. Index TREASNOX’s are 7-digit numbers between 2000001 and 2000069. The complete list of treasuries indexes is below:
TREASNOX
TERM TYPE DESCRIPTION
Index Family: RISKFREE
RISKFREE
2000001
Risk-Free Rates - 1 Month
2000002
Risk-Free Rates - 3 Months
Riskfree2
2000061
Risk Free 4-Week
2000062
Risk Free 13 Week
2000063
Risk Free 26-Week
Index Family: FIXEDTERM
FIXEDTERM
2000003
1 Year Bonds, Start Date 1/31/1941
2000004
2 Year Bonds, Start Date 1/31/1941
2000005
5 Year Bonds, Start Date 4/30/1941
2000006
7 Year Bonds, Start Date 4/30/1941
2000007
10 Year Bonds, Start Date 5/31/1941
2000008
20 Year Bonds, Start Date 1/31/1942
2000009
30 Year Bonds, Start Date 11/29/1941
Index Family: TERMSTRUCT
FAMA TERM STRUCTURE
2000010
Fama 12 Month T-Bills - 1 Month
2000011
Fama 12 Month T-Bills - 2 Month
2000012
Fama 12 Month T-Bills - 3 Month
2000013
Fama 12 Month T-Bills - 4 Month
2000014
Fama 12 Month T-Bills - 5 Month
2000015
Fama 12 Month T-Bills - 6 Month
2000016
Fama 12 Month T-Bills - 7 Month
2000017
Fama 12 Month T-Bills - 8 Month
2000018
Fama 12 Month T-Bills - 9 Month
2000019
Fama 12 Month T-Bills - 10 Month
2000020
Fama 12 Month T-Bills - 11 Month
2000021
Fama 12 Month T-Bills - 12 Month
2000022
Fama 6 Month T-Bills - 1 Month
2000023
Fama 6 Month T-Bills - 2 Month
2000024
Fama 6 Month T-Bills - 3 Month
2000025
Fama 6 Month T-Bills - 4 Month
2000026
Fama 6 Month T-Bills - 5 Month
2000027
Fama 6 Month T-Bills - 6 Month
Index Family: BONDMAPORT
FAMA BOND PORTFOLIO RETURN
2000028
Fama BondPort Return - 6Mo Range <=6Month
2000029
Fama BondPort Return - 6Mo Range <=12Month
2000030
Fama BondPort Return - 6Mo Range <=18Month
2000031
Fama BondPort Return - 6Mo Range <=24Month
2000032
Fama BondPort Return - 6Mo Range <=30Month
2000033
Fama BondPort Return - 6Mo Range <=36Month
2000034
Fama BondPort Return - 6Mo Range <=42Month
2000035
Fama BondPort Return - 6Mo Range <=48Month
2000036
Fama BondPort Return - 6Mo Range <=54Month
2000037
Fama BondPort Return - 6Mo Range <=60Month
2000038
Fama BondPort Return > 60 <= 120 Month
2000039
Fama BondPort Return > 120 Month
2000040
Fama BondPort Return - 12Mo Range <=12Month
2000041
Fama BondPort Return - 12Mo Range <=24Month
2000042
Fama BondPort Return - 12Mo Range <=36Month
2000043
Fama BondPort Return - 12Mo Range <=48Month
2000044
Fama BondPort Return - 12Mo Range <=60Month
2000045
Reserved for future use - Duplicate of 2000038
2000046
Reserved for future use - Duplicate of 2000039
Index Family: DISCBOND
FAMA-BLISS DISCOUNT BONDS
2000047
Fama Bliss Discount Bonds 1 Year
2000048
Fama Bliss Discount Bonds 2 Year
2000049
Fama Bliss Discount Bonds 3 Year
2000050
Fama Bliss Discount Bonds 4 Year
2000051
Fama Bliss Discount Bonds 5 Year
Index Family: RATES
RATES
200052
1-Month Certificate of Deposit Rate
200053
3-Month Certificate of Deposit Rate
2000054
6-Month Certificate of Deposit Rate
2000055
30-Day Commercial Paper Rate
2000056
60-Day Commercial Paper Rate
2000057
90-Day Commercial Paper Rate
2000058
Federal Funds Effective Rate
2000059
Federal Funds Minimum Trading Range
2000060
Fedreal Funds Maximum Trading Range
Entering A Key Value
Identify the treasury or treasuries of interest. There are three ways to specify a key value. First, if you know the key value you want, you can type it directly in the text field. Another option is to browse the database sequentially. Use the arrow buttons to the right of the Find... button to step through the database in the order of the key type you have chosen. Finally, the Find… button opens the Treasuries Search window, where you can use a search form to locate a particular treasury in the CRSP database.
Using The Treasuries Search Window
If you have selected TREASNOX as your query’s key type, you can search by partial or complete TREASNOX, Index Family, or Term Type Description.
If you have chosen any of the other three key types, search the Treasuries Search window by one of the following criteria: TREASNO, CRSPID, CUSIP, Date Issued, or Maturity Date.
Treasuries matching your search criteria appear in a table in the main area of the window. If your search yields many matches, click the column labels to re-sort the table in the order most useful to you. When you have found the treasury you want, click its row in the table, and then click the Select button. Its TREASNO will appear in the identifier field of the Input tab.
Accessing All Treasuries
To access all treasuries in the database, select the All Treasuries radio button.
Using An Input File
Finally, you may choose to access only certain treasuries specified in an input file. To use an input file, first create a plain text file (using the Microsoft Windows Notepad accessory, for instance) with an entry for each treasury, one per row, using the key type of your choice. For example, to access four particular treasuries by TREASNO, create the following four line file:
200007
200014
200158
200161
Save the file in your data environment’s working directory. Next, Click the Input File option in the Treasury Selection area, and then click the Browse… button to locate the input file you created. The Browse window opens automatically to the working directory. Now select the preferred key type from the Choose Key Type menu.
Data Item Selection
Use the Data Items tab to control what individual data items your query will use. Any items you have chosen appear in the Selected Data Items table.
Select/Edit Data Items
Clicking this button launches the interface for selecting individual items in TrsQuery.
Note: For complete definitions of data items and calculations employed by the CRSP US Treasury Databases, refer to the Treasury Guide at http://www. crsp.chicagobooth.edu/documentation.
The TrsQuery 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 constituent 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., “return”) to find items of interest. Navigate and Search both result in found data items appearing 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 right panel of the screen.
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 functions available are:
Click the OK button to confirm the addition of the selected items to your query. Click cancel to exit the TrsQuery 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 user-created text file. A data item file is a plain text file containing a list of TrsQuery data ItemIDs, one per line. For example, in Notepad or another text editor, create a file with the following four lines:
treasno
tname
tdbid
tdask
Save the file as trsimport.txt in your CRSPSift working directory. Now click the Import Data Item File... button and select trsimport.txt. The data items listed in the file will be added your query.
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 data items, saving you the time of selecting and adjusting a group’s items individually.
The following data groups are available in TrsQuery.
Groups correspond to the item groupings of the tree navigation structure in the TrsQuery Data Items window. Some data groups apply to individual treasury issues, while others apply to treasury series. The table indicates to which category a group applies.
Data Group
Aggregate Series
Issue
Daily Treasury Items
Index Daily
X
Quote Daily
X
Yield Daily
X
Fixed Term
Fixed Term Header
X
Fixed Term Timeseries
X
Monthly Treasury Items
Fama Bond
X
Fama-Bliss Discount
X
Fama-Bliss Riskfree
X
Index Monthly
X
Quote Monthly
X
Term Struct
X
Yield Monthly
X
Rates
Rates Header
X
Rates Timeseries
X
Treasury Descriptions
Daily Debt
X
Debt
X
Fixed Term
X
Rates
X
Treasury Index
X
Treasury Issue
X
Treasury Master
X
Treasury Payments
X
Output Options
You can save the output of a TrsQuery 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 TrsQuery window.
By default, the output of the last query execution is shown. 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 treasury, or different data or options related to the same treasury. 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 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 select a location to save the file.
Support 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, TrsQuery will generate one or more output files, one for each unique output format produced by 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, 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 in Windows Explorer.
Output File Suffixes
The following table lists output file extensions for each type of treasury data.
Subscription Information
CRSPSift’s TrsQuery tool is used to access CRSP Daily and Monthly US Treasuries Data. Individual treasury issues and a variety of treasury series are available.
Query Date Range Specification
Locate the TrsQuery toolbar below the main CRSPSift toolbar.
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, TrsQuery returns data from the last three months of the CRSP database included in your query’s Environment. For more information on Environments, see Chapter 8: Environments.
Treasury Selection
Selecting A Key Type
Select what type of key your query will use from the Choose Key Type drop-down menu in the Treasury Selection area. Available keys are:
TREASNO - (default) CRSP’s Unique US Treasury Number Identifier can be used to access individual issues. TREASNOs are 6-digit numbers between 200001 and 206341.
CUSIP - Treasury CUSIP. The Committee on Uniform Security Identification Procedures began assigning CUSIP identification numbers in 1968. Issues that matured prior to 1968 are assigned the value OXX. The earliest maturity in the file with a CUSIP is February 15, 1969.
CRSPID - The CRSP Issue Identification Number in the format YYYYMMDD. TCCCCE, where:
For example, 19850515.504250 identifies a 4¼% callable bond which matures May 15, 1985.
TREASNOX - CRSP’s Unique US Treasury Index Number Identifier. Index TREASNOX’s are 7-digit numbers between 2000001 and 2000069. The complete list of treasuries indexes is below:
RISKFREE
FIXEDTERM
FAMA TERM STRUCTURE
FAMA BOND PORTFOLIO RETURN
FAMA-BLISS DISCOUNT BONDS
RATES
Entering A Key Value
Identify the treasury or treasuries of interest. There are three ways to specify a key value. First, if you know the key value you want, you can type it directly in the text field. Another option is to browse the database sequentially. Use the arrow buttons to the right of the Find... button to step through the database in the order of the key type you have chosen. Finally, the Find… button opens the Treasuries Search window, where you can use a search form to locate a particular treasury in the CRSP database.
Using The Treasuries Search Window
If you have selected TREASNOX as your query’s key type, you can search by partial or complete TREASNOX, Index Family, or Term Type Description.
If you have chosen any of the other three key types, search the Treasuries Search window by one of the following criteria: TREASNO, CRSPID, CUSIP, Date Issued, or Maturity Date.
Treasuries matching your search criteria appear in a table in the main area of the window. If your search yields many matches, click the column labels to re-sort the table in the order most useful to you. When you have found the treasury you want, click its row in the table, and then click the Select button. Its TREASNO will appear in the identifier field of the Input tab.
Accessing All Treasuries
To access all treasuries in the database, select the All Treasuries radio button.
Using An Input File
Finally, you may choose to access only certain treasuries specified in an input file. To use an input file, first create a plain text file (using the Microsoft Windows Notepad accessory, for instance) with an entry for each treasury, one per row, using the key type of your choice. For example, to access four particular treasuries by TREASNO, create the following four line file:
200007
200014
200158
200161
Save the file in your data environment’s working directory. Next, Click the Input File option in the Treasury Selection area, and then click the Browse… button to locate the input file you created. The Browse window opens automatically to the working directory. Now select the preferred key type from the Choose Key Type menu.
Data Item Selection
Use the Data Items tab to control what individual data items your query will use. Any items you have chosen appear in the Selected Data Items table.
Select/Edit Data Items
Clicking this button launches the interface for selecting individual items in TrsQuery.
Note: For complete definitions of data items and calculations employed by the CRSP US Treasury Databases, refer to the Treasury Guide at http://www. crsp.chicagobooth.edu/documentation.
The TrsQuery 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 constituent 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., “return”) to find items of interest. Navigate and Search both result in found data items appearing 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 right panel of the screen.
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 functions available are:
Click the OK button to confirm the addition of the selected items to your query. Click cancel to exit the TrsQuery 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 user-created text file. A data item file is a plain text file containing a list of TrsQuery data ItemIDs, one per line. For example, in Notepad or another text editor, create a file with the following four lines:
treasno
tname
tdbid
tdask
Save the file as trsimport.txt in your CRSPSift working directory. Now click the Import Data Item File... button and select trsimport.txt. The data items listed in the file will be added your query.
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 data items, saving you the time of selecting and adjusting a group’s items individually.
The following data groups are available in TrsQuery.
Groups correspond to the item groupings of the tree navigation structure in the TrsQuery Data Items window. Some data groups apply to individual treasury issues, while others apply to treasury series. The table indicates to which category a group applies.
Output Options
You can save the output of a TrsQuery 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 TrsQuery window.
By default, the output of the last query execution is shown. 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 treasury, or different data or options related to the same treasury. 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 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 select a location to save the file.
Support File Formats
Viewing Output Files
If you select a non-screen output format, TrsQuery will generate one or more output files, one for each unique output format produced by 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, 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 in Windows Explorer.
Output File Suffixes
The following table lists output file extensions for each type of treasury data.