path: root/content/blog/2022-03-03-financial-database.org

#+title: Maintaining a Personal Financial Database
#+date: 2022-03-03
#+description: An example project showing to build and maintain a simple financial database.
#+filetags: :personal:

* Personal Financial Tracking
For the last 6-ish years, I've tracked my finances in a spreadsheet.
This is common practice in the business world, but any good dev will
cringe at the thought of storing long-term data in a spreadsheet. A
spreadsheet is not for long-term storage or as a source of data to pull
data/reports.

As I wanted to expand the functionality of my financial data (e.g.,
adding more reports), I decided to migrate the data into a database. To
run reports, I would query the database and use a language like Python
or Javascript to process the data, perform calculations, and visualize
the data.

* SQLite
When choosing the type of database I wanted to use for this project, I
was split between three options:

1. MySQL: The database I have the most experience with and have used for
   years.
2. PostgreSQL: A database I'm new to, but want to learn.
3. SQLite: A database that I've used for a couple projects and have
   moderate experience.

I ended up choosing SQLite since it can be maintained within a single
=.sqlite= file, which allows me more flexibility for storage and backup.
I keep this file in my cloud storage and pull it up whenever needed.

** GUI Editing
Since I didn't want to try and import 1000--1500 records into my new
database via the command line, I opted to use
[[https://sqlitebrowser.org/][DB Browser for SQLite (DB4S)]] as a GUI
tool. This application is excellent, and I don't see myself going back
to the CLI when working in this database.

DB4S allows you to copy a range of cells from a spreadsheet and paste it
straight into the SQL table. I used this process for all 36 accounts,
1290 account statements, and 126 pay statements. Overall, I'm guessing
this took anywhere between 4--8 hours. In comparison, it probably took
me 2-3 days to initially create the spreadsheet.

#+caption: DB4S
[[https://img.cleberg.net/blog/20220303-maintaining-a-personal-financial-database/db4s.png]]

** Schema
The schema for this database is actually extremely simple and involves
only three tables (for now):

1. Accounts
2. Statements
3. Payroll

*Accounts*

The Accounts table contains summary information about an account, such
as a car loan or a credit card. By viewing this table, you can find
high-level data, such as interest rate, credit line, or owner.

#+begin_src sql
CREATE TABLE "Accounts" (
    "AccountID"     INTEGER NOT NULL UNIQUE,
    "AccountType"   TEXT,
    "AccountName"   TEXT,
    "InterestRate"  NUMERIC,
    "CreditLine"    NUMERIC,
    "State"         TEXT,
    "Owner"         TEXT,
    "Co-Owner"      TEXT,
    PRIMARY KEY("AccountID" AUTOINCREMENT)
)
#+end_src

*Statements*

The Statements table uses the same unique identifier as the Accounts
table, meaning you can join the tables to find a monthly statement for
any of the accounts listed in the Accounts table. Each statement has an
account ID, statement date, and total balance.

#+begin_src sql
CREATE TABLE "Statements" (
    "StatementID"   INTEGER NOT NULL UNIQUE,
    "AccountID"     INTEGER,
    "StatementDate" INTEGER,
    "Balance"       NUMERIC,
    PRIMARY KEY("StatementID" AUTOINCREMENT),
    FOREIGN KEY("AccountID") REFERENCES "Accounts"("AccountID")
)
#+end_src

*Payroll*

The Payroll table is a separate entity, unrelated to the Accounts or
Statements tables. This table contains all information you would find on
a pay statement from an employer. As you change employers or obtain new
perks/benefits, just add new columns to adapt to the new data.

#+begin_src sql
CREATE TABLE "Payroll" (
    "PaycheckID"            INTEGER NOT NULL UNIQUE,
    "PayDate"               TEXT,
    "Payee"                 TEXT,
    "Employer"              TEXT,
    "JobTitle"              TEXT,
    "IncomeRegular"         NUMERIC,
    "IncomePTO"             NUMERIC,
    "IncomeHoliday"         NUMERIC,
    "IncomeBonus"           NUMERIC,
    "IncomePTOPayout"       NUMERIC,
    "IncomeReimbursements"  NUMERIC,
    "FringeHSA"             NUMERIC,
    "FringeStudentLoan"     NUMERIC,
    "Fringe401k"            NUMERIC,
    "PreTaxMedical"         NUMERIC,
    "PreTaxDental"          NUMERIC,
    "PreTaxVision"          NUMERIC,
    "PreTaxLifeInsurance"   NUMERIC,
    "PreTax401k"            NUMERIC,
    "PreTaxParking"         NUMERIC,
    "PreTaxStudentLoan"     NUMERIC,
    "PreTaxOther"           NUMERIC,
    "TaxFederal"            NUMERIC,
    "TaxSocial"             NUMERIC,
    "TaxMedicare"           NUMERIC,
    "TaxState"              NUMERIC,
    PRIMARY KEY("PaycheckID" AUTOINCREMENT)
)
#+end_src

** Python Reporting
Once I created the database tables and imported all my data, the only
step left was to create a process to report and visualize on various
aspects of the data.

In order to explore and create the reports I'm interested in, I utilized
a two-part process involving Jupyter Notebooks and Python scripts.

1. Step 1: Jupyter Notebooks

   When I need to explore data, try different things, and re-run my code
   cell-by-cell, I use Jupyter Notebooks. For example, I explored the
   =Accounts= table until I found the following useful information:

   #+begin_src python
   import sqlite3
   import pandas as pd
   import matplotlib

   # Set up database filename and connect
   db = "finances.sqlite"
   connection = sqlite3.connect(db)
   df = pd.read_sql_query("SELECT ** FROM Accounts", connection)

   # Set global matplotlib variables
   %matplotlib inline
   matplotlib.rcParams['text.color'] = 'white'
   matplotlib.rcParams['axes.labelcolor'] = 'white'
   matplotlib.rcParams['xtick.color'] = 'white'
   matplotlib.rcParams['ytick.color'] = 'white'
   matplotlib.rcParams['legend.labelcolor'] = 'black'

   # Display graph
   df.groupby(['AccountType']).sum().plot.pie(title='Credit Line by Account Type', y='CreditLine', figsize=(5,5), autopct='%1.1f%%')
   #+end_src

2. Step 2: Python Scripts

   Once I explored enough through the notebooks and had a list of
   reports I wanted, I moved on to create a Python project with the
   following structure:

   #+begin_src txt
   finance/
   ├── notebooks/
   │   │   ├── account_summary.ipynb
   │   │   ├── account_details.ipynb
   │   │   └── payroll.ipynb
   ├── public/
   │   │   ├── image-01.png
   │   │   └── image-0X.png
   ├── src/
   │   └── finance.sqlite
   ├── venv/
   ├── _init.py
   ├── database.py
   ├── process.py
   ├── requirements.txt
   └── README.md
   #+end_src

   This structure allows me to:

   1. Compile all required python packages into =requirements.txt= for
      easy installation if I move to a new machine.
   2. Activate a virtual environment in =venv/= so I don't need to
      maintain a system-wide Python environment just for this project.
   3. Keep my =notebooks/= folder to continuously explore the data as I
      see fit.
   4. Maintain a local copy of the database in =src/= for easy access.
   5. Export reports, images, HTML files, etc. to =public/=.

   Now, onto the differences between the code in a Jupyter Notebook and
   the actual Python files. To create the report in the Notebook snippet
   above, I created the following function inside =process.py=:

   #+begin_src python
   # Create summary pie chart
   def summary_data(accounts: pandas.DataFrame) -> None:
       accounts_01 = accounts[accounts["Owner"] == "Person01"]
       accounts_02 = accounts[accounts["Owner"] == "Person02"]
       for x in range(1, 4):
           if x == 1:
               df = accounts
               account_string = "All Accounts"
           elif x == 2:
               df = accounts_01
               account_string = "Person01's Accounts"
           elif x == 3:
               df = accounts_02
               account_string = "Person02's Accounts"
           print(f"Generating pie chart summary image for {account_string}...")
           summary_chart = (
               df.groupby(["AccountType"])
               .sum()
               .plot.pie(
                   title=f"Credit Line by Type for {account_string}",
                   y="CreditLine",
                   autopct="%1.1f%%",
               )
           )
           summary_chart.figure.savefig(f"public/summary_chart_{x}.png", dpi=1200)
   #+end_src

   The result? A high-quality pie chart that is read directly by the
   =public/index.html= template I use.

   #+caption: Summary Pie Chart
   [[https://img.cleberg.net/blog/20220303-maintaining-a-personal-financial-database/summary_chart.png]]

   Other charts generated by this project include:

   - Charts of account balances over time.
   - Line chart of effective tax rate (taxes divided by taxable income).
   - Salary projections and error limits using past income and inflation
     rates.
   - Multi-line chart of gross income, taxable income, and net income.

   The best thing about this project? I can improve it at any given
   time, shaping it into whatever helps me the most for that time. I
   imagine that I will be introducing an asset tracking table soon to
   track the depreciating value of cars, houses, etc. Who knows what's
   next?