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 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 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))
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 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))
def testAddandGetAccounts(self):
qif_obj = qif.Qif()
acc = qif.Account(name='My Cc')
qif_obj.add_account(acc)
res = qif_obj.get_accounts(name='My Cc')
self.assertTrue(len(res))