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.

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 97-2003 Workbook (*.xls) - Excel version 8. NOTE: Excel 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 Workbook (*.xlsx) - Excel XML
  • MAT-files (*.mat) - Matlab version 7
  • SAS Dataset (*.sas8bdat) - 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
Treasury Master tmstr
Treasury Issue treas
Treasury Index tidx
Treasury Payments tpaymt
Yield Daily yieldd
Quote Daily quoted
Index Daily indexd
Yield Monthly yieldm
Quote Monthly quotem
Index Monthly indexm
Fama-Bliss Discount fbdisc
Fama-Bliss Riskfree fbrfree
Term Struct term
Fama Bond fbond
Debt debt
Daily Debt ddebt
Fixed Term fterm
Fixed Term Header fh
Fixed Term Timeseries ds
Rates rate
Rates Header rh
Rates Timeseries rs