def testcreateQifFile(self): qif_obj = qif.Qif() acc = qif.Account(name='My Cc', account_type='Bank') qif_obj.add_account(acc) cat = qif.Category(name='food') qif_obj.add_category(cat) tr1 = qif.Transaction(amount=0.55) acc.add_transaction(tr1, header='!Type:Bank') tr2 = qif.Transaction() tr2.amount = -6.55 tr2.to_account = 'Cash' acc.add_transaction(tr2) self.assertTrue(str(qif_obj))
def testCreateSplit(self): expected='''!Account NAssets:Checking Accounts:Checking TBank ^ !Type:Bank D02/14/2004 T-100 S[Expenses:Groceries] $-40 S[Expenses:Clothing] $-60 ^ ''' qif_obj = qif.Qif() chk_acc = qif.Account(name='Assets:Checking Accounts:Checking', account_type='Bank') qif_obj.add_account(chk_acc) expense = qif.Transaction() expense.date = datetime(2004, 2, 14) expense.amount = -100 splitA = qif.AmountSplit() splitA.to_account = 'Expenses:Groceries' splitA.amount = -40 expense.splits.append(splitA) splitB = qif.AmountSplit() splitB.to_account = 'Expenses:Clothing' splitB.amount = -60 expense.splits.append(splitB) chk_acc.add_transaction(expense, header='!Type:Bank') self.assertEqual(expected, str(qif_obj))
def main(): today = str(dt.date.today()) # format_str required for interpretating date by qifparse format_str = "%Y-%m-%d" # check current directory for matching files by Rabobank flist = [] for file in os.listdir("."): if (file.endswith(".csv") and (file.startswith("CSV_A")) or file.startswith("CSV_O")): flist.append(file) # iterate csv-files and generate related qif file(s) for f in flist: df = pd.read_csv(f, thousands=",", encoding="latin1") # define list of accounts and rename columns alist = df["IBAN/BBAN"].unique().tolist() columndict = { "Datum": "date", "Naam tegenpartij": "payee", "Omschrijving-1": "memo", "Bedrag": "amount", } df.rename(columns=columndict, inplace=True) df.loc[:, "amount"] = df["amount"] / 100 # establish qif_obj qif_obj = qif.Qif() for a in alist: acc = qif.Account(name=str(a)) qif_obj.add_account(acc) print(acc) for index, row in df[df["IBAN/BBAN"] == a].iterrows(): # print(index,row) tr = qif.Transaction() tr.amount = row["amount"] tr.date = dt.datetime.strptime(row["date"], format_str) tr.payee = row["payee"] tr.memo = row["memo"] # tr.to_account = itag acc.add_transaction(tr, header="!Type:Bank") print(tr) fname = "Import_" + today + "_" + str(f) + "_.qif" with open(fname, "w") as output: output.write(str(qif_obj))
for tr in s.transactions: if not tr.splits or len(tr.splits) < 2: continue # split empty transactions if tr.splits[0].account.parent == tpl: continue # skip template transactions assert isinstance(tr, Transaction) splits = sorted(tr.splits, key=sort_split) if all(sp.account.commodity.namespace == "CURRENCY" for sp in splits): sp1, sp2 = splits[:2] item = qif.Transaction( date=tr.post_date, num=tr.num, payee=tr.description, amount=sp1.value, memo=sp1.memo, ) if isinstance(map[sp2.account.fullname], qif.Account): item.to_account = sp2.account.fullname else: item.category = sp2.account.fullname if len(splits) > 2: for sp in splits[1:]: if isinstance(map[sp.account.fullname], qif.Account): asp = qif.AmountSplit( amount=-sp.value, memo=sp.memo, to_account=sp.account.fullname,
def qif(book, output): """Export to QIF format. This scripts export a GnuCash BOOK to the QIF format. """ import piecash map_gnc2qif = { "CASH": 'Cash', "BANK": 'Bank', "RECEIVABLE": 'Bank', "PAYABLE": 'Ccard', "MUTUAL": 'Bank', "CREDIT": 'Ccard', "ASSET": 'Oth A', "LIABILITY": 'Oth L', "TRADING": 'Oth L', # 'Invoice', # Quicken for business only "STOCK": 'Invst', } with piecash.open_book(book, open_if_lock=True) as s: b = _qif.Qif() map = {} for acc in s.accounts: if acc.parent == s.book.root_template: continue elif acc.type in ["INCOME", "EXPENSE", "EQUITY"]: item = _qif.Category( name=acc.fullname, description=acc.description, expense=acc.type == "EXPENSE", income=acc.type == "INCOME" or acc.type == "EQUITY", ) b.add_category(item) elif acc.type in map_gnc2qif: actype = map_gnc2qif[acc.type] if actype == "Invst": # take parent account item = _qif.Account(name=acc.fullname, account_type=actype) else: item = _qif.Account(name=acc.fullname, account_type=actype) b.add_account(item) else: print("unknow {} for {}".format(acc.type, acc.fullname)) map[acc.fullname] = item # print(str(b)) def split_type(sp): qif_obj = map[sp.account.fullname] if isinstance(qif_obj, _qif.Account): return qif_obj.account_type else: return "Expense" if qif_obj.expense else "Income" def sort_split(sp): type = split_type(sp) if type == "Invst": return 1 elif type in ["Expense", "Income"]: return 2 else: return 0 tpl = s.book.root_template for tr in s.transactions: if not tr.splits or len(tr.splits) < 2: continue # split empty transactions if tr.splits[0].account.parent == tpl: continue # skip template transactions splits = sorted(tr.splits, key=sort_split) if all(sp.account.commodity.namespace == "CURRENCY" for sp in splits): sp1, sp2 = splits[:2] item = _qif.Transaction( date=tr.post_date, num=tr.num, payee=tr.description, amount=sp1.value, memo=sp1.memo, ) if isinstance(map[sp2.account.fullname], _qif.Account): item.to_account = sp2.account.fullname else: item.category = sp2.account.fullname if len(splits) > 2: for sp in splits[1:]: if isinstance(map[sp.account.fullname], _qif.Account): asp = _qif.AmountSplit( amount=-sp.value, memo=sp.memo, to_account=sp.account.fullname, ) else: asp = _qif.AmountSplit( amount=-sp.value, memo=sp.memo, category=sp.account.fullname, ) item.splits.append(asp) map[sp1.account.fullname].add_transaction( item, header="!Type:{}".format( map[sp1.account.fullname].account_type)) else: # match pattern of splits for an investment sp_account, sp_security, sp_others = splits[0], splits[ 1], splits[2:] assert split_type(sp_account) in ["Bank", "Cash"] assert split_type(sp_security) in ["Invst"] assert all(split_type(sp) == "Expense" for sp in sp_others) assert sp_security.account.parent.type == "BANK", "Security account has no parent STOCK account (aka a Brokerage account)" item = _qif.Investment( date=tr.post_date, action="Buy" if sp_security.quantity > 0 else "Sell", security=sp_security.account.commodity.mnemonic, price=sp_security.value / sp_security.quantity, quantity=sp_security.quantity, amount=sp_security.value, commission=sum(sp.value for sp in sp_others), first_line=tr.description, to_account=sp_account.account.fullname, amount_transfer=abs(sp_account.value), ) map[sp_security.account.fullname]\ .add_transaction(item, header="!Type:{}".format(split_type(sp_security))) output.write(str(b))