content/blog/2022-03-03-financial-database.org

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