diff options
Diffstat (limited to 'content/blog/2022-03-03-financial-database.md')
| -rw-r--r-- | content/blog/2022-03-03-financial-database.md | 50 |
1 files changed, 24 insertions, 26 deletions
diff --git a/content/blog/2022-03-03-financial-database.md b/content/blog/2022-03-03-financial-database.md index bfe5a98..726e7ee 100644 --- a/content/blog/2022-03-03-financial-database.md +++ b/content/blog/2022-03-03-financial-database.md @@ -62,7 +62,7 @@ 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. -``` sql +```sql CREATE TABLE "Accounts" ( "AccountID" INTEGER NOT NULL UNIQUE, "AccountType" TEXT, @@ -83,7 +83,7 @@ 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. -``` sql +```sql CREATE TABLE "Statements" ( "StatementID" INTEGER NOT NULL UNIQUE, "AccountID" INTEGER, @@ -101,7 +101,7 @@ 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. -``` sql +```sql CREATE TABLE "Payroll" ( "PaycheckID" INTEGER NOT NULL UNIQUE, "PayDate" TEXT, @@ -141,13 +141,13 @@ 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. -### Step 1: Jupyter Notebooks +### 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: +cell-by-cell, I use Jupyter Notebooks. For example, I explored the `Accounts` +table until I found the following useful information: -``` python +```python import sqlite3 import pandas as pd import matplotlib @@ -169,12 +169,12 @@ matplotlib.rcParams['legend.labelcolor'] = 'black' df.groupby(['AccountType']).sum().plot.pie(title='Credit Line by Account Type', y='CreditLine', figsize=(5,5), autopct='%1.1f%%') ``` -### Step 2: Python Scripts +### 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: +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: -``` txt +```txt finance/ ├── notebooks/ │ │ ├── account_summary.ipynb @@ -199,16 +199,15 @@ This structure allows me to: 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. +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`: +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`: -``` python +```python # Create summary pie chart def summary_data(accounts: pandas.DataFrame) -> None: accounts_01 = accounts[accounts["Owner"] == "Person01"] @@ -244,13 +243,12 @@ Chart](https://img.cleberg.net/blog/20220303-maintaining-a-personal-financial-da 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. +- 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? +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? |