My Project 5: Building an Expense Tracker (with Python + SQLite + Streamlit)

💰 Building an Expense Tracker with Python + SQLite + Streamlit

---

This project is a bit more challenging than the previous ones. I struggled quite a lot while building it, but I kept going because I believe nothing truly sticks unless I work through the difficult parts myself.

I'm still on my journey to becoming a developer, but this is something I've learned: The beginning is always unfamiliar and difficult, but with repetition, things slowly become natural. If you feel overwhelmed right now, maybe that feeling itself is proof that you're learning something new. If it never feels overwhelming, perhaps it means you're only doing what you're already comfortable with.

I also couldn't upload anything recently because of my university finals, but now that everything is done, I'll be posting regularly again. My posts may be small and insignificant, but I'm genuinely grateful to everyone who stops by and reads them. Let's keep going and grow into great developers together. 🚀

---

This project is a complete expense management tool built with Python and SQLite. It includes a console version and a fully interactive Streamlit web version.

Through this project, I practiced:

• CRUD operations with SQLite
• Select, Insert, Delete queries
• Data aggregation (monthly & category summaries)
• UI building with Streamlit
• Displaying data using DataFrames & charts

---

📂 Project Structure

expense_tracker/
│
├── expense_app.py             # Console version
├── expense_app_streamlit.py   # Streamlit version
├── expenses.db                # SQLite DB (auto-created)
└── README.md                  # (optional)

---

🧱 1. Console Version (expense_app.py)

Features:

• Add expenses
• Show all expenses
• Monthly summary
• Category summary
• Delete by ID

📌 Code

import sqlite3
from datetime import datetime

# Add new expense
def add_expense():
    amount = float(input("Amount (CHF): "))
    category = input("Category (Food, Transport, Baby, etc): ")
    note = input("Note: ")
    date = datetime.now().strftime("%Y-%m-%d")

    conn = sqlite3.connect("expenses.db")
    cur = conn.cursor()

    cur.execute(
        "INSERT INTO expenses (amount, category, note, date) VALUES (?, ?, ?, ?)",
        (amount, category, note, date),
    )

    conn.commit()
    conn.close()

    print("Expense saved!")

# Show all expenses
def show_expenses():
    conn = sqlite3.connect("expenses.db")
    cur = conn.cursor()

    cur.execute("SELECT id, amount, category, note, date FROM expenses ORDER BY date DESC")
    rows = cur.fetchall()

    conn.close()

    print("\n=== Expense List ===")
    if not rows:
        print("No expenses recorded.")
        return

    for r in rows:
        print(f"{r[0]}. {r[4]} | {r[1]} CHF | {r[2]} | {r[3]}")

# Delete an expense
def delete_expense():
    show_expenses()
    delete_id = input("Enter ID to delete: ")

    conn = sqlite3.connect("expenses.db")
    cur = conn.cursor()

    cur.execute("DELETE FROM expenses WHERE id = ?", (delete_id,))
    conn.commit()
    conn.close()

    print("Deleted!")

# Monthly summary
def show_monthly_summary():
    conn = sqlite3.connect("expenses.db")
    cur = conn.cursor()

    cur.execute("""
        SELECT SUBSTR(date, 1, 7) AS month, SUM(amount)
        FROM expenses
        GROUP BY month
        ORDER BY month DESC
    """)

    rows = cur.fetchall()
    conn.close()

    print("\n=== Monthly Summary ===")
    if not rows:
        print("No data.")
        return

    for r in rows:
        print(f"{r[0]} : {r[1]} CHF")

# Category summary
def show_category_summary():
    conn = sqlite3.connect("expenses.db")
    cur = conn.cursor()

    cur.execute("""
        SELECT category, SUM(amount)
        FROM expenses
        GROUP BY category
        ORDER BY SUM(amount) DESC
    """)

    rows = cur.fetchall()
    conn.close()

    print("\n=== Category Summary ===")
    if not rows:
        print("No data.")
        return

    for r in rows:
        print(f"{r[0]} : {r[1]} CHF")

# Main menu
def main():
    print("=== Expense Tracker ===")
    print("1) Add Expense")
    print("2) Show Expenses")
    print("3) Monthly Summary")
    print("4) Category Summary")
    print("5) Delete Expense")
    print("6) Exit")

    choice = input("Select option: ")

    if choice == "1":
        add_expense()
    elif choice == "2":
        show_expenses()
    elif choice == "3":
        show_monthly_summary()
    elif choice == "4":
        show_category_summary()
    elif choice == "5":
        delete_expense()
    else:
        print("Goodbye!")

if __name__ == "__main__":
    main()

---

🌤️ 2. Streamlit Version (expense_app_streamlit.py)

Added features:

• Side menu navigation
• Pandas DataFrames for clean display
• Auto-generated bar charts
• Record deletion with preview
• Instant UI updates

📌 Code

import streamlit as st
import sqlite3
from datetime import datetime
import pandas as pd

DB = "expenses.db"

# Load data from database
def get_data():
    conn = sqlite3.connect(DB)
    df = pd.read_sql_query("SELECT * FROM expenses ORDER BY date DESC", conn)
    conn.close()
    return df

# Add new expense
def add_expense(amount, category, note):
    conn = sqlite3.connect(DB)
    cur = conn.cursor()
    date = datetime.now().strftime("%Y-%m-%d")
    cur.execute(
        "INSERT INTO expenses (amount, category, note, date) VALUES (?, ?, ?, ?)",
        (amount, category, note, date),
    )
    conn.commit()
    conn.close()

st.title("💰 Sabin's Expense Tracker")

menu = st.sidebar.radio("Menu", [
    "Add Expense",
    "Show Expenses",
    "Monthly Summary",
    "Category Summary",
    "Delete Expense"
])

# Monthly summary from DB
def get_monthly_summary():
    conn = sqlite3.connect(DB)
    df = pd.read_sql_query("""
        SELECT SUBSTR(date, 1, 7) AS month, SUM(amount) AS total
        FROM expenses
        GROUP BY month
        ORDER BY month DESC
    """, conn)
    conn.close()
    return df

# Category summary from DB
def get_category_summary():
    conn = sqlite3.connect(DB)
    df = pd.read_sql_query("""
        SELECT category, SUM(amount) AS total
        FROM expenses
        GROUP BY category
        ORDER BY total DESC
    """, conn)
    conn.close()
    return df

# Add Expense Page
if menu == "Add Expense":
    st.subheader("Add New Expense")

    amount = st.number_input("Amount (CHF)", min_value=0.0, format="%.2f")
    category = st.text_input("Category")
    note = st.text_area("Note")

    if st.button("Save"):
        if amount == 0 or category.strip() == "":
            st.warning("Amount and category required.")
        else:
            add_expense(amount, category, note)
            st.success("Expense saved!")

# Show Expenses Page
elif menu == "Show Expenses":
    st.subheader("Expense List")
    df = get_data()

    if df.empty:
        st.info("No expenses found.")
    else:
        st.dataframe(df)

# Monthly Summary Page
elif menu == "Monthly Summary":
    st.subheader("📅 Monthly Summary")
    df = get_monthly_summary()

    if df.empty:
        st.info("No data.")
    else:
        st.dataframe(df)
        st.bar_chart(df.set_index("month")["total"])

# Category Summary Page
elif menu == "Category Summary":
    st.subheader("📊 Category Summary")
    df = get_category_summary()

    if df.empty:
        st.info("No data.")
    else:
        st.dataframe(df)
        st.bar_chart(df.set_index("category")["total"])

# Delete Expense Page
elif menu == "Delete Expense":
    st.subheader("🗑 Delete Expense")

    df = get_data()

    if df.empty:
        st.info("No expenses to delete.")
    else:
        delete_id = st.selectbox("Select Expense ID to delete:", df["id"])
        selected_row = df[df["id"] == delete_id].iloc[0]

        st.write(f"Amount: {selected_row['amount']} CHF")
        st.write(f"Category: {selected_row['category']}")
        st.write(f"Note: {selected_row['note']}")
        st.write(f"Date: {selected_row['date']}")

        if st.button("Delete"):
            conn = sqlite3.connect(DB)
            cur = conn.cursor()
            cur.execute("DELETE FROM expenses WHERE id = ?", (delete_id,))
            conn.commit()
            conn.close()
            st.success("Expense deleted!")

---

📚 3. What I Learned

• Creating a local database with SQLite
• Reading & writing structured data
• Grouping and aggregating values (SQL)
• Streamlit layouts, sidebar, charts
• Working with Pandas for data display

---

🔧 4. Future Improvements

• Filter by date range
• Multiple currencies
• Export to CSV / Excel
• Add login system
• Pie charts for category spending