TrsQuery: Treasuries Data Access
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.
|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.
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:
|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
|2000001||Risk-Free Rates - 1 Month|
|2000002||Risk-Free Rates - 3 Months|
|2000061||Risk Free 4-Week|
|2000062||Risk Free 13 Week|
|2000063||Risk Free 26-Week|
Index Family: 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
|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:
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:
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.
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|
|Fixed Term Header||X|
|Fixed Term Timeseries||X|
|Monthly Treasury Items|
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.
|Data Group||Output File Suffix|
|Fixed Term Header||fh|
|Fixed Term Timeseries||ds|