Expense Tracking Application Using Python Tkinter

Recently we got the requirement to create a desktop application for tracking expenses on an individual level. So we have created this expense tracking application using python tkinter with sqlite3 database.

SQLite3 is free database software that comes built-in with python. The below command shows to import the SQLite3 module:

import sqlite3

Expense Tracking Application Using Python

In this section, we have explained the creation of the Interface of the application using python tkinter. The entire code is divided into 12 parts and for each part, we have mentioned the description in the bold comment.

1. Import Modules

The first step in the creation of the application is importing the necessary modules. Here we have imported all the modules available in tkinter (*). TTK is the new member of the tkinter family that needs to be imported separately in the end we have imported a message box module to display the prompt.

2. Create Object, title, and window size

The next step is to create an object for the tkinter module. In the code, ws is the name assigned to the object. Using this object name we have further assigned a title and size to the python tkinter window.

Learn more about Python Tkinter Title

3. Infinite Loop

The mainloop used in the last line of the code holds the screen for a user. The user can see and interact with the window because of the mainloop. In the backend, a window appears and disappears immediately in microseconds. Mainloop repeats this process unlimited time due to which a new screen appears with the updated changes.

4. Global Variables

We have named them global variables because we will be using these variables in different parts of the program. We were aware of putting the same font for all the widgets in a program that is why value with font name and size is stored in a simple letter. And later used this letter to assign a font to an application text.

amtvar = IntVar()
dopvar = StringVar()

These two are the text variables one with the integer data type assigned to store the purchase amount and the other one is a string assigned to record the date of purchase.

5. Frame Widgets

The frame widget in python tkinter is used to hold other widgets on them and it becomes convenient to move a group of widgets simply by moving the container widget (frame).

Two frames are created with the names f1 and f2 both are positioned using pack geometry manager. The f1 frame holds all the widgets except the Treeview and scrollbar widget in python tkinter. These two widgets are placed on the f2 frame.

6. Label Widgets, 7. Entry Widget, 8 entry grid placement

The Label widget in python tkinter is used to display the text on the application window. Item name, item price, and purchase date are the labels used in this application.

The Entry widget in python tkinter allows user to provide their input by typing in a rectangular box. We have used grid geometry manager to position all the widgets inside the f1 frame widget.

9. Action Buttons, 10 Button grid placement

Action buttons are used in the application window that will perform or trigger a function every time they are clicked on the application window. So far we have created 6 types of buttons on the application.

  • Save Record
  • Clear Entry
  • Exit
  • Total Balance
  • Update
  • Delete

11. Treeview to view the record

Treeview is used to display the output of the record in an organized tabular format. The Treeview creation has three parts. First, we have created a Treeview and then added a header or columns to it after using the style we have improved its look and feel.

12. Scrollbar widget

The vertical scrollbar is added to the Treeview widget so that if data exceeds the size of the screen, the user can still view it by scrolling up and down directions.

#1 import modules
from tkinter import *
from tkinter import ttk
from tkinter import messagebox


#2 create object, title and window size
ws = Tk()
ws.title('Office Expense')
# ws.geometry('600x400')

#4 global variables
f = ('Times new roman', 14)
amtvar = IntVar()
dopvar = StringVar()

#5 frame widgets
f2 = Frame(ws)
f2.pack() 

f1 = Frame(
    ws,
    padx=10,
    pady=10,
)
f1.pack(expand=True, fill=BOTH)


#6 Label Widget
Label(f1, text='ITEM NAME', font=f).grid(row=0, column=0, sticky=W)
Label(f1, text='ITEM PRICE', font=f).grid(row=1, column=0, sticky=W)
Label(f1, text='PURCHASE DATE', font=f).grid(row=2, column=0, sticky=W)

#7 Entry Widgets 
item_name = Entry(f1, font=f)
item_amt = Entry(f1, font=f, textvariable=amtvar)
transaction_date = Entry(f1, font=f, textvariable=dopvar)


#8 Entry grid placement
item_name.grid(row=0, column=1, sticky=EW, padx=(10, 0))
item_amt.grid(row=1, column=1, sticky=EW, padx=(10, 0))
transaction_date.grid(row=2, column=1, sticky=EW, padx=(10, 0))


#9 Action buttons
cur_date = Button(
    f1, 
    text='Current Date', 
    font=f, 
    bg='#04C4D9', 
    command=None,
    width=15
    )

submit_btn = Button(
    f1, 
    text='Save Record', 
    font=f, 
    command=None, 
    bg='#42602D', 
    fg='white'
    )

clr_btn = Button(
    f1, 
    text='Clear Entry', 
    font=f, 
    command=None, 
    bg='#D9B036', 
    fg='white'
    )

quit_btn = Button(
    f1, 
    text='Exit', 
    font=f, 
    command=None, 
    bg='#D33532', 
    fg='white'
    )

total_bal = Button(
    f1,
    text='Total Balance',
    font=f,
    command=None
)

total_bal = Button(
    f1,
    text='Total Balance',
    font=f,
    bg='#486966',
    fg='white',
    command=None
)

total_spent = Button(
    f1,
    text='Total Spent',
    font=f,
    fg='white',
    command=None
)

update_btn = Button(
    f1, 
    text='Update',
    bg='#C2BB00',
    command=None,
    fg='white',
    font=f
)

del_btn = Button(
    f1, 
    text='Delete',
    bg='#BD2A2E',
    fg='white',
    command=None,
    font=f
)

#10 Button grid placement
cur_date.grid(row=3, column=1, sticky=EW, padx=(10, 0))
submit_btn.grid(row=0, column=2, sticky=EW, padx=(10, 0))
clr_btn.grid(row=1, column=2, sticky=EW, padx=(10, 0))
quit_btn.grid(row=2, column=2, sticky=EW, padx=(10, 0))
total_bal.grid(row=0, column=3, sticky=EW, padx=(10, 0))
update_btn.grid(row=1, column=3, sticky=EW, padx=(10, 0))
del_btn.grid(row=2, column=3, sticky=EW, padx=(10, 0))

#11 treeview to view the record
tv = ttk.Treeview(f2, selectmode='browse', columns=(1, 2, 3, 4), show='headings', height=8, )
tv.pack(side="left")

tv.column(1, anchor=CENTER, stretch=NO, width=70)
tv.column(2, anchor=CENTER)
tv.column(3, anchor=CENTER)
tv.column(4, anchor=CENTER)
tv.heading(1, text="Serial no")
tv.heading(2, text="Item Name", )
tv.heading(3, text="Item Price")
tv.heading(4, text="Purchase Date")

style = ttk.Style()
style.theme_use("default")
style.map("Treeview")

#12 scrollbar widget
scrollbar = Scrollbar(f2, orient='vertical')
scrollbar.configure(command=tv.yview)
scrollbar.pack(side="right", fill="y")
tv.config(yscrollcommand=scrollbar.set)


#3 infinite loop
ws.mainloop()

Also, check: Python Tkinter Canvas Tutorial

Backend Explanation of the Application

In this section, we will explain to you all the source code related to a database in the expense manager application created using python tkinter and sqlite3 database.

We have created a class with the necessary methods to perform CRUD operations and later this class is used as and when in the program to view, insert, update and delete the data from the sqlite3 database using python tkinter.

1 Module

The SQLite3 module has been imported to perform all the activities related to the database.

2. Class, Constructor, and Methods

We have created a class with the name Database and this class has methods to perform CRUD operations. the code after def __init__(self, db) is a constructor which accepts a database name as a parameter and also it creates a table with the name expense_record if not exist already.

  • The fetchRecord() method allows users to write a SQL query to fetch the information.
  • The insertRecord() method adds data inside the database.
  • The removeRecord() method deletes the existing data from the database.
  • The updateRecord() method allows updating the changes in the database.

We have created this as a separate module with the name mydb.py so the user will import this module into the main program as shown in the next section.

#1 module
import sqlite3

#2 class, constructor and methods 
class Database:
    def __init__(self, db):
        self.conn = sqlite3.connect(db)
        self.cur = self.conn.cursor()
        self.cur.execute(
            "CREATE TABLE IF NOT EXISTS expense_record (item_name text, item_price float, purchase_date date)")
        self.conn.commit()

    def fetchRecord(self, query):
        self.cur.execute(query)
        rows = self.cur.fetchall()
        return rows

    def insertRecord(self, item_name, item_price, purchase_date):
        self.cur.execute("INSERT INTO expense_record VALUES (?, ?, ?)",
                         (item_name, item_price, purchase_date))
        self.conn.commit()

    def removeRecord(self, rwid):
        self.cur.execute("DELETE FROM expense_record WHERE rowid=?", (rwid,))
        self.conn.commit()

    def updateRecord(self, item_name, item_price, purchase_date, rid):
        self.cur.execute("UPDATE expense_record SET item_name = ?, item_price = ?, purchase_date = ? WHERE rowid = ?",
                         (item_name, item_price, purchase_date, rid))
        self.conn.commit()

    def __del__(self):
        self.conn.close()

Read: Python Tkinter Progress bar widget

Function and Other Operations Explanation

In this section, we will discuss the part of source code other than GUI and database. We will discuss the following thing:

1. Module

In the previous section, we have created a module with the name mydb.py, here have imported that module and all of its functions. Using the methods inside it we can Insert, Update, View, and Delete the data from the database in python tkinter.

2. Object for database

We have created an object for the class Database and we have named the object data. Also, this object accepts a parameter that will be the name of the database. We have provided myexpense.db, but you provide any name with the extension db.

Please note that you load the existing database by providing its name at this place but that database must have the same columns.

3. Global Variable

Variables created inside a function have scope within that function only. Global variables can be accessed through the program and their values are affected by changes made at any point.

  • The count=0 is used later in the program while inserting data in the Treeview. There we have incremented the value for the count so that iid can have a unique value each time.
  • The SQLite3 has a feature of automatically maintaining the unique row id with the name rowid that needs to be mentioned while fetching the data from the database
select rowid, * from expense_record;

This rowid is the one displayed as a serial number in the GUI interface of the application. We have stored the serial number of the row selected by the user on the interface in the global variable selected_rowid.

Now, since we have a rowid of the row that is selected by the user now we have put this in a where clause of Update and Delete functions.

4. Functions

Let’s discuss the functions used in this application. Please note that these functions are other than the database class functions. These are mainly focused on using those methods.

The saveRecord() is using insertRecord() method of the Database class to store the data in the database. We have passed the required parameter being fetched from the entry widget. This function is triggered by the submit_btn Button in python tkinter.

  • The setDate() function uses date from the system and sets it on the entry widget in a day, month, and year format.
  • The clearEntries() function clears all the entry widget.It uses the delete method and provides start and end values to remove it. This function is triggered by a clr_btn button.
  • The fetch_records() function retrieves rowid and other information from the database using the fetchRecord() method from the Database class. After fetching data it publishes the data on the Treeview widget in a tabular format.
  • The select_record() function is related to the selection of rows on the treeview. Every time a user clicks on any of the rows this function is triggered as it is binded with the mouse click. This function is responsible for storing the rowid in the global variable.
    This method prints the selected row on the entry widgets which further can be updated.
  • The update_record() function updates the existing information in the database.It uses updaRecord() method from the Database class.
  • The total_balance() function displays the popup message with the total spent amount and the remaining amount. By default have provided 5000 as a base amount you change it as per your budget.
  • The refreshData() is another important method as this allows users to view the update in real-time. While inserting, updating, and deleting data we have to perform this refresh operation at the end so that changes may appear in real-time.
    If we won’t use this function we have to restart the application each time to view the changes.
  • The deleteRow() function deletes the selected row from the database and the refresh function immediately shows the changes. So this way suppose the user selected a row and then clicked on the delete button.
    This function will remove the entry from the database and since the entry is removed from the database the row will disappear after an automatic refresh.
#1. module
from mydb import *

#2. object for database
data = Database(db='myexpense.db')

#3. global variables
count = 0
selected_rowid = 0

#4. functions
def saveRecord():
    global data
    data.insertRecord(item_name=item_name.get(), item_price=item_amt.get(), purchase_date=transaction_date.get())
       
def setDate():
    date = dt.datetime.now()
    dopvar.set(f'{date:%d %B %Y}')

def clearEntries():
    item_name.delete(0, 'end')
    item_amt.delete(0, 'end')
    transaction_date.delete(0, 'end')

def fetch_records():
    f = data.fetchRecord('select rowid, * from expense_record')
    global count
    for rec in f:
        tv.insert(parent='', index='0', iid=count, values=(rec[0], rec[1], rec[2], rec[3]))
        count += 1
    tv.after(400, refreshData)

def select_record(event):
    global selected_rowid
    selected = tv.focus()    
    val = tv.item(selected, 'values')
  
    try:
        selected_rowid = val[0]
        d = val[3]
        namevar.set(val[1])
        amtvar.set(val[2])
        dopvar.set(str(d))
    except Exception as ep:
        pass


def update_record():
    global selected_rowid

    selected = tv.focus()
	# Update record
    try:
        data.updateRecord(namevar.get(), amtvar.get(), dopvar.get(), selected_rowid)
        tv.item(selected, text="", values=(namevar.get(), amtvar.get(), dopvar.get()))
    except Exception as ep:
        messagebox.showerror('Error',  ep)

	# Clear entry boxes
    item_name.delete(0, END)
    item_amt.delete(0, END)
    transaction_date.delete(0, END)
    tv.after(400, refreshData)
    

def totalBalance():
    f = data.fetchRecord(query="Select sum(item_price) from expense_record")
    for i in f:
        for j in i:
            messagebox.showinfo('Current Balance: ', f"Total Expense: ' {j} \nBalance Remaining: {5000 - j}")

def refreshData():
    for item in tv.get_children():
      tv.delete(item)
    fetch_records()
    
def deleteRow():
    global selected_rowid
    data.removeRecord(selected_rowid)
    refreshData()

Read: Python Tkinter Stopwatch

Complete Source Code for main.py

The below source code is for the application created using python tkinter. Save this file with the name main.py and execute it to run this complete application.

# import modules 
from tkinter import *
from tkinter import ttk
import datetime as dt
from mydb import *
from tkinter import messagebox

# object for database
data = Database(db='test.db')

# global variables
count = 0
selected_rowid = 0

# functions
def saveRecord():
    global data
    data.insertRecord(item_name=item_name.get(), item_price=item_amt.get(), purchase_date=transaction_date.get())
       
def setDate():
    date = dt.datetime.now()
    dopvar.set(f'{date:%d %B %Y}')

def clearEntries():
    item_name.delete(0, 'end')
    item_amt.delete(0, 'end')
    transaction_date.delete(0, 'end')

def fetch_records():
    f = data.fetchRecord('select rowid, * from expense_record')
    global count
    for rec in f:
        tv.insert(parent='', index='0', iid=count, values=(rec[0], rec[1], rec[2], rec[3]))
        count += 1
    tv.after(400, refreshData)

def select_record(event):
    global selected_rowid
    selected = tv.focus()    
    val = tv.item(selected, 'values')
  
    try:
        selected_rowid = val[0]
        d = val[3]
        namevar.set(val[1])
        amtvar.set(val[2])
        dopvar.set(str(d))
    except Exception as ep:
        pass


def update_record():
    global selected_rowid

    selected = tv.focus()
	# Update record
    try:
        data.updateRecord(namevar.get(), amtvar.get(), dopvar.get(), selected_rowid)
        tv.item(selected, text="", values=(namevar.get(), amtvar.get(), dopvar.get()))
    except Exception as ep:
        messagebox.showerror('Error',  ep)

	# Clear entry boxes
    item_name.delete(0, END)
    item_amt.delete(0, END)
    transaction_date.delete(0, END)
    tv.after(400, refreshData)
    

def totalBalance():
    f = data.fetchRecord(query="Select sum(item_price) from expense_record")
    for i in f:
        for j in i:
            messagebox.showinfo('Current Balance: ', f"Total Expense: ' {j} \nBalance Remaining: {5000 - j}")

def refreshData():
    for item in tv.get_children():
      tv.delete(item)
    fetch_records()
    
def deleteRow():
    global selected_rowid
    data.removeRecord(selected_rowid)
    refreshData()

# create tkinter object
ws = Tk()
ws.title('Daily Expenses')

# variables
f = ('Times new roman', 14)
namevar = StringVar()
amtvar = IntVar()
dopvar = StringVar()

# Frame widget
f2 = Frame(ws)
f2.pack() 

f1 = Frame(
    ws,
    padx=10,
    pady=10,
)
f1.pack(expand=True, fill=BOTH)


# Label widget
Label(f1, text='ITEM NAME', font=f).grid(row=0, column=0, sticky=W)
Label(f1, text='ITEM PRICE', font=f).grid(row=1, column=0, sticky=W)
Label(f1, text='PURCHASE DATE', font=f).grid(row=2, column=0, sticky=W)

# Entry widgets 
item_name = Entry(f1, font=f, textvariable=namevar)
item_amt = Entry(f1, font=f, textvariable=amtvar)
transaction_date = Entry(f1, font=f, textvariable=dopvar)

# Entry grid placement
item_name.grid(row=0, column=1, sticky=EW, padx=(10, 0))
item_amt.grid(row=1, column=1, sticky=EW, padx=(10, 0))
transaction_date.grid(row=2, column=1, sticky=EW, padx=(10, 0))


# Action buttons
cur_date = Button(
    f1, 
    text='Current Date', 
    font=f, 
    bg='#04C4D9', 
    command=setDate,
    width=15
    )

submit_btn = Button(
    f1, 
    text='Save Record', 
    font=f, 
    command=saveRecord, 
    bg='#42602D', 
    fg='white'
    )

clr_btn = Button(
    f1, 
    text='Clear Entry', 
    font=f, 
    command=clearEntries, 
    bg='#D9B036', 
    fg='white'
    )

quit_btn = Button(
    f1, 
    text='Exit', 
    font=f, 
    command=lambda:ws.destroy(), 
    bg='#D33532', 
    fg='white'
    )

total_bal = Button(
    f1,
    text='Total Balance',
    font=f,
    bg='#486966',
    command=totalBalance
)

total_spent = Button(
    f1,
    text='Total Spent',
    font=f,
    command=lambda:data.fetchRecord('select sum(ite)')
)

update_btn = Button(
    f1, 
    text='Update',
    bg='#C2BB00',
    command=update_record,
    font=f
)

del_btn = Button(
    f1, 
    text='Delete',
    bg='#BD2A2E',
    command=deleteRow,
    font=f
)

# grid placement
cur_date.grid(row=3, column=1, sticky=EW, padx=(10, 0))
submit_btn.grid(row=0, column=2, sticky=EW, padx=(10, 0))
clr_btn.grid(row=1, column=2, sticky=EW, padx=(10, 0))
quit_btn.grid(row=2, column=2, sticky=EW, padx=(10, 0))
total_bal.grid(row=0, column=3, sticky=EW, padx=(10, 0))
update_btn.grid(row=1, column=3, sticky=EW, padx=(10, 0))
del_btn.grid(row=2, column=3, sticky=EW, padx=(10, 0))

# Treeview widget
tv = ttk.Treeview(f2, columns=(1, 2, 3, 4), show='headings', height=8)
tv.pack(side="left")

# add heading to treeview
tv.column(1, anchor=CENTER, stretch=NO, width=70)
tv.column(2, anchor=CENTER)
tv.column(3, anchor=CENTER)
tv.column(4, anchor=CENTER)
tv.heading(1, text="Serial no")
tv.heading(2, text="Item Name", )
tv.heading(3, text="Item Price")
tv.heading(4, text="Purchase Date")

# binding treeview
tv.bind("<ButtonRelease-1>", select_record)

# style for treeview
style = ttk.Style()
style.theme_use("default")
style.map("Treeview")

# Vertical scrollbar
scrollbar = Scrollbar(f2, orient='vertical')
scrollbar.configure(command=tv.yview)
scrollbar.pack(side="right", fill="y")
tv.config(yscrollcommand=scrollbar.set)

# calling function 
fetch_records()

# infinite loop
ws.mainloop()

The below output shows the interface of the Expense management applications. You can provide the purchase item name, and item price and then click on the current date button to fetch the current date.

The date can be changed if required and then click on the save record button to store the data in the database and the respective data will be displayed on the Treeview widget on the screen.

The clear button will clean the entry widgets and the exit will close the application. The total balance button will display the total spent amount and the remaining amount that is fed into the program.

Click on any row and the data will start appearing in the respective entry widgets. Make changes in the data and click on the update button and the record will be updated accordingly if you want to delete the row then click on the delete button.

expense management system using python tkinter
Expense Tracking Application Using Python Tkinter Example

Read: How to make a calculator in Python

Complete Source code Database mydb.py

The below source code is for creating a database in sqlite3 using python tkinter. Save the file with the name mydb.py in the same directory where the main program is saved.

import sqlite3

class Database:
    def __init__(self, db):
        self.conn = sqlite3.connect(db)
        self.cur = self.conn.cursor()
        self.cur.execute(
            "CREATE TABLE IF NOT EXISTS expense_record (item_name text, item_price float, purchase_date date)")
        self.conn.commit()

    def fetchRecord(self, query):
        self.cur.execute(query)
        rows = self.cur.fetchall()
        return rows

    def insertRecord(self, item_name, item_price, purchase_date):
        self.cur.execute("INSERT INTO expense_record VALUES (?, ?, ?)",
                         (item_name, item_price, purchase_date))
        self.conn.commit()

    def removeRecord(self, rwid):
        self.cur.execute("DELETE FROM expense_record WHERE rowid=?", (rwid,))
        self.conn.commit()

    def updateRecord(self, item_name, item_price, purchase_date, rid):
        self.cur.execute("UPDATE expense_record SET item_name = ?, item_price = ?, purchase_date = ? WHERE rowid = ?",
                         (item_name, item_price, purchase_date, rid))
        self.conn.commit()

    def __del__(self):
        self.conn.close()

The below image shows a representation of data on the SQLite3 database. We are viewing this data using DB Browser (SQLite) software.

python tkinter expense manager database
Expense Tracking Application Using Python Tkinter

Also, take a look at some more Python Tkinter tutorials.

So, in this Python tutorial, we understood how to build an Expense Tracking Application Using Python Tkinter. Here we have each step with the help of an example in Python.