For the past ~6 months, I’ve been using an Android application to keep track of my daily spending. To my annoyance, I found out that the app doesn’t have an export functionality. I didn’t want to invest more time in a platform that I couldn’t get my data out of, so I started looking for another solution.
I’ve looked into budgeting systems before, and I’ve seen both command-line (ledger) and GUI systems (GNUCash). Now; both of these are great software, and I can appreciate how Double-entry bookkeeping is a useful thing for accounting purposes. But while they are powerful, they’re not as simple as they could be.
I decided to go with CSV files. CSV is one of the most universal file formats, it’s simple and obvious. I can process it with pretty much every programming language and import it to pretty much every spreadsheet software. Or… I could use a shell script to run calculations with SQLite.
If I ever want to migrate to another system; it will probably be possible to convert this file with a shell script, or even a sed command.
I create monthly CSV files in order to keep everything nice and tidy, but the script adapts to everything from a single CSV file to one file for each day/hour/minute.
Here’s what an example file looks like:
Date,Amount,Currency,Category,Description
2019-04-02,5.45,EUR,Food,Centra
2019-04-03,2.75,EUR,Transport,Bus to work
And here’s the script:
#!/bin/sh
days=${1:-7}
cat *.csv | sed '/^Date/d' > combined.csv.temp
output=$(sqlite3 <<EOF
create table Transactions(Date, Amount, Currency, Category, Description);
.mode csv
.import combined.csv.temp Transactions
.mode list
select 'Amount spent today:',
coalesce(sum(Amount), 0) from Transactions where Date = '$(date +%Y-%m-%d)';
select '';
select 'Last $days days average:',
sum(Amount)/$days, Currency from Transactions where Date > '$(date --date="-$days days" +%Y-%m-%d)'
group by Currency;
select '';
select 'Last $days days by category';
select '=======================';
select Category, sum(Amount) from Transactions
where Date > '$(date --date="-$days days" +%Y-%m-%d)'
group by Category order by sum(Amount) desc;
EOF
)
rm combined.csv.temp
echo "$output" | sed 's/|/ /g'
This is the output of the command
[leo@leo-arch budget]$ ./budget.sh
Amount spent today: 8.46
Last 7 days average: 15.35 EUR
Last 7 days by category
=======================
Groceries 41.09
Transport 35.06
Food 31.35
[leo@leo-arch budget]$ ./budget.sh 5
Amount spent today: 8.46
Last 5 days average: 11.54 EUR
Last 5 days by category
=======================
Groceries 29.74
Transport 17.06
Food 10.9
[leo@leo-arch budget]$