Perl Price History Script
jralls at ceridwen.us
Mon Oct 31 10:06:21 EDT 2016
> On Oct 30, 2016, at 11:06 PM, David T. via gnucash-user <gnucash-user at gnucash.org> wrote:
> Recently, and over the years, people have requested tools to manage better the price database in GnuCash. Two major areas of concern were:
> 1) Eliminating unneeded old entries, and
> 2) Retrieving a regulated series of past entries to facilitate reporting.
> I spent some time this weekend trying my hand at addressing these two specific issues, and would like to share the results with the list.
> The script, nph.pm (for "new price history”), is available at https://github.com/sunfish62/NewPriceHistory <https://github.com/sunfish62/NewPriceHistory>
> This script works on an SQLite GnuCash file. In sequence it:
> - Copies your data file to a new file
> - Clears the prices table of all price entries that have Finance* as their source (leaving user-supplied prices intact)
> - Retrieves a complete list of the stocks and mutual funds in the file
> - Retrieves monthly price quotes for all holdings and inserts them into the prices table
> I have a number of warnings and conditions to offer:
> * BIG WARNING #1: I am not much of a programmer, so I guarantee nothing. USE AT YOUR OWN RISK.
> * BIG WARNING #1a: Because of BW#1, if something doesn’t work or goes wrong, I WILL PROBABLY NOT BE OF ANY HELP. I barely understand perl, and am in no position to help debug beyond the blazingly obvious.
> * I have only tested it on my own data file, so your results may vary.
> * Although it’s supposed to modify only a copy of your data, it’s always a good idea to have a safe backup somewhere before you run it.
> * It operates on database back end files; if your data file is XML (which is, after all the default), then you must save it as a database before using. You can always Save As to go back to XML.
> * I have used it with SQLite only; if you are using another database back end, you will no doubt have to change things.
> * I have tested it only with USD as the currency; if you have stocks or mutual funds denominated in another currency, it would be interesting to see how that worked out.
> * The script assumes that all stocks and mutual funds are denominated in the SAME currency, so if you have a file that contains commodities denominated in more than one currency, your results will probably be wacky. Personally, I wouldn’t use this script on such a file.
> * If a ticker cannot be found, Finance::QuoteHist outputs a number of ugly messages to the console that don’t affect the overall script.
> * Before running, you will in all likelihood need to install a couple of extra perl modules, listed at the top of the script.
> With all that out of the way, I hope the script will help someone out there. Comments and suggestions are also always welcome!
BIG WARNING NUMBER 0: This script writes directly to the GnuCash database without using the GnuCash API. It might corrupt your database and render it unusable. Make a copy and work with the copy, testing thoroughly after importing the prices.
BIG WARNING NUMBER 2: If something goes wrong and you ask the GnuCash developers for help, we'll just tell you to toss the database and use your backup. MAKE SURE YOU HAVE A GOOD BACKUP!
The prices table is stand-alone and no core data depends on it, so the worst that will happen (aside from GnuCash crashing on startup) is that all of the values in the Accounts page, the summary bar, and reports will be wrong for everything that you retrieve prices for.
More information about the gnucash-user