Create a Personal Finance Monthly Report from CSV exports

Hey there, Darian here. As engineers, we spend our days automating complex production systems, but I’ve found that applying those same skills to our personal lives can be just as rewarding. For years, I spent the first Sunday of every month manually downloading CSVs from three different credit cards and two bank accounts. I’d then painstakingly merge, categorize, and sum them up in a spreadsheet. It was a tedious, two-hour process I dreaded. One day it clicked: this is just an ETL pipeline. I wrote a simple Python script to do it for me, and now the whole process takes about 5 seconds. This guide will walk you through building that same timesaver.

Prerequisites

• Python 3 installed on your machine.
• A basic understanding of Python and its syntax.
• Access to your bank or credit card’s website to download transaction history as CSV files.

The Guide: From Raw CSVs to Actionable Report

Step 1: Setting Up Your Project

First things first, let’s get our workspace ready. I’ll skip the standard virtualenv setup since you likely have your own workflow for that. The key is to create a dedicated project directory. Inside that directory, make a subfolder named exports. This is where you’ll save all the raw CSV files you download from your financial institutions. You’ll also need the pandas library, which is the gold standard for data manipulation in Python. You can get it by running the command to install packages for Python, targeting ‘pandas’. Let’s jump straight to the logic.

Step 2: The Core Script – Loading and Merging

The first challenge is to read all our different CSV files and combine them into one single dataset. Banks don’t use a standard format, so we need a flexible way to grab everything in our exports folder. We’ll use Python’s os and glob libraries to find the files and pandas to read and combine them.

Here’s the initial Python script, let’s call it report_generator.py:

import pandas as pd
import os
import glob

def load_all_transactions(path):
    """
    Finds all CSV files in the specified path, reads them into pandas DataFrames,
    and concatenates them into a single DataFrame.
    """
    all_files = glob.glob(os.path.join(path, "*.csv"))
    if not all_files:
        print("Error: No CSV files found in the 'exports' directory.")
        return pd.DataFrame() # Return empty DataFrame if no files

    df_list = []
    for filename in all_files:
        try:
            df = pd.read_csv(filename)
            df_list.append(df)
        except Exception as e:
            print(f"Could not read file {filename}: {e}")
    
    if not df_list:
        print("Error: Could not process any of the CSV files found.")
        return pd.DataFrame()

    combined_df = pd.concat(df_list, ignore_index=True)
    return combined_df

# --- Main execution part ---
if __name__ == "__main__":
    EXPORT_PATH = 'exports'
    transactions_df = load_all_transactions(EXPORT_PATH)
    
    if not transactions_df.empty:
        print("Successfully loaded and combined all transactions!")
        print(f"Total transactions found: {len(transactions_df)}")
        print("First 5 rows:")
        print(transactions_df.head())

This code block defines a function that finds every file ending in .csv inside our exports folder, attempts to read each one, and then stacks them all together into a single master DataFrame. It’s robust enough to warn you if it can’t find or read files.

Step 3: Cleaning and Standardizing the Data

This is where things get interesting. Your credit card company might name the date column “Transaction Date,” while your bank calls it “Posted Date.” One might call the merchant “Description” and another “Details.” We need to standardize this. We’ll create a function to rename columns to a common format and ensure our ‘Amount’ and ‘Date’ columns are in the right data type.

Pro Tip: Before writing the cleaning function, open each of your CSVs and inspect the headers. You need to know what you’re converting from. My script below is based on common examples, but you’ll need to customize the column_map for your specific files.

# Add this function to your report_generator.py

def clean_data(df):
    """
    Standardizes column names, converts data types, and handles common issues.
    """
    column_map = {
        'Transaction Date': 'Date',
        'Posted Date': 'Date',
        'Description': 'Details',
        'Payee': 'Details',
        'Amount (USD)': 'Amount',
        'Debit': 'Amount'
    }
    df.rename(columns=column_map, inplace=True)

    # Check if essential columns exist
    if 'Date' not in df.columns or 'Details' not in df.columns or 'Amount' not in df.columns:
        print("Error: The DataFrame is missing one of the essential columns: 'Date', 'Details', 'Amount'.")
        # In a real script, you might want more robust error handling
        return pd.DataFrame()

    # Convert Date column to datetime objects
    df['Date'] = pd.to_datetime(df['Date'], errors='coerce')
    
    # Convert Amount to a numeric type, removing any currency symbols
    # This assumes debits are negative, credits are positive. Some banks do the opposite.
    if df['Amount'].dtype == 'object':
        df['Amount'] = df['Amount'].replace({'\$': ''}, regex=True).astype(float)
    
    # Drop rows where critical data might be missing after cleaning
    df.dropna(subset=['Date', 'Amount', 'Details'], inplace=True)
    
    return df

# --- Update the main execution part ---
if __name__ == "__main__":
    EXPORT_PATH = 'exports'
    transactions_df = load_all_transactions(EXPORT_PATH)

    if not transactions_df.empty:
        cleaned_df = clean_data(transactions_df.copy()) # Use .copy() to avoid SettingWithCopyWarning
        if not cleaned_df.empty:
            print("\nData cleaned and standardized successfully.")
            print(cleaned_df.head())

Step 4: Categorization Logic

This is the magic. We’ll write a function that looks for keywords in the transaction ‘Details’ and assigns a category. This is highly personal, so you’ll build your own dictionary of keywords.

# Add this dictionary and function to your script

CATEGORIES = {
    'Groceries': ['SAFEWAY', 'TRADER JOE', 'SPROUTS', 'INSTACART'],
    'Utilities': ['PG&E', 'COMCAST', 'T-MOBILE', 'VERIZON'],
    'Subscriptions': ['NETFLIX', 'SPOTIFY', 'HBO MAX', 'AMZN Mktp US'],
    'Restaurants': ['DOORDASH', 'UBER EATS', 'STARBUCKS', 'MCDONALD\'S'],
    'Shopping': ['AMAZON', 'TARGET', 'BEST BUY'],
    'Gas/Transport': ['SHELL', 'CHEVRON', 'UBER TRIP', 'LYFT'],
    'Income': ['DEPOSIT', 'PAYROLL']
}

def categorize_transaction(details):
    """
    Assigns a category to a transaction based on keywords in its details.
    """
    details = str(details).upper()
    for category, keywords in CATEGORIES.items():
        for keyword in keywords:
            if keyword in details:
                return category
    return 'Miscellaneous' # A default category for anything that doesn't match

# --- Update the main execution part ---
if __name__ == "__main__":
    EXPORT_PATH = 'exports'
    transactions_df = load_all_transactions(EXPORT_PATH)

    if not transactions_df.empty:
        cleaned_df = clean_data(transactions_df.copy())
        if not cleaned_df.empty:
            cleaned_df['Category'] = cleaned_df['Details'].apply(categorize_transaction)
            print("\nCategorization complete.")
            print(cleaned_df[['Details', 'Category']].head())

Step 5: Generating the Final Report

With all our data loaded, cleaned, and categorized, the final step is to group by our new ‘Category’ column and sum the amounts. This gives us the monthly summary we’re after. We can print it to the console and also save it to a new CSV for our records.

Pro Tip: For my own setup, I also filter the DataFrame by month before grouping. This allows me to run the report on a massive, ever-growing collection of CSVs and just get the summary for the most recent month. You can do this with pandas like so: df[df['Date'].dt.month == 10].

# --- Final update to the main execution part ---
if __name__ == "__main__":
    EXPORT_PATH = 'exports'
    transactions_df = load_all_transactions(EXPORT_PATH)

    if transactions_df.empty:
        print("Exiting: No data to process.")
        # In a function you'd use 'return' here.
    else:
        cleaned_df = clean_data(transactions_df.copy())
        if cleaned_df.empty:
            print("Exiting: Data cleaning failed.")
        else:
            cleaned_df['Category'] = cleaned_df['Details'].apply(categorize_transaction)
            
            # Assuming debits are negative numbers, we only want to sum expenses
            monthly_expenses = cleaned_df[cleaned_df['Amount'] < 0]
            
            # Group by category and sum the amounts
            report = monthly_expenses.groupby('Category')['Amount'].sum().round(2).sort_values()
            
            print("\n--- Monthly Finance Report ---")
            print(report)
            
            # Save the report to a CSV file
            report.to_csv('monthly_summary.csv')
            print("\nReport saved to monthly_summary.csv")

Common Pitfalls

Here are a few places I stumbled when I first built this, so you can watch out for them:

• Inconsistent Debit/Credit Formatting: My main bank exports debits as negative numbers, but a credit card exported them as positive numbers in a “Debit” column and credits in a “Credit” column. You may need to add logic in the clean_data function to handle this, like multiplying a ‘Debit’ column by -1 before merging it into the ‘Amount’ column.
• Date Parsing Errors: Dates can be tricky (MM/DD/YY vs. YYYY-MM-DD). The pd.to_datetime(df['Date'], errors='coerce') line is a lifesaver. It converts what it can and puts NaT (Not a Time) for anything it can’t parse, which we then drop with dropna().
• Overlapping Keywords: Be careful with your CATEGORIES dictionary. If you have “AMZN” for ‘Shopping’ and “AMZN Mktp US” for ‘Subscriptions’, a transaction with “AMZN Mktp US” might get incorrectly flagged as ‘Shopping’ first depending on your logic. Order matters, or make your keywords more specific.

Conclusion

And that’s it. You now have a script that turns a folder of messy CSVs into a clean, categorized financial summary. You can run it manually once a month, or if you’re like me, you can set up a simple cron job to run it automatically. For example, a job like 0 2 * * 1 python3 report_generator.py would run it every Monday at 2 AM. This little piece of automation has genuinely saved me hours and gives me a much clearer picture of my finances with zero recurring effort. Hope it helps you too.

Darian Vance

Lead Cloud Architect & DevOps Strategist

With over 12 years in system architecture and automation, Darian specializes in simplifying complex cloud infrastructures. An advocate for open-source solutions, he founded TechResolve to provide engineers with actionable, battle-tested troubleshooting guides and robust software alternatives.

🤖 Frequently Asked Questions