gnucash bond quotes

[ZedThou]

Ever since buying bonds I've been unable to track their price in gnucash without manually entering the prices. So now I've made an extension of the Finance::Quote Perl modules that grabs the quotes from Fidelity Fixed Income Search, given the CUSIP. I'll submit it for inclusion into the Finance::Quote distribution, but in the meantime I thought I'd advertise it here since it may be useful to some. The module is at
http://hea-www.harvard.edu/~rpete/FidelityFixed.pm which you would put into the Finance/Quote Perl module subdirectory, and then modify Quote.pm to include FidelityFixed (line 170 or so).

[ZedThou]

Here are the security editor settings for a specific bond.

edit - it is counterintuitive but the Symbol field is the important one, rather than the CUSIP field

[Storm]

Very interesting.  Does gnucash work well in Windows?  I've been using Google Docs to track my PP, but it is also quite a pain to have to manually type in the price of my long term treasuries every time.

[ZedThou]

gnucash is a full-fledged double-entry accounting package, so it might be overkill for portfolio tracking. On the other hand, I have set up a separate "Tracking - positive" account that does exactly that - every disparate asset which is a "stock", "bond" or "gold" are put into their own subaccounts. Then for accounting purposes there's the exact mirror, "Tracking - negative", whose assets balance the positive. In this way I can see the total value of each PP asset class that I have at a given moment in time.

I've never tried gnucash in Windows but I imagine it works fine based on my experiences with other gtk+ apps on Windows (gimp, in particular). I think to get the quoting feature working you'd have to install ActivePerl as well.

This may be too much work just for the purpose of keeping an eye on allocations.

[TBV]

ZedThou:  Thanks for your valuable contribution.  I tried it out on a random 30-yr Treasury (CUSIP=912810QN1) and noticed that it returned a value of $134.72 following the close on Jan. 6, 2012.  The FidelityFixed website's BID price was $134.687.  The ASK price was $134.750 and the THIRD PARTY Price was $134.703.  So that leaves me with two questions: 1) What did the $134.72 represent?  2) How can I tweak the code so that your pm file delivers the BID price?

[ZedThou]

Right now the "price" is the average of bid/ask rounded to the nearest cent. I don't really know what would be most appropriate. Perhaps the 3rd party price. The code is easy to modify, just add a line

$info{$symbol, 'price'}  = $info{$symbol, 'bid'};

after the '# clean things up a bit' section of code, and then restart gnucash.

edit - I've updated the pm file to set price=bid

[TBV]

Thanks very much. I was hoping someone would come along and do precisely what you've done.  Good job!!

[TBV]

gnucash is a full-fledged double-entry accounting package, so it might be overkill for portfolio tracking. On the other hand, I have set up a separate "Tracking - positive" account that does exactly that - every disparate asset which is a "stock", "bond" or "gold" are put into their own subaccounts. Then for accounting purposes there's the exact mirror, "Tracking - negative", whose assets balance the positive. In this way I can see the total value of each PP asset class that I have at a given moment in time.

I've never tried gnucash in Windows but I imagine it works fine based on my experiences with other gtk+ apps on Windows (gimp, in particular). I think to get the quoting feature working you'd have to install ActivePerl as well.

This may be too much work just for the purpose of keeping an eye on allocations.

For those GnuCash users who want to keep a more conventional chart of accounts, they can still track their HB4xPP holdings in a single, simple screen.  First, export their GnuCash "Advanced Portfolio" report to an html file.  Second, link the html file to a sheet in an Excel or LibreOffice Calc file.  Third, inside the spreadsheet file, create a second sheet that uses SUMIF calculated fields to capture the relevant asset data from the linked report found on the first sheet.  Fourth, create a final third sheet that uses calculated expressions to concatenate the asset data listed on the second sheet into the four categories of the HB4xPP.

Step 3 is needed to provide step 4 with stable cell references, since actual assets (and their location in the linked report) may change over time.  Each time the AP report is exported to overwrite the target file, the spreadsheet file will update the results when opened.

[MikeK]

ZedThou,

I've been a gnucash user for close to 13 years now, but quit trying to track my portfolio with it many years ago.  Now that I'm considering moving to the PP, I might just have to give the portolio parts of gnucash another try and will be sure to use your patched Finance::Quote perl module for updates.

Can you post more info on how you set up the accounts?  (Via email if this isn't the appropriate forum is fine)

[ZedThou]

MikeK, send me a pm with your email and I'll send you a bare-bones gnucash file with example tracking accounts set up.

[fnord123]

While I don't use gnucash, this is still an extremely helpful post to me.  I use google spreadsheets to track my PP, and until now had been using the PRICE() function to calculate the value of 30 year treasuries, based on the current yield of treasuries.  Now I can have much more exact values, even accounting for bid/ask spreads.  Thank you!!!

Edit: Just saw this thread - http://gyroscopicinvesting.com/forum/ht ... ic.php?t=6 - so removing my equivalent formula here.

[bradrh]

Thanks for doing this & sharing it.  This is really useful, or at least it will be when I get it working
I installed it just like you said.  If I run gnc-fq-dump on the cmd line it works, and it looks like it is returning everything needed.  I have the security set up just like your example.  But when I press the GetQuotes button it says: 'Unable to retrieve quotes for these items:' & lists the 5 bonds that I'm trying to get quotes for from fidelityfixed.
Any ideas?

[ZedThou]

bradrh, are you using a Unix-like system? If so, could you try running the following command and then send me a PM with the output? Hopefully we can troubleshoot this via PM.

perl -MData::Dumper -MFinance::Quote -le '$q = Finance::Quote->new(); print Dumper { $q->fetch("fidelityfixed", shift) };' 912810QT8

[ZedThou]

I'm not sure if there was anyone using my previous module. If so, they'll have noticed that it stopped working quite some time ago since Fidelity now requires a login to view bond quotes.

So, I've written a new module, which grabs the quotes from Treasury Direct  Of course it will only fetch quotes for government bonds, but otherwise it works just the same as the previous module. If anyone wants it, send me a PM.

[Pointedstick]

Nice work! Are you planning to commit it to the source repo too?

[ZedThou]

Nice work! Are you planning to commit it to the source repo too?

I tried with the last version and received zero response, so I won't bother this time.

[Pointedstick]

They don't have a github repo where you can submit a merge request or something? Or you did and nobody cared?

[ZedThou]

They don't have a github repo where you can submit a merge request or something? Or you did and nobody cared?

I'm so glad you asked, since it made me go looking to find out. It turns out that they have moved to github and someone had put my previous module there! So, this new module has a few kinks, but I will work them out and hopefully get it added and have the previous one removed.

[Pointedstick]

Awesome! That's great news. I appreciate your effort on this. It's nice to know that one day when I go back to Linux, this option will be there.

[Jack Jones]

So, this new module has a few kinks, but I will work them out and hopefully get it added and have the previous one removed.

Do you have the new module available anywhere for me to test?