view account balance and sales (plot csv data exported from online banking)

Umsaetze_graph_Volksbank.py

#!/usr/bin/python

"""
	Pascal / 18-APR-2021
	graphische Visualisierung der Umsatzanzeige
	(am Beispiel: Volksbank - Umsatzexport - CSV)
"""

import csv
import numpy as np
import datetime as dt
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import matplotlib.ticker as ticker
import sys,os
import hashlib


print "Graph Umsaetze"
print "=============="
print ""


# helperfunc

def str2float(s):
	s = np.char.replace(s, '.', '')
	s = np.char.replace(s, ',', '.')
	return s.astype(float)

def datestr2date(d):
	return dt.datetime.strptime(d,'%d.%m.%Y').date()

# analyze function to get balance in individual file

def get_umsaetze_from_file(fn,last=None):

	umsaetze = [] # for this individual file

	print 'use file = "'+fn+'"'

	f = open(fn, 'rb')
	reader = csv.reader(f, delimiter=';', quotechar='"')

	collecting = False  # header found, start to collect on next row

	for row in reader:
		# ~print "row:",row

		if (len(row)<1):
			if (collecting):
				# ~print "ENDE"
				break
			continue

		if (collecting):
			butag = row[0]
			sign  = 1 if row[12]=='H' else -1
			euros = sign * str2float(row[11])
			notiz = row[3]

			if (not notiz):
				# ~print row
				notiz = row[8].replace("\n", '').strip()

			# ~print butag,euros

			rowhash = hashlib.md5(''.join(row)).hexdigest()
			# ~print "rowhash:",rowhash

			umsaetze.append([butag,euros,rowhash,notiz])


		if (not collecting and row[0]=="Buchungstag"):
			# ~print "start"
			collecting = True

	# ~print "collected: ", len(umsaetze)

	# start collecting actual data

	umsaetze.reverse()
	umsaetze_out = []
	collecting2 = (not last) # last row found to prevent overlap (normally init with False except no start-marker was passed, then collect all)
	for butag,euros,rowhash,notiz in umsaetze:
		if (collecting2):
			# ~print "collect"
			umsaetze_out.append([butag,euros,rowhash,notiz])
		elif (rowhash == last):
			# ~print "found, start collecting on next row"
			collecting2 = True
		# ~else:
			# ~print "skip"


	if (not collecting2):
		print "ERROR, hash not found"
		sys.exit(1)

	print "collected: ", len(umsaetze_out) , "/" , len(umsaetze)

	if (not umsaetze_out):
		print "nothing to add here"
		return [],last # reuse last hash

	firstdate = umsaetze_out[0][0]
	lastdate = umsaetze_out[-1][0]

	# ~print "first date: ", firstdate, datestr2date(firstdate)
	# ~print "last date: ", lastdate, datestr2date(lastdate)

	this_last_hash = umsaetze_out[-1][2]
	# ~print "last hash =",this_last_hash
	# ~print ""

	return umsaetze_out,this_last_hash

#-----------------------------------------------------------------------

# DEMO:
# ~fn = "...... .csv"
# ~print "DEMO: use file=",fn
# ~get_umsaetze_from_file(fn,"cb86746ac43XXX")
# ~sys.exit()


# find files

path = os.getcwd()
# read the entries
files = sorted(os.listdir(path))  # ~files = list(reversed(sorted(os.listdir(path))))

# init lists
umsaetze_all = []
last_hash = None

for fn in files:
	if (fn.startswith("Umsaetze_") and fn.endswith(".csv")):
		print "fn=",fn
		# load it
		umsaetze_out, last_hash = get_umsaetze_from_file(fn, last_hash) # get Umsaetze and update last_hash
		print "add entries:",len(umsaetze_out)
		umsaetze_all.extend(umsaetze_out)
		print ""
		print ""

print "total len =" , len(umsaetze_all)

firstdate = umsaetze_all[0][0]
lastdate = umsaetze_all[-1][0]

print "first date: ", firstdate, datestr2date(firstdate)
print "last date: ", lastdate, datestr2date(lastdate)

# ~sys.exit()

# -----------------------------------------------------------------
kapital0 = 0
#kapital0 = 1234.56 # define start if data is not complete from initial account creation
kapital = kapital0
dates_list = []
kapital_list = []
euros_pos = []
euros_neg = []

for i,(butag,euros,rowhash,notiz) in enumerate(umsaetze_all):
	print butag, euros, kapital

	kapital += euros
	kapital_list.append(kapital)

	butag_dt = datestr2date(butag)
	dates_list.append(butag_dt)
	if (euros > 0):
		euros_pos.append([i, butag_dt,euros]) # save global index
	else:
		euros_neg.append([i, butag_dt,euros])

print "Guthaben/Kaptial : ", ("%.2f" % kapital)
print "letzter Buchungstag : ", butag


# PLOT -----------------------------------------------------------------


# ~plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%d.%m.%Y'))
#plt.gca().xaxis.set_major_locator(mdates.DayLocator())

# ~plt.gca().yaxis.set_major_locator(ticker.AutoLocator())
# ~plt.gca().yaxis.set_minor_locator(ticker.AutoMinorLocator())

line, = plt.plot(dates_list, kapital_list, label="Kapital" , color="blue" , linewidth=2 )
#~ plt.plot(x,y2, label="Transaktion")

line1, = plt.plot(np.array(euros_neg)[:,1],np.array(euros_neg)[:,2], 'r.', markersize=7, label="Transaktion < 0")
line2, = plt.plot(np.array(euros_pos)[:,1],np.array(euros_pos)[:,2], 'g.', markersize=7, label="Transaktion > 0")

# ~plt.plot( [min(x),max(x)],[0,max(y1)] , color="black" , linestyle="dotted" )
# ~plt.plot( [min(x),max(x)],[0,y1[-1]] , color="black" )
#~ plt.axhline(y=kapital0, linewidth=.5)
# ~plt.axvline(x=min(x), color="grey", linewidth=1.)

# ~plt.gcf().autofmt_xdate()
plt.xlabel('Buchungstag')
plt.ylabel('Kontostand')

plt.legend()

plt.grid(True)


# ~https://stackoverflow.com/questions/31303601/animate-a-python-pyplot-by-moving-a-point-plotted-via-scatter
point, = plt.plot(dates_list[0],kapital_list[0], 'o', color="black")

# ~https://matplotlib.org/stable/users/event_handling.html#simple-picking-example
# ~https://matplotlib.org/stable/gallery/event_handling/coords_demo.html#sphx-glr-gallery-event-handling-coords-demo-py
# ~https://matplotlib.org/stable/api/_as_gen/matplotlib.pyplot.connect.html
# ~https://stackoverflow.com/questions/7908636/possible-to-make-labels-appear-when-hovering-over-a-point-in-matplotlib

# init Annotation
# ~annot = plt.annotate('', xy=(0,0))
annot = plt.annotate("abc",xy=(dates_list[0],kapital_list[0]))# ,xycoords="axes pixels")#  ,xytext=(5,1),textcoords = 'offset pixels')
annot.set_bbox(dict(facecolor='white', alpha=0.75, edgecolor='black'))

def euro2str(e):
	s = str(np.round(e,2))
	if (e > 0):
		s = '+'+s
	return s+" EUR"

def update_annotation(index, mouse_pos):
	butag,euros,rowhash,notiz = umsaetze_all[index]
	kapital = kapital_list[index]
	# ~x,y=line.get_data()
	# ~print butag,euros
	# ~annot.xy = (dates_list[index], kapital_list[index])
	# ~plt.draw() # update canvas

	annot.get_bbox_patch().set_edgecolor("red" if (euros < 0) else "green")

	text = "Buchungstag: "+butag+" (#"+str(index)+")\n"+notiz+"\nTransaktion: "+euro2str(euros)+"\nKapital: "+euro2str(kapital)
	annot.set_text(text)

	x,y = mouse_pos
	annot.set_x(x)
	annot.set_y(y)

	x,y = (dates_list[index], kapital_list[index])
	point.set_data(x,y)

	annot.set_visible(True)
	point.set_visible(True)

def hover(event):
	print event#,  event.mouseevent, event.artist
	cont, ind = line.contains(event) # check if event is found on plot
	cont1, ind1 = line1.contains(event)
	cont2, ind2 = line2.contains(event)
	print cont,ind
	mouse_x,mouse_y = (event.xdata,event.ydata) # mouse position in plot
	mouse_pos = (mouse_x,mouse_y)
	if cont:
		index = ind["ind"][0]
		update_annotation(index, mouse_pos)
	elif cont1:
		# ~print cont1,ind1
		index = euros_neg[ind1["ind"][0]][0] # get global index
		# ~print "global:",index
		update_annotation(index, mouse_pos)
	elif cont2:
		index = euros_pos[ind2["ind"][0]][0] # get global index
		update_annotation(index, mouse_pos)
	else:
		annot.set_text('')
		annot.set_visible(False)
		point.set_visible(False)
	plt.draw() # update canvas


plt.connect("motion_notify_event", hover)


plt.show()