- Import the table of prices from the WSJ: =importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",0)
- 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)
How to value direct bond holdings on Google Docs?
Moderator: Global Moderator
Re: How to value direct bond holdings on Google Docs?
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:
- dualstow
- Executive Member
- Posts: 14306
- Joined: Wed Oct 27, 2010 10:18 am
- Location: synagogue of Satan
- Contact:
Re: How to value direct bond holdings on Google Docs?
Thank you, fnord! Good to finally see you around again.
Re: How to value direct bond holdings on Google Docs?
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.dualstow wrote:No, it's not. Definitely broken in google docs. I've seen two or three threads asking about it, but so far no answers.jswinner wrote: Dualstow: Is this still working for you, my sheet is not returning the price.
Melveyr used to fix it.
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
- dualstow
- Executive Member
- Posts: 14306
- Joined: Wed Oct 27, 2010 10:18 am
- Location: synagogue of Satan
- Contact:
Re: How to value direct bond holdings on Google Docs?
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.
For me, the result was within $2 of what Vanguard showed in Cost Basis.
Re: How to value direct bond holdings on Google Docs?
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
- dualstow
- Executive Member
- Posts: 14306
- Joined: Wed Oct 27, 2010 10:18 am
- Location: synagogue of Satan
- Contact:
Re: How to value direct bond holdings on Google Docs?
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!
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!
- Pointedstick
- Executive Member
- Posts: 8866
- Joined: Tue Apr 17, 2012 9:21 pm
- Contact:
Re: How to value direct bond holdings on Google Docs?
Were you a build engineer in a previous life?!ZedThou wrote: I should also have mentioned that I'm not using a spreadsheet, but rather Perl, parsing the HTML.
Human behavior is economic behavior. The particulars may vary, but competition for limited resources remains a constant.
- CEO Nwabudike Morgan
- CEO Nwabudike Morgan
Re: How to value direct bond holdings on Google Docs?
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
- Pointedstick
- Executive Member
- Posts: 8866
- Joined: Tue Apr 17, 2012 9:21 pm
- Contact:
Re: How to value direct bond holdings on Google Docs?
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.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.
Human behavior is economic behavior. The particulars may vary, but competition for limited resources remains a constant.
- CEO Nwabudike Morgan
- CEO Nwabudike Morgan
Re: How to value direct bond holdings on Google Docs?
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.
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
- Pointedstick
- Executive Member
- Posts: 8866
- Joined: Tue Apr 17, 2012 9:21 pm
- Contact:
Re: How to value direct bond holdings on Google Docs?
Bah! Typical open-source project.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.
Human behavior is economic behavior. The particulars may vary, but competition for limited resources remains a constant.
- CEO Nwabudike Morgan
- CEO Nwabudike Morgan
- dualstow
- Executive Member
- Posts: 14306
- Joined: Wed Oct 27, 2010 10:18 am
- Location: synagogue of Satan
- Contact:
Re: How to value direct bond holdings on Google Docs?
I've been using the above, but there seems to be a discrepancy between the WSJ table and other sources.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:
- Import the table of prices from the WSJ: =importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",0)
- 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)
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
Re: How to value direct bond holdings on Google Docs?
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.
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.
- dualstow
- Executive Member
- Posts: 14306
- Joined: Wed Oct 27, 2010 10:18 am
- Location: synagogue of Satan
- Contact:
Re: How to value direct bond holdings on Google Docs?
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.
- Kriegsspiel
- Executive Member
- Posts: 4052
- Joined: Sun Sep 16, 2012 5:28 pm
Re: How to value direct bond holdings on Google Docs?
Bump.
fnord, what exactly are you putting into the google docs spreadsheet with those two formulas? What is the vlookup one referring to?
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.
-
- Junior Member
- Posts: 5
- Joined: Fri Dec 20, 2013 4:45 pm
Re: How to value direct bond holdings on Google Docs?
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.
=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.
- Kriegsspiel
- Executive Member
- Posts: 4052
- Joined: Sun Sep 16, 2012 5:28 pm
Re: How to value direct bond holdings on Google Docs?
Cool, thanks man.
You there, Ephialtes. May you live forever.
-
- Executive Member
- Posts: 527
- Joined: Mon Aug 24, 2015 3:12 pm
Re: How to value direct bond holdings on Google Docs?
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.
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.