Clearly, then, we need an IF() statement. For the maturities entered as years, we simply want the number (e.g., 2, 3, 5, etc). Great! Unfortunately, it won’t be correct for the other cells because we don’t want to divide, say, 2 years by 12. That will extract the number of months (3 or 6, depending on the cell). 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. 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). Essentially, we want to extract the 3 from A3 and then divide by 12. 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. In A3, the maturity is shown as “3 Months,” but we need it displayed in years. First, let’s fix the problem with the maturities. We have a copy of the data that we can now modify as much as we want. Now, copy this down and across the range A2:E9 to pull in all of the data. Go to Sheet2 and in A1 enter the formula: =Sheet1!A1 (better, just type = and then click on A1 in the original sheet). So, we’ll work on another worksheet and pull the data in from the original worksheet. 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. 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. Second, the yields are shown in percentage form rather than decimal (e.g., 3.08 instead of 0.0308). We need to manipulate this to convert it into consistent units (years, in this case). First, note that the maturities in column A are text (3 month, 6 month), etc. Ok, we now have the data in our worksheet, but it isn’t really ready to chart. 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. 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. 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. Excel will now place a Web Query into your worksheet in the location that you specify in the next dialog box. Click the one next to “US Treasury Bonds Rates” and then click the Import button. Notice the yellow arrows that point to tables that you can capture with the Web Query. The picture below shows the dialog box in Excel 2007 (it is very similar in previous versions): Simply enter the URL to be taken to the page that contains the data that you want to capture. Whichever version of Excel you use, that will launch a dialog box containing a mini browser. In Excel 2007, go to the Data tab and then click From Web in the Get External Data group on the Ribbon. In Excel 2003 (and earlier), you can create a Web Query by choosing Data-> Import External Data-> New Web Query. This is where the Web Query comes into the picture.Įxcel has had the Web Query feature for several years, so you are probably already familiar with it. Much better to have the worksheet fetch the data from the Website in real-time (or something close). Obviously, we could just copy the data and then paste it into Excel, but then the data will be stale almost immediately. What we want to do is grab this data and use it in an XY Scatter chart in an Excel workbook. If you go to the Yahoo! Finance Bonds Center you will see a page like the one pictured below: Treasury yield curve using Excel’s Web Query feature and data from Yahoo! Finance. In this post I’m going to show how you can create a “live” U.S. The data is readily available, so creating your own yield curve isn’t that difficult. I’ve used many sources over the years, but my favorite disappeared a few years ago. 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.
0 Comments
Leave a Reply. |