Display treasury bond price using CUSIP in Google Sheets

Updated: August 5, 2020

The Wall Street Journal page has been redesigned in a way that prevents scraping data, and I haven’t been able to find another source. If you know of one, please share a link in the comments.

I’ve come up with a new approach that generates an accurate estimate of the Treasury bond price using interest rate options. First, select an option that’s closest to your treasury’s maturity:

• IRX is the current 13-week Treasury bill yield
• FVX is the current 5yr Treasury note yield
• TNX is the current 10yr Treasury note yield
• TYX is the current 30yr Treasury bond yield

Note, you’ll need to divide these by 1000 to convert to actual percentage yield.

Use the PRICE() function to price the security based on the expected yield, using one of the above options as the source of the expected yield.

PRICE(settlement, maturity, rate, yield, redemption, frequency, [day_count_convention])

In my case I wanted to price a 30yr treasury bond with a 2% rate, settlement date of 2/18/20 and a maturity date of 2/15/50.

=PRICE(DATE(2020,2,18),DATE(2050,2,15),.02,GOOGLEFINANCE("TYX")/1000,100,2)

The only downside to this approach is the estimate becomes more inaccurate the further you are from the target date of the option. For instance, when treasury bond is 20 years from maturity, the above formula will still be estimating it as a newly issued 30yr.

You could use the 10yr and 30yr to find the implied yield on a treasury of your exact maturity at any point between those two, but this assumes the yield curve is straight between those two maturities.

You can calculate the implied yield to maturity for this treasury with:

=J3*((30-J16)/30)+J2*(1-((30-J16)/30))

Assuming:

• J2 = 10yr yield: =GOOGLEFINANCE("TNX")/1000
• J3 = 30yr yield: =GOOGLEFINANCE("TYX")/1000
• B16 = current bond’s maturity date
• J16 = current bond’s years till maturity: =(B16-TODAY())/365.25

As of today, the 10yr yield is 0.55% and the 30yr is 1.23%. The formula above would give you 0.89% on a 20yr but the real 20yr yield today is 1.01%.

---

Unfortunately, Google Finance function doesn’t work for the CUSIP of a Treasury bond. But if you can find a table online with accurate prices, you can scrape the data using importxml(), and retrieve the data from a specific cell using Index().

The Wall Street Journal has this page with up-to-date Treasury prices, sorted by maturity. Scroll down to your bond for reference when building the spreadsheet.

Assuming your bond’s maturity date is stored in cell D10, use this function to get the current price:

=Index(importxml("http://wsj.com/mdc/public/page/2_3020-treasury.html","//tr[td='" & D10 & "']"), 1, 4)

The last two numbers indicate the row and column to use. Starting from the first row matching the maturity (D10), we’re requesting the data found in the first row, fourth column.

There may be multiple bonds with the same maturity but differing coupons, and therefore different values. Check this page to see where your bond appears in the list. If your bond appeared in the second row, use the following function:

=Index(importxml("http://wsj.com/mdc/public/page/2_3020-treasury.html","//tr[td='" & D10 & "']"), 2, 4)

Additional Reading

• How to accurately calculate the value of a CD
• Consider iBonds for your Emergency Fund