Deutsche Bank CSV To Meaningful Analysis of Money Spent

monthly_expenses.py

"""
Converts Deutsche Bank's CSV of transactions to various categories to see what you're spending money on.

This isn't the neatest code I've written. But it gets what I need done.
I want to see how much I spend on various categories every month. That's it.

Output looks like:

2020-03-01 00:00:00 to 2020-03-31 00:00:00
essentials: -1275.2
food: -417.85
clothing: 0
amazon: -79.72

2020-04-01 00:00:00 to 2020-04-30 00:00:00
essentials: -1275.07
food: -365.19
clothing: 0
amazon: -164.23
"""
import csv
import datetime
from itertools import islice


def last_day_of_month(any_day):
    """https://stackoverflow.com/questions/51293632/how-do-i-divide-a-date-range-into-months-in-python"""
    next_month = any_day.replace(day=28) + datetime.timedelta(days=4)  # this will never fail
    return next_month - datetime.timedelta(days=next_month.day)


def month_list(start_date, end_date):
    """https://stackoverflow.com/questions/51293632/how-do-i-divide-a-date-range-into-months-in-python"""
    result = []
    while True:
        if start_date.month == 12:
            next_month = start_date.replace(year=start_date.year + 1, month=1, day=1)
        else:
            next_month = start_date.replace(month=start_date.month + 1, day=1)
        if next_month > end_date:
            break
        result.append([start_date, last_day_of_month(start_date)])
        start_date = next_month
    result.append([start_date, end_date])
    return result


def auto_str(cls):
    def __str__(self):
        return '%s(%s)' % (
            type(self).__name__,
            ', '.join('%s=%s' % item for item in vars(self).items())
        )

    cls.__str__ = __str__
    cls.__repr__ = __str__
    return cls


@auto_str
class Record:
    def __init__(self, beneficiary=None, details=None, date=None, payment=None, amount=None):
        self.date = date
        self.payment = payment
        self.beneficiary = beneficiary
        self.details = details
        self.amount = amount


categories = {
    "essentials": [Record(beneficiary="anton maier"), Record(beneficiary="swm versorgungs"),
                   Record(beneficiary="vodafone gmbh"),
                   Record(beneficiary="paypal", details="vodafone"), Record(beneficiary="zurich insurance")],
    "food": [Record(details="subway"), Record(details="lidl"), Record(details="edeka"), Record(details="vollcorner"),
             Record(details="aldi")],
    "clothing": [Record(details="decathlon"), Record(details="karstadt")],
    "amazon": [Record(beneficiary="amazon")]
}


def matches(matcher, record):
    if matcher.details is not None and matcher.beneficiary is not None:
        return matcher.details in record.details and matcher.beneficiary in record.beneficiary
    if matcher.details is not None:
        return matcher.details in record.details
    if matcher.beneficiary is not None:
        return matcher.beneficiary in record.beneficiary


def query(records, start_date, end_date, category):
    """Some really bad querying."""
    queried = []
    filtered = [record for record in records if start_date <= record.date < end_date]
    for record in filtered:
        for matcher in category:
            if matches(matcher, record):
                queried.append(record)
    return queried


def query_misc(records, start_date, end_date, categories):
    misc = []
    filtered = [record for record in records if start_date <= record.date < end_date and record.amount < 0]
    for record in filtered:
        matched = False
        for category in categories:
            for matcher in category:
                if matches(matcher, record):
                    matched = True
        if not matched:
            misc.append(record)

    for record in misc:
        print(record)
    return misc


def print_monthly(start_date, end_date):
    for month_range in month_list(start_date, end_date):
        print(month_range[0], "to", month_range[1])

        for name, matchers in categories.items():
            queried = query(records, month_range[0], month_range[1], matchers)
            print(f"{name}: {sum([record.amount for record in queried])}")

        misc = query_misc(records, month_range[0], month_range[1], list(categories.values()))
        print(f"misc: {sum([record.amount for record in misc])}")

        spent = [record for record in records if
                 month_range[0] <= record.date < month_range[1] and record.amount < 0]
        print(f"total spent: {sum([record.amount for record in spent])}")

        saved = [record for record in records if month_range[0] <= record.date < month_range[1]]
        print(f"total saved: {sum([record.amount for record in saved])}")

        # for record in queried:
        #     print(record)
        print("")


if __name__ == '__main__':
    records = []

    file = open("/home/rish/Documents/Everything/2020/Bank Statements/db-20200206-20200804.csv", encoding="iso-8859-1")
    reader = csv.reader(islice(file, 7, None), delimiter=";")
    for line in reader:
        try:
            date = datetime.datetime.strptime(line[0], '%m/%d/%Y')
        except Exception:
            print('failed to parse ', line, "\n")
            continue
        amount = line[15] + line[16]
        records.append(
            Record(beneficiary=line[3].lower(), details=line[4].lower(), date=date, payment=line[2].lower(),
                   amount=float(amount.replace(',', ''))))

    print_monthly(datetime.datetime(2020, 2, 1), datetime.datetime(2020, 7, 31))