BankExp App

Overview

A work in progress project for an app to store some of my bank statements and to view and manage that data. The idea would be to save the data from the csv files to a database and use Django to build an app to interact with that data.

GitHub repo for this project: bankexp.
A working version of the app is running here: BankExp Project (creds: user: demo / pass: demo2022).

The csv files I've got with the transactions in my accounts look similar. Generally, there are five useful columns

For the chequing account

Some of the csv files for chequing transactions have these columns:

Date, Transaction Description, Withdrawals, Deposits, Balance

where Withdrawals would mean any outflow of funds from the chequing account while Deposits would mean any inflow of funds into the chequing account.

For the credit card account

One type of csv file for credit card transactions has these columns:

Date, Transaction Description, Debit, Credit, Balance

where Debit would mean any outflow of funds from the credit card account while Credit would mean any inflow of funds into the credit card account.

Other type I got has these columns:

Stage,Transaction Date,Posted Date,Card No.,Description,Debit,Credit

In the end I used the first type as it seems to have all the data I needed.

Working along, the main focus has been on the transactions and their related accounts. But after using the app for a while, I thought that it might be useful to have a way to record some expenses related to some of these transactions (like, gas, groceries, etc). That meant a new model for the expense table. Later on, I found it useful to move the type of expense as well as the location of the expense to their separate tables as I can do some sorting and grouping related to these new tables.

And then I got the name of the project Banking + Expenses = BankExp

So the project has practlically two tracks which interconnect: the transactions and related accounts, one track, and the expense record, expense type, and expense location the other track. The interconnection between these two tracks is done through relationship between expense record and transactions.

From another point of view, the project is built using three apps:

• the BankExp app - the main app and the reason the project exists
• a Base app which holds files and configs mainly related to the site as a whole and some common used classes
• a Users app to keep data related to the users.

The BankExp App Models

To represent the data, a model for transaction linked to a model for account proved to be the useful choice. These transaction records look pretty much like time-series data. Both these models have a field owner linked to the User model.

Even that they are similar, I am using two different models for each type of transaction: chequing and credit card.

Chequing Transaction model looks mainly like this

class ChequingTransaction(models.Model):
    """ A class representing a Chequing Transaction """

    account = models.ForeignKey(
        ChequingAccount, 
        related_name = 'transactions', 
        related_query_name = 'transaction',
        verbose_name = "Account",
        on_delete=models.PROTECT)
    tr_date = models.DateField(verbose_name = "Date", help_text = "Transaction Date")
    tr_desc = models.CharField(max_length=200, verbose_name = "Description")
    tr_withdrawal = models.DecimalField(
        max_digits=10, 
        decimal_places=3, 
        validators=[MinValueValidator(0.001)],
        blank = True, null = True,
        verbose_name = "Withdrawal")
    tr_deposits = models.DecimalField(
        max_digits=10, 
        decimal_places=3, 
        validators=[MinValueValidator(0.001)],
        blank = True, null = True,
        verbose_name = "Deposits")
    tr_balance = models.DecimalField(
        max_digits=10, 
        decimal_places=3, 
        blank = True, null = True,
        verbose_name = "Balance",
        help_text = "Transaction Balance")
    tags = models.ManyToManyField(
        Tag,
        related_name = "chq_trs",
        related_query_name = "chq_tr",
        verbose_name = "Tags",
        blank=True)
    owner = models.ForeignKey(
        User, 
        on_delete=models.CASCADE)

The tr_date, tr_desc, tr_withdrawal, tr_deposits, and tr_balance represent the specific columns in the csv file.
account relates to the chequing account model.

and the Chequing Account model

class ChequingAccount(models.Model):
    """ A class representing a Chequing Account """

    name = models.CharField(max_length=200, verbose_name = "Account Name")  
    notes = RichTextUploadingField(blank=True, default = '')
    owner = models.ForeignKey(
        User, 
        on_delete=models.CASCADE)

Credit Card Transaction model looks mainly like this

class CreditCardTransaction(models.Model):
    """ A class representing a Credit Card Transaction """

    account = models.ForeignKey(
        CreditCardAccount, 
        related_name = 'transactions', 
        related_query_name = 'transaction',
        verbose_name = "Account",
        on_delete=models.PROTECT)
    tr_date = models.DateField(verbose_name = "Date", help_text = "Transaction Date")
    tr_desc = models.CharField(max_length=200, verbose_name = "Description")
    tr_debit = models.DecimalField(
        max_digits=10, 
        decimal_places=3, 
        validators=[MinValueValidator(0.001)],
        blank = True, null = True,
        verbose_name = "Debit")
    tr_credit = models.DecimalField(
        max_digits=10, 
        decimal_places=3, 
        validators=[MinValueValidator(0.001)],
        blank = True, null = True,
        verbose_name = "Credit")
    tr_balance = models.DecimalField(
        max_digits=10, 
        decimal_places=3, 
        blank = True, null = True,
        verbose_name = "Balance",
        help_text = "Transaction Balance")
    tags = models.ManyToManyField(
        Tag,
        related_name = "cc_trs",
        related_query_name = "cc_tr",
        verbose_name = "Tags",
        blank=True)
    owner = models.ForeignKey(
        User, 
        on_delete=models.CASCADE)

and the Credit Card Account model

class CreditCardAccount(models.Model):
    """ A class representing a Credit Card Account """

    name = models.CharField(max_length=200, verbose_name = "Account Name")  
    notes = RichTextUploadingField(blank=True, default = '')
    owner = models.ForeignKey(
        User, 
        on_delete=models.CASCADE)

With these four models the data from the csv files seem to be well represented.
For these types of models I initially used an abstract model but then I thought let's do a bit WET instead of so much DRY.

For the transaction model:

The account name is not unique for different users. There can be accounts with the same name for different users.
tr_date for both types of transactions, represents the date when the transaction happened. In some of the csv files I found also the date when the transaction was posted but I think the date of the transaction is enough for what I need here.
tr_withdrawal and tr_deposits for the chequing transaction would mean any funds outflow from the account and, respectively, any funds inflow into the account.
Similarly for the credit card transaction, tr_debit and tr_credit would mean any funds outflow from the account and, respectively, any funds inflow into the account.
account, tr_date, tr_desc, and owner fields cannot be empty. All the other fields may have no values. So, fundamentally, those four fields describe a record for a transaction.

Tag Model

I found it useful to have a way to group some of these transactions together and do some data summing. A solution for that was to create a new model (and the corresponding database table) that would work like a tag. The transactions would get a tag and then in the detail view of the tag I can sum together all the transactions having that specific tag.

The model for the tag has a name field, a notes field, and the owner field.

class Tag(models.Model):
    """ A class representing a Tag.
    The idea for this model would be to mark a trasaction 
    (chequing or credit card) so these 
    can be displayed together in the tag detail.
    """

    name = models.CharField(max_length=200)
    owner = models.ForeignKey(
        User, 
        on_delete=models.CASCADE)

    def __str__(self):
        return self.name

    class Meta:
        verbose_name = "Tag"
        verbose_name_plural = 'Tags'
        ordering = ["name"]
        constraints = [
            models.UniqueConstraint(
                fields = ['owner', 'name', ],
                name = 'unique_owner_name_tag'
            )
        ]

    def get_absolute_url(self):
        return "/tags/{0}/".format(self.id)

The Expense Record and its related models

There are then, three models for the expense part of the app: ExpenseRecord, ExpenseType, and Location.

The Expense Type model has three fields: name, notes, and owner.

class ExpenseType(models.Model):
    """ A class represneting a type of expens like Gas, Paint.
    The Expense Type is tied to an expense record. 
    """

    name = models.CharField(max_length=200, verbose_name = "Expense")
    notes = RichTextUploadingField(blank=True, default = '')
    owner = models.ForeignKey(
        User, 
        on_delete=models.CASCADE)

The Location model has five fields: name, address, prov_st, country, and the owner.

class Location(models.Model):
    name = models.CharField(max_length=200, help_text = "Like Walmart, Costco")
    address = models.CharField(max_length=200, blank=True)
    prov_st = models.CharField(max_length=20, blank=True, verbose_name = "Province/State")
    country = models.CharField(max_length=20, blank=True)
    owner = models.ForeignKey(
        User, 
        on_delete=models.CASCADE)

The Expense Record model is related to the transactions models. An expense record can also be added without a transaction for example when adding a record for an expense for which the csv file is not yet available. Later on this expense record can be linked to a specific transaction. It also works for expenses paid in cash for which there would be no transaction.

class ExpenseRecord(models.Model):
    """A class reprenting an expense record tied to an expense type.
    There can be several expense reconrds on one transaction.
    """
    exp_type = models.ForeignKey(
        ExpenseType, 
        related_name = 'exp_recs', 
        related_query_name = 'exp_rec',
        verbose_name = "Expense Type",
        on_delete=models.PROTECT)
    exp_date = models.DateField(verbose_name = "Expense Date",) 
    amount = models.DecimalField(
        max_digits=10, 
        decimal_places=3, 
        validators=[MinValueValidator(0.01)])
    tax = models.DecimalField(
        max_digits=7, 
        decimal_places=3, 
        validators=[MinValueValidator(0.01)], 
        blank=True, 
        null=True) 
    location = models.ForeignKey(
        Location, 
        related_name = 'exp_recs', 
        related_query_name = 'exp_rec',
        on_delete=models.SET_NULL,
        default = '',
        blank = True,
        null = True,)
    chq_tr = models.ForeignKey(
        ChequingTransaction, 
        related_name = 'exp_recs', 
        related_query_name = 'exp_rec',
        verbose_name = "Chequing Transaction",
        blank = True,
        null = True,
        on_delete=models.PROTECT)
    cc_tr = models.ForeignKey(
        CreditCardTransaction, 
        related_name = 'exp_recs', 
        related_query_name = 'exp_rec',
        verbose_name = "Credit Card Transaction",
        blank = True,
        null = True,
        on_delete=models.PROTECT)
    quantity = models.CharField(max_length=20, blank=True, default = '')
    details = RichTextUploadingField(blank=True, default = '', verbose_name = "Record Details")
    files = models.FileField(upload_to='media/expenses/', blank=True, null=True)
    owner = models.ForeignKey(User, on_delete=models.CASCADE)

For the quantity field, it seemed better to be a CharField rather than one of the number fields. It can hold data like 40L or 2m.

All in all, these nine models help to pretty much describe the data needed as well as relationships between various database tables.

Models and relationships

ChequingAccount:        - owner     - FK --> User
CreditCardAccount:      - owner     - FK --> User
Tag:                    - owner     - FK --> User
ChequingTransaction:    - owner     - FK --> User
                        - account   - Fk --> ChequingAccount
CreditCardTransaction:  - owner     - FK --> User
                        - account   - Fk --> CreditCardAccount
ExpenseType:            - owner     - FK --> User
Location:               - owner     - FK --> User
ExpenseRecord:          - exp_type  - FK --> ExpenseType
                        - location  - FK --> Location
                        - chq_tr    - FK --> ChequingTransaction
                        - cc_tr     - FK --> CreditCardTransaction
                        - owner     - FK --> User

Uniqueness

One important functionality for the project and the apps was that the data for each user needs to be totally separated. When the user logs in they would see only their own data as it seems easier to focus on that specific data.
This required setting a lot of uniqueness in the models. It also came with some challenges.

Both models for accounts have the unique constraint composed of the owner and the name fields. The idea is that there can be accounts with the same name for different users but in the same time an account would be unique for one user.

For the transaction models, the uniquenes is made up from seven fields:
owner, account, tr_date, tr_desc, tr_withdrawal, tr_deposits, and tr_balance for chequing transaction record
owner, account, tr_date, tr_desc, tr_debit, tr_credit, and tr_balance for credit card transaction record

These might need some tweaking. The goal here would be to uniquely represent a record for each of the transactions for a specific user. This proved to be essential for import functionality.

Expense type model has the unique constraint formed from name and owner fields while the Location model has its uniqueness made from the owner, name, and address fields.

The Expense Record model has more fields needed to uniquely identify a record: owner, exp_type, exp_date, amount, location, chq_tr, cc_tr, quantity.