I've been using the free version of Expense Manager to keep track of my expenses last 2017 and luckily I was able to make complete entries for the whole year. This year I plan to set a monthly budget and try to stick to it and I will be using last year's data.
Expense Manager uses sqlite as storage and it has an option to backup the data either as raw sqlite data or csv. Instead of using the raw sqlite data backup, I created a new database (using sqlite3) and created the expenses schema is below.
CREATE TABLE expenses(
date_incurred DATE,
amount NUMERIC(8,2),
category VARCHAR(27),
description VARCHAR(24)
);
After preprocessing the csv file by removing unneeded columns, sample insert statements generated are shown below.
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-01',-55,'Snacks',NULL);
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-01',-21,'Transportation',NULL);
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-02',-1150,'Others','radio');
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-02',-66,'Others','battery');
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-02',-1700,'Others','bag');
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-02',-500,'Dinner',NULL);
Some of my expense categories:
Snacks
Transportation
Others
Dinner
Medicines and Supplements
Breakfast
Lunch
Groceries
Apartment Rent
Cable/Internet
Laundry
Water
Cellphone Load
Haircut
Clothing
Mom Allowance
Books and Reading Materials
Rest and Recreation
Now to answer some of the interesting questions.
What was my total expenses for February 2017?
>select sum(amount) from expenses where date_incurred>="2017-02-01" and date_incurred<="2017-02-28";
What was my total expense for 2017?
>select sum(amount) from expenses;
What are the categories of my expenses?
>select distinct category from expenses;
How many times did I pay my apartment rent in 2017?
>select count(amount) from expenses where category="Apartment Rent";
How much did I pay for breakfast in 2017?
>select sum(amount) from expenses where category="Breakfast";
How much is my expense per month in 2017?
>select strftime('%m',date_incurred) as month, sum(amount) as amt from expenses where strftime('%Y',date_incurred)='2017' group by month;
How much did I spend for lunch each month, total and average, in 2017?
>select strftime('%m',date_incurred) as month, sum(amount) as amt,avg(amount) as mean from expenses where strftime('%Y',date_incurred)='2017' and category="Lunch" group by month order by amt;
What is the average amount I spent for lunch in 2017?
>select avg(amount) from expenses where category='Lunch';
My budget for snacks for one month should be.
>select avg(amount)*30 from expenses where category='Snacks';
Expense Manager uses sqlite as storage and it has an option to backup the data either as raw sqlite data or csv. Instead of using the raw sqlite data backup, I created a new database (using sqlite3) and created the expenses schema is below.
CREATE TABLE expenses(
date_incurred DATE,
amount NUMERIC(8,2),
category VARCHAR(27),
description VARCHAR(24)
);
After preprocessing the csv file by removing unneeded columns, sample insert statements generated are shown below.
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-01',-55,'Snacks',NULL);
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-01',-21,'Transportation',NULL);
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-02',-1150,'Others','radio');
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-02',-66,'Others','battery');
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-02',-1700,'Others','bag');
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-02',-500,'Dinner',NULL);
Some of my expense categories:
Snacks
Transportation
Others
Dinner
Medicines and Supplements
Breakfast
Lunch
Groceries
Apartment Rent
Cable/Internet
Laundry
Water
Cellphone Load
Haircut
Clothing
Mom Allowance
Books and Reading Materials
Rest and Recreation
Now to answer some of the interesting questions.
What was my total expenses for February 2017?
>select sum(amount) from expenses where date_incurred>="2017-02-01" and date_incurred<="2017-02-28";
What was my total expense for 2017?
>select sum(amount) from expenses;
What are the categories of my expenses?
>select distinct category from expenses;
How many times did I pay my apartment rent in 2017?
>select count(amount) from expenses where category="Apartment Rent";
How much did I pay for breakfast in 2017?
>select sum(amount) from expenses where category="Breakfast";
How much is my expense per month in 2017?
>select strftime('%m',date_incurred) as month, sum(amount) as amt from expenses where strftime('%Y',date_incurred)='2017' group by month;
How much did I spend for lunch each month, total and average, in 2017?
>select strftime('%m',date_incurred) as month, sum(amount) as amt,avg(amount) as mean from expenses where strftime('%Y',date_incurred)='2017' and category="Lunch" group by month order by amt;
What is the average amount I spent for lunch in 2017?
>select avg(amount) from expenses where category='Lunch';
My budget for snacks for one month should be.
>select avg(amount)*30 from expenses where category='Snacks';
