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?
Google sheets and WSJ bond prices
Moderator: Global Moderator
Re: Google sheets and WSJ bond prices
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.
-
- Executive Member
- Posts: 1102
- Joined: Fri Jan 06, 2012 9:04 am
Re: Google sheets and WSJ bond prices
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.
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.