Documentation

This project provides a simple and pythonic interface to GnuCash files stored in SQL (sqlite3, Pandostgres and MySQL) for Linux and Windows (not tested on Mac OS).

piecash is a pure python package, tested on python 3.6/3.7/3.8, that can be used as an alternative to:

  • the official python bindings (as long as no advanced book modifications and/or engine calculations are needed). This is specially useful on Windows where the official python bindings may be tricky to install or if you want to work with python 3.

  • XML parsing/reading of XML GnuCash files if you prefer python over XML/XLST manipulations.

piecash is built on the excellent SQLAlchemy library and does not require the installation of GnuCash itself.

piecash allows you to:

  • create a GnuCash book from scratch or edit an existing one

  • create new accounts, transactions, etc or change (within some limits) existing objects.

  • read/browse all objects through an intuitive interface

A simple example of a piecash script:

with open_book("example.gnucash") as book:
    # get default currency of book
    print( book.default_currency )  # ==> Commodity<CURRENCY:EUR>

    # iterating over all splits in all books and print the transaction description:
    for acc in book.accounts:
        for sp in acc.splits:
            print(sp.transaction.description)

As piecash is essentially a SQLAlchemy layer, it could be potentially reused by any web framework that has a SQLAlchemy interface to develop REST API or classical websites. It can also be used for reporting purposes.

The project has reached beta stage. Knowledge of SQLAlchemy is at this stage not anymore required to use it and/or to contribute to it. Some documentation for developers on the object model of GnuCash as understood by the author is available here.

Warning

  1. Always do a backup of your gnucash file/DB before using piecash.

  2. Test first your script by opening your file in readonly mode (which is the default mode)

Installation

To install with pip:

$ pip install piecash

or to upgrade if piecash is already installed:

$ pip install -U piecash
piecash comes with 6 extra options (each option depends on extra packages that will be installed only if the option is chosen):
For developers, two extra options:
  • test: to install what is needed for testing piecash

  • dev: to install what is needed for developing piecash (docs, …)

To install these options, simply specify them between brackets after the piecash package:

$ pip install piecash[pandas,qif,postgres]

To install with pipenv:

$ pipenv install piecash

Otherwise, you can install by unpacking the source distribution from PyPI and then:

$ python setup.py install

If you are on MS Windows and not so familiar with python, we would suggest you to install the miniconda python distribution from Continuum Analytics available at http://conda.pydata.org/miniconda.html (you can choose whatever version 3.X of python you would like) and then run the following command in the command prompt (cmd.exe):

$ conda create -n piecash_venv python=3 pip sqlalchemy
$ activate piecash_venv
$ pip install piecash

The first command create a new python environment named “piecash_venv” with python 3.7, pip and sqlalchemy installed.

The second command activates the newly created piecash_venv. Afterwards, you only need to execute this command before using python through the command line.

The third command installs piecash and its dependencies. piecash depends also on sqlalchemy but as the sqlalchemy package requires a compiler if it is installed through pip, we found it easier to install it through conda (this is done in the first command).

If you need to use directly the python interpreter in the newly created “piecash_env”, you can find it installed in your user folder under Miniconda3\envs\piecash_venv\python.exe (or Miniconda2\…).

On OS X, this option may also be valuable.

Quickstart

The simplest workflow to use piecash starts by opening a GnuCash file

import piecash

# open a GnuCash Book
book = piecash.open_book("test.gnucash", readonly=True)

and then access GnuCash objects through the book, for example to query the stock prices

# example 1, print all stock prices in the Book
# display all prices
for price in book.prices:
    print(price)
<Price 2014-12-22 : 0.702755 EUR/CAD>
<Price 2014-12-19 : 0.695658 EUR/CAD>
<Price 2014-12-18 : 0.689026 EUR/CAD>
<Price 2014-12-17 : 0.69005 EUR/CAD>
<Price 2014-12-16 : 0.693247 EUR/CAD>
<Price 2014-12-22 : 51.15 USD/YHOO>
<Price 2014-12-19 : 50.88 USD/YHOO>
<Price 2014-12-18 : 50.91 USD/YHOO>
<Price 2014-12-17 : 50.12 USD/YHOO>
<Price 2014-12-16 : 48.85 USD/YHOO>
...

or to query the accounts:

for account in book.accounts:
    print(account)
Account<[EUR]>
Account<Assets[EUR]>
Account<Assets:Current Assets[EUR]>
Account<Assets:Current Assets:Checking Account[EUR]>
Account<Assets:Current Assets:Savings Account[EUR]>
Account<Assets:Current Assets:Cash in Wallet[EUR]>
Account<Income[EUR]>
Account<Income:Bonus[EUR]>
Account<Income:Gifts Received[EUR]>
...
Account<Expenses[EUR]>
Account<Expenses:Commissions[EUR]>
Account<Expenses:Adjustment[EUR]>
Account<Expenses:Auto[EUR]>
Account<Expenses:Auto:Fees[EUR]>
...
Account<Liabilities[EUR]>
Account<Liabilities:Credit Card[EUR]>
Account<Equity[EUR]>
Account<Equity:Opening Balances[EUR]>
...

or to create a new expense account for utilities:

# retrieve currency
EUR = book.commodities.get(mnemonic='EUR')

# retrieve parent account
acc_exp = book.accounts.get(fullname="Expenses:Utilities")

# add a new subaccount to this account of type EXPENSE with currency EUR
new_acc = piecash.Account(name="Cable", type="EXPENSE", parent=acc_exp, commodity=EUR)

# save changes (it should raise an exception if we opened the book as readonly)
book.save()

Most basic objects used for personal finance are supported (Account, Split, Transaction, Price, …).

The piecash command line interface

The piecash CLI offers the following features:

$ piecash -h
Usage: piecash [OPTIONS] COMMAND [ARGS]...

Options:
  -h, --help  Show this message and exit.

Commands:
  export      Exports GnuCash ENTITIES.
  ledger      Export to ledger-cli format.
  qif         Export to QIF format.
  sql-create  Create an empty book with gnucash
  sql-dump    Dump SQL schema of the gnucash sqlite book

To export specific entities out of a GnuCash book:

$ piecash export -h
Usage: piecash export [OPTIONS] BOOK [customers|vendors|prices]

  Exports GnuCash ENTITIES.

  This scripts export ENTITIES from the BOOK in a CSV format. When possible,
  it exports in a format that can be used to import the data into GnuCash.

  Remarks:
  - for customers and vendors, the format does not include an header
  - for prices, the format can be used with the `piecash import` command.

Options:
  --output FILENAME  File to which to export the data (default=stdout)
  --inactive         Include inactive entities (for vendors and customers)
  -h, --help         Show this message and exit.

To export a GnuCash book to the ledger-cli format:

$ piecash ledger -h
Usage: piecash ledger [OPTIONS] BOOK

  Export to ledger-cli format.

  This scripts export a GnuCash BOOK to the ledget-cli format.

Options:
  --locale / --no-locale          Export currency amounts using locale for
                                  currencies format

  --commodity-notes / --no-commodity-notes
                                  Include the commodity_notes for the
                                  commodity (hledger does not support
                                  commodity commodity_notes

  --short-account-names / --no-short-account-names
                                  Use the short name for the accounts instead
                                  of the full hierarchical name.

  --output FILENAME               File to which to export the data
                                  (default=stdout)

  -h, --help                      Show this message and exit.

Or in python

In [1]: book = open_book(gnucash_books + "simple_sample.gnucash", open_if_lock=True)

In [2]: from piecash import ledger

# printing the ledger-cli (https://www.ledger-cli.org/) representation of the book
In [3]: print(ledger(book))
commodity EUR

account Asset
	check commodity == "EUR"

account Liability
	check commodity == "EUR"

account Income
	check commodity == "EUR"

account Expense
	check commodity == "EUR"

account Equity
	check commodity == "EUR"

account Equity:Opening Balances - EUR
	check commodity == "EUR"


2014-11-30 Opening Balance
	Equity:Opening Balances - EUR             EUR -500.00
	Asset                                     EUR 500.00

2014-12-24 initial load
	Liability                                 EUR -1,000.00
	Asset                                     EUR 1,000.00

2014-12-24 expense 1
	Asset                                     EUR -200.00
	Expense                                   EUR 200.00

2014-12-24 income 1
	Income                                    EUR -150.00
	Asset                                     EUR 150.00

2014-12-24 loan payment
	Asset                                     EUR -130.00 ;   monthly payment     
	Expense                                   EUR 30.00 ;   interest            
	Liability                                 EUR 100.00 ;   capital             



# printing the ledger-cli (https://www.ledger-cli.org/) representation of the book using regional settings (locale) for currency output
In [4]: print(ledger(book, locale=True))
commodity €

account Asset
	check commodity == "EUR"

account Liability
	check commodity == "EUR"

account Income
	check commodity == "EUR"

account Expense
	check commodity == "EUR"

account Equity
	check commodity == "EUR"

account Equity:Opening Balances - EUR
	check commodity == "EUR"


2014-11-30 Opening Balance
	Equity:Opening Balances - EUR             -€500.00
	Asset                                     €500.00

2014-12-24 initial load
	Liability                                 -€1,000.00
	Asset                                     €1,000.00

2014-12-24 expense 1
	Asset                                     -€200.00
	Expense                                   €200.00

2014-12-24 income 1
	Income                                    -€150.00
	Asset                                     €150.00

2014-12-24 loan payment
	Asset                                     -€130.00 ;   monthly payment     
	Expense                                   €30.00 ;   interest            
	Liability                                 €100.00 ;   capital             

For more information on how to use piecash, please refer to the Tutorials on Using existing objects and Creating new objects, the Example scripts or the package documentation.