Introduction

Ever received those transaction notification emails from your bank or payment services and wished there was an easy way to compile them into a neat spreadsheet? That’s exactly what this Credit Card Transaction Logger does! Built with Python, this application automatically extracts transaction information from emails and organizes them in Google Sheets, saving you from manual data entry and helping you keep track of your expenses effortlessly.

In this post, I’ll walk you through the components of this project, how it works, and how you can set it up for yourself. Whether you’re looking to track your personal finances or just interested in learning how to automate data extraction from emails, this project offers valuable insights and practical solutions.

What Does It Do?

The Credit Card Transaction Logger performs these key functions:

  1. Connects to your Gmail account using secure authentication
  2. Searches for transaction notification emails from supported merchants/banks
  3. Extracts key transaction details like card numbers, amounts, and merchant names
  4. Organizes this data in a structured Google Sheet
  5. Tracks the last runtime to ensure it only processes new emails in future runs

Currently, the system supports transaction emails from:

  • Grab
  • Metrobank

The beauty of this setup is that it’s easily extensible - you can add support for other merchants by following the patterns established in the existing code.

Setting Up Your Environment

Getting started with the Credit Card Transaction Logger is straightforward:

  1. Clone the repository:

    git clone https://github.com/alphiree/cc-transaction-logger.git
cd cc-transaction-logger-v2

  2. Install dependencies using uv:

    # Install uv if you don't have it
pip install uv # or follow the installation process here: https://docs.astral.sh/uv/getting-started/installation/

  3. Set up Google Sheets API credentials:

    • Go to the Google Cloud Console
    • Create a new project
    • Enable the Google Sheets API and Google Drive API
    • Create a service account and download the credentials as JSON
    • Save the credentials file as sheet-creds.json in the project root

  4. Create a .env file with required environment variables (see Configuration section below)

How to Use It

Using the application is as simple as running:

uv run main.py

This kicks off the entire workflow - connecting to Gmail, finding transaction emails, extracting data, and updating your Google Sheet. The script automatically tracks when it was last run, so it will only process new emails in subsequent executions.

Configuration

For the application to work correctly, you’ll need to set up environment variables in a .env file:

# Gmail credentials
GMAIL_EMAIL=your-email@gmail.com
GMAIL_APP_PASSWORD=your-app-specific-password

# Google Sheets configuration
GOOGLE_SHEET_CREDS_PATH=sheet-creds.json
GOOGLE_SHEET_ID=your-google-sheet-id
STATEMENT_DAY=9

# User configuration
PAYER_USERS=user_1,user_2,others

# Runtime tracking (will be updated automatically)
LAST_RUNTIME=YYYY-MM-DD HH:MM:SS

A few important notes about these settings:

  • For GMAIL_APP_PASSWORD, you’ll need to create an app-specific password in your Google Account security settings. This is more secure than using your regular account password.
  • The GOOGLE_SHEET_ID can be found in your Google Sheet URL - it’s the long string of characters between /d/ and /edit in the URL.
  • STATEMENT_DAY represents the day of the month when your credit card statement is generated, which helps organize transactions by billing cycle.
  • PAYER_USERS is a comma-separated list that populates a dropdown menu in your Google Sheet, allowing you to assign transactions to different people.

Inside the Google Sheet

When the script runs successfully, it creates or updates a Google Sheet that looks something like this:

Google Sheet Example

The sheet includes the following columns:

  • paid: A checkbox to mark if the transaction has been paid
  • date: The transaction date and time
  • card_number: The last digits of the card used
  • total_amount: The transaction amount (formatted with 2 decimal places)
  • merchant: The merchant name from the transaction
  • payer: A dropdown selection of possible payers (from your PAYER_USERS configuration)

Each month gets its own worksheet tab, named according to the statement date (e.g., “20240509” for a statement period ending May 9, 2024). This organization makes it easy to track expenses by billing cycle and keep your financial records neatly arranged.

Here’s what the actual sheet might look like:

| paid | date                | card_number | total_amount | merchant                | payer   |
|------|---------------------|-------------|--------------|-------------------------|---------|
| □    | 2024-05-01 14:32:45 | ****1234    | 125.50       | Coffee Shop             | user_1  |
| ☑    | 2024-05-02 09:15:28 | ****5678    | 47.25        | Gas Station             | user_2  |
| □    | 2024-05-03 18:43:15 | ****1234    | 89.75        | Online Retailer         | others  |

The script automatically applies formatting to the sheet, including:

  • Number formatting for the amount column
  • Checkbox UI for the “paid” column
  • Dropdown selection for the “payer” column
  • Appropriate column widths for better readability

Project Architecture

The project is structured around these core components:

  • main.py: The entry point that coordinates the entire process
  • utils/gmail.py: Handles Gmail connection and email retrieval via IMAP
  • utils/googlesheets.py: Manages Google Sheets operations using the gspread library
  • utils/extractors/: Contains merchant-specific email extractors:
    • base.py: The base extractor class that defines the common interface
    • grab.py: Implementation for Grab transaction emails
    • metrobank.py: Implementation for Metrobank transaction emails

The workflow follows these steps:

  1. Connect to Gmail using IMAP
  2. Fetch emails from specified merchants within the configured date range
  3. Parse the email content to extract transaction data using regex patterns
  4. Format this data into a pandas DataFrame for easier manipulation
  5. Create or update a Google Sheet with the extracted transactions
  6. Save the last runtime to ensure incremental processing in future runs

Adding Support for New Merchants

One of the strengths of this project is its extensibility. To add support for a new bank or merchant:

  1. Create a new extractor file in utils/extractors/ following the pattern in existing files
  2. Implement the extract_payment_info() method to parse email content specific to that merchant
  3. Add the merchant name to the MERCHANTS list in main.py
  4. Update the extractors dictionary in utils/extractors/__init__.py

Here’s a simplified example of what an extractor might look like:

from utils.extractors.base import BaseExtractor
import re

class NewBankExtractor(BaseExtractor):
    def __init__(self):
        self.merchant_email = "notifications@newbank.com"

    def extract_payment_info(self, email_body, email_subject=None):
        # Extract card number (last 4 digits)
        card_match = re.search(r'card ending in (\d{4})', email_body, re.IGNORECASE)
        card_number = card_match.group(1) if card_match else "Unknown"

        # Extract amount
        amount_match = re.search(r'amount of PHP\s*([\d,.]+)', email_body, re.IGNORECASE)
        amount = float(amount_match.group(1).replace(',', '')) if amount_match else 0.0

        # Return the extracted data
        return (f"****{card_number}", amount, "New Bank")

Benefits and Use Cases

This automated transaction logger offers several advantages:

  • Time-saving: No more manual copying and pasting from emails to spreadsheets
  • Accuracy: Eliminates human error in data entry
  • Consistency: Ensures a uniform format for all transaction records
  • Historical tracking: Maintains a searchable history of all transactions
  • Expense sharing: Makes it easy to track who paid for what with the payer dropdown
  • Statement reconciliation: Organize transactions by statement period for easier comparison with official statements

Future Enhancements

While the current version works well, there are several potential improvements:

  • Support for more merchants and banks
  • Categorization of expenses (e.g., groceries, dining, transportation)
  • Data visualization and spending trend analysis
  • Integration with budgeting tools
  • Mobile notifications when new transactions are logged
  • OCR capability for extracting data from attached PDF statements

Conclusion

The Credit Card Transaction Logger demonstrates how a relatively simple Python application can significantly streamline a common financial task. By leveraging the Gmail and Google Sheets APIs, we’ve created a system that automatically captures transaction data and presents it in an organized, accessible format.

Whether you’re tracking personal expenses, managing shared household costs, or helping a small business monitor credit card usage, this tool provides a practical solution that saves time and reduces the tedium of manual data entry.

Feel free to fork the project, adapt it to your needs, and contribute improvements back to the community!

References: