value of a CUSIP number in GoogleDocs?

[patrickjhall]

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

Many thanks

[dualstow]

Perhaps not, but if you haven't seen this page,
scroll or search for "fnord123's method"

[rickb]

Also see http://www.bogleheads.org/forum/viewtopic.php?t=60039

[melveyr]

Value of a bond by CUSIP?

Done.

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



Let me know if you have any questions.

[SteveGo]

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

Value of a bond by CUSIP?

Done.

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



Let me know if you have any questions.

[Tortoise]

Slick, melveyr!

[patrickjhall]

Value of a bond by CUSIP?

Done.

Brilliant! Thank you!

[dualstow]

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.

[SteveGo]

Fidelity values your holdings using the "Third Party" price, not the bid or ask.

[dualstow]

Fidelity values your holdings using the "Third Party" price, not the bid or ask.

ah, thanks.

[melveyr]

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

[flyingpylon]

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.

[Storm]

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 ... CUSIP,1,10),5,2)

You'll need to replace CUSIP with the actual CUSIP number or a cell reference (for example, mine references $B3).

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.

Thanks, melveyr's spreadsheet has changed, as you mentioned, and no longer contains bonds.  Your formula still isn't working correctly for me.  I get a parse error when I try;

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

Any ideas what I'm doing wrong?

[flyingpylon]

Yes, you followed my incorrect instructions!    Sorry about that.  I have fixed it.

The problem was that you did not have quotation marks around the CUSIP number.

[Storm]

Yes, you followed my incorrect instructions!    Sorry about that.  I have fixed it.

The problem was that you did not have quotation marks around the CUSIP number.

Thanks!  My spreadsheet is finally automatically updating bond prices.  This is awesome!

[dualstow]

Works great, thank you!

[flyingpylon]

Just for future reference, in case Fidelity changes the page again and it breaks your spreadsheet formula, here is what the formula does:

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

The ImportHtml function gets the page at that url and looks in the html code for tables (indicated by the 1 but you could also use the word "table" including the quotes).

The 10 tells ImportHtml to import the 10th table in the html code (you can find this by doing "view source" on the page and searching for the string "<table" until you come to the right one).

The Index function retrieves the value in the 5th row (indicated by the 5) and 2nd cell (indicated by the 2) of the imported table. In html tables, rows are wrapped with <tr> tags and cells with <th> or <td> tags.

[Lone Wolf]

Just for future reference, in case Fidelity changes the page again and it breaks your spreadsheet formula, here is what the formula does:

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

Very nice!  It looks like this works great.  Thanks, everyone!

[foglifter]

To ensure this valuable technique is easy to find I've posted a screenshot and a brief explanation into the bond buying tutorial.

[foglifter]

It appears that Fidelity has changed the URL for the bond details page. I have updated the Google Docs formula in my post in the Treasury Bond Buying Tutorial.