Expense Tracker Python Project.txt

import sqlite3
import pandas as pd
from datetime import datetime

# --- Database setup ---
conn = sqlite3.connect('expenses.db')
cur = conn.cursor()

cur.execute('''
CREATE TABLE IF NOT EXISTS transactions (
    id INTEGER PRIMARY KEY,
    date TEXT NOT NULL,
    category TEXT NOT NULL,
    amount REAL NOT NULL,
    description TEXT
)
''')
conn.commit()

# --- Data Validation ---
def validate_entry(date_str, category, amount):
    try:
        datetime.strptime(date_str, "%Y-%m-%d")
    except ValueError:
        raise ValueError("Invalid date format (YYYY-MM-DD required)")
    if not category:
        raise ValueError("Category required")
    try:
        amount = float(amount)
    except ValueError:
        raise ValueError("Amount must be a number")
    return True

# --- Add Transaction ---
def add_transaction(date, category, amount, description=""):
    validate_entry(date, category, amount)
    cur.execute("INSERT INTO transactions (date, category, amount, description) VALUES (?, ?, ?, ?)",
                (date, category, float(amount), description))
    conn.commit()

# --- Search/Filter ---
def search_transactions(category=None, min_amount=None, max_amount=None, start_date=None, end_date=None):
    query = "SELECT * FROM transactions WHERE 1=1"
    params = []
    if category:
        query += " AND category=?"
        params.append(category)
    if min_amount:
        query += " AND amount>=?"
        params.append(float(min_amount))
    if max_amount:
        query += " AND amount<=?"
        params.append(float(max_amount))
    if start_date:
        query += " AND date>=?"
        params.append(start_date)
    if end_date:
        query += " AND date<=?"
        params.append(end_date)
    df = pd.read_sql_query(query, conn, params=params)
    return df

# --- Report Generation ---
def report(period='daily'):
    df = pd.read_sql_query("SELECT * FROM transactions", conn)
    df['date'] = pd.to_datetime(df['date'])
    if period == 'daily':
        grouped = df.groupby(df['date'].dt.date)['amount'].sum()
    elif period == 'weekly':
        grouped = df.groupby(df['date'].dt.isocalendar().week)['amount'].sum()
    elif period == 'monthly':
        grouped = df.groupby(df['date'].dt.to_period('M'))['amount'].sum()
    else:
        raise ValueError("Invalid period")
    print(grouped)
    return grouped

# --- Export to CSV ---
def export_report_to_csv(df, filename):
    df.to_csv(filename, index=False)
    print(f"Report exported to {filename}")

# --- Example Usage ---

# add_transaction("2024-06-01", "Food", 12.5, "Lunch")
# add_transaction("2024-06-02", "Transport", 3.0, "Bus fare")
# add_transaction("2024-06-02", "Food", 8.0, "Dinner")

# df = search_transactions(category="Food")
# print(df)

# grouped = report('daily')
# export_report_to_csv(df, 'expenses_report.csv')