value of a CUSIP number in GoogleDocs?

Discussion of the Bond portion of the Permanent Portfolio

Moderator: Global Moderator

patrickjhall

value of a CUSIP number in GoogleDocs?

Post by patrickjhall » Mon Jan 16, 2012 8:45 am

Has anyone figured out a reliable way of pulling in specific LTT treasure values, by CUSIP number, real-time into GoogleDocs?

Many thanks
User avatar
dualstow
Executive Member
Executive Member
Posts: 10449
Joined: Wed Oct 27, 2010 10:18 am
Contact:

Re: value of a CUSIP number in GoogleDocs?

Post by dualstow » Fri Jan 20, 2012 2:43 pm

Perhaps not, but if you haven't seen this page,
scroll or search for "fnord123's method"
RIP Charlie Daniels
rickb
Executive Member
Executive Member
Posts: 762
Joined: Mon Apr 26, 2010 12:12 am

Re: value of a CUSIP number in GoogleDocs?

Post by rickb » Sat Jan 21, 2012 1:02 am

User avatar
melveyr
Executive Member
Executive Member
Posts: 971
Joined: Mon Jun 28, 2010 3:30 pm
Location: Seattle, WA
Contact:

Re: value of a CUSIP number in GoogleDocs?

Post by melveyr » Sat Jan 21, 2012 10:57 am

Value of a bond by CUSIP?

Done.

https://docs.google.com/spreadsheet/ccc ... CQ3c#gid=0

;D

Let me know if you have any questions.
everything comes from somewhere and everything goes somewhere
SteveGo
Full Member
Full Member
Posts: 82
Joined: Sun May 22, 2011 12:59 pm
Location: Texas

Re: value of a CUSIP number in GoogleDocs?

Post by SteveGo » Sat Jan 21, 2012 3:37 pm

Thanks for that. Just put it into my spreadsheet. Works great. I decided to use the "third party price," since that is what Fidelity uses to value the Treasuries in my account there.

Guess I did not know you could get those Bond quotes without being logged in.

+1 to melveyr
melveyr wrote: Value of a bond by CUSIP?

Done.

https://docs.google.com/spreadsheet/ccc ... CQ3c#gid=0

;D

Let me know if you have any questions.
Steve G
User avatar
Tortoise
Executive Member
Executive Member
Posts: 1611
Joined: Sat Nov 06, 2010 2:35 am

Re: value of a CUSIP number in GoogleDocs?

Post by Tortoise » Sat Jan 21, 2012 4:56 pm

Slick, melveyr!
Joe is just Biden his time now.
patrickjhall

Re: value of a CUSIP number in GoogleDocs?

Post by patrickjhall » Sun Jan 22, 2012 6:21 pm

melveyr wrote: Value of a bond by CUSIP?

Done.
Brilliant! Thank you!
User avatar
dualstow
Executive Member
Executive Member
Posts: 10449
Joined: Wed Oct 27, 2010 10:18 am
Contact:

Re: value of a CUSIP number in GoogleDocs?

Post by dualstow » Sat Jan 28, 2012 6:55 am

Are you getting slightly different values in google docs and in fidelity itself?

For example, for CUSIP 912810QL5 I get 123.812 in g-docs but 123.719 in fidelity (in my actual holdings).
Perhaps one is one day out of date? But I think the previous price was 123.094. ???
RIP Charlie Daniels
SteveGo
Full Member
Full Member
Posts: 82
Joined: Sun May 22, 2011 12:59 pm
Location: Texas

Re: value of a CUSIP number in GoogleDocs?

Post by SteveGo » Sat Jan 28, 2012 7:23 am

Fidelity values your holdings using the "Third Party" price, not the bid or ask.
Steve G
User avatar
dualstow
Executive Member
Executive Member
Posts: 10449
Joined: Wed Oct 27, 2010 10:18 am
Contact:

Re: value of a CUSIP number in GoogleDocs?

Post by dualstow » Sat Jan 28, 2012 7:27 am

SteveGo wrote: Fidelity values your holdings using the "Third Party" price, not the bid or ask.
ah, thanks.
RIP Charlie Daniels
User avatar
melveyr
Executive Member
Executive Member
Posts: 971
Joined: Mon Jun 28, 2010 3:30 pm
Location: Seattle, WA
Contact:

Re: value of a CUSIP number in GoogleDocs?

Post by melveyr » Sat Jan 28, 2012 10:10 am

I added the bond description, third party price, and the YTM. Please copy over to your spreadsheets if you would like.  :)

https://docs.google.com/spreadsheet/ccc ... CQ3c#gid=0
everything comes from somewhere and everything goes somewhere
flyingpylon
Executive Member
Executive Member
Posts: 516
Joined: Fri Jan 06, 2012 9:04 am

Re: value of a CUSIP number in GoogleDocs?

Post by flyingpylon » Wed Mar 21, 2012 10:22 am

It looks like Fidelity has changed the page that displays bond prices.

I updated the cell in my Google Spreadsheet that displays the price to this:

=index(ImportHtml("http://fixedincome.fidelity.com/fi/FIBo ... USIP",1,10),5,2)

You'll need to replace CUSIP with the actual CUSIP number surrounded by quotes or alternatively, a cell reference without the quotes. For example, my formula replaces "CUSIP" with $B3 like this:

=index(ImportHtml("http://fixedincome.fidelity.com/fi/FIBo ... "&$B3,1,10),5,2)

If you're putting the CUSIP directly into the formula, you could also put it directly after the = and before the " and then remove the &"CUSIP" entirely.  The & just concatenates the two values. So it would look like this:

=index(ImportHtml("http://fixedincome.fidelity.com/fi/FIBo ... USIP",1,10),5,2)

The value returned is the third party price. If you'd prefer the bid price, change the 5 to a 1, or for the ask price change the 5 to a 2.

Melveyr's spreadsheet mentioned above doesn't appear to list bond prices anymore.
Last edited by flyingpylon on Wed Mar 21, 2012 12:06 pm, edited 1 time in total.
Post Reply