Google sheets and WSJ bond prices

Discussion of the Bond portion of the Permanent Portfolio

Moderator: Global Moderator

Post Reply
User avatar
jason
Executive Member
Executive Member
Posts: 170
Joined: Mon Jun 10, 2013 4:10 pm

Google sheets and WSJ bond prices

Post by jason » Tue Jan 02, 2018 12:34 pm

I frequently have problems loading bond prices into my google spread sheet. I am using this formula:
=importhtml("http://www.wsj.com/mdc/public/page/2_3020-treasury.html", "table", 3)

Sometimes, if I change the ,3 to something else like 0, it will work. But I can't get it to work consistently. It often just says "#N/A". And when I mouse-over it, it says "imported content is empty." But if I go directly to the WSJ web page, it typically loads fine. Am I doing something wrong?
Mr Vacuum
Executive Member
Executive Member
Posts: 165
Joined: Tue Jan 19, 2016 11:51 am

Re: Google sheets and WSJ bond prices

Post by Mr Vacuum » Tue Jan 02, 2018 12:52 pm

Same here. It seems to be a glitch on the google servers, because changing the query at all then changing it back is enough to get it to work again.
flyingpylon
Executive Member
Executive Member
Posts: 425
Joined: Fri Jan 06, 2012 9:04 am

Re: Google sheets and WSJ bond prices

Post by flyingpylon » Wed Jun 19, 2019 10:02 am

This is an old thread but the title is appropriate. It looks like WSJ updated their bond price page, which caused my Google Sheets formula to fail.

The new formula I'm using is this:

=vlookup(date(YYYY,MM,DD),importhtml("https://www.wsj.com/market-data/bonds/treasuries", "table",1),3,FALSE)

Where YYYY,MM,DD is the year, month, and day that the bond matures. This formula uses the bid price, which is the 3rd column in the table (hence the 3). If you want the ask price, change the 3 to a 4.
Post Reply