Chart the Yield Curve Using Yahoo! Finance Data

Friday, January 4, 2008

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:

=LEFT)/12

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.

For more information regarding Excel and bonds, please see my bond valuation and bond yields tutorials.

Posted by .(JavaScript must be enabled to view this email address) on 01/04 at 02:50 PM

Permalink Tell-a-Friend

Comments

Wow, this is cool, I’ve been trying to find out how to do this very thing for a while now….thanks so much for posting this on the web!


Posted by jcash on 06/13 at 09:03 AM | #

I’m glad that you liked it. Thanks for stopping by.


Posted by Tim on 06/13 at 11:22 AM | #

Hi Dr Tim. I like your Excel finance blog. I have produced some excel models like web-query live-updated model and bond portfolio analysis. However, I want to learn more about optimisation model using Monte Carlo. Cheers.


Posted by keziana on 07/05 at 08:04 PM | #

Thanks. I’ve got lots of good stuff along those lines coming eventually. If you’d like to fool around with some Monte Carlo simulation, take a look at my ExcelSim 2003 add-in. It is meant for learning, not for real world usage. Don’t trust my coding skills for anything that might cost you real money. grin


Posted by Tim on 07/05 at 10:34 PM | #

Ha ha… I like the word “fool”. It sounds like Dr Taleb… wink Anyway, even a professional finance software often makes a lot of errors.


Posted by keziana on 07/06 at 03:53 PM | #

Exactly. I hope that you’ve never been Fooled by Randomness.


Posted by Tim on 07/06 at 05:44 PM | #

In your example you use U.S. Treasury what if I want to use someone else like WalMart?  How do I get to the bond rate so I see the 3 mo, 6 mo, etc.  When I do a search on a bond I don’t see this information.


Posted by Mark on 07/11 at 07:31 AM | #

Mark, that is an excellent question. The best that you can do is to go to the FINRA TRACE Advanced Bond Screener. That tool will allow you to find information about bonds that are publicly traded (Treasury, Corporate, or Municipal). You can search by company name, ticker, or CUSIP.

You can get similar information from the Yahoo! Bond Center. Note that if you go to the bond screener, you can do a more focused search, but you can’t search on the company name from that screen. On the main page of the Bond Center you can search by name in the upper left corner.

Bonds have always been a mostly OTC-traded investment, and dealers have considered their quotes to be proprietary information. That changed in 2001 after the SEC urged the NASD to improve bond market transparency. The result was TRACE, which has improved over the years. Still, getting data for bonds is not nearly as cheap and easy as for stocks.


Posted by Tim on 07/11 at 12:11 PM | #

I entered the formula   =LEFT(Sheet1!A3,FIND(“ “,Sheet1!A3))/12   in A3 as sugested but all i get is an error. (the formula you entered contains an error).
This is in reference of the U.S treasury yield curve. Any help will be highly appreciated.  I’m using microsoft excel 2003. Not sure if it matters, also, typing in formula =Sheet1!A2:E9 gives me #value. What am i doing wrong?
Thanks.

Best regards

[Two comments combined]


Posted by edward tinca on 02/09 at 09:53 PM | #

I’m not sure what is wrong with the first formula. The only way that I can make it return the error message that you got is by copying it directly from post (above). If that is what you did, then you need to replace the quote marks. For some reason, they don’t paste in as normal quotes. So, just retype them and see if that fixes it.

For the second one, I think that you misinterpreted the instructions. That wasn’t the formula, you need to type

=Sheet1!A1

into cell A1 (in Sheet2), and then copy the formula and paste it into cells A2:E9.


Posted by Tim on 02/09 at 10:53 PM | #

Tim, I appreciate the help, Thanks.I did as you suggested and it worked out beautiful.

Thanks again.


Posted by Edward Tinca on 02/10 at 01:42 PM | #

Commenting is not available in this channel entry.