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

Bill Erickson

Bill Erickson is a freelance WordPress developer and a contributing developer to the Genesis framework. For the past 14 years he has worked with attorneys, publishers, corporations, and non-profits, building custom websites tailored to their needs and goals.

Ready to upgrade your website?

I build custom WordPress websites that look great and are easy to manage.

Let's Talk

Reader Interactions

Comments

  1. DigDug says

    Thanks for your work on this. The most liquid and traded asset in the world and you can’t get an automated price for it outside of an investment platform. Just bizarre.

Leave A Reply