WSJ import into Google sheets stopped working

Discussion of the Bond portion of the Permanent Portfolio

Moderator: Global Moderator

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

WSJ import into Google sheets stopped working

Post by jason » Wed Oct 02, 2019 9:02 am

Hello,
I've been importing the US Treasury prices from the Wall Street Journal bond pricing page into my Google sheet for years. It stopped working for me recently. Have other people noticed the same thing? Is there a workaround or alternative source?
Thanks!
flyingpylon
Executive Member
Executive Member
Posts: 1102
Joined: Fri Jan 06, 2012 9:04 am

Re: WSJ import into Google sheets stopped working

Post by flyingpylon » Wed Oct 02, 2019 10:16 am

Looks like they are no longer using a simple HTML table to display the data, so the Google Sheets formula fails. That sucks.

If anyone is good with regular expressions, the data is still there and could probably be retrieved. Unfortunately I am short on both regex skills and time to figure it out, so maybe there's another source out there.
User avatar
dualstow
Executive Member
Executive Member
Posts: 14226
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: WSJ import into Google sheets stopped working

Post by dualstow » Wed Oct 02, 2019 12:31 pm

A few weeks ago, I started just typing in the values from Vanguard occasionally. I was going to look for a real fix, but I noticed the stock prices Google Sheets was pulling weren’t that hot either. They didn’t match Yahoo & Vanguard.
RIP Marcello Gandini
User avatar
jason
Executive Member
Executive Member
Posts: 248
Joined: Mon Jun 10, 2013 4:10 pm

Re: WSJ import into Google sheets stopped working

Post by jason » Wed Oct 02, 2019 3:16 pm

The pricing data does load into the source code of the URL, so if there is a possibility of somehow parsing the source code and inserting the data into a Google sheet, that would be great. This is way over my head, technically.
User avatar
Mark Leavy
Executive Member
Executive Member
Posts: 1950
Joined: Thu Mar 01, 2012 10:20 pm
Location: US Citizen, Permanent Traveler

Re: WSJ import into Google sheets stopped working

Post by Mark Leavy » Thu Oct 03, 2019 12:29 am

Stopped working for me also. I couldn't figure out how to get the data out of the WSJ page, so in the meantime, I've reverted back to pulling the data from Treasury Direct.

Code: Select all

=importhtml("https://www.treasurydirect.gov/GA-FI/FedInvest/todaySecurityPriceDate.htm","table",1) 
Unfortunately, it is only closing prices and only posted for a few hours per day. Most of the time it is a bad link.

If anyone has a better fix, I'm all ears.
Mark
User avatar
jason
Executive Member
Executive Member
Posts: 248
Joined: Mon Jun 10, 2013 4:10 pm

Re: WSJ import into Google sheets stopped working

Post by jason » Thu Oct 03, 2019 12:12 pm

Does anyone know if there are other Google sheets functions that can work such as these?
ImportXML
ImportFEED
ImportDATA
mukramesh
Executive Member
Executive Member
Posts: 165
Joined: Fri Sep 12, 2014 3:27 pm

Re: WSJ import into Google sheets stopped working

Post by mukramesh » Mon Oct 07, 2019 11:52 am

Ayah, I thought it was just my Google sheet that stopped working! It was nice being able to see the entire yield curve at a glance.

Has anyone found a decent workaround yet?
Kbg
Executive Member
Executive Member
Posts: 2815
Joined: Fri May 23, 2014 4:18 pm

Re: WSJ import into Google sheets stopped working

Post by Kbg » Mon Oct 07, 2019 10:19 pm

Excel has some excellent tools for data scrapping now. But yeah, you pay for it and you have to learn how to use the tools.
escafandro
Executive Member
Executive Member
Posts: 156
Joined: Tue Apr 26, 2011 7:15 pm

Re: WSJ import into Google sheets stopped working

Post by escafandro » Thu Oct 31, 2019 7:41 pm

Hi, any news with this issue?
User avatar
jason
Executive Member
Executive Member
Posts: 248
Joined: Mon Jun 10, 2013 4:10 pm

Re: WSJ import into Google sheets stopped working

Post by jason » Thu Nov 21, 2019 10:48 am

I've been reading online how you can extract/scrape data from websites using a Google Apps Script, but I can't figure it out. Does anyone know how to write a script and run it in google sheets? There has got to be a way to get the WSJ data imported into a Google spread sheet. Please help!!
User avatar
jason
Executive Member
Executive Member
Posts: 248
Joined: Mon Jun 10, 2013 4:10 pm

Re: WSJ import into Google sheets stopped working

Post by jason » Mon Dec 09, 2019 5:07 pm

Hello,
Now the bond prices at treasurydirect.gov are not importing into my google sheet. Is anyone having success with importing Treasury prices into a google sheet? If so, how? Please help!
User avatar
jason
Executive Member
Executive Member
Posts: 248
Joined: Mon Jun 10, 2013 4:10 pm

Re: WSJ import into Google sheets stopped working

Post by jason » Thu Jan 02, 2020 10:50 am

*bump*
Is anyone able to import Treasury prices into Google sheets? Nothing is working for me now.
User avatar
Mark Leavy
Executive Member
Executive Member
Posts: 1950
Joined: Thu Mar 01, 2012 10:20 pm
Location: US Citizen, Permanent Traveler

Re: WSJ import into Google sheets stopped working

Post by Mark Leavy » Thu Jan 02, 2020 11:03 pm

The short answer is "no".

My current hack:

Every morning when I update my accounts, I open:
https://www.wsj.com/market-data/bonds/treasuries
In one tab of a browser.

Using my cursor I manually select the table and then copy and paste it into a "Bonds Prices" sheet in my primary Google spread sheet. From there, all of my old formulas take over and work just fine.

It adds a few seconds to my normal morning routine - and it is not fully automatic. But it works.
mukramesh
Executive Member
Executive Member
Posts: 165
Joined: Fri Sep 12, 2014 3:27 pm

Re: WSJ import into Google sheets stopped working

Post by mukramesh » Mon Jan 06, 2020 2:19 pm

Mark Leavy wrote:
Thu Jan 02, 2020 11:03 pm

My current hack:
Omg I can't believe I didn't realize this. Works for me too. Thanks for the tip.
mg315
Junior Member
Junior Member
Posts: 3
Joined: Tue Jul 14, 2015 10:13 pm

Re: WSJ import into Google sheets stopped working

Post by mg315 » Wed Aug 05, 2020 1:03 pm

I think I found a way to get somewhat accurate pricing for treasury bonds in Google Sheets.

You can use the TYX interest rate option to get the current yield on a 30yr treasury. With your particular bond's settlement date, maturity date, and interest rate, you can use the PRICE() function to calculate the current value of the bond.

Ex: I have a 30yr treasury bond with a 2% rate, settlement date of 2/18/20 and a maturity date of 2/15/50. This gives me an estimated price for the bond:

Code: Select all

=PRICE(DATE(2020,2,18),DATE(2050,2,15),.02,GOOGLEFINANCE("TYX")/1000,100,2)
The only problem with this approach is you're using the yield of a fresh 30yr bond on a bond with a shorter maturity. You could combine this with the 10yr interest rate option (TNX) to estimate the yield at your specific maturity, but that's just as likely to be wrong in the other direction because the yield curve isn't a straight line. Averaging the current 10yr and 30yr yields results in a 0.89% estimated 20yr yield, while the actual 20yr yield is 1.01%.

This approach works fine for my needs right now. If I get close to a rebalancing band or my bonds approach 20yr I'll manually pull in the data to get an accurate price.
User avatar
jhogue
Executive Member
Executive Member
Posts: 755
Joined: Wed Jun 28, 2017 10:47 am

Re: WSJ import into Google sheets stopped working

Post by jhogue » Wed Aug 05, 2020 2:24 pm

As an alternative, Fidelity's website has a constantly updated table of fixed income yields, including Treasury, corporate, and municipal bonds.
You don't have to be a Fidelity customer to use it and you don't have to import it into Google sheets to use it.

See: https://fixedincome.fidelity.com/ftgw/f ... ding?bar=p

To get the current price of Treasury bonds, you select the desired maturity. The bid/ask price spread for each of the available issues with that maturity will appear in table format. They also have a pop out yield table available, which I find useful.
“Groucho Marx wrote:
A stock trader asked him, "Groucho, where do you put all your money?" Groucho was said to have replied, "In Treasury bonds", and the trader said, "You can't make much money on those." Groucho said, "You can if you have enough of them!"
User avatar
dualstow
Executive Member
Executive Member
Posts: 14226
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: WSJ import into Google sheets stopped working

Post by dualstow » Wed Aug 05, 2020 8:24 pm

Google sheets is not even working with stock prices lately. I’ve all but given up on them for live prices of any kind.
RIP Marcello Gandini
Post Reply