Generate Beancount ledger from Banktivityfor iOS

readme.md

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.

banktivity.py

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