cmc/cleberg.net

  1#+date:        [2022-03-03 Thu 00:00:00]
  2#+title:       Personal Finance Tracker with SQLite
  3#+description: Building a personal finance tracker with SQLite, Python, and Jupyter.
  4#+slug:        financial-database
  5#+filetags:    :self-hosting:
  6
  7* Personal Financial Tracking
  8
  9For the last 6-ish years, I've tracked my finances in a spreadsheet. This is
 10common practice in the business world, but any good dev will cringe at the
 11thought of storing long-term data in a spreadsheet. A spreadsheet is not for
 12long-term storage or as a source of data to pull data/reports.
 13
 14As I wanted to expand the functionality of my financial data (e.g., adding more
 15reports), I decided to migrate the data into a database. To run reports, I would
 16query the database and use a language like Python or Javascript to process the
 17data, perform calculations, and visualize the data.
 18
 19* SQLite
 20
 21When choosing the type of database I wanted to use for this project, I was split
 22between three options:
 23
 241. MySQL: The database I have the most experience with and have used for years.
 252. PostgreSQL: A database I'm new to, but want to learn.
 263. SQLite: A database that I've used for a couple projects and have moderate
 27   experience.
 28
 29I ended up choosing SQLite since it can be maintained within a single =.sqlite=
 30file, which allows me more flexibility for storage and backup. I keep this file
 31in my cloud storage and pull it up whenever needed.
 32
 33** Visual Editing
 34
 35Since I didn't want to try and import 1000--1500 records into my new database
 36via the command line, I opted to use [[https://sqlitebrowser.org/][DB Browser for SQLite (DB4S)]] as a GUI
 37(graphical user interface) tool. This application is excellent, and I don't see
 38myself going back to the CLI (command line interface) when working in this
 39database.
 40
 41DB4S allows you to copy a range of cells from a spreadsheet and paste it
 42straight into the SQL table. I used this process for all 36 accounts, 1290
 43account statements, and 126 pay statements. Overall, I'm guessing this took
 44anywhere between 4--8 hours. In comparison, it probably took me 2-3 days to
 45initially create the spreadsheet.
 46
 47** Schema
 48
 49The schema for this database is actually extremely simple and involves only
 50three tables (for now):
 51
 521. Accounts
 532. Statements
 543. Payroll
 55
 56*Accounts*
 57
 58The Accounts table contains summary information about an account, such as a car
 59loan or a credit card. By viewing this table, you can find high-level data, such
 60as interest rate, credit line, or owner.
 61
 62#+begin_src sql
 63CREATE TABLE "Accounts" (
 64    "AccountID"     INTEGER NOT NULL UNIQUE,
 65    "AccountType"   TEXT,
 66    "AccountName"   TEXT,
 67    "InterestRate"  NUMERIC,
 68    "CreditLine"    NUMERIC,
 69    "State"         TEXT,
 70    "Owner"         TEXT,
 71    "Co-Owner"      TEXT,
 72    PRIMARY KEY("AccountID" AUTOINCREMENT)
 73)
 74#+end_src
 75
 76*Statements*
 77
 78The Statements table uses the same unique identifier as the Accounts table,
 79meaning you can join the tables to find a monthly statement for any of the
 80accounts listed in the Accounts table. Each statement has an account identified
 81(ID), statement date, and total balance.
 82
 83#+begin_src sql
 84CREATE TABLE "Statements" (
 85    "StatementID"   INTEGER NOT NULL UNIQUE,
 86    "AccountID"     INTEGER,
 87    "StatementDate" INTEGER,
 88    "Balance"       NUMERIC,
 89    PRIMARY KEY("StatementID" AUTOINCREMENT),
 90    FOREIGN KEY("AccountID") REFERENCES "Accounts"("AccountID")
 91)
 92#+end_src
 93
 94*Payroll*
 95
 96The Payroll table is a separate entity, unrelated to the Accounts or Statements
 97tables. This table contains all information you would find on a pay statement
 98from an employer. As you change employers or obtain new perks/benefits, just add
 99new columns to adapt to the new data.
100
101#+begin_src sql
102CREATE TABLE "Payroll" (
103    "PaycheckID"            INTEGER NOT NULL UNIQUE,
104    "PayDate"               TEXT,
105    "Payee"                 TEXT,
106    "Employer"              TEXT,
107    "JobTitle"              TEXT,
108    "IncomeRegular"         NUMERIC,
109    "IncomePTO"             NUMERIC,
110    "IncomeHoliday"         NUMERIC,
111    "IncomeBonus"           NUMERIC,
112    "IncomePTOPayout"       NUMERIC,
113    "IncomeReimbursements"  NUMERIC,
114    "FringeHSA"             NUMERIC,
115    "FringeStudentLoan"     NUMERIC,
116    "Fringe401k"            NUMERIC,
117    "PreTaxMedical"         NUMERIC,
118    "PreTaxDental"          NUMERIC,
119    "PreTaxVision"          NUMERIC,
120    "PreTaxLifeInsurance"   NUMERIC,
121    "PreTax401k"            NUMERIC,
122    "PreTaxParking"         NUMERIC,
123    "PreTaxStudentLoan"     NUMERIC,
124    "PreTaxOther"           NUMERIC,
125    "TaxFederal"            NUMERIC,
126    "TaxSocial"             NUMERIC,
127    "TaxMedicare"           NUMERIC,
128    "TaxState"              NUMERIC,
129    PRIMARY KEY("PaycheckID" AUTOINCREMENT)
130)
131#+end_src
132
133** Python Reporting
134
135Once I created the database tables and imported all my data, the only step left
136was to create a process to report and visualize on various aspects of the data.
137
138In order to explore and create the reports I'm interested in, I utilized a
139two-part process involving Jupyter Notebooks and Python scripts.
140
141*** Step 1: Jupyter Notebooks
142
143When I need to explore data, try different things, and re-run my code
144cell-by-cell, I use Jupyter Notebooks. For example, I explored the =Accounts=
145table until I found the following useful information:
146
147#+begin_src python
148import sqlite3
149import pandas as pd
150import matplotlib
151
152# Set up database filename and connect
153db = "finances.sqlite"
154connection = sqlite3.connect(db)
155df = pd.read_sql_query("SELECT ** FROM Accounts", connection)
156
157# Set global matplotlib variables
158%matplotlib inline
159matplotlib.rcParams['text.color'] = 'white'
160matplotlib.rcParams['axes.labelcolor'] = 'white'
161matplotlib.rcParams['xtick.color'] = 'white'
162matplotlib.rcParams['ytick.color'] = 'white'
163matplotlib.rcParams['legend.labelcolor'] = 'black'
164
165# Display graph
166df.groupby(['AccountType']).sum().plot.pie(title='Credit Line by Account Type', y='CreditLine', figsize=(5,5), autopct='%1.1f%%')
167#+end_src
168
169*** Step 2: Python Scripts
170
171Once I explored enough through the notebooks and had a list of reports I wanted,
172I moved on to create a Python project with the following structure:
173
174#+begin_src txt
175finance/
176├── notebooks/
177│   │   ├── account_summary.ipynb
178│   │   ├── account_details.ipynb
179│   │   └── payroll.ipynb
180├── public/
181│   │   ├── image-01.png
182│   │   └── image-0X.png
183├── src/
184│   └── finance.sqlite
185├── venv/
186├── _init.py
187├── database.py
188├── process.py
189├── requirements.txt
190└── README.md
191#+end_src
192
193This structure allows me to:
194
1951. Compile all required python packages into =requirements.txt= for easy
196   installation if I move to a new machine.
1972. Activate a virtual environment in =venv/= so I don't need to maintain a
198   system-wide Python environment just for this project.
1993. Keep my =notebooks/= folder to continuously explore the data as I see fit.
2004. Maintain a local copy of the database in =src/= for easy access.
2015. Export reports, images, HTML files, etc. to =public/=.
202
203Now, onto the differences between the code in a Jupyter Notebook and the actual
204Python files. To create the report in the Notebook snippet above, I created the
205following function inside =process.py=:
206
207#+begin_src python
208# Create summary pie chart
209def summary_data(accounts: pandas.DataFrame) -> None:
210    accounts_01 = accounts[accounts["Owner"] == "Person01"]
211    accounts_02 = accounts[accounts["Owner"] == "Person02"]
212    for x in range(1, 4):
213        if x == 1:
214            df = accounts
215            account_string = "All Accounts"
216        elif x == 2:
217            df = accounts_01
218            account_string = "Person01's Accounts"
219        elif x == 3:
220            df = accounts_02
221            account_string = "Person02's Accounts"
222        print(f"Generating pie chart summary image for {account_string}...")
223        summary_chart = (
224            df.groupby(["AccountType"])
225            .sum()
226            .plot.pie(
227                title=f"Credit Line by Type for {account_string}",
228                y="CreditLine",
229                autopct="%1.1f%%",
230            )
231        )
232        summary_chart.figure.savefig(f"public/summary_chart_{x}.png", dpi=1200)
233#+end_src
234
235The result? A high-quality pie chart that is read directly by the
236=public/index.html= template I use.
237
238Other charts generated by this project include:
239
240- Charts of account balances over time.
241- Line chart of effective tax rate (taxes divided by taxable income).
242- Salary projections and error limits using past income and inflation rates.
243- Multi-line chart of gross income, taxable income, and net income.
244
245The best thing about this project? I can improve it at any given time, shaping
246it into whatever helps me the most for that time. I imagine that I will be
247introducing an asset tracking table soon to track the depreciating value of
248cars, houses, etc. Who knows what's next?