Project of the week — Budget App

8 min readAug 11, 2019

I created a small budget script that saves expenses into an excel file using Pandas and NumPy.

I’ve always had a hard time budgeting my money effectively. I’ve tried to use different budgeting apps but they just never worked for me. There were so many features that I didn’t need or that I did need but wasn’t available.

The wonderful thing about programming is that now I have this ability to custom make a budgeting app that works for me and this could be something that helps others too!

App Design

I’m going to be using Python as the programming language because eventually I’ll add features to analyze spending habits. There is a python module called openpyxl that allows users to create scripts to edit and create Excel files. NumPy and Pandas are going to be used to perform calculations and data analysis.

The features of the app will be very simple at first. I think at first I’ll have three columns. One column will be for the spending item, the next column will be the price of the item, and the final column is the date.

Creating and editing Excel files

To create and edit an excel workbook you have to initialize the workbook:

from openpyxl import Workbookwb = Workbook()#edit the current worksheetws.wb.active

You can add values to specific cells with key value pairs:

ws['A1'] = 'Hey there kris'#to save edits to the filewb.save('budget.xlsx')

What we have to figure out:

How to create and label different columns
Ask for input from the user about spending item, price and date
Edit the corresponding columns and save the file

Using Pandas

As it turns out Pandas has methods to read and write to excel files. So instead of using openpyxl, I’ll be using the Pandas module. This makes it easier to store data into the excel file with the following code:

try:
    df = pd.read_excel('Budget.xlsx')
except:
    writer = ExcelWriter('Budget.xlsx')df = pd.DataFrame({'Item':[],'Cost':[],'Day':[]})
writer = ExcelWriter('Budget.xlsx')
df.to_excel(writer,'Sheet1',index=False)
writer.save()

The script will try to create a data frame object from the excel file Budget but if it does not exist, the file will be created. A data frame is created and saved to the excel file with three columns as Items, Cost, and Day. The next step is to ask for user input about the item, cost and date of purchase. Then I need to figure out a good way to store that data so it can be pulled later on.

User Input

The input() method is used to get user input for the script. For now I’ll save it into a variable and save it to the excel file.

item = input('Item/Object you spent your money on: ')
cost = int(input('How much did %s cost?' % item))
day = input('When did you buy %S? (mm/dd/yyyy): " % item)

The updated code is:

try:
    df = pd.read_excel('Budget.xlsx')
except:
    writer = ExcelWriter('Budget.xlsx')
    
item = input('Item/object you spent your money on: ')
cost = int(input('How much did %s cost? ($)' % item))
day = input('When did you buy %s? (mm/dd/yyyy): ' % item)df = pd.DataFrame({'Item':[item],'Cost':[cost],'Day':[day]})
writer = ExcelWriter('Budget.xlsx')
df.to_excel(writer,'Sheet1',index=False)
writer.save()

Data storage

I need to figure out a way to store this data so it can be displayed somehow. I might also want to perform operations on the data, which is a sign to create an Expense class. The class will store all the data in a list for now and when we need to add operations to our data we can add them as methods to the Expense class.

To keep our code clean I’ll create the class in another file and import it to the budget app.

class Expense:
    item = []
    cost = []
    date = []expense = Expense()

This creates the Expense class and each attribute is an empty list. Then in the budget.py file at the top I imported this file so I can use the class.

from expense_class.py import Expense

The user will be prompted for the item,cost and date. This information will be stored in the expense class and then pushed into the dataframe.

item = input('Item/object you spent your money on: ')
cost = int(input('How much did %s cost? ($): ' % item))
day = input('When did you buy %s? (mm/dd/yyyy): ' % item)
expense = Expense(item,cost,day)df2 = pd.DataFrame({'Item':[expense.item],'Cost':[expense.cost],'Day':[expense.date]})
df = df.append(df2,ignore_index=True, sort = True)

Progress

So far we have an app that stores information about items, their cost and the date of purchase to an excel file. Now I want to clean up the code and start wrapping some of these statements into functions. Then I’ll add some authentication for the user input to ensure they’re entering an integer for the cost and the proper date format.

The first function is read_expense(), which tries to read if there is an excel file containing budget information and creates a data frame with it. If not, it’ll create the data frame.

def read_expense():
    try:
        global df
        df = pd.read_excel('Budget.xlsx')
        print('Welcome back '  + df['Name'][0])
        print('Reading your budget')
        time.sleep(1)
        return df
    except FileNotFoundError:
        print('No expense data found. Let\'s create some.')
        name = input('What is your name?')
        df = pd.DataFrame({'Name':[name],'Item':['NaN'],'Cost':['NaN'],'Day':['NaN']})
        return df

Cleaning up user input

I want the user to be able to have the ability to input more than one item instead of the script cancelling after the first item. I’ll wrap some of the code in an add_expense() function in a while loop.

def add_expense(dataframe):
    item = input('Item/object you spent your money on: ')
    cost = int(input('How much did %s cost? ($): ' % item))
    day = input('When did you buy %s? (mm/dd/yyyy): ' % item)
    expense = Expense(item,cost,day)
    df2 = pd.DataFrame({'Item':[expense.item],'Cost':[expense.cost],'Day':[expense.date]})
    df = df.append(df2,ignore_index=True, sort = True)
    writer = ExcelWriter('Budget.xlsx')
    df.to_excel(writer,'Sheet1',index=False)
    writer.save()

This will be the code that adds to the expenses and updates the excel file. Now I want to wrap this in a while loop and add some logic to give the user fluidity in what they want to do with their budget information.

read_expense()
adding = True
while adding:
    choice = input('What do you want to do with your budget information? (Add item, Delete item, Check spending)')
    if choice.lower()[0] == 'a':
        add_expense(df)
    elif choice.lower()[0] == 'e':
        adding = False

I set an infinite while loop with a user input to decide what the app will do. The choice.lower()[0] will convert the string to lowercase and return the first element which should be ‘a’ if they want to add an item. I can do the same for checking spending and adding an option to end the script which would look like:

adding = True
while adding:
    choice = input('What do you want to do with your budget information? (Add item, Delete item, Check spending, End): ')
    if choice.lower()[0] == 'a':
        add_expense()
    elif choice.lower()[0] == 'd':
        delete_expense()
    elif choice.lower()[0] == 'e':
        adding = False
    elif choice.lower()[0] == 'c':
        show_expenses()

Pandas DataFrame Indexing

I’ve been creating this script as I learned more about Pandas and I’ve just discovered indexing of the Pandas DataFrame. I think a better and cleaner way to lay out the information would to have one of the columns as the index, either the cost or date. For now I’ll just put the cost column as the index. The pd.DataFrame method takes in an argument to define the index which looks like:

df2 = pd.DataFrame({'Cost':[expense.cost],'Day':[expense.date]}, index = [expense.item])

I would also have to update the read_expense() function to:

def read_expense():
    try:
        global df
        df = pd.read_excel('Budget.xlsx',index_col=0)
        print('Reading your budget')
        time.sleep(1)
    except FileNotFoundError:
        print('No expense data found. Let\'s create some.')
        name = input('What is your name? ')
        df = pd.DataFrame({'Cost':[],'Day':[]}, index = [])
        return df

This is a script that will run in the console window (for now) so let’s see what that looks like.

The script seems to work for now, it creates the budget excel file if no file is currently found. I’m able to add expenses and the while loop works fine. Let’s check the excel file to ensure it was saved properly.

This unit test seems to be working great.

Now it would be better to see an updated list of the expenses after I add each item. I don’t have a function for checking the spending yet so lets add that.

Show_expense()

I’ll add a show expense function to display the dataframe which is fairly simple.

def show_expenses():
    print('---------------------------')
    print(df)
    print('---------------------------')

I added the dashes to make it look neater in the console window. I can add this to the add_expense() function to also print the dataframe after each added expense. Let’s check it out in the console window.

The check function works and it looks decent. I think adding some basic statistics to this function might be useful. I’ll convert the cost column to a NumPy array and perform the .sum() aggregate method to calculate the total money spent.

def show_expenses():
    print('---------------------------')
    print(df)
    print('---------------------------')
    total_money = np.array(df['Cost']).sum()
    print('Stats:')
    print('You spent a total of %s dollars this week' % total_money)
    print('---------------------------')

Now let’s add a function to delete expenses then check the script as a whole.

Delete_expense()

Pandas has a method that can remove items based on the index which is useful in this case because the index are the expense items.

def delete_expense():
    global df
    print(df)
    item_delete =  input('What item do you want to remove? ')
    df = df.drop(item_delete, axis=0)
    print('---------------------------')
    print(df)
    print('---------------------------')
    save_excel(df)

The axis=0 argument removes the entire row that contains the index, item_delete. We have everything for our script to run, now let’s test it out.

Final test

We can successfully add multiple items and I like how it’s displayed in the console.

The check function displays the DataFrame along with the sum of the costs.

The delete function successfully removes the item from the data frame and returns the updated expenses.

Moving forward

The script is far from perfect, I plan to connect this to a database when I learn SQL and maybe I’ll make a GUI or front-end for this to make it more appealing to other users.

The dates don’t sort properly and I’ll want to add more statistics to the show_expenses() function too. I want to allow the user to sort the data frame and delete expenses through other means like cost or date.

In week 4 I’ll be learning Matplotlib and I’ll take those methods of showing plots and apply them to this script to allow the user to visualize their spending.