Budget Manager: BillSense and BalanceSense

Recently one of the Big 4 Bank in Australia released a functionality in their Mobile Banking app called BillSense. It is a feature that analyses your transactions and predicts your future bills.

As a customer of the said bank, I understand how the BillSense work is by looking at my past transactions, and identify future bills from it. The app then asks my confirmation of which is bills, and which is not. This data then is used by the app to project future balance, and ask myself to set apart some funds for those bills.

While this is great, it is however aimed for the Millennials, in which I am not. For example, it doesn’t predict future income or other regular expenses such as dining out or groceries. Which means it doesn’t estimate future balance.

I know that I am disciplined with my spending and I have a very good idea of my regular bills. What I need therefore is a balance projection to predict when I will be able to achieve my savings or retirement goal.

In this post, I aim to build my own version of BillSense on my own Budget Manager. If you haven’t read the previous post yet, I suggest you read that first, because this post aim to add functionality on top of that.

Fortunately, my Budget Manager is much more refined than my bank accounts. As I tailored it for myself, it categorises my transactions much more accurately, therefore I can project my balances based on my spending for each category. For example, I can predict my future income, estimate non-bills regular spendings like groceries, dining out, Clothing or Gadgets and such, as well as the regular bills (like BillSense). This should give me a very good idea where my future would be.

Just for fun, let’s call this little project “BalanceSense”.

Table Set Up

First I will create a table with identical structure as my Transactions table. Let’s call this table the_Prophet.

CREATE TABLE "the_Prophet" (
	"Transaction_ID"	INTEGER NOT NULL,
	"Account_ID"	INTEGER NOT NULL,
	"Category_ID"	INTEGER,
	"Payee_ID"	INTEGER,
	"Payee_name"	TEXT,
	"Tran_date"	DATE NOT NULL,
	"tran_value"	REAL NOT NULL,
	"Memo"	TEXT,
	"Tax_flag"	Boolean CHECK(Tax_flag IN (0,1)),
	"Transfer_ID"	INTEGER, tag TEXT,
	PRIMARY KEY("Transaction_ID")
)

Then I will populate it with data from the Transactions table.

Seasonal Transactions

First I will insert the transactions that would probably have the same amount each year.

This vary from person to person, however usually they are the same:
1. Electricity bills. Electricity bills are seasonal, in which we usually spend more in winter for heating, or in summer for cooling.
2. Gas bills. For the same reason above, gas bills tend to be higher in winter.
3. Car Maintenance and Insurance: Motor Vehicle maintenance and bills usually occur yearly.
4. Gardening: Gardens need more work in the summer.
5. Vacation costs: We usually go on vacation on the same periods each year. Exception to this is Easter times, which does move around between March and April.
6. Any membership costs or website renewals.

insert into the_Prophet
select null 
	,account_id
	,Category_ID
	,Payee_id
	,Payee_name
	, date(tran_date, '+365 day') as date  
	,tran_value
	,Memo
	,Tax_flag
	,Transfer_ID
	,tag
from Transactions
where tran_date between (select date(max(tran_date), '-365 days') as date from transactions) and (select max(tran_date) from transactions) 
	and category_id in (<insert the category IDs here>)
;	

Irregular Transactions

Then we insert transactions that follow a trend of the past few months. In my code I will use the past 3 months average as an estimate.
This usually are things that we spend on the store:
1. Groceries
2. Dining Out (although we usually dine out more in certain months)
3. Entertainment such as Cable TV, Snacks, Games, etc.

We simply need to repeat this sql 4 times. Since it is repetitive, it is best to put this into Python loops.

day_range = range(90,365,90)
for x in day_range:
    insert_sql='''
        insert into the_Prophet
        select null 
        	,account_id
        	,Category_ID
        	,Payee_id
        	,Payee_name
        	,date(tran_date, '+'''+str(x)+''' day') as date
        	,tran_value
        	,Memo
        	,Tax_flag
        	,Transfer_ID
        	,tag
        from Transactions
        where tran_date between (select date(max(tran_date), '-90 days') as date from transactions) and (select max(tran_date) from transactions) 
        	and category_id in (<insert the category IDs here>)
    ;'''
    conn.execute(insert_sql).fetchall()

Regular Transactions

Finally, we insert transactions that occur almost always the same amount each month.
These are transactions such as:
1. Mortgage/Rent Repayments
2. Salary
3. Childcare/school fees
4. Phone/Internet bills

day_range = range(30,365,30)
for x in day_range:
    insert_sql='''
        insert into the_Prophet
        select null 
        	,account_id
        	,Category_ID
        	,Payee_id
        	,Payee_name
        	,date(tran_date, '+'''+str(x)+''' day') as date
        	,tran_value
        	,Memo
        	,Tax_flag
        	,Transfer_ID
        	,tag
        from Transactions
        where tran_date between (select date(max(tran_date), '-30 days') as date from transactions) and (select max(tran_date) from transactions) 
        	and category_id in (<insert the category IDs here>)
    ;'''
    conn.execute(insert_sql).fetchall()

Finally we only need to union this into our reporting query as defined in the Budget Manager post, and it’s done. I can now forecast my balance and savings for the next 12 months! Now I know when I can buy a new Tesla, or a new home 🙂

In the short future, I will try to forecast my wealth for the next foreseeable years. I will factor in predictable changes in my lifestyle from year to year (which have 2 growing children), it should be interesting. Stay tuned by following me!