Page 2 of 2

Re: WSJ import into Google sheets stopped working

Posted: Thu Jan 02, 2020 11:03 pm
by Mark Leavy
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.

Re: WSJ import into Google sheets stopped working

Posted: Mon Jan 06, 2020 2:19 pm
by mukramesh
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.

Re: WSJ import into Google sheets stopped working

Posted: Wed Aug 05, 2020 1:03 pm
by mg315
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.

Re: WSJ import into Google sheets stopped working

Posted: Wed Aug 05, 2020 2:24 pm
by jhogue
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.

Re: WSJ import into Google sheets stopped working

Posted: Wed Aug 05, 2020 8:24 pm
by dualstow
Google sheets is not even working with stock prices lately. I’ve all but given up on them for live prices of any kind.