💰 Smart Expense Tracker with Insights

MCA Academic Project | Django + PostgreSQL + Chart.js

---

📋 Table of Contents

1. Project Overview
2. Tech Stack
3. Features
4. Project Structure
5. Setup Instructions
6. Database Design & SQL Explained
7. How Insights Are Generated
8. Sample Test Data
9. UI Screens Description
10. API Endpoints

---

Project Overview

ExpenseIQ is a full-stack web application that helps users track their daily expenses, visualize spending patterns, and receive smart, rule-based financial insights.

Academic Purpose: Demonstrates practical use of Django ORM, relational databases, CRUD operations, authentication, data aggregation with SQL GROUP BY / SUM, and frontend data visualization.

---

Tech Stack

Layer	Technology	Purpose
Backend	Django 5.0 (Python)	Web framework, ORM, auth, routing
Database	PostgreSQL / MySQL	Relational data storage
Frontend	HTML5 + Bootstrap 5	Responsive UI
Charts	Chart.js 4.4	Pie chart and bar/line chart
Icons	Bootstrap Icons	Navigation and UI icons
Fonts	Google Fonts	Plus Jakarta Sans + DM Mono

---

Features

✅ Core Features

Feature	Description
User Registration	Create account with username, email, password
Login / Logout	Session-based authentication via Django's auth system
Add Expense	Record amount, category, description, date
View Expenses	Paginated list with category + date filters
Edit Expense	Update any field of an existing expense
Delete Expense	Soft confirmation dialog before deletion
Monthly Summary	Total spending displayed on dashboard

🚀 Advanced Features

Feature	Description
Smart Categorization	Keyword-based auto-detection of category from description
Insights Dashboard	Rule-based messages about spending patterns
Budget Alert System	Warning when spending exceeds 80% of monthly limit
CSV Export	Download expense report as .csv file
Category Pie Chart	Visual breakdown of spending by category
Monthly Trend Chart	Bar chart showing last 6 months of spending
AJAX Auto-Suggest	Live category suggestion while typing description

---

Project Structure

smart_expense_tracker/
│
├── manage.py                          # Django CLI utility
├── requirements.txt                   # Python dependencies
├── README.md                          # This file
│
├── expense_tracker/                   # Django project config
│   ├── __init__.py
│   ├── settings.py                    # Database, apps, auth config
│   ├── urls.py                        # Root URL routing
│   └── wsgi.py                        # WSGI entry point
│
├── expenses/                          # Main Django app
│   ├── __init__.py
│   ├── models.py                      # Expense + Budget database models
│   ├── views.py                       # All view logic (auth, CRUD, dashboard)
│   ├── urls.py                        # App-level URL patterns
│   ├── forms.py                       # Django Form classes
│   ├── admin.py                       # Django Admin registration
│   ├── utils.py                       # Smart categorization + insights logic
│   ├── migrations/
│   │   ├── __init__.py
│   │   └── 0001_initial.py            # DB schema migration
│   └── management/
│       └── commands/
│           └── load_sample_data.py    # Custom command: python manage.py load_sample_data
│
├── templates/
│   ├── base.html                      # Master layout (navbar, toasts, CDN links)
│   └── expenses/
│       ├── login.html                 # Login page
│       ├── register.html              # Registration page
│       ├── dashboard.html             # Main analytics dashboard
│       ├── expense_list.html          # Expense list with filters
│       ├── expense_form.html          # Add/Edit expense form
│       ├── expense_confirm_delete.html# Delete confirmation
│       └── set_budget.html            # Monthly budget form
│
└── static/                            # Static assets (CSS/JS if added locally)

---

Setup Instructions

Prerequisites

• Python 3.10+
• PostgreSQL (or MySQL) installed and running
• pip package manager

---

Step 1: Clone / Download the Project

# If using git:
git clone <your-repo-url>
cd smart_expense_tracker

# Or just extract the ZIP and:
cd smart_expense_tracker

Step 2: Create a Virtual Environment

# Create virtual environment
python -m venv venv

# Activate it:
# Windows:
venv\Scripts\activate
# macOS/Linux:
source venv/bin/activate

Step 3: Install Dependencies

pip install -r requirements.txt

Note: If using MySQL instead of PostgreSQL, install mysqlclient instead of psycopg2-binary:

pip install mysqlclient

Step 4: Create the Database

PostgreSQL:

-- In psql terminal:
CREATE DATABASE expense_tracker_db;
CREATE USER postgres WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE expense_tracker_db TO postgres;

MySQL:

-- In MySQL terminal:
CREATE DATABASE expense_tracker_db CHARACTER SET utf8mb4;

Step 5: Configure Database Settings

Open expense_tracker/settings.py and update the DATABASES section:

# For PostgreSQL (default):
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'expense_tracker_db',
        'USER': 'postgres',        # your PostgreSQL username
        'PASSWORD': 'password',    # your PostgreSQL password
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

# For MySQL, uncomment the MySQL block and comment out PostgreSQL.

# For SQLite (easiest for testing — no setup needed):
# DATABASES = {
#     'default': {
#         'ENGINE': 'django.db.backends.sqlite3',
#         'NAME': BASE_DIR / 'db.sqlite3',
#     }
# }

Step 6: Run Migrations

# Creates all database tables from models.py
python manage.py migrate

This runs the SQL CREATE TABLE statements for:

• expenses_expense
• expenses_budget
• All Django built-in tables (users, sessions, etc.)

Step 7: Load Sample Data

# Populates DB with demo user + 30 realistic expenses
python manage.py load_sample_data

Output:

🚀 Loading sample data...
  ✓ Created user: admin / admin123
  ✓ Budget set: ₹15000/month
  ✓ Created 36 sample expenses
==================================================
✅ Sample data loaded successfully!

Step 8: Create Superuser (optional, for /admin/ panel)

python manage.py createsuperuser

Step 9: Run the Development Server

python manage.py runserver

Open your browser and go to: http://127.0.0.1:8000/

Login with:

• Username: admin
• Password: admin123

---

Database Design & SQL Explained

Entity Relationship Diagram

auth_user (Django built-in)
    │ id  PK
    │ username
    │ password
    │ email
    │
    ├──── expenses_expense
    │         id          PK (BIGSERIAL)
    │         user_id     FK → auth_user.id
    │         amount      DECIMAL(10,2)
    │         category    VARCHAR(50)
    │         description TEXT
    │         date        DATE
    │
    └──── expenses_budget
              id            PK (BIGSERIAL)
              user_id       FK → auth_user.id  UNIQUE
              monthly_limit DECIMAL(10,2)

Key SQL Queries Used (via Django ORM)

1. Total Expenses This Month

-- Django ORM: Expense.objects.filter(user=user, date__year=Y, date__month=M).aggregate(Sum('amount'))
SELECT SUM(amount)
FROM expenses_expense
WHERE user_id = 1
  AND EXTRACT(YEAR FROM date)  = 2025
  AND EXTRACT(MONTH FROM date) = 4;

2. Category-wise Breakdown (Pie Chart)

-- Django ORM: .values('category').annotate(total=Sum('amount'))
SELECT category, SUM(amount) AS total
FROM expenses_expense
WHERE user_id = 1
  AND EXTRACT(MONTH FROM date) = 4
GROUP BY category
ORDER BY total DESC;

3. Monthly Trend (Last 6 Months)

-- Django ORM: .values('date__year','date__month').annotate(total=Sum('amount'))
SELECT EXTRACT(YEAR FROM date) AS yr,
       EXTRACT(MONTH FROM date) AS mo,
       SUM(amount) AS total
FROM expenses_expense
WHERE user_id = 1
  AND date >= '2024-10-01'
GROUP BY yr, mo
ORDER BY yr, mo;

4. Budget Check

-- Django ORM: Budget.objects.get_or_create(user=user)
SELECT monthly_limit
FROM expenses_budget
WHERE user_id = 1;

5. Filter Expenses by Category + Date Range

-- Django ORM: .filter(category='Food', date__gte='2025-04-01', date__lte='2025-04-30')
SELECT * FROM expenses_expense
WHERE user_id = 1
  AND category = 'Food'
  AND date BETWEEN '2025-04-01' AND '2025-04-30'
ORDER BY date DESC;

Django ORM → SQL Mapping

Django ORM Method	SQL Equivalent
.filter(user=user)	WHERE user_id = <id>
.aggregate(Sum('amount'))	SELECT SUM(amount)
.values('category')	SELECT category (for GROUP BY)
.annotate(total=Sum())	GROUP BY category with aggregation
.order_by('-date')	ORDER BY date DESC
.get_or_create()	SELECT ... INSERT IF NOT EXISTS
.delete()	DELETE FROM ... WHERE id = <id>

---

How Insights Are Generated

Insights are generated by the generate_insights() function in expenses/utils.py.

This uses simple rule-based logic — no machine learning required.

Rules Applied:

Rule 1: Category Dominance
  IF a single category > 40% of total spending
  → "You spent X% on Food. Consider reviewing..."

Rule 2: Month-over-Month Comparison
  IF current month spending > last month by 20%
  → "Your spending increased by X% compared to last month"
  IF current month spending < last month by 10%
  → "Great! You saved ₹X compared to last month"

Rule 3: Category-specific Tips
  IF Food > 35% of spending → "Try cooking at home..."
  IF Transport > 25%       → "Consider public transport..."
  IF Entertainment > 20%   → "Review your subscriptions..."

Rule 4: Data Quality
  IF only one category used → "Make sure you're logging all spending"

Smart Categorization Algorithm:

smart_categorize("Zomato biryani dinner")
  → lowercase: "zomato biryani dinner"
  → Check Food keywords: ['zomato', 'swiggy', 'lunch', ...]
  → Match found: 'zomato'
  → Return: 'Food'  ✓

The keyword dictionary in utils.py covers 50+ common Indian expense keywords across 9 categories.

---

Sample Test Data

After running python manage.py load_sample_data, the database contains:

Demo User

Field	Value
Username	admin
Password	admin123
Email	admin@expenseiq.com

Sample Expenses (36 records across 3 months)

Category	Example Description	Amount Range
Food	"Swiggy biryani dinner"	₹150 – ₹400
Food	"Grocery from DMart"	₹800 – ₹2500
Transport	"Uber to airport"	₹300 – ₹800
Shopping	"Amazon delivery - headphones"	₹800 – ₹3000
Entertainment	"Netflix subscription"	₹199 – ₹649
Health	"Gym membership monthly"	₹800 – ₹2000
Utilities	"Electricity bill"	₹500 – ₹2000
Education	"Udemy Python course"	₹400 – ₹800
Rent	"Monthly house rent"	₹8000 – ₹15000

Budget

• Monthly limit: ₹15,000

---

UI Screens Description

1. Login Page (/login/)

• Clean centered card with gradient purple background
• Username + password fields with show/hide password toggle
• "Demo credentials" hint box
• Link to registration page

2. Registration Page (/register/)

• First/last name, username, email, password fields
• Form validation with error messages
• Link back to login

3. Dashboard (/dashboard/)

• 4 stat cards (This Month, Last Month, Remaining Budget, Budget Used %)
• Budget Alert banner (shown if > 80% spent)
• Doughnut pie chart — category-wise spending
• Bar chart — 6-month spending trend
• Smart Insights panel — 3-5 rule-based messages
• Category breakdown table with progress bars
• Recent expenses — last 5 transactions

4. Expense List (/expenses/)

• Filter bar (category dropdown, start date, end date)
• Full data table with sortable columns
• Color-coded category badges
• Edit (pencil) and Delete (trash) action buttons
• Running total at the bottom
• Export CSV button

5. Add / Edit Expense (/expenses/add/ or /expenses/<id>/edit/)

• Smart categorization banner
• Amount field with ₹ prefix
• Description textarea (triggers auto-suggest)
• Category dropdown (auto-filled by AJAX)
• Date picker
• "Auto-detected" badge flashes when category is auto-set

6. Budget Page (/budget/)

• Current budget display
• Quick preset buttons (₹5K, ₹10K, ₹15K, ₹20K, ₹30K, ₹50K)
• Custom input for any amount
• 80% warning threshold explained

7. Delete Confirmation (/expenses/<id>/delete/)

• Centered modal-style card
• Shows category, amount, date of expense to be deleted
• Requires POST (not GET) for security

---

API Endpoints

Method	URL	Description
GET	/	Redirect to dashboard
GET	/login/	Login page
POST	/login/	Process login
GET	/register/	Registration page
POST	/register/	Create new user
GET	/logout/	Logout and redirect
GET	/dashboard/	Main analytics dashboard
GET	/expenses/	List all expenses (with filters)
GET	/expenses/add/	Add expense form
POST	/expenses/add/	Save new expense
GET	/expenses/<id>/edit/	Edit expense form
POST	/expenses/<id>/edit/	Update expense
GET	/expenses/<id>/delete/	Delete confirmation
POST	/expenses/<id>/delete/	Confirm and delete expense
GET	/budget/	Set budget form
POST	/budget/	Save budget
GET	/export/csv/	Download CSV file
GET	/api/categorize/?description=	AJAX: returns suggested category (JSON)
GET	/admin/	Django admin panel

---

Common Issues & Solutions

Issue	Solution
psycopg2 install error	Install libpq-dev or use psycopg2-binary
Database connection refused	Check PostgreSQL is running on port 5432
ALLOWED_HOSTS error	Add your server IP/domain to ALLOWED_HOSTS
Static files not loading	Run python manage.py collectstatic
Migration error	Delete migrations/ (except __init__.py), re-run
No module named expenses	Ensure you're in the project root with venv activated

---

Project Author Notes

This project is designed for MCA-level academic submission and demonstrates:

• ✅ Relational Database Design — Foreign keys, normalized schema
• ✅ Django ORM — Abstraction over raw SQL with the same power
• ✅ SQL Aggregation — SUM, GROUP BY, HAVING via Django annotate/aggregate
• ✅ Authentication & Authorization — Session-based, login_required decorator
• ✅ CRUD Operations — Create, Read, Update, Delete with form validation
• ✅ RESTful-style routing — Clean URL patterns
• ✅ AJAX/JSON API — Asynchronous requests for smart features
• ✅ Data Visualization — Chart.js integration with backend data
• ✅ Business Logic — Rule-based insights without ML complexity
• ✅ Export Functionality — CSV generation with Python's built-in csv module
• ✅ Responsive UI — Bootstrap 5 grid and components