Examples of programs written with piecash

You can find examples of programs/scripts (loosely based on the scripts for the official python bindings for gnucash or on questions posted on the mailing list) in the examples subfolder.

Creating and opening gnucash files

from __future__ import print_function
import os
import tempfile

from piecash import open_book, create_book, GnucashException


FILE_1 = os.path.join(tempfile.gettempdir(), "not_there.gnucash")
FILE_2 = os.path.join(tempfile.gettempdir(), "example_file.gnucash")

if os.path.exists(FILE_2):
    os.remove(FILE_2)

# open a file that isn't there, detect the error
try:
    book = open_book(FILE_1)
except GnucashException as backend_exception:
    print("OK", backend_exception)

# create a new file, this requires a file type specification
with create_book(FILE_2) as book:
    pass

# open the new file, try to open it a second time, detect the lock
# using the session as context manager automatically release the lock and close the session
with open_book(FILE_2) as book:
    try:
        with open_book(FILE_2) as book_2:
            pass
    except GnucashException as backend_exception:
        print("OK", backend_exception)

os.remove(FILE_2)

Creating an account

#!/usr/bin/env python
##  @file
#   @brief Example Script simple sqlite create
#   @ingroup python_bindings_examples

from __future__ import print_function
import os

from piecash import create_book, Account, Commodity, open_book
from piecash.core.factories import create_currency_from_ISO

filename = os.path.abspath("test.blob")
if os.path.exists(filename):
    os.remove(filename)

with create_book(filename) as book:
    a = Account(
        parent=book.root_account,
        name="wow",
        type="ASSET",
        commodity=create_currency_from_ISO("CAD"),
    )

    book.save()

with open_book(filename) as book:
    print(book.root_account.children)
    print(book.commodities.get(mnemonic="CAD"))

os.remove(filename)

Creating a transaction

#!/usr/bin/env python
# # @file
# @brief Creates a basic set of accounts and a couple of transactions
# @ingroup python_bindings_examples
from decimal import Decimal
import os
import tempfile

from piecash import create_book, Account, Transaction, Split, Commodity
from piecash.core.factories import create_currency_from_ISO

FILE_1 = os.path.join(tempfile.gettempdir(), "example.gnucash")

with create_book(FILE_1, overwrite=True) as book:
    root_acct = book.root_account
    cad = create_currency_from_ISO("CAD")
    expenses_acct = Account(
        parent=root_acct, name="Expenses", type="EXPENSE", commodity=cad
    )
    savings_acct = Account(parent=root_acct, name="Savings", type="BANK", commodity=cad)
    opening_acct = Account(
        parent=root_acct, name="Opening Balance", type="EQUITY", commodity=cad
    )
    num1 = Decimal("4")
    num2 = Decimal("100")
    num3 = Decimal("15")

    # create transaction with core objects in one step
    trans1 = Transaction(
        currency=cad,
        description="Groceries",
        splits=[
            Split(value=num1, account=expenses_acct),
            Split(value=-num1, account=savings_acct),
        ],
    )

    # create transaction with core object in multiple steps
    trans2 = Transaction(currency=cad, description="Opening Savings Balance")

    split3 = Split(value=num2, account=savings_acct, transaction=trans2)

    split4 = Split(value=-num2, account=opening_acct, transaction=trans2)

    # create transaction with factory function
    from piecash.core.factories import single_transaction

    trans3 = single_transaction(
        None, None, "Pharmacy", num3, savings_acct, expenses_acct
    )

    book.save()

Modifying existing transactions/splits

from piecash import open_book, ledger, Split

# open a book
with open_book(
    "../gnucash_books/simple_sample.gnucash", readonly=True, open_if_lock=True
) as mybook:
    # iterate on all the transactions in the book
    for transaction in mybook.transactions:
        # add some extra text to the transaction description
        transaction.description = (
            transaction.description + " (some extra info added to the description)"
        )
        # iterate over all the splits of the transaction
        # as we will modify the transaction splits in the loop,
        # we need to use list(...) to take a copy of the splits at the start of the loop
        for split in list(transaction.splits):
            # create the new split (here a copy of the each existing split
            # in the transaction with value/quantity divided by 10)
            new_split = Split(
                account=split.account,
                value=split.value / 10,
                quantity=split.quantity / 10,
                memo="my new split",
                transaction=transaction,  # attach the split to the current transaction
            )
    # register the changes (but not save)
    mybook.flush()

    # print the book in ledger format to view the changes
    print(ledger(mybook))

    # save the book
    # this will raise an error as readonly=True (change to readonly=False to successfully save the book)
    mybook.save()

Delete an account in a book

import csv
from pathlib import Path

from piecash import open_book, Account

GNUCASH_BOOK = "../gnucash_books/simple_sample.gnucash"

# open the book and the export file
with open_book(GNUCASH_BOOK, readonly=True, open_if_lock=True) as book:
    # show accounts
    print(book.accounts)
    print("Number of splits in the book:", len(book.splits))
    # select the 3rd account
    account = book.accounts[2]
    print(account, " has splits: ", account.splits)

    # delete the account from the book
    book.delete(account)
    # flush the change
    book.flush()
    # check the account has disappeared from the book and its related split too
    print(book.accounts)
    print("Number of splits in the book:", len(book.splits))

    # even if the account object and its related object still exists
    print(account, " has splits: ", account.splits)

    # do not forget to save the book if you want
    # your changes to be saved in the database

Save/cancel changes in a book

from __future__ import print_function
from piecash import create_book

# create by default an in memory sqlite version
with create_book(echo=False) as book:

    print("Book is saved:", book.is_saved, end=" ")
    print(" ==> book description:", book.root_account.description)

    print("changing description...")
    book.root_account.description = "hello, book"
    print("Book is saved:", book.is_saved, end=" ")
    print(" ==> book description:", book.root_account.description)

    print("saving...")
    book.save()

    print("Book is saved:", book.is_saved, end=" ")
    print(" ==> book description:", book.root_account.description)

    print("changing description...")
    book.root_account.description = "nevermind, book"
    print("Book is saved:", book.is_saved, end=" ")
    print(" ==> book description:", book.root_account.description)

    print("cancel...")
    book.cancel()

    print("Book is saved:", book.is_saved, end=" ")
    print(" ==> book description:", book.root_account.description)

Create a book with some accounts and add a transaction

from piecash import create_book, Account

# create a book with some account tree structure
with create_book(
    "../gnucash_books/simple_book_transaction_creation.gnucash", overwrite=True
) as mybook:
    mybook.root_account.children = [
        Account(
            name="Expenses",
            type="EXPENSE",
            commodity=mybook.currencies(mnemonic="USD"),
            placeholder=True,
            children=[
                Account(
                    name="Some Expense Account",
                    type="EXPENSE",
                    commodity=mybook.currencies(mnemonic="USD"),
                ),
            ],
        ),
        Account(
            name="Assets",
            type="ASSET",
            commodity=mybook.currencies(mnemonic="USD"),
            placeholder=True,
            children=[
                Account(
                    name="Current Assets",
                    type="BANK",
                    commodity=mybook.currencies(mnemonic="USD"),
                    placeholder=True,
                    children=[
                        Account(
                            name="Checking",
                            type="BANK",
                            commodity=mybook.currencies(mnemonic="USD"),
                        )
                    ],
                ),
            ],
        ),
    ]
    # save the book
    mybook.save()

from piecash import open_book, Transaction, Split
from datetime import datetime
from decimal import Decimal

# reopen the book and add a transaction
with open_book(
    "../gnucash_books/simple_book_transaction_creation.gnucash",
    open_if_lock=True,
    readonly=False,
) as mybook:
    today = datetime.now()
    # retrieve the currency from the book
    USD = mybook.currencies(mnemonic="USD")
    # define the amount as Decimal
    amount = Decimal("25.35")
    # retrieve accounts
    to_account = mybook.accounts(fullname="Expenses:Some Expense Account")
    from_account = mybook.accounts(fullname="Assets:Current Assets:Checking")
    # create the transaction with its two splits
    Transaction(
        post_date=today.date(),
        enter_date=today,
        currency=USD,
        description="Transaction Description!",
        splits=[
            Split(account=to_account, value=amount, memo="Split Memo!"),
            Split(account=from_account, value=-amount, memo="Other Split Memo!"),
        ],
    )
    # save the book
    mybook.save()

from piecash import ledger

# check the book by exporting to ledger format
with open_book(
    "../gnucash_books/simple_book_transaction_creation.gnucash", open_if_lock=True
) as mybook:
    print(ledger(mybook))

Export transactions to a CSV file

import csv
from pathlib import Path

from piecash import open_book

fields = [
    "DATE",
    "TRANSACTION VALUE",
    "DEBIT/CREDIT INDICATOR",
    "ACCOUNT",
    "ACCOUNT CODE",
    "CONTRA ACCOUNT",
    "CONTRA ACCOUNT CODE",
    "ENTRY TEXT",
]

GNUCASH_BOOK = "../gnucash_books/simple_sample.gnucash"
CSV_EXPORT = "export.csv"
REPORTING_YEAR = 2019

# open the book and the export file
with open_book(GNUCASH_BOOK, readonly=True, open_if_lock=True) as mybook, Path(
    CSV_EXPORT
).open("w", newline="") as f:
    # initialise the CSV writer
    csv_writer = csv.DictWriter(f, fieldnames=fields)
    csv_writer.writeheader()

    # iterate on all the transactions in the book
    for transaction in mybook.transactions:
        # filter transactions not in REPORTING_YEAR
        if transaction.post_date.year != REPORTING_YEAR:
            continue

        # handle only transactions with 2 splits
        if len(transaction.splits) != 2:
            print(
                f"skipping transaction {transaction} as it has more"
                f" than 2 splits in the transaction, dunno what to export to CSV"
            )
            continue

        # assign the two splits of the transaction
        split_one, split_two = transaction.splits
        # build the dictionary with the data of the transaction
        data = dict(
            zip(
                fields,
                [
                    transaction.post_date,
                    split_one.value,
                    split_one.is_debit,
                    split_one.account.name,
                    split_one.account.code,
                    split_two.account.name,
                    split_two.account.code,
                    transaction.description,
                ],
            )
        )
        # write the transaction to the CSV
        csv_writer.writerow(data)

Extract Split information as pandas DataFrame

from piecash import open_book

# open a book
with open_book("../gnucash_books/simple_sample.gnucash", open_if_lock=True) as mybook:
    # print all splits in account "Asset"
    asset = mybook.accounts(fullname="Asset")
    for split in asset.splits:
        print(split)

    # extract all split information to a pandas DataFrame
    df = mybook.splits_df()

    # print for account "Asset" some information on the splits
    print(df.loc[df["account.fullname"] == "Asset", ["transaction.post_date", "value"]])

Filtered transaction reports

from __future__ import print_function
import datetime
import re
import os.path

from piecash import open_book


if __name__ == "__main__":
    this_folder = os.path.dirname(os.path.realpath(__file__))
    s = open_book(
        os.path.join(this_folder, "..", "gnucash_books", "simple_sample.gnucash"),
        open_if_lock=True,
    )
else:
    s = open_book(
        os.path.join("gnucash_books", "simple_sample.gnucash"), open_if_lock=True
    )

# get default currency
print(s.default_currency)

regex_filter = re.compile("^/Rental/")

# retrieve relevant transactions
transactions = [
    tr
    for tr in s.transactions  # query all transactions in the book/session and filter them on
    if (
        regex_filter.search(tr.description)  # description field matching regex
        or any(regex_filter.search(spl.memo) for spl in tr.splits)
    )  # or memo field of any split of transaction
    and tr.post_date.date() >= datetime.date(2014, 11, 1)
]  # and with post_date no later than begin nov.


# output report with simple 'print'
print(
    "Here are the transactions for the search criteria '{}':".format(
        regex_filter.pattern
    )
)
for tr in transactions:
    print("- {:%Y/%m/%d} : {}".format(tr.post_date, tr.description))
    for spl in tr.splits:
        print(
            "\t{amount}  {direction}  {account} : {memo}".format(
                amount=abs(spl.value),
                direction="-->" if spl.value > 0 else "<--",
                account=spl.account.fullname,
                memo=spl.memo,
            )
        )

# same with jinja2 templates
try:
    import jinja2
except ImportError:
    print(
        "\n\t*** Install jinja2 ('pip install jinja2') to test the jinja2 template version ***\n"
    )
    jinja2 = None

if jinja2:
    env = jinja2.Environment(trim_blocks=True, lstrip_blocks=True)
    print(
        env.from_string(
            """
    Here are the transactions for the search criteria '{{regex.pattern}}':
    {% for tr in transactions %}
    - {{ tr.post_date.strftime("%Y/%m/%d") }} : {{ tr.description }}
      {% for spl in tr.splits %}
        {{ spl.value.__abs__() }} {% if spl.value < 0 %} --> {% else %} <-- {% endif %} {{ spl.account.fullname }} : {{ spl.memo }}
      {% endfor %}
    {% endfor %}
    """
        ).render(transactions=transactions, regex=regex_filter)
    )