Spent and earned in 2023

summary-of-the-year.md

Firefly III

These queries can help you get an overview of your expenses and income in 2023. Although Firefly III contains many useful reports, sometimes it's helpful to have a quick overview of what you want to know exactly. And although the year is not yet over, these queries can give you valuable insights already.

These queries group transactions by "expense account", so they will probably only work if you use the expense account to indicate the exact shop or brand you spent your money at.

Big single expenses in 2023

This query shows your biggest single expenses this year, spent at one place you have not gone back to.

SQL query:

select SUM(transactions.amount) as total_amount, count(accounts.name) as transaction_count, accounts.name from transactions
left join transaction_journals on transaction_journals.id = transactions.transaction_journal_id
left join accounts on transactions.account_id = accounts.id
left join transaction_types on transaction_journals.transaction_type_id = transaction_types.id
where amount > 0
and transaction_types.type = "Withdrawal"
and transaction_journals.date >= "2023-01-01"
and transaction_journals.date <= "2023-12-31"
group by accounts.name
having transaction_count = 1
order by total_amount DESC, accounts.name ASC

All monthly expenses

Assuming of course you had this recurring payment the entire year, this query will show you your monthly obligations. You can finetune the numbers to find other regular expenses.

SQL query:

select SUM(transactions.amount) as total_amount, count(accounts.name) as transaction_count, accounts.name from transactions
left join transaction_journals on transaction_journals.id = transactions.transaction_journal_id
left join accounts on transactions.account_id = accounts.id
left join transaction_types on transaction_journals.transaction_type_id = transaction_types.id
where amount > 0
and transaction_types.type = "Withdrawal"
and transaction_journals.date >= "2023-01-01"
and transaction_journals.date <= "2023-12-31"
group by accounts.name
having transaction_count > 9 and transaction_count < 13
order by total_amount DESC, accounts.name ASC

All weekly expenses

This query is the same as the one above, and should show you where you spent money about weekly. Depending on how regular your schedule is, if you had a lot of holiday or time off, this should show you every place where you spend money weekly-ish.

SQL query:

select SUM(transactions.amount) as total_amount, count(accounts.name) as transaction_count, accounts.name from transactions
left join transaction_journals on transaction_journals.id = transactions.transaction_journal_id
left join accounts on transactions.account_id = accounts.id
left join transaction_types on transaction_journals.transaction_type_id = transaction_types.id
where amount > 0
and transaction_types.type = "Withdrawal"
and transaction_journals.date >= "2023-01-01"
and transaction_journals.date <= "2023-12-31"
group by accounts.name
having transaction_count > 45 and transaction_count < 54
order by total_amount DESC, accounts.name ASC

Expenses for a specific budget

This query will group your expenses in budget "X" (or any budgey you prefer) by expense account. It will help you see where you spent the most budgeted money for a specific budget.

If you want to see multiple budgets, change the line that says and budgets.name [..] to and budgets.name IN ("X", "Y").

SQL query:

select SUM(transactions.amount) as total_amount, count(accounts.name) as transaction_count, accounts.name as account_name, budgets.name as budget_name from transactions
left join transaction_journals on transaction_journals.id = transactions.transaction_journal_id
left join accounts on transactions.account_id = accounts.id
left join transaction_types on transaction_journals.transaction_type_id = transaction_types.id
left join budget_transaction_journal ON budget_transaction_journal.transaction_journal_id = transaction_journals.id
left join budgets ON budgets.id = budget_transaction_journal.budget_id
where amount > 0
and transaction_types.type = "Withdrawal"
and transaction_journals.date >= "2023-01-01"
and transaction_journals.date <= "2023-12-31"
and budgets.name = "X"
group by accounts.name, budgets.name
having transaction_count > 0
order by total_amount DESC, accounts.name ASC

Expenses for a specific category

You can do the same grouping for a specific category.

SQL query:

TODO

Biggest incomes

TODO

Single deposits

TODO

Most often overspent?

TODO