Tracking bonds in google spreadsheet or other from CUSIP?

[coinstar]

I found an old thread on here where one member found a way to pull the bond value from Fidelity but that method doesn't work anymore. I think Fidelity locked it off. Now I'm updating my spreadsheets by hand. Any better way to do it? I'm not locked into google spreadsheets.

[Kriegsspiel]

=Index(importxml("http://wsj.com/mdc/public/page/2_3020-t ... tml","//tr[td='"&D6&"']"),1,4)

Where D6 is my cell with the maturity in it. I can't even remember where I got that formula from. Probably fnord.

[dualstow]

Yeah, probably from fnord.
I have this in a cell:

=vlookup(date(2041,11,15),wsj!A206:C304,3,FALSE)
- "wsj" is the name of a tab in the same sheet that loads all that Wall Street Journal data, the same as Krieg's above I guess.
- After enough instruments are issued, I have to adjust the cell range, putting a bigger number after "C:" so that the CUSIP can still be found.
- I think the google sheet updates around 3:00pm EST and Fidelity the next morning, but I'm not positive.

[coinstar]

=Index(importxml("http://wsj.com/mdc/public/page/2_3020-t ... tml","//tr[td='"&D6&"']"),1,4)

Where D6 is my cell with the maturity in it. I can't even remember where I got that formula from. Probably fnord.

This works, thank you. It only updates at 3pm EST daily, correct?

[fnord123]

Dualstow pretty much nailed what I do.  The only difference is I lay out the cells like this:

02/15/2039 30 Year Treasury    | $1,216.17
05/15/2039 30 Year Treasury    | $1,357.89

The first (A) column is a text description of the bond - nice and readable
The second (B) column has the following formula:  =10*vlookup(datevalue(left(A32,10)),'WSJ Treasury Prices'!$A$1:$D$498,3,FALSE)

The datevalue(left(A32,10)) part is a tweak that allows me to avoid having to go in and muck with the formula to add new bonds.  Instead, I just cut'n'paste the same formula next to every text label of every 30Y Treasury whose price I am interested in.

[jason]

=Index(importxml("http://wsj.com/mdc/public/page/2_3020-t ... tml","//tr[td='"&D6&"']"),1,4)

Where D6 is my cell with the maturity in it. I can't even remember where I got that formula from. Probably fnord.

Regarding importing long term Treasury prices into my Google doc spreadsheet, one problem is that if I import using the above WSJ.com link, the prices are updated occasionally throughout the day, apparently, but are not priced real-time.  What I did in the past was add the % gain of TLT to the previous day's closing price of my Treasuries to get a very close estimation of the exact current price.  But since the WSJ prices change a few times during the day, I really can't add TLT"s % gain to the price because I may end up double-adding any price changes.  This was not a problem when I used to import the prices from Fidelity because that always just imported the previous day's closing price.  So, I could check it at any time during day and add the TLT % change to the price.  But Fidelity killed price importing into my spreadsheet.  So, my question is, is there any way that I can import the previous closing price rather than importing prices from WSJ, so that I can estimate real-time prices by adding the % change of TLT?
Thanks!
Jason

[TrevorShrade]

is there any way that I can import the previous closing price rather than importing prices from WSJ, so that I can estimate real-time prices by adding the % change of TLT?

=IFERROR(Importxml("http://wsj.com/mdc/public/page/2_3020-treasury-"&TEXT(TODAY()-1,"YYYYMMDD")&".html","//tr[td='"&A1&"']/td[3]"),IFERROR(Importxml("http://wsj.com/mdc/public/page/2_3020-treasury-"&TEXT(TODAY()-2,"YYYYMMDD")&".html","//tr[td='"&A1&"']/td[3]"),Importxml("http://wsj.com/mdc/public/page/2_3020-treasury-"&TEXT(TODAY()-3,"YYYYMMDD")&".html","//tr[td='"&A1&"']/td[3]")))


Just change A1 to the cell that contains the maturity date in M/D/YYYY format.

If you receive an error make sure cell A1 is set to "Plain Text" by selecting Formant>Number>Plain Text

[DragonJoey3]

This pulls the data direct from the US treasury website where "A8" is the cell with the CUSIP in it. 

Code: Select all

=VLOOKUP(A8,IMPORTHTML("https://www.treasurydirect.gov/GA-FI/FedInvest/todaySecurityPriceDate.htm","table",1), 7, FALSE)

However it has a few limitations:

• It only works after 1:00pm EST on days when the market is open
• It is occasionally finicky and needs the 1 changed to a 0.  (Treasury removes a table from the site).

Still it's the best resource I've found without bothering to lookup how to POST data to a URL from Google Docs.  I recommend giving it a shot after 1pm Monday afternoon.

[Greg]

For those that track U.S. Treasury STRIPS, I figured this one out today:

=ImportXml("http://wsj.com/mdc/public/page/2_3020-t ... tml","//tr[td='2042 Aug 15']/td[3]")

Put the bond in the "2042 Aug 15" spot. It'll pop up with two numbers (not sure why). But then you can just hide those and choose the bottom number which is the bond price and copy and paste it into a location you want.

Also is another thread that maybe should get linked together with this one. Not sure if there are other ones on this forum or not.

http://gyroscopicinvesting.com/forum/bo ... ogle-docs/