All of my banking transactions get put into a sqlite database, imported from csv...

gen220 · on Dec 29, 2019

SQLite is an underrated interface for this kind of stuff.

Do you support things like nested categories (Expenses:Food:Groceries) and budgets? If so, do you keep them in your data model, or do you construct them in the queries?

Also, just curious, have you considered using gnu Ledger? If so, what prompted you to choose SQLite over it?

decasteve · on Dec 30, 2019

No nested categories. Mostly it’s centred around having the correct amounts for tax returns. Some categories like groceries and eating out I have but I haven’t really used it for budgeting—just curiosity. I tag everything as business or personal. I have a Tax/HST column to make that easier.

Every month I get all of my various accounts exported as csv and then import into sqlite with a single command. It was just something I was tinkering with at first and then it became something I used.

I have considered gnu Ledger but my own personal tool does what I need and it was fun to do. So I haven’t felt the need to.

jpeeler · on Dec 29, 2019

I'd love to see what your schema looks like, assuming it's beyond what you mentioned (and obvious items like date and transaction amount).

decasteve · on Dec 30, 2019

My .schema is:

CREATE TABLE account (transaction_date text, amount integer, hst integer, subtotal integer, description text, debitcredit integer, category_id integer, account_id integer, business_percent integer, notes text);

CREATE TABLE category(category_id int, name text);