How to value direct bond holdings on Google Docs?

Discussion of the Bond portion of the Permanent Portfolio

Moderator: Global Moderator

fnord123
Executive Member
Executive Member
Posts: 233
Joined: Sun Apr 25, 2010 9:33 pm

Re: How to value direct bond holdings on Google Docs?

Post by fnord123 » Sun Sep 15, 2013 9:45 am

I find the PRICE() function often doesn't match the actual price of the bonds as reported by Fidelity or the WSJ, so I do the following:
  1. Import the table of prices from the WSJ:  =importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",0)
  2. Use the VLOOKUP funciton to get the exact current price of the bond by maturity date, e.g.: =vlookup(date(2042,11,15),A165:C440,3,FALSE)
User avatar
dualstow
Executive Member
Executive Member
Posts: 14232
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: How to value direct bond holdings on Google Docs?

Post by dualstow » Sun Sep 15, 2013 1:13 pm

Thank you, fnord! Good to finally see you around again.
Sam Bankman-Fried sentenced to 25 years
User avatar
ZedThou
Full Member
Full Member
Posts: 53
Joined: Thu Sep 22, 2011 9:59 pm

Re: How to value direct bond holdings on Google Docs?

Post by ZedThou » Wed Sep 25, 2013 7:38 pm

dualstow wrote:
jswinner wrote: Dualstow:  Is this still working for you, my sheet is not returning the price.
No, it's not. Definitely broken in google docs.  I've seen two or three threads asking about it, but so far no answers.
Melveyr used to fix it.
I had been pulling URLs like https://fixedincome.fidelity.com//ftgw/ ... =912810QT8 and using the 3rd party price, but as of yesterday it has been requiring a Fidelity login to display 3rd party. So from now on I'll just observe for a few days how the 3rd party prices compare to bid/ask and in the future use those relationships to get an approximate price.
Oh, such a beautiful tarmac. Look how smooth it is. See how smooth it is? And it's warm, and it's hard. - Charley Boorman Long Way Round
User avatar
dualstow
Executive Member
Executive Member
Posts: 14232
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: How to value direct bond holdings on Google Docs?

Post by dualstow » Wed Sep 25, 2013 7:52 pm

Zed, did you see fnord's idea in Reply#25? Works great.
For me, the result was within $2 of what Vanguard showed in Cost Basis.
Sam Bankman-Fried sentenced to 25 years
User avatar
ZedThou
Full Member
Full Member
Posts: 53
Joined: Thu Sep 22, 2011 9:59 pm

Re: How to value direct bond holdings on Google Docs?

Post by ZedThou » Wed Sep 25, 2013 7:56 pm

I should also have mentioned that I'm not using a spreadsheet, but rather Perl, parsing the HTML. In principle I could grab the WSJ table and emulate the vlookup function, and I will do just that if Fidelity Fixed ever disappears completely. But for now, using what Fidelity will still give is easiest.
Oh, such a beautiful tarmac. Look how smooth it is. See how smooth it is? And it's warm, and it's hard. - Charley Boorman Long Way Round
User avatar
dualstow
Executive Member
Executive Member
Posts: 14232
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: How to value direct bond holdings on Google Docs?

Post by dualstow » Wed Sep 25, 2013 8:11 pm

Ah, I see.
I've got a Perl book, but I'm mainly using it to elevate my satellite radio antenna on the window sill.
If I had put in the time to learn the language, I would probably want to reap the benefits and do it your way!
Sam Bankman-Fried sentenced to 25 years
User avatar
Pointedstick
Executive Member
Executive Member
Posts: 8864
Joined: Tue Apr 17, 2012 9:21 pm
Contact:

Re: How to value direct bond holdings on Google Docs?

Post by Pointedstick » Wed Sep 25, 2013 9:11 pm

ZedThou wrote: I should also have mentioned that I'm not using a spreadsheet, but rather Perl, parsing the HTML.
Were you a build engineer in a previous life?! ;)
Human behavior is economic behavior. The particulars may vary, but competition for limited resources remains a constant.
- CEO Nwabudike Morgan
User avatar
ZedThou
Full Member
Full Member
Posts: 53
Joined: Thu Sep 22, 2011 9:59 pm

Re: How to value direct bond holdings on Google Docs?

Post by ZedThou » Wed Sep 25, 2013 10:10 pm

Wish I could say this was rocket surgery but it's not actually that complicated. Just a Finance::Quote submodule that scrapes a web page to get bond values by CUSIP. I do it this way because I use Gnucash to keep track of all financial transactions, and an eye on current allocations.
Oh, such a beautiful tarmac. Look how smooth it is. See how smooth it is? And it's warm, and it's hard. - Charley Boorman Long Way Round
User avatar
Pointedstick
Executive Member
Executive Member
Posts: 8864
Joined: Tue Apr 17, 2012 9:21 pm
Contact:

Re: How to value direct bond holdings on Google Docs?

Post by Pointedstick » Wed Sep 25, 2013 10:41 pm

ZedThou wrote: Wish I could say this was rocket surgery but it's not actually that complicated. Just a Finance::Quote submodule that scrapes a web page to get bond values by CUSIP. I do it this way because I use Gnucash to keep track of all financial transactions, and an eye on current allocations.
Is this built into GNUcash, or your custom addition? If the latter, have you committed it to the project's source tree yet? Sounds useful.
Human behavior is economic behavior. The particulars may vary, but competition for limited resources remains a constant.
- CEO Nwabudike Morgan
User avatar
ZedThou
Full Member
Full Member
Posts: 53
Joined: Thu Sep 22, 2011 9:59 pm

Re: How to value direct bond holdings on Google Docs?

Post by ZedThou » Wed Sep 25, 2013 10:49 pm

Gnucash gets asset values from the Perl Finance::Quote module. Previously the module had no mechanism for retrieving bond quotes given CUSIP, so my modification is a Finance::Quote submodule which does this by scraping the Fidelity Fixed web page. I've submitted the code to the Finance::Quote developers for inclusion in their next release, but they haven't been communicative at all. It's a bit puzzling - perhaps not many users hold individual bonds.

Just as well since every few months Fidelity Fixed moves the goalposts and I have to make modifications to keep things working.
Oh, such a beautiful tarmac. Look how smooth it is. See how smooth it is? And it's warm, and it's hard. - Charley Boorman Long Way Round
User avatar
Pointedstick
Executive Member
Executive Member
Posts: 8864
Joined: Tue Apr 17, 2012 9:21 pm
Contact:

Re: How to value direct bond holdings on Google Docs?

Post by Pointedstick » Wed Sep 25, 2013 11:15 pm

ZedThou wrote: Gnucash gets asset values from the Perl Finance::Quote module. Previously the module had no mechanism for retrieving bond quotes given CUSIP, so my modification is a Finance::Quote submodule which does this by scraping the Fidelity Fixed web page. I've submitted the code to the Finance::Quote developers for inclusion in their next release, but they haven't been communicative at all. It's a bit puzzling - perhaps not many users hold individual bonds.
Bah! Typical open-source project.  >:(
Human behavior is economic behavior. The particulars may vary, but competition for limited resources remains a constant.
- CEO Nwabudike Morgan
User avatar
dualstow
Executive Member
Executive Member
Posts: 14232
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: How to value direct bond holdings on Google Docs?

Post by dualstow » Sun Nov 10, 2013 10:22 am

fnord123 wrote: I find the PRICE() function often doesn't match the actual price of the bonds as reported by Fidelity or the WSJ, so I do the following:
  1. Import the table of prices from the WSJ:  =importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",0)
  2. Use the VLOOKUP funciton to get the exact current price of the bond by maturity date, e.g.: =vlookup(date(2042,11,15),A165:C440,3,FALSE)
I've been using the above, but there seems to be a discrepancy between the WSJ table and other sources.
For example, CUSIP 912828VQ0 with maturity date 7/31/18 (this is just a 5-year note in my vp) -->
Using the WSJ table, it's got a price of $104 and change.
But, according to both Vanguard and something I found online called treasurywiki.appspot.com, it's closer to $100 and change. Hmm.
http://treasurywiki.appspot.com/treasur ... n-xTZR_WKE
Sam Bankman-Fried sentenced to 25 years
fnord123
Executive Member
Executive Member
Posts: 233
Joined: Sun Apr 25, 2010 9:33 pm

Re: How to value direct bond holdings on Google Docs?

Post by fnord123 » Sun Nov 10, 2013 7:45 pm

I just looked at the price for the 02/15/2039 treasury. Here's what I found:
Vanguard: $95.781
WSJ: $95.789
TreasuryWiki: $97.64
PRICE(): $93.804
Fidelity: $95.671

At least for 02/15/3029, I find WSJ/Vanguard/Fidelity are all pretty close to each other, with TreasuryWiki being 2% high and PRICE() being 2% low.
User avatar
dualstow
Executive Member
Executive Member
Posts: 14232
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: How to value direct bond holdings on Google Docs?

Post by dualstow » Mon Nov 11, 2013 7:11 am

I don't know why, fnord, but whenever I look at long bonds, the prices seem to be more accurate and closer to each other. It's the 5-year notes that seem to have such a discrepancy. Strange.
Sam Bankman-Fried sentenced to 25 years
User avatar
Kriegsspiel
Executive Member
Executive Member
Posts: 4052
Joined: Sun Sep 16, 2012 5:28 pm

Re: How to value direct bond holdings on Google Docs?

Post by Kriegsspiel » Mon Jun 02, 2014 4:46 pm

Bump.

fnord, what exactly are you putting into the google docs spreadsheet with those two formulas? What is the vlookup one referring to?
You there, Ephialtes. May you live forever.
TrevorShrade
Junior Member
Junior Member
Posts: 5
Joined: Fri Dec 20, 2013 4:45 pm

Re: How to value direct bond holdings on Google Docs?

Post by TrevorShrade » Sat Jun 07, 2014 4:18 pm

Here try this:

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

Just change 2/15/2042 to the maturity date of the Bond you wish to lookup.  If you want to pull other data from the tables at http://wsj.com/mdc/public/page/2_3020-treasury.html  you can change the number 3 to whichever column you wish to pull data from.



Or if you already have a cell with the maturity date listed:

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

Just change B14 to the cell that contains the maturity date in M/D/YYYY format. 
Last edited by TrevorShrade on Sat Jan 30, 2016 2:13 am, edited 1 time in total.
User avatar
Kriegsspiel
Executive Member
Executive Member
Posts: 4052
Joined: Sun Sep 16, 2012 5:28 pm

Re: How to value direct bond holdings on Google Docs?

Post by Kriegsspiel » Sun Jun 08, 2014 2:33 pm

Cool, thanks man.
You there, Ephialtes. May you live forever.
Jack Jones
Executive Member
Executive Member
Posts: 522
Joined: Mon Aug 24, 2015 3:12 pm

Re: How to value direct bond holdings on Google Docs?

Post by Jack Jones » Fri Dec 30, 2016 10:42 am

Does this page make sense to anyone?

http://wsj.com/mdc/public/page/2_3020-t ... #treasuryB

Take for example the 1/5/2017 bill. Ask is 0.325 which is supposedly the "discount to face value". So my interpretation of that is someone was willing to sell the bill for $1000 - 0.325 = $999.675. My intuition says that is incorrect because the bill matures in a few days. I must be looking at this wrong.
Post Reply