Finances using SQLite.

This is about how to do simple finances using nothing more than SQLite.

You can use a GUI like SQLiteStudio which at least runs on MacOS, Windows, Linux and FreeBSD.

But nothing here requires a GUI or writing any "code" but does require writing some SQL.

I'll cover just the basics, getting a schema setup and writing some useful reports.

The theory/plan

First let's talk about what I track and why only this.

I don't track individual transactions, and most of my spending is via CC, so I track the CC bills and other expenses that come out monthly. I also track my monthly income. I don't care about spending $5 on coffee or whatever, I just care that I'm meeting my expense and savings goals over time. If I need to check on a particular transaction, once a year I download a CSV or PDF of all my CC expenses for the year and save them in the same directory as this SQLite file.

One could of course take what I've done here and adapt it to whatever data scheme you want, including tracking individual daily expenses, if one so desired.

So, let's get started, first we need to setup the tables(schema).

Schema

Overall we will have 4 tables:

• lk_type a lookup table for a type/category
• lk_account lookup table for accounts/banks
• cashflow table recording the monthly in and outs of an account
• yearly table tracking my networth across years.

lk_type table:

CREATE TABLE lk_type (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    type       TEXT    UNIQUE,
    is_income  BOOLEAN,
    is_expense BOOLEAN
);

CREATE TABLE cashflow (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    type_id            CONSTRAINT fk_lk_type_id REFERENCES lk_type (ID) NOT DEFERRABLE INITIALLY IMMEDIATE NOT NULL,
    event_date DATE    NOT NULL,
    name       TEXT,
    amount     DECIMAL NOT NULL,
    due_date   DATE,
);

Cashflow expenses and income views

CREATE VIEW cashflow_expense_balance AS
    SELECT strftime('%Y-%m', event_date) AS ym,
           strftime('%Y', event_date) AS year,
           strftime('%m', event_date) AS month,
           sum(amount) AS expense_balance
      FROM cashflow
           LEFT JOIN
           lk_type ON lk_type.id = cashflow.type_id
     WHERE lk_type.is_expense
     GROUP BY strftime('%Y-%m', event_date) 
     ORDER BY ym ASC;

CREATE VIEW cashflow_income_balance AS
    SELECT strftime('%Y-%m', event_date) AS ym,
           strftime('%Y', event_date) AS year,
           strftime('%m', event_date) AS month,
           sum(amount) AS income_balance
      FROM cashflow
           LEFT JOIN
           lk_type ON lk_type.id = cashflow.type_id
     WHERE lk_type.is_income
     GROUP BY strftime('%Y-%m', event_date) 
     ORDER BY ym ASC;;

First report, cashflow monthly, along with savings rate

CREATE VIEW cashflow_monthly_report AS
    SELECT ci.ym,
           printf("$ %.2f", ci.income_balance) AS income,
           printf("$ %.2f", ce.expense_balance) AS expenses,
           printf("$ %.2f", ci.income_balance - ce.expense_balance) AS savings,
           printf("%.2f %", ( (ci.income_balance - ce.expense_balance) / ci.income_balance) * 100) AS savings_rate
      FROM cashflow_expense_balance AS ce
           JOIN
           cashflow_income_balance ci ON ce.ym = ci.ym
     ORDER BY ci.ym ASC;