Skip to content

Instantly share code, notes, and snippets.

@mayo
Last active February 16, 2019 06:21
Show Gist options
  • Save mayo/751b30f3e5111dbd03b257342c17a133 to your computer and use it in GitHub Desktop.
Save mayo/751b30f3e5111dbd03b257342c17a133 to your computer and use it in GitHub Desktop.
Generate Beancount ledger from Banktivityfor iOS

Unfortunately Banktivity doesn't have any good way of exporting data.

  1. Download your Banktivity data from your iOS device using iTunes File Sharing
  2. Point this at the SQLite file
  3. Output will go to stdout. Note that the transactions aren't deduped (eg. transfers between accounts).

Written for Python 3.

from datetime import date
import sqlite3
EPOCH_OFFSET = 978278400
conn = sqlite3.connect('banktivity.sql')
conn.row_factory = sqlite3.Row
first_date = None
accounts = set([])
txn_c = conn.cursor()
for txn in txn_c.execute('''SELECT ZPLINEITEM,
ZLINEITEMSOURCE.ZPDATE AS ZPDATE,
ZPAMOUNT,
ZPDETAILS,
ZPACCOUNT,
ZPTRANSACTION,
ZPNOTE,
ZPTITLE
FROM ZLINEITEMSOURCE
LEFT JOIN ZLINEITEM ON (ZLINEITEM.Z_PK=ZLINEITEMSOURCE.ZPLINEITEM)
LEFT JOIN ZTRANSACTION ON (ZLINEITEM.ZPTRANSACTION=ZTRANSACTION.Z_PK)
ORDER BY ZLINEITEMSOURCE.ZPDATE ASC'''):
#WHERE ZLINEITEMSOURCE.Z_PK=2213
# Now we have the main bank transaction
txn_id = txn['ZPTRANSACTION']
txn_date = date.fromtimestamp(txn['ZPDATE'] + EPOCH_OFFSET)
txn_note = txn['ZPNOTE']
if not first_date:
first_date = txn_date
if not txn_note:
if txn['ZPDETAILS'].startswith(txn['ZPTITLE']) and len(txn['ZPDETAILS']) != len(txn['ZPTITLE']):
txn_note = txn['ZPDETAILS'][len(txn['ZPTITLE']) + 3:]
elif len(txn['ZPTITLE']) > len(txn['ZPDETAILS']):
txn_note = txn['ZPTITLE']
else:
txn_note = txn['ZPDETAILS']
print('''{:%Y-%m-%d} * "{:s}"'''.format(txn_date, txn_note))
print(''' ;{}'''.format(txn['ZPTITLE']))
print(''' ;{}'''.format(txn['ZPNOTE']))
print(''' ;{}'''.format(txn['ZPDETAILS']))
# Get all associated entries
entry_c = conn.cursor()
entries = entry_c.execute('''SELECT *
FROM ZLINEITEM
LEFT JOIN ZACCOUNT ON (ZACCOUNT.Z_PK=ZLINEITEM.ZPACCOUNT)
WHERE ZPTRANSACTION=?
ORDER BY ZLINEITEM.Z_PK ASC''', (txn_id,)).fetchall()
for entry in entries:
account = entry['ZPFULLNAME'] or 'UnknownAccount'
amount = entry['ZPTRANSACTIONAMOUNT']
width = 51 - len(str(amount))
print(''' {:<{width}s} {:.2f} CAD'''.format(account, amount, width=width))
accounts.add(account)
print()
for account in accounts:
print('''{:%Y-%m-%d} open {:s}'''.format(first_date, account))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment