Magazine article Modern Trader

A Home Brew Treasury Yield Curve

Magazine article Modern Trader

A Home Brew Treasury Yield Curve

Article excerpt

Every day the Wall Street Journal prints a U.S. Treasury yield curve chart for all existing Treasury bonds, notes and bills. Using a modem and spreadsheet, it's easy to create this yield curve the night before the paper comes by accessing the Liberty Link, the bulletin board system (BBS) of the Federal Reserve Bank of New York ((212) 720-2652).

Once in the system, select "L" to get to the "Library of Files" from the main menu. Type "S quotes" to enter the library (directory) containing daily quotes for U.S. Treasury Securities.

To download the proper file, type "D quoMMDD.YY". For example, to download today's (Aug. 7, 1995) file the command is "D quo0807.95". The BBS will ask you to specify the downloading protocol.

Massaging the data If you download the information into Excel 4.0, the data for each line of text will be put into the first cell of every row. Now we need to "parse" each line of data in both areas to extract the maturity dates and yield-to-maturity values to construct the yield curve.

Select the first row of T-bond and T-note data by clicking on cell A13. Now, select all the rows in this area by holding down the control and shift keys together and pressing the down arrow key. Now click on Parse in the Data menu. A dialog box will appear; click on the Guess button, and brackets will appear around the values Excel thinks should go together. One of these values is the maturity date, and we have to make sure to separate the date from any text that might follow by inserting two back-to-back brackets between the date and this value. Now click the OK button to start the parsing process. Repeat this process on the T-bill data set.

When the parsing is finished, each data item should be separated into columns. To create a new worksheet, select all the dates for the T-bonds and T-notes (in column C if the parsing worked) from the original worksheet and copy them to column A of the new spreadsheet. Do the same with the yield-to-maturity, which should be in column H. Paste the yields into column C of the new worksheet next to the corresponding maturity dates, leaving column B blank.

Go to the line immediately below the last line in the new worksheet and paste the T-bill dates from the quotes sheet into column A below the T-bonds and T-notes data, and paste the T-bill yield-to-maturity rates in column C of the new sheet right below those for the T-bonds and T-notes. The result should be two columns of data with the dates in column A, the yields in column C and a blank column B.

To create a formula to calculate the time-to-maturity, type in =(A1-TODAY0)/365.25 into cell B1. Use the Fill Down command to copy this formula into all cells in column B next to the dates in column A. The result is the number of years remaining until the instrument matures. …

Search by... Author
Show... All Results Primary Sources Peer-reviewed


An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.