226 lines
9.0 KiB
Python
226 lines
9.0 KiB
Python
#BankView - Calculate and Build Banking Stats
|
|
#Cal.W 2020
|
|
|
|
import csv, statistics, os
|
|
from datetime import datetime
|
|
|
|
import matplotlib
|
|
import matplotlib.pyplot as plt
|
|
import matplotlib.dates as mdates
|
|
|
|
TRANSACTION_TYPES = ['Other', "Tap and Pay", "Card", "Deposit"]
|
|
|
|
#transDetails, transDate, transType, transAcc
|
|
def transDetails(transDetails):
|
|
transAcc = None
|
|
transType = TRANSACTION_TYPES[0]
|
|
transDetails = " ".join(transDetails.split())
|
|
transDate = None
|
|
|
|
z = transDetails.split(" Value Date: ")
|
|
if len(z) >= 2:
|
|
transDate = z[1]
|
|
transDetails = z[0]
|
|
|
|
#Don't Check the first (default) or last ()
|
|
for x in TRANSACTION_TYPES[1:-1]:
|
|
y = transDetails.split(" "+x+" ")
|
|
if len(y) >= 2:
|
|
transType = x
|
|
transAcc = y[1]
|
|
transDetails = y[0]
|
|
break
|
|
|
|
return transDetails, transDate, transType, transAcc
|
|
|
|
#Return a list of transactions in the form:
|
|
# 0 1 2 3 4 5 6
|
|
#(date(Transaction Date), date(Initialized), float(Amount), str(Details), float(Acc Balance), str(Payment Type), str(Acc))
|
|
def importTransactionData(fileName="import.csv", transactions=list(), delimiter=',', newline=''):
|
|
rawTransactions = list()
|
|
|
|
with open(fileName, newline=newline) as importCSVFile:
|
|
importCSV = csv.reader(importCSVFile, delimiter=delimiter)
|
|
rawTransactions = [x[:4] for x in importCSV]
|
|
|
|
#(date(Transaction Date), date(Initialized), float(Amount), str(Details), float(Acc Balance), str(Payment Type), str(Acc))
|
|
for x in rawTransactions:
|
|
y = transDetails(x[2])
|
|
transactions.append(
|
|
(
|
|
datetime.strptime(x[0], "%d/%m/%Y"),
|
|
datetime.strptime(
|
|
y[1] if y[1] else x[0],
|
|
"%d/%m/%Y"
|
|
),
|
|
float(str(x[1]).replace(",", "")),
|
|
str(y[0]),
|
|
float(str(x[3]).replace(",", "")),
|
|
TRANSACTION_TYPES[-1] if y[2] == TRANSACTION_TYPES[0] and float(str(x[1]).replace(",", "")) > 0 else str(y[2]),
|
|
str(y[3]) if y[3] else ""
|
|
)
|
|
)
|
|
|
|
#Filter out any duplicates - the 2 lines are the same (logic wise) as the 8 under it
|
|
seen = set()
|
|
transactions = [x for x in transactions if x not in seen and not seen.add(x)] #oh and the "not seen.add" is cause add always returns None
|
|
##seen = set()
|
|
##trans = []
|
|
##for x in transactions:
|
|
## if x not in seen:
|
|
## trans.append(x)
|
|
## seen.add(x)
|
|
## else:
|
|
## print(x)
|
|
##transactions = trans
|
|
|
|
transactions = sorted(transactions, reverse = True, key = lambda x: x[1]) #Sort from init date.
|
|
|
|
|
|
return transactions
|
|
|
|
|
|
def loadTransactionData(fileName="transData.csv"):
|
|
transactions = list()
|
|
|
|
with open(fileName, newline='') as importCSVFile:
|
|
importCSV = csv.reader(importCSVFile, delimiter=',')
|
|
transactions = [x[:7] for x in importCSV]
|
|
|
|
transactions = [
|
|
(
|
|
datetime.strptime(x[0], "%d/%m/%Y"),
|
|
datetime.strptime(str(x[1]), "%d/%m/%Y"),
|
|
float(x[2]),
|
|
str(x[3]),
|
|
float(x[4]),
|
|
str(x[5]),
|
|
str(x[6]) if len(x) > 6 else ""
|
|
) for x in transactions
|
|
]
|
|
|
|
transactions = sorted(transactions, reverse = True, key = lambda x: x[1]) #Sort from init date.
|
|
|
|
return transactions
|
|
|
|
def saveTransactionData(transactions, fileName="transData.csv"):
|
|
with open(fileName, 'w', newline='') as f:
|
|
writer = csv.writer(f)
|
|
writer.writerows([
|
|
(
|
|
x[0].strftime("%d/%m/%Y"),
|
|
x[1].strftime("%d/%m/%Y"),
|
|
x[2],
|
|
x[3],
|
|
x[4],
|
|
x[5],
|
|
x[6],
|
|
) for x in transactions
|
|
])
|
|
|
|
return
|
|
|
|
|
|
#Load and if found update the transaction db
|
|
def loadUpdateTransactionData(dbFileName="transData.csv", importFileName="import.csv", mvImportFileName=None):
|
|
if mvImportFileName is None: mvImportFileName = './imported_'+datetime.now().strftime("%Y-%m-%d")+'.csv'
|
|
|
|
trans = list()
|
|
if(os.path.isfile(dbFileName)):
|
|
trans = loadTransactionData(dbFileName)
|
|
|
|
if (os.path.isfile(importFileName)):
|
|
trans = importTransactionData(importFileName, transactions=trans)
|
|
os.rename(importFileName, mvImportFileName)
|
|
saveTransactionData(trans)
|
|
|
|
if len(trans) < 1:
|
|
raise ImportError("No transactions to use!")
|
|
|
|
return trans
|
|
|
|
|
|
|
|
|
|
def calculateStats(transactions, yearRangeVal=None, monthRangeVal=None, otherFelid=None):
|
|
yearRange = lambda x: True
|
|
if isinstance(yearRangeVal, type(yearRange)): yearRange = yearRangeVal
|
|
if isinstance(yearRangeVal, list) or isinstance(yearRangeVal, tuple): yearRange = lambda x: x[1].year in yearRangeVal
|
|
if isinstance(yearRangeVal, int) or isinstance(yearRangeVal, float): yearRange = lambda x: x[1].year == int(yearRangeVal)
|
|
|
|
monthRange = lambda x: True
|
|
if isinstance(monthRangeVal, type(monthRange)): monthRange = monthRangeVal
|
|
if isinstance(monthRangeVal, list) or isinstance(monthRangeVal, tuple): monthRange = lambda x: x[1].month in monthRangeVal
|
|
if isinstance(monthRangeVal, int) or isinstance(monthRangeVal, float): monthRange = lambda x: x[1].month == int(monthRangeVal)
|
|
|
|
if not isinstance(otherFelid, type(monthRange)): otherFelid = lambda x: True
|
|
|
|
periodTransactions = [x for x in transactions if yearRange(x) and monthRange(x) and otherFelid(x)]
|
|
periodTransactions = sorted(periodTransactions, reverse = True, key = lambda x: x[1])
|
|
|
|
stats = {
|
|
"totalCredits" : round(sum([abs(x[2]) for x in periodTransactions if x[2] > 0]), 2) if len([abs(x[2]) for x in periodTransactions if x[2] > 0]) > 0 else float(0),
|
|
"totalDebits" : round(sum([abs(x[2]) for x in periodTransactions if x[2] < 0]), 2) if len([abs(x[2]) for x in periodTransactions if x[2] < 0]) > 0 else float(0),
|
|
"largestCredit" : max(periodTransactions, key=lambda x: x[2]) if len(periodTransactions) > 0 else None,
|
|
"largestDebit" : min(periodTransactions, key=lambda x: x[2]) if len(periodTransactions) > 0 else None,
|
|
"averageCredit" : round(statistics.fmean([abs(x[2]) for x in periodTransactions if x[2] > 0]), 2) if len([abs(x[2]) for x in periodTransactions if x[2] > 0]) > 0 else float(0),
|
|
"averageDebit" : round(statistics.fmean([abs(x[2]) for x in periodTransactions if x[2] < 0]), 2) if len([abs(x[2]) for x in periodTransactions if x[2] < 0]) > 0 else float(0),
|
|
"netBalance" : round(sum([x[2] for x in periodTransactions]), 2) if len(periodTransactions) > 0 else float(0),
|
|
"averageBalance" : round(statistics.fmean([x[4] for x in periodTransactions]), 2) if len(periodTransactions) > 0 else float(0),
|
|
"highestBalance" : float(max(periodTransactions, key=lambda x: x[4])[4]) if len(periodTransactions) > 0 else float(0),
|
|
"lowestBalance" : float(min(periodTransactions, key=lambda x: x[4])[4]) if len(periodTransactions) > 0 else float(0),
|
|
}
|
|
|
|
return stats, periodTransactions
|
|
|
|
|
|
def drawTransactionGraphs(trans):
|
|
stats, statTrans = calculateStats(trans)
|
|
|
|
fig, ax = plt.subplots(2, sharex=True)
|
|
#ax[0].xaxis.set_major_formatter(mdates.DateFormatter('%m/%Y'))
|
|
#ax[0].xaxis.set_major_locator(mdates.DayLocator(interval=60))
|
|
ax[0].fmt_xdata = mdates.DateFormatter('%Y-%m-%d')
|
|
ax[0].plot([x[1].date() for x in statTrans], [x[4] for x in statTrans])
|
|
ax[0].set(title='Transaction History')
|
|
ax[0].tick_params(
|
|
axis='x', # changes apply to the x-axis
|
|
which='both', # both major and minor ticks are affected
|
|
bottom=False, # ticks along the bottom edge are off
|
|
top=False, # ticks along the top edge are off
|
|
labelbottom=False # labels along the bottom edge are off
|
|
)
|
|
ax[0].grid()
|
|
|
|
|
|
#ax[1].xaxis.set_major_formatter(mdates.DateFormatter('%m/%Y'))
|
|
#ax[1].xaxis.set_major_locator(mdates.DayLocator())
|
|
ax[1].fmt_xdata = mdates.DateFormatter('%Y-%m-%d')
|
|
ax[1].plot([x[1].date() for x in statTrans], [x[2] for x in statTrans])
|
|
ax[1].set(title='Transaction Amounts Over Time')
|
|
ax[1].grid()
|
|
|
|
fig.tight_layout()
|
|
fig.autofmt_xdate()
|
|
|
|
fig.text(0.5, 0.05, 'Date (Month/Year)', ha='center')
|
|
fig.text(0.015, 0.5, 'Transaction Amount ($)', va='center', rotation='vertical')
|
|
#fig.savefig("test.png")
|
|
plt.show()
|
|
|
|
|
|
def foo(transactions):
|
|
years = []
|
|
for trans in transactions:
|
|
if trans[1].year not in years:
|
|
years.append(trans[1].year)
|
|
|
|
for year in years:
|
|
pass
|
|
|
|
|
|
if __name__ == "__main__":
|
|
trans = loadUpdateTransactionData()
|
|
|
|
drawTransactionGraphs(trans)
|
|
|