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))