feat: finish converting md to org

1 files changed, 173 insertions, 154 deletions

diff --git a/blog/2022-03-03-financial-database.org b/blog/2022-03-03-financial-database.org
index e0f524d..c097c64 100644
--- a/blog/2022-03-03-financial-database.org
+++ b/blog/2022-03-03-financial-database.org
@@ -1,153 +1,166 @@
-+++
-date = 2022-03-03
-title = "Maintaining a Personal Financial Database"
-description = "Detailing my process for creating, maintaining, and utilizing a personal financial database."
-draft = false
-+++
-
-## 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.
+#+title: Maintaining a Personal Financial Database
+#+date:  2022-03-03
+
+** Personal Financial Tracking
+:PROPERTIES:
+:CUSTOM_ID: personal-financial-tracking
+:END:
+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
+:PROPERTIES:
+:CUSTOM_ID: sqlite
+:END:
+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
-[DB Browser for SQLite (DB4S)](https://sqlitebrowser.org/) as a GUI tool. This
-application is excellent, and I don't see myself going back to the CLI
-when working in this database.
+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
+:PROPERTIES:
+:CUSTOM_ID: gui-editing
+:END:
+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.
+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.
 
-![DB4S](https://img.cleberg.net/blog/20220303-maintaining-a-personal-financial-database/db4s.png)
+#+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):
+*** Schema
+:PROPERTIES:
+:CUSTOM_ID: schema
+:END:
+The schema for this database is actually extremely simple and involves
+only three tables (for now):
 
 1. Accounts
 2. Statements
 3. Payroll
 
-**Accounts**
+*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.
+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
+#+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)
+    "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**
+*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.
+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.
 
-```sql
+#+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")
+    "StatementID"   INTEGER NOT NULL UNIQUE,
+    "AccountID"     INTEGER,
+    "StatementDate" INTEGER,
+    "Balance"       NUMERIC,
+    PRIMARY KEY("StatementID" AUTOINCREMENT),
+    FOREIGN KEY("AccountID") REFERENCES "Accounts"("AccountID")
 )
-```
+#+end_src
 
-**Payroll**
+*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.
+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.
 
-```sql
+#+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)
+    "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)
 )
-```
-
-### 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.
-
-#### Step 1: Jupyter Notebooks
-
+#+end_src
+
+*** Python Reporting
+:PROPERTIES:
+:CUSTOM_ID: python-reporting
+:END:
+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.
+
+**** Step 1: Jupyter Notebooks
+:PROPERTIES:
+:CUSTOM_ID: step-1-jupyter-notebooks
+:END:
 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
+#+begin_src python
 import sqlite3
 import pandas as pd
 import matplotlib
@@ -167,14 +180,17 @@ 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%%')
-```
-
-#### Step 2: Python Scripts
+#+end_src
 
-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:
+**** Step 2: Python Scripts
+:PROPERTIES:
+:CUSTOM_ID: step-2-python-scripts
+:END:
+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_example
 finance/
 ├── notebooks/
 │   │   ├── account_summary.ipynb
@@ -191,23 +207,24 @@ finance/
 ├── process.py
 ├── requirements.txt
 └── README.md
-```
+#+end_example
 
 This structure allows me to:
 
-1. Compile all required python packages into `requirements.txt` for easy
+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`:
-
-```python
+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"]
@@ -233,21 +250,23 @@ def summary_data(accounts: pandas.DataFrame) -> None:
             )
         )
         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.
+=public/index.html= template I use.
 
-![Summary Pie Chart](https://img.cleberg.net/blog/20220303-maintaining-a-personal-financial-database/summary_chart.png)
+#+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.
+- 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?