Introduction

This article discusses GnuCash and the advantages of using a proper database file format, which GnuCash supports. The article includes and describes a number of useful Python programs -- programs that require a user's GnuCash database to be saved in the SQLite database format. Of particular interest to dedicated GnuCash users is a way to restore the online stock price update feature that was recently lost because of a policy change at Yahoo.

But first, the backstory. To manage my financial records, until recently I've been using my own program PLCash, a relatively simple Java-based program that served my needs, but one that couldn't read online bank record downloads, which meant a lot of hand data entries and inevitable errors.

As much as I would like to continue using my own program, I'm in the midst of switching over to GnuCash, a much better program, and, like PLCash, free. In this article I describe GnuCash, related issues like SQLite database manipulation and related issues.

There are many reasons for my having abandoned my own program. PLCash doesn't save its data in a database, which makes external analysis and import/export difficult. And unlike PLCash, GnuCash uses double-entry bookkeeping, a great advantage that may requires some explanation. And finally, GnuCash happily imports modern online bank records and integrates the results into its account database, which eliminates hand entries for all but a few accounts from institutions that don't offer usable data downloads (Fidelity, are you listening?).

I could have updated my own program, but I became somewhat philosophical when I saw how powerful GnuCash had become over about a decade of time. I realized with great effort I could only create an imitation of a program that many knowledgeable people have struggled with over years of time.

GnuCash opens a number of interesting possibilities:

• The most desirable GnuCash data storage option (among several available) is in the format of the database utility SQLite, an option I strongly recommend because of the opportunities this opens up for data analysis and manipulation of the database, separate from GnuCash itself.
• Because GnuCash accepts QFX-format data transfers and downloads, this makes it possible to download full account data from many banks and financial institutions with an online presence, and import the records directly into GnuCash, thereby eliminating the many errors that accompany hand data entry.
• GnuCash can automatically download current equity prices at the push of a button. At least, until recently — see the discussion below about getting this feature working again.

Getting Started

To be able to follow this article, and to directly experience GnuCash, install GnuCash and some related utilities:

• Linux:

  • Install GnuCash, SQLite and other utilities from a shell (Ubuntu example):

    • $ sudo apt install gnucash sqlite3 libdbd-sqlite3 python-sqlite python3-pip
    • $ sudo pip install piecash
• Windows:

  • As is true for most things, doing anything Microsoft didn't think of is much harder than it is on Linux. Visit these sites to download the required packages:

    • GnuCash
    • SQLite
    • Python (download the 3+ version)

  • I must add that the following instructions assume a Linux environment and I haven't tested the procedures as thoroughly on Windows as I have on Linux. Some things may turn out differently and (ironically, given Linux's reputation) may require more hacking to get things working.

First Steps

• For all the options to be described, before taking any actions that write data, back up your data in multiple locations. Make complete copies of your financial databases, save them in different places.
• If you don't have a financial program and GnuCash is your first, be sure to set up your account database using the "sqlite3" data format (File ... Save As ... Data Format: sqlite3).
• If you already have a financial program and want to export its data to GnuCash, export the prior program's data in QIF format if available, because GnuCash accepts this format (PLCash offers this export option).
• If you're already using GnuCash but you chose the default data export format (i.e. XML), for the next phase of this article — and on general principles — I highly recommend that you export your database in the sqlite3 format as explained above.
• One final word. Did I mention that you should back up your data in multiple locations?

Now we can perform some useful operations on GnuCash databases and accounts using relatively simple Python programs. Remember to back up your GnuCash database file before running any of the programs listed below, and make sure you have exited the GnuCash program before running them (because GnuCash puts a lock on its own database while running).

Python: Scan All Accounts

If you've been following these instructions in sequence, you should have your records in the SQLite database format, which means you can search them in many interesting ways not possible with other formats. Although there are other options, ideally GnuCash relies on a database engine named "SQLite" to manage its database, and we can access GnuCash's database in much the same way using a Python library called "piecash." Here's a relatively simple Python program that performs a subtle but useful operation (click here for a downloadable plain-text version). Here's a listing and a detailed description (click the provided links to highlight particular sections of the program):

• Users should edit line 21 to point to a local sqlite3-format GnuCash database file.
• Line 23 creates an instance of a piecash-library "book" object. Because it doesn't include the optional readonly=False argument, this specific book instance prevents any changes to to the datatbase — desirable in most cases .
• Line 24 calls a recursive method that scans all accounts and takes a sum of all their balances.
• Lines 12-17 is a recursive method that efficiently locates and sums the balances of all the accounts, while printing a running balance alongside the account names.

Without some understanding of double-entry bookkeeping, one might wonder how important such a full account scan might be — isn't it just a bunch of numbers thrown together? But because of how double-entry bookkeeping works, if the GnuCash database has been designed and set up correctly, with all transactions referred to by one account or another, the final balance should be zero. If this isn't true, the database needs repair and/or re-engineering, because it must contain transactions that are malformed or broken, or that refer to nonexistent sources or destinations.

Think about this. A rationally configured double-entry bookkeeping system has accounts that represent sources of income, various accounts to hold the funds, and accounts that represent expenditures and liabilities. Because the above scanner visits all the accounts in its global scan, the final result should be zero. So looked at this way, the above program is a sanity check on the structure and integrity of GnuCash's database.

Using the programs

A note about this and all programs in this section. To use one:

• Download the program by right-clicking the plain-text listing link above.
• Save the result in any convenient location, and for Linux make it executable. In a shell session:

• $ chmod +x program_name.py

• Edit the program line that refers to the target GnuCash database — replace the destination with your own.
• Run the program — on Linux, like this:

• $ ./program_name.py

• If you like the result and want to work with it further, capture and save the program's output like this:

• $ ./program_name.py > output-file-name

Python: Summarize Transactions

This program scans a chosen account, creates a list of transactions sorted by date, and provides an ending balance that should agree with that provided by GnuCash for that account. Then, in a second phase, it creates a list of category subtotals, sorted by total amount, as a way for one to see how funds are being spent. As before, click this link to acquire the program in executable plain-text form, and be sure to click the links below the listing:

• Users should edit line 12 to point to a local sqlite3-format GnuCash database file.
• Line 14 creates an instance of the piecash book object.
• Line 16 identifies the account to be summarized (see notes below on how to specify the account).
• Lines 20-46 scan the account by transaction and produce a running balance.
• Then lines 48-66 display the same results subtotaled by category.

Let's take a closer look at line 16. In this line, we identify the account of interest. In GnuCash, accounts are organized in a hierarchy, a "tree" if you will. A given account might be listed in GnuCash account transactions as "Assets:Subcategory:AccountName" as an example. To identify this example account to the above program, one would write —

account = book.
 root_account.
  children(name='Assets').
   children(name='Subcategory').
    children(name='AccountName')

— but (unlike this example and in most cases) on one line.

I find this program quite useful in ferreting out expenditures I didn't realize I was making, or in the amounts the program revealed. By summarizing categories over time, it shows how quickly small expenditures can grow.

Python: Manipulate Transactions

Using this program, one can change the source or destination of transactions, create new accounts, move blocks of transactions from one account to another, and many other things. But these powers also mean this program has the potential to thoroughly mess up a database if the operations aren't set up correctly. Always back up your GnuCash database before submitting it to this program.

I've used this program to move transactions wrongly assigned to wastebasket accounts like "Unspecified", "Imbalance-USD" and similar names during the processing of bank records. These are accounts that GnuCash creates on the fly to contain transactions whose assignment isn't clear. GnuCash does this to avoid stopping the reading of bank data for each transaction whose location in the local database isn't obvious. The idea is that the user can then visit the throwaway accounts and reassign the unidentified transactions.

I created this problem for myself by downloading an entire year of credit card transactions and submitting them to GnuCash before grasping the implications of the fact that bank credit card records don't specify categories, only the names of sources — creating and assigning categories is up to the user. The result was that I had hundreds of transactions with a destination of "Imbalance-USD".

By the time I realized the problem, I was nearly to the solution — a Python program that would read the credit card account and reassign the transactions in a more rational way. This example program is a much simpler version of my filtering program — it does something easier to understand, just to demonstrate the basic idea of scanning transactions, searching for specific content, and acting on the result.

This program's ability to manipulate transactions is by itself sufficient reason to keep one's GnuCash database in SQLite format, if there weren't other reasons — a proper database format just makes things much easier than other file types.

In this example program, transactions that involve a particular kind of payment into the account are moved to a different account. This is just an example to show how the program works — it's not something that one would be likely to do in reality.

By the way — have I mentioned that you simply must back up your GnuCash database file before thinking about using this program?

As before, click this link to acquire the program in executable plain-text form, and be sure to click the links below the listing:

• Users should edit line 13 to point to a local sqlite3-format GnuCash database file.
• Line 22 contains a flag that, if set to "False", allows the program to commit its results to your database. The default setting is recommended until the reader understands the program and all that can go wrong.
• Line 25 creates an instance of the piecash book object.
• Line 27 accesses the source account and line 32 accesses the destination. If the destination doesn't exist it's created in lines 34-40.
• Line 47 decides whether a given transaction is appropriate for the program's action. It verifies that the transaction originates in the "from" account, and that a certain phrase is present in the description that identifies it as a particular kind of payment into the account (i.e. an automatic payment). if the test succeeds, line 50 reassigns the transaction to the destination account.
• Lines 55-58 verify that some changes have been made and whether the user wants to commit those changes to the database. Remember in this connection that no changes will be made unless the readonly flag in line 22 has been set to "False."

Python: Online Quotes

At the time I write this (November 2017), GnuCash's online stock price acquisition feature has stopped working, because Yahoo has decided to shut down its stock quote server. But Yahoo isn't the only source for stock quotes online, indeed there are many. I've chosen one and written a program that uses it to insert current stock prices into a specified GnuCash database. Click here to download this program in usable plain-text form. Here's a detailed description (click the links):

• Users should edit line 23 to point to a local sqlite3-format GnuCash database file.
• In lines 59-114 in the above listing, function "acquire_online_prices()" manages the acquisition of commodity prices needed by GnuCash.
  • Lines 73-79 create a list of commodities marked within GnuCash as needing periodic price updates.
  • Line 82 eliminates any duplicate symbols.
  • Line 85 calls a function (described below) that returns a list of Web-acquired commodity prices.
  • Line 88 creates a U.S. dollar currency object — users may want to change this.
  • Lines 91-102 process the acquired prices and enter them into the GnuCash database.
  • Line 99 adds each new price to the GnuCash price table.
  • Line 105 commits the changes to the GnuCash database.
• In lines 25-57 in the above listing, function "get_prices_for()" acquires the needed commodity prices from an online source.
  • Line 33 creates a list of needed symbol names.
  • Lines 35-48 connect to an online site and acquires results for all the needed commodities.
  • Lines 51-56 process the results and create a dictionary that pairs symbols with prices.
  • Lines 53-54 use a simple regular expression to extract symbols and matching prices from the webpage.

Be careful with this program. As with all programs that can write to your database, be certain you have a backup in case of problems. The program will only acquire stock prices for stocks that have been flagged as needing price updates (in the GnuCash Security Editor), just as the earlier Yahoo-based method did, so if there are no updates, check this configuration detail.

Notes

I want to point out a few of the things about GnuCash that led to my abandoning my own program in favor of it. First, already mentioned, double-entry bookkeeping is the gold standard in accounting, and programs that can't support it are second-rate. Second and a bit more subtle, GnuCash doesn't store its numerical data as computer floating-point numbers that have well-known limitations, it stores and expresses them as the ratio of two "BIGINT" data types, i.e. two 64-bit binary numbers.

This doesn't mean one can enter a transaction dollar amount of 2⁶⁴ (18,446,744,073,709,551,616), because (a) the basis for GnuCash's internal numerical processing is the penny, not the dollar (true with appropriate terminology change for all supported currencies), and (b) a 64-bit signed integer (to allow for negative amounts) has a range of ± 2⁶³-1 (±9,223,372,036,854,775,807), which gives a maximum meaningful dollar transaction range of ±$92,233,720,368,547,758.07.

In the autumn of 2017, the U.S. national debt was about $20,467,375,664,755.00, so a GnuCash account transaction can accommodate a value about 4500 times larger than today's national debt. I would have been happier if the ratio between the national debt and GnuCash's maximum amount were larger, since we keep sending the same fools to Washington. But more to the point, if all entered amounts are expressed in integer pennies, rounding errors aren't possible because GnuCash only saves and uses integers internally.

Testing the Limits

Being a scientist and on realizing this about GnuCash, I didn't want to accept on faith that it can accommodate a range of ± 2⁶³-1 pennies, so I performed an experiment:

Yes, boys and girls, the single red "-" is what GnuCash printed as an account balance. This experiment takes GnuCash "around the bend", one penny at a time, from legitimate positive values to legitimate negative ones, with a single peculiar result that represents a meaningless value in the context of a signed integer. The conclusion? We should be able to use GnuCash to track the national debt for another five years or so, after which it will have to be rewritten to support much larger numbers. But GnuCash's use of large integers to represent values internally speaks to the quality of thinking that went into its design, and parenthetically vindicates my decision to abandon my own program.

Thanks for reading! Click here to post comments or to report errors in the Python programs.