Gyroscopic Investing

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)

Thank you, fnord! Good to finally see you around again.

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.

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.

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.

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!

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?!

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.

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.

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.

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. 

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

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.

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.

Bump.

fnord, what exactly are you putting into the google docs spreadsheet with those two formulas? What is the vlookup one referring to?

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. 

Cool, thanks man.

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.