The U.S. Treasury yield curve is of tremendous importance in the financial world, so those of us who teach finance often find it desirable to show a chart of the current yield curve. I’ve used many sources over the years, but my favorite disappeared a few years ago. The data is readily available, so creating your own yield curve isn’t that difficult. It is a bit of a hassle, though. In this post I’m going to show how you can create a “live” U.S. Treasury yield curve using Excel’s Web Query feature and data from Yahoo! Finance.
If you go to the Yahoo! Finance Bonds Center you will see a page like the one pictured below:
What we want to do is grab this data and use it in an XY Scatter chart in an Excel workbook. Obviously, we could just copy the data and then paste it into Excel, but then the data will be stale almost immediately. Much better to have the worksheet fetch the data from the Website in real-time (or something close). This is where the Web Query comes into the picture.
Excel has had the Web Query feature for several years, so you are probably already familiar with it. In Excel 2003 (and earlier), you can create a Web Query by choosing Data—> Import External Data—> New Web Query. In Excel 2007, go to the Data tab and then click From Web in the Get External Data group on the Ribbon. Whichever version of Excel you use, that will launch a dialog box containing a mini browser. Simply enter the URL to be taken to the page that contains the data that you want to capture. The picture below shows the dialog box in Excel 2007 (it is very similar in previous versions):
Notice the yellow arrows that point to tables that you can capture with the Web Query. Click the one next to “US Treasury Bonds Rates” and then click the Import button. Excel will now place a Web Query into your worksheet in the location that you specify in the next dialog box. Note that if you click the Properties button before placing the query, you can specify things like the refresh frequency, data formatting, and so on. Once you place the query in your worksheet, you will see a brief message stating that Excel is getting the data and then you will see the data. If you need to change the query, or refresh the data, just right click anywhere in the query data and choose the appropriate menu item.
Ok, we now have the data in our worksheet, but it isn’t really ready to chart. First, note that the maturities in column A are text (3 month, 6 month), etc. We need to manipulate this to convert it into consistent units (years, in this case). Second, the yields are shown in percentage form rather than decimal (e.g., 3.08 instead of 0.0308). This doesn’t matter much, but I want my Y-axis to show percentages, so I’ll have to manipulate and format these numbers as well. Now, since we are going to create an XY Scatter chart, we need the manipulated maturities to be in the furthest left column, but we can’t just stick another column in the middle of our query data. So, we’ll work on another worksheet and pull the data in from the original worksheet. Go to Sheet2 and in A1 enter the formula: =Sheet1!A1 (better, just type = and then click on A1 in the original sheet). Now, copy this down and across the range A2:E9 to pull in all of the data.
We have a copy of the data that we can now modify as much as we want. First, let’s fix the problem with the maturities. In A3, the maturity is shown as “3 Months,” but we need it displayed in years. That means that we will need to use some Text functions (see my post on Dealing with Unusual Date Formats for more information) to extract the number of months and convert it to a fraction of a year. Essentially, we want to extract the 3 from A3 and then divide by 12. We’ll also do the same in A4. Now, we could just extract the first character of the string, but that won’t work for every cell in the column (and I like to use one consistent formula wherever possible). So, our strategy will be to find the location of the first space in the string and then extract everything to the left of that. In A3 on Sheet2 enter this formula:
That will extract the number of months (3 or 6, depending on the cell). Great! Unfortunately, it won’t be correct for the other cells because we don’t want to divide, say, 2 years by 12. For the maturities entered as years, we simply want the number (e.g., 2, 3, 5, etc). Clearly, then, we need an IF() statement. However, the IF() statement needs to be able to figure out whether to divide by 12 or not. We’ll do this by checking to see if the text after the number says “Month” or “Year.” One problem with that, though, is that Yahoo! might change the text to plural instead of singular and that would break our test. So, let’s just extract the first character and see if it is “M” or “Y.” The formula to extract that character is:
=MID+1,1) = “M”
which will be either TRUE or FALSE. Note that we are just checking for an “M.” If it is there, then we need to divide by 12 to get the fraction of a year, otherwise we just need the raw number. So, put the following formula into A3:
=VALUE+1,1) = “M”,LEFT)/12,LEFT)))
Note that I have surrounded the whole thing in the Value() function to convert the resulting string into a number. This isn’t strictly necessary, but it doesn’t hurt and it does make the numbers align properly. Copy that down, and you are done with this part.
The other change that we want to make is to convert the yields into decimal form. Go to B3 and change the formula from =Sheet1!B3 to =Sheet1!B3/100, and then format the number to show the % sign. Now, copy that to the other yields.
All that remains is to create the chart. In Sheet2, select A2:B9 and also E2:E9 (hold down the CTRL key while selecting the second range). Now, create the XY Scatter chart. This gives me a yield curve chart that shows today’s yields (from B2:B9) compared to yields from one month ago (E2:E9). Label the X and Y axes however you like. For the chart title, we’ll create a dynamic string that will include today’s date. In A11 on Sheet2, type “Chart Title” as the label, and then in B11 enter the following formula:
=“U.S. Treasury Yield Curve “&CHAR&TEXT,“mmm. Dd, yyyy”)
That will create the title with today’s date and insert a line break (Char(13)) so that it looks nice. To actually get that title into the chart, just add a title as usual (it can say anything) and then select it. In the formula bar, type =A11 and then press Enter.
When you have the chart formatted the way that you want it, right click in the chart and choose Move Chart. Well move it so that it is an object in Sheet1. By putting the chart back into Sheet1, we never again have to look at Sheet2. Any time you want an updated yield curve, just open the workbook, right click in the query data on Sheet1 and refresh it. Your chart and data will be updated automatically. The picture below shows the result:
You can download the example worksheet. Note: This is an Excel 2007 file. If you are using Excel 2003, you will need to use the free Compatibility Pack from Microsoft if you haven’t already installed it. Also, you may get a security warning about Data Connections being disabled for security reasons. If so, you can just click the button and enable the content.
Commenting is not available in this channel entry.