python Library
familienKonto
Filtert die Umsätze eines CSV-Kontoauszugs an Hand von Schlüsselworten und erzeugt daraus eine doppelte Buchführung, die sich in Excel importieren lässt.
Die Schlüsselworte werden mittels catMarker.json formatiert:
{
"Wohnen":[
"Strom",
"WASSER",
"Hausratversicherung"
],
"Leben":[
"tegut",
"ALDI"
],
"Luxus":[ ],
"Einkommen":[ ]
}
#import csv import tkinter as tk from tkinter import filedialog import pandas import json """ 1. show all transactions in your browser, export them to a CSV file 2. Open CSV by Excel, check: the first 12 lines are header 3. Filter all transactions to the structure 'account' by: python familienKonto.py """ # ============================================ # def readCSV(fileName, headLines=12, delimiter=";"): print("Reading: ",fileName) print("Dropping first "+str(headLines)+" lines") #accountRaw = pandas.read_csv(fileName, sep=delimiter, skiprows=headLines, encoding='ANSI') accountRaw = pandas.read_csv(fileName, sep=delimiter, skiprows=headLines, encoding='latin1') #check names of columns and find sender and receiver colNames = list(accountRaw.keys()) for i,cn in enumerate(colNames): if cn.find("uftraggeber")>0: AuftraggeberMarker = cn if cn.find("Zahlungspflichtiger")>0: ZahlungspflichtigerMarker = cn prepos = ZahlungspflichtigerMarker #prepos = AuftraggeberMarker try: foo = accountRaw[prepos][0] except: print("prepos is:",prepos) print("account keys are:",colNames) exit() #process all lines of data NoData = len(accountRaw[colNames[0]]) account = [] for n in range(NoData): thisValue = accountRaw['Umsatz'][n] thisSign = accountRaw[' '][n] #the sign got a blank identifier thisDate = accountRaw['Buchungstag'][n] if pandas.isna( accountRaw[prepos][n] ): #detect missing values and drop line print("drop line:", accountRaw['Vorgang/Verwendungszweck'][n], thisValue) continue #drop this line else: #adapt number format German to international, drop thousends delimter, replace decimal sign thisValue = thisValue.replace(".", "") thisValue = thisValue.replace(",", ".") #pick relevant fields thisAccount={} thisAccount["date"] = thisDate thisAccount["month"] = thisDate[3:5] thisDesc = str( accountRaw[prepos][n] )+"\t" thisDesc = thisDesc + accountRaw['Vorgang/Verwendungszweck'][n].replace("\n", "") if thisSign=="S": thisAccount["value"] = -1.0*float(thisValue) else: thisAccount["value"] = +1.0*float(thisValue) thisAccount["desc"] = thisDesc #put fields of this accounting entry to list of all account.append(thisAccount) return account # ============================================ # def cathegorizeTransactions(account): """ Cathegorize the transactions in parts with catNames. For each cathegory there may be multipe catMarkers. Transactions that cannot be cathegorized are printed to extend the filters. Sorting strings are imported from 'catMarker.json' """ #first read markers for cathegories with open("catMarker.json") as json_file: catMarker = json.load(json_file) catNames = catMarker.keys() print(" ###########################") #check for each accounting entry, in which cathegory = virtaul account it matches cathegories = [] for i,a in enumerate(account): thisDesc = "_"+a["desc"] cathegories.append( {} ) catsFound=0 for cN in catNames: cathegories[i][cN]=0 for cM in catMarker[cN]: if thisDesc.find(cM)>0: catsFound = catsFound+1 value = a["value"] cathegories[i][cN] = -1*value #print entries that can not be matched yet if catsFound==0: #cathegories[i]["unklar"]=1 print(thisDesc) #print(cathegories) return cathegories # ============================================ # def exportFilteredData(account, cathegories, fileName="familienFinanzen.txt"): """ Export all transactions to a new TXT file, acomplished by the cathegorie indicators. You may want to import this to Excel again (Try: Refresh all Data) and convert the values. Then copy the values only to the balance and add manual changes as required. for each account, the cathegories order the value to a specific person """ fp = open(fileName, 'w', encoding='utf-8') aKeys = account[0].keys() try: cKeys = cathegories[0].keys() except: print(cathegories) exit() thisTrans = "" for a in aKeys: thisTrans = thisTrans+str(a)+"\t" for c in cKeys: thisTrans = thisTrans+c+"\t" fp.write(thisTrans+"\n") NoData = len(account) for d in range(NoData): thisA = account[d] thisC = cathegories[d] splitArne2Daniela = False if thisA["desc"].find("undesagentur fuer")>0: splitArne2Daniela = True if splitArne2Daniela: thisC["Arne"] = -0.5*thisA["value"] thisC["Dani"] = 0 thisTrans = account2txt(thisA,thisC) fp.write(thisTrans+"\n") thisA["desc"] = "split" thisC["Dani"] = -0.5*thisA["value"] thisC["Arne"] = 0 thisA["value"] = 0 thisTrans = account2txt(thisA,thisC) fp.write(thisTrans+"\n") else: thisTrans = account2txt(thisA,thisC) fp.write(thisTrans+"\n") fp.close() print("Filtered data written to: ",fileName) # ============================================ # def account2txt(account,cathegories): """ convert one account plus its cathegories to a CSV line """ cKeys = cathegories.keys() thisTrans = "" thisTrans = thisTrans+str(account["date"])+"\t" thisTrans = thisTrans+str(account["month"])+"\t" thisTrans = thisTrans + '{0:1.2f}'.format(account["value"]) + "\t" desc = account["desc"].replace("\t", "_") thisTrans = thisTrans + desc + "\t" for c in cKeys: thisTrans = thisTrans + str(cathegories[c])+"\t" #replace decimal sign to operate Excel on German system #thisTrans.replace(".", ",") return thisTrans # ============================================ # def browse_file(file_label, ArnesMain_button ): file_path = filedialog.askopenfilename(filetypes=[("CSV files", "*.csv")]) if file_path: file_label.config(text="Selected file: " + file_path) ArnesMain_button.config(state=tk.NORMAL) ArnesMain(file_path) # ============================================ # def runGUI(): # Create the main window root = tk.Tk() root.title("CSV File Selector") ArnesMain_button = tk.Button(root, text="", state=tk.DISABLED) # Create widgets file_label = tk.Label(root, text="No file selected") browse_button = tk.Button(root, text="Browse", command=browse_file(file_label, ArnesMain_button ) ) # Place widgets in the window file_label.pack(pady=10) browse_button.pack(pady=10) ArnesMain_button.pack(pady=10) # Start the GUI event loop #root.mainloop() root.destroy() # Close the Tkinter window # ============================================ # def ArnesMain( fileName ): #read accounts from CSV if isinstance( fileName, list) and len(fileName)>0: print(fileName) print("Read set of CSV files") exit() account = [] for f in fileName: print("reading:",f ) thisA = readCSV( f ) account.append( thisA ) else: account = readCSV( fileName ) #sort entries to real and virtual accounts cathegories = cathegorizeTransactions(account) #export filtered data to TXT exportFilteredData(account,cathegories) # ============================================ # if __name__ == "__main__": fileName = [] #fileName.append( "Umsaetze_DE69672300004119675601_2024.03.01.csv" ) if len( fileName )>0: ArnesMain( fileName ) else: try: runGUI() except: ArnesMain( fileName )
Index of Library
Der gesamte Sourcecode darf gemäß GNU General Public License weiterverbreitet werden.