Skip to content

Instantly share code, notes, and snippets.

@tiramiseb

tiramiseb/homebank_future.py

Created June 4, 2016 18:51
Show Gist options
  • Save tiramiseb/80e594072e8655e3ba28d651c63b32ba to your computer and use it in GitHub Desktop.
Save tiramiseb/80e594072e8655e3ba28d651c63b32ba to your computer and use it in GitHub Desktop.
Download ZIP
Display a table of future operations for HomeBank (it may not work for you, I have made it specific to my needs)
Raw
homebank_future.py
#!/usr/bin/env python
# -!- coding: utf8 -!-
import calendar
import datetime
import locale
import xml.etree.ElementTree as ET
from gi.repository import Gtk
# First install python-prettytable
SOURCE = "<your homebank file>.xhb"
PERIOD_END_IN_X_DAYS = 365 * 2
###############################################################################
# Initialize variables
# Strings for displaying
locale.setlocale(locale.LC_ALL, '')
months_names = calendar.month_name[1:]
categories_text = u"Catégories"
end_of_months_text = u"Fins de mois"
expenses_text = u"Dépenses"
incomes_text = u'Revenus'
no_category_text = u'pas de catégorie'
totals_text = u"Totaux"
categories = {'0': {'name': no_category_text, 'operations': {},
'children': {}, 'amounts': {}, 'type': 'debit',
'budget': [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]}}
totals = {}
end_of_months = {}
operations_dates = {}
initial = 0
today = datetime.datetime.now()
current_month = datetime.datetime(year=today.year, month=today.month, day=1)
end_date = today + datetime.timedelta(days=PERIOD_END_IN_X_DAYS)
# Validation levels
PAST_OPERATION = 1
FUTURE_OPERATION = 2
PLANNED_OPERATION = 3
BUDGET = 4
font_colors = [
u"#000000",
u"#0000aa",
u"#00aaaa",
u"#aa00aa",
u"#aa0000"
]
overbudget_prefix = u'<span foreground="#009900">\u2714</span> '
underbudget_prefix = u'<span foreground="#FF0000">\u2718</span> '
CREDIT_CATEGORY_FLAG = 2
REPEAT_UNIT_DAY = 0 # "unit" attribute undefined
REPEAT_UNIT_WEEK = 1
REPEAT_UNIT_MONTH = 2
REPEAT_UNIT_YEAR = 3
REPEAT_FAV_FLAG = 4
REPEAT_LIMIT_ENABLED_FLAG = 128
ALLOW_WEEKEND = 0 # "weekend" attribute undefined
FORCE_BEFORE_WEEKEND = 1
FORCE_AFTER_WEEKEND = 2
###############################################################################
# Functions
# Start day is 1 day before 1/1/1, but Python cannot work on dates before 1/1/1
start = datetime.datetime(1, 1, 1, 0, 0)
startdelta = datetime.timedelta(days=1)
def timestamp_to_datetime(stamp):
return datetime.timedelta(days=int(stamp))+start-startdelta
def get_category_by_id(cat_id, parent=categories):
if parent.has_key(cat_id):
return parent[cat_id]
else:
for newparent in parent.values():
cat = get_category_by_id(cat_id, newparent['children'])
if cat:
return cat
return None
def add_operation(operation):
print operation.get('category', '0')
category = get_category_by_id(operation.get('category', '0'))
# print category
year = operation['date'].year
month = operation['date'].month - 1 # jan=0, dec=11
op_amount = float(operation['amount'])
if not category['operations'].has_key(year):
category['operations'][year] = [[],[],[],[],[],[],[],[],[],[],[],[]]
if not category['amounts'].has_key(year):
category['amounts'][year] = [{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0}]
category['operations'][year][month].append(operation)
category['amounts'][year][month]['amount'] += op_amount
if operation['type'] == 'regular' and operation['date'] < today:
if category['amounts'][year][month]['validated'] < PAST_OPERATION:
category['amounts'][year][month]['validated'] = PAST_OPERATION
elif operation['type'] == 'regular':
if category['amounts'][year][month]['validated'] < FUTURE_OPERATION:
category['amounts'][year][month]['validated'] = FUTURE_OPERATION
elif operation['type'] == 'planned':
if category['amounts'][year][month]['validated'] < PLANNED_OPERATION:
category['amounts'][year][month]['validated'] = PLANNED_OPERATION
elif operation['type'] == 'budget':
if category['amounts'][year][month]['validated'] < BUDGET:
category['amounts'][year][month]['validated'] = BUDGET
if category.has_key('parent'):
parent = get_category_by_id(category['parent'])
if not parent['amounts'].has_key(year):
parent['amounts'][year] = [{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0}]
parent['amounts'][year][month]['amount'] += op_amount
if operation['type'] == 'regular' and operation['date'] < today:
if parent['amounts'][year][month]['validated'] < PAST_OPERATION:
parent['amounts'][year][month]['validated'] = PAST_OPERATION
elif operation['type'] == 'regular':
if parent['amounts'][year][month]['validated'] < FUTURE_OPERATION:
parent['amounts'][year][month]['validated'] = FUTURE_OPERATION
elif operation['type'] == 'planned':
if parent['amounts'][year][month]['validated'] < PLANNED_OPERATION:
parent['amounts'][year][month]['validated'] = PLANNED_OPERATION
if not operations_dates.has_key(year):
operations_dates[year] = []
if not month in operations_dates[year]:
operations_dates[year].append(month)
if not totals.has_key(year):
totals[year] = [[0,0,0], [0,0,0], [0,0,0], [0,0,0], [0,0,0], [0,0,0],
[0,0,0], [0,0,0], [0,0,0], [0,0,0], [0,0,0], [0,0,0]]
totals[year][month][0] += op_amount
if op_amount >= 0:
income_expense_index = 1
else:
income_expense_index = 2
totals[year][month][income_expense_index] += op_amount
###############################################################################
# Parse data
tree = ET.parse(SOURCE)
root = tree.getroot()
###############################################################################
# Parse accounts
for account in root.iter('account'):
initial += float(account.attrib['initial'])
###############################################################################
# Parse categories
for cat in root.iter('cat'):
category = cat.attrib
if int(category.get('flags', 0)) & CREDIT_CATEGORY_FLAG:
category['type'] = 'credit'
else:
category['type'] = 'debit'
budget_all = category.get('b0', 0)
category['budget'] = [
float(category.get('b1', budget_all)),
float(category.get('b2', budget_all)),
float(category.get('b3', budget_all)),
float(category.get('b4', budget_all)),
float(category.get('b5', budget_all)),
float(category.get('b6', budget_all)),
float(category.get('b7', budget_all)),
float(category.get('b8', budget_all)),
float(category.get('b9', budget_all)),
float(category.get('b10', budget_all)),
float(category.get('b11', budget_all)),
float(category.get('b12', budget_all))
]
category['operations'] = {}
category['children'] = {}
category['amounts'] = {}
key = category['key']
if category.has_key('parent'):
categories[category['parent']]['children'][key] = category
else:
categories[key] = category
###############################################################################
# Parse operations
for ope in root.iter('ope'):
data = ope.attrib
data['date'] = timestamp_to_datetime(data['date'])
data['type'] = 'regular'
if data.has_key('samt'):
op_template = {}
op_wording = data.get('wording', '')
op_categories = data['scat'].split('||')
op_amounts = data['samt'].split('||')
op_memos = data['smem'].split('||')
if data.has_key('info'):
op_template['info'] = data['info']
if data.has_key('account'):
op_template['account'] = data['account']
if data.has_key('payee'):
op_template['payee'] = data['payee']
if data.has_key('flags'):
op_template['flags'] = data['flags']
if data.has_key('date'):
op_template['date'] = data['date']
op_template['type'] = 'regular'
for rank in range(0, len(op_amounts)):
suboperation = op_template.copy()
if op_categories[rank]:
suboperation['category'] = op_categories[rank]
if op_amounts[rank]:
suboperation['amount'] = op_amounts[rank]
if op_memos[rank]:
if op_wording:
suboperation['wording'] = ' - '.join((op_wording,
op_memos[rank]))
else:
suboperation['wording'] = op_memos[rank]
elif op_wording:
suboperation['wording'] = op_wording
add_operation(suboperation)
else:
add_operation(data)
###############################################################################
# Parse planned/repeat operations
for fav in root.iter('fav'):
template = fav.attrib
template['nextdate'] = timestamp_to_datetime(template.get('nextdate', '1'))
template['type'] = 'planned'
date = template['nextdate']
# Repetition parameters...
# => number of duration units between two repetitions
repeat_every = int(template.get('every', 1))
# => duration unit between two repetitions
repeat_unit = int(template.get('unit', 0))
if repeat_unit == REPEAT_UNIT_WEEK:
repeat_timedelta = datetime.timedelta(days=7) * repeat_every
def get_next(date):
return date + repeat_timedelta
elif repeat_unit == REPEAT_UNIT_MONTH:
def get_next(date):
year = date.year
month = date.month + repeat_every
day = date.day
if month > 12:
increment_years = month/12
year += increment_years
month -= increment_years*12
return datetime.datetime(year=year, month=month, day=day)
elif repeat_unit == REPEAT_UNIT_YEAR:
def get_next(date):
return datetime.datetime(year=date.year+repeat_every,
month=date.month,
day=date.day)
else:
repeat_timedelta = datetime.timedelta(days=1) * repeat_every
def get_next(date):
return date + repeat_timedelta
# => how many repetitions
if int(template.get('flags', 0)) & REPEAT_LIMIT_ENABLED_FLAG:
repeat = int(template.get('limit', 9999))
else:
repeat = 9999
# => template['weekend'] is ignored...
if int(template.get('flags', 0)) & REPEAT_FAV_FLAG:
while date < end_date and repeat > 0:
operation = template.copy()
operation['date'] = date
add_operation(operation)
# Prepare next occurrence
date = get_next(date)
repeat -= 1
###############################################################################
# Now that we know all dates for existing operations, create the
# all_dates list, from the first date to the end of the period
all_dates = []
first_year = sorted(operations_dates.keys())[0]
first_month = operations_dates[first_year][0]
last_year = end_date.year
last_month = end_date.month
all_years = range(first_year, last_year+1)
for year in all_years:
if year == all_years[0]:
first_month_for_loop = first_month
else:
first_month_for_loop = 0
if year == all_years[-1]:
last_month_for_loop = last_month
else:
last_month_for_loop = 11
for month in range(first_month_for_loop, last_month_for_loop+1):
all_dates.append((year, month))
###############################################################################
# Compare amounts and budget
# Then count budget as operations amounts for the next months
def add_budget(categoriesdict=categories):
for category in categoriesdict.values():
add_budget(category['children'])
if category['budget'] != [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]:
for year, month in all_dates:
budget = category['budget'][month]
if budget:
this_month = datetime.datetime(year=year, month=month+1,
day=1)
try:
amount = category['amounts'][year][month]['amount']
except KeyError as e:
if e.args[0] == year:
amount = 0
else:
raise
if this_month >= current_month:
# Current month or future operations, create a fake
# operation as if it was the total of the future ops
if (budget > 0 and amount < budget) or \
(budget < 0 and amount > budget):
add_operation({
'amount': budget - amount,
'category': category['key'],
'date': this_month,
'type': 'budget'
})
amount = category['amounts'][year][month]['amount']
if not year in category['amounts']:
category['amounts'][year] = [
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0},
{'amount': 0, 'validated': 0,
'over_budget': 0}]
category['amounts'][year][month]['over_budget'] = \
amount - budget
add_budget()
###############################################################################
# Generate the table headers and the totals line
header = []
totals_line = [u'#bbbbbb', u'<b><i>{}</i></b>'.format(totals_text)]
total_incomes_line = [u'#aaccaa', u'<b><i>{}</i></b>'.format(incomes_text)]
total_expenses_line = [u'#ccaaaa', u'<b><i>{}</i></b>'.format(expenses_text)]
end_of_months_line = [u'#ffffff',u'<b><i>{}</i></b>'.format(end_of_months_text)]
current_year = None
previous_value = initial
for year, month in all_dates:
if year != current_year:
current_year = year
header.append('{}\n{}'.format(year, months_names[month]))
else:
header.append('\n{}'.format(months_names[month]))
this_month_total = totals[year][month][0]
if float(this_month_total) < 0:
this_month_total_color = '#dd0000'
else:
this_month_total_color = '#009900'
totals_line.append(
u'<span foreground="{}"><b><i>{:+.2f}</i></b></span>'.format(
this_month_total_color, this_month_total))
total_incomes_line.append(u'<b><i>{:.2f}</i></b>'.format(
totals[year][month][1]))
total_expenses_line.append(u'<b><i>{:.2f}</i></b>'.format
(-totals[year][month][2]))
previous_value += totals[year][month][0]
end_of_months_line.append(u'<b><i>{:.2f}</i></b>'.format(previous_value))
###############################################################################
# Prepare data for the displayed table
def category_type_and_name(cat):
return (cat['type'] == 'debit', cat['name'])
lines = []
def make_table_lines(categoriesdict=categories, level=0):
if level:
catname_format = u' '*level + u'> {}'
amount_format =u'<span foreground="{fg}">{prefix}{amount:.2f} €</span>'
income_background = '#eeffee'
expense_background = '#ffeeee'
else:
catname_format = u'<b>{}</b>'
amount_format = \
u'<span foreground="{fg}">{prefix}<b>{amount:.2f}</b> €</span>'
income_background = '#ccddcc'
expense_background = '#ddcccc'
for cat in sorted(categoriesdict.values(), key=category_type_and_name):
if cat['type'] == 'debit':
line = [expense_background]
else:
line = [income_background]
line.append(catname_format.format(cat['name']))
for year, month in all_dates:
try:
amount = cat['amounts'][year][month]['amount']
validated = cat['amounts'][year][month]['validated']
over_budget = cat['amounts'][year][month]['over_budget']
if over_budget > 0:
amount_prefix = overbudget_prefix
elif over_budget < 0:
amount_prefix = underbudget_prefix
else:
amount_prefix = ''
except KeyError as e:
if e.args[0] == year:
line.append('')
continue # continue the "for" loop with the next category
else:
raise
if amount:
line.append(amount_format.format(fg=font_colors[validated],
prefix=amount_prefix,
amount=amount))
else:
line.append('')
lines.append(line)
make_table_lines(cat['children'], level+1)
make_table_lines()
###############################################################################
# Display in a graphical window
class ReportWindow(Gtk.Window):
def __init__(self):
Gtk.Window.__init__(self, title="HomeBank report")
self.set_default_size(1000, 600)
# The list, initialization
liststore = Gtk.ListStore(str, str, *[str]*len(all_dates))
liststore.append(total_incomes_line)
liststore.append(total_expenses_line)
liststore.append(totals_line)
liststore.append(end_of_months_line)
for line in lines:
liststore.append(line)
# The list, displayed
treeview = Gtk.TreeView(model=liststore)
renderer_categories = Gtk.CellRendererText()
treeview.append_column(Gtk.TreeViewColumn(categories_text,
renderer_categories,
markup=1, background=0))
renderer_values = Gtk.CellRendererText(xalign=1)
for pos, col in enumerate(header):
treeview.append_column(Gtk.TreeViewColumn(col, renderer_values,
markup=pos+2,
background=0))
# Make the list horizontally scrollable
scrolledwindow = Gtk.ScrolledWindow()
scrolledwindow.add(treeview)
self.add(scrolledwindow)
win = ReportWindow()
win.connect("delete-event", Gtk.main_quit)
win.show_all()
Gtk.main()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment