python Library
ArnesKonto
Keine Erläuterungen gefunden.
#import csv import tkinter as tk from tkinter import filedialog import pandas import json import colorama from colorama import Fore, Back, Style colorama.init() """ 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 ArnesKonto.py """ class FilterAccounts: def __init__(self): self.debug = False #self.debug = True self.format="int" #de / int self.headLines = 12 self.delimiter = ";" self.accountIdentifyer = {"giro":"3676","credit":"4866","consum":"1292"} self.responsible={"credit":"Auftraggeber/Zahlungsempfänger","giro":"Empfänger/Zahlungspflichtiger","consum":"Empfänger/Zahlungspflichtiger"} self.fields={} self.fields["giro"] ={"amount":"Umsatz","sign":" ","date":"Buchungstag","desc":"Vorgang/Verwendungszweck"} self.fields["credit"]={"amount":"Umsatz","sign":" ","date":"Buchungstag","desc":"Vorgang/Verwendungszweck"} self.fields["consum"]={"amount":"Umsatz","sign":" ","date":"Buchungstag","desc":"Vorgang/Verwendungszweck"} self.accountNames = self.accountIdentifyer.keys() self.outFile = "arnesFinanzen.txt" # ============================================ # def echo(self, msg, type="error"): """ Print message on command line in colored style. Display ERROR in red. Warning in yellow. """ if type=='ok': print(Back.GREEN + msg + Style.RESET_ALL) else type=='warn': print(Fore.YELLOW + Style.BRIGHT + 'WARNING: '+ msg + Style.RESET_ALL) else: print(Fore.RED + Style.BRIGHT + 'ERROR: '+ msg + Style.RESET_ALL) # ============================================ # def readGiro(self, fileName): """ Here, we parse the CSV banking export. 'accountIdentifyer' is a dictionary that holds fragment of account-No for each account-type/name. """ if self.debug: self.echo("Reading: "+fileName,'ok') print("Dropping first "+str(self.headLines)+" lines") accountRaw = pandas.read_csv(fileName, sep=self.delimiter, skiprows=self.headLines, encoding='latin1') for i,an in enumerate(self.accountNames): if fileName.find(self.accountIdentifyer[an])>0: accType = an responsible = self.responsible[an] fields = self.fields[an] #check names of columns and find sender and receiver colNames = list(accountRaw.keys()) if self.debug: print("colNames of data",colNames) for i,cn in enumerate(colNames): if cn.find("_"+responsible)>0: responsibleCol = cn #process all lines of data NoData = len(accountRaw[colNames[0]]) account = [] for n in range(NoData): thisValue = accountRaw[fields["amount"]][n] thisDate = accountRaw[fields["date"]][n] if pandas.isna( accountRaw[responsible][n] ): #detect missing values and drop line print("drop line:", accountRaw[fields["desc"]][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[responsible][n] )+"\t" thisDesc = thisDesc + accountRaw[fields["desc"]][n].replace("\n", "") if fields["sign"]: thisSign = accountRaw[fields["sign"]][n] if thisSign=="S": value = -1.0*float(thisValue) else: value = +1.0*float(thisValue) else: value = float(thisValue) #to have multiple real accounts inside the accounting, sort here for i,an in enumerate(self.accountNames): if accType==an: thisAccount[an] = value else: thisAccount[an] = 0 #special transfers between real accounts, customize here! if accType == 'giro': if thisDesc.find("astschrift aus Kartenzahlung")>0: thisAccount["credit"] = -1*thisAccount["giro"] else thisDesc.find("ASTERCARD Abrechnun")>0: thisAccount["credit"] = -1*thisAccount["giro"] else thisDesc.find("Bargeldverfuegung")>0: thisAccount["credit"] = -1*thisAccount["giro"] else thisDesc.find("UECKLAGE")>0: thisAccount["depot"] = -1*thisAccount["giro"] else thisDesc.find("Arne Jachens")>0 and thisDesc.find("GUTSCHRIFT")>0: thisAccount["depot"] = -1*thisAccount["giro"] else accType == 'credit' and thisDesc.find("Abrechnung")>0: thisAccount["credit"] = 0 #no doublet thisAccount["desc"] = thisDesc #put fields of this accounting entry to list of all account.append(thisAccount) return account # ============================================ # def cathegorizeTransactions(self, 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' """ accountNames = self.accountIdentifyer.keys() #first read markers for cathegories #self.catNames = ["KrankenV","Versicherung","Ruecklage","Unterhalt","Leben","Anschaffungen","Reise","Auto","Konsum","Einkommen"] with open("catMarker.json") as json_file: catMarker = json.load(json_file) catNames = catMarker.keys() if self.debug: self.echo("cathegorizeTransactions",'ok') print("catNames",catNames) print("catMarker",catMarker) print(" ###########################") #check for each accounting entry, in which cathegory = virtaul account it matches cathegories = [] for i,a in enumerate(account): a = account[i] thisDesc = "_"+a["desc"] #check total over all accounts to find transactions sumA = 0 for an in accountNames: sumA = sumA + a[an] cathegories.append( {} ) catsFound=0 for cN in catNames: #as default, set all columns =0 cathegories[-1][cN] = 0 if abs( sumA )==0: #Umbuchung catsFound = catsFound+1 else: #for each cathegory, check for catMarkers for cM in catMarker[cN]: if thisDesc.find(cM)>0: catsFound = catsFound+1 cathegories[-1][cN] = -1*sumA if self.debug: print(i,cN,sumA) #print entries that can not be matched yet if catsFound==0: print(thisDesc) #print(cathegories) return cathegories # ============================================ # def exportFilteredData(self, account, cathegories, format=".", mode="w"): """ 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 """ fileName = self.outFile try: fp = open(fileName, mode, encoding='utf-8') except: echo("ERROR: could not write to file!") print(fileName) exit() 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] thisTrans = self.account2txt(thisA,thisC) fp.write(thisTrans+"\n") fp.close() self.echo("Filtered data written to: "+fileName,'ok') # ============================================ # def account2txt(self, account, cathegories): """ convert one account plus its cathegories to a CSV line """ myName = "Jachens Dr. Arne" cKeys = cathegories.keys() thisTrans = "" thisTrans1 = "" thisTrans1 = thisTrans1+str( account["date"] )+"\t" thisTrans1 = thisTrans1+str( account["month"] )+"\t" for an in self.accountNames: thisTrans = thisTrans+'{0:1.2f}'.format( account[an] )+"\t" #thisTrans = thisTrans+'{0:1.2f}'.format( account["giro"] )+"\t" #thisTrans = thisTrans+'{0:1.2f}'.format( account["credit"] )+"\t" #thisTrans = thisTrans+'{0:1.2f}'.format( account["depot"] )+"\t" desc = account["desc"].replace("\t", "_") desc = desc.replace( myName+"_EINZUGSERMAECHTIGUNG", "E_" ) desc = desc.replace( myName+"_GUTSCHRIFT", "G_" ) thisTrans = thisTrans + desc + "\t" for c in cKeys: thisTrans = thisTrans + str( cathegories[c] )+"\t" #replace decimal sign to operate Excel on German system if format=="de": thisTrans = thisTrans.replace(".", ",") thisTrans = thisTrans1 + thisTrans return thisTrans # ============================================ # def browse_file(file_label, ArnesMain_button, FA ): 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, FA ) # ============================================ # def runGUI( FA ): # 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, FA ) ) # 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, FA ): #read accounts from CSV if isinstance( fileName, list) and len(fileName)>0: init = True for f in fileName: FA.echo("reading: "+f,'ok') account = FA.readGiro( f ) #sort entries to real and virtual accounts cathegories = FA.cathegorizeTransactions(account) if init: init = False mode = "w" else: mode = "a" #export filtered data to TXT FA.exportFilteredData(account, cathegories, format=".", mode=mode) else: account = FA.readGiro( fileName ) #sort entries to real and virtual accounts cathegories = FA.cathegorizeTransactions(account) #export filtered data to TXT FA.exportFilteredData(account, cathegories) # ============================================ # if __name__ == "__main__": fileName = [] #fileName.append( "Umsaetze_DE16672300004009813676_2024.04.09.csv" ) FA = FilterAccounts() if len( fileName )>0: ArnesMain( fileName, FA ) else: runGUI( FA ) """ try: runGUI( accountIdentifyer ) except: #ArnesMain( fileName, accountIdentifyer, format ) print("ERROR: execution of GUI failed!") print("Try to set file name(s) manually.") """
Index of Library
Der gesamte Sourcecode darf gemäß GNU General Public License weiterverbreitet werden.