Utilities Analysis Tool

A modular Python tool for analyzing and visualizing utility charges from multiple sources, including water, sewer, trash, electric, laundry, and insurance.

recording.mov

Overview

This project processes utility charge data from rental platform statements and financial transaction exports, categorizes charges, and generates comprehensive reports with interactive visualizations.

The tool combines data from two primary sources:

• Rental platform statements: Rent and utility statements that include charges for water, sewer, trash, HVAC-Gas, pest control, and account billing fees
• Transaction exports: Financial transaction exports that capture utility payments to various service providers (electric, laundry, insurance, etc.)

By merging these data sources, the tool provides a complete view of all utility expenses across different billing cycles and payment methods.

Features

• Multi-source data processing: Combines charges from service provider statements and transaction exports
• Automatic categorization: Maps charges to categories (Water, Sewer, Trash, Electric, Laundry, Insurance, etc.)
• Interactive visualizations:
  • Scrollable monthly breakdown charts (shows 4 months at a time)
  • Monthly totals overlay with line chart
  • Category-based color coding
• Comprehensive reporting:
  • HTML report with interactive Plotly charts
  • PNG export for static charts
  • Monthly breakdown tables
  • Category totals with percentages
  • Summary statistics cards

Project Structure

utilities/
├── data/                      # Data directory
│   ├── raw/                   # Raw input CSV files
│   │   ├── bilt.csv          # Bilt statement charges
│   │   └── utilities.csv      # Financial transaction exports
│   └── processed/             # Processed data (auto-generated)
├── output/                    # Output files (auto-generated)
│   ├── reports/               # HTML reports
│   └── charts/                # PNG charts
├── src/                       # Source code package
│   └── utilities_analyzer/
│       ├── __init__.py        # Package initialization
│       ├── config.py          # Configuration and constants
│       ├── data_loader.py     # Data loading functions
│       ├── processors.py      # Data processing functions
│       ├── visualizations.py  # Chart creation functions
│       ├── report_generator.py # HTML report generation
│       └── cli.py             # Command-line interface
├── tests/                     # Unit tests
├── .gitignore                 # Git ignore file
├── requirements.txt           # Python dependencies
├── README.md                  # This file
├── analyze.py                 # Main entry point
└── analyze_water.py          # Legacy script (deprecated)

Installation

Prerequisites

• Python 3.9 or higher
• pip (Python package installer)

Setup

1. Clone or download this repository

2. Install dependencies:

   pip install -r requirements.txt

   Or install individually:

   pip install pandas matplotlib plotly

Usage

Basic Usage

Run the analysis from the project root:

python analyze.py

Or using Python 3 explicitly:

python3 analyze.py

Output

The script generates:

1. PNG Chart: output/charts/monthly_charges_chart.png

   • High-resolution (300 DPI) bar chart
   • Monthly breakdown by category
   • Utilities total overlay line

2. HTML Report: output/reports/utilities_report.html

   • Interactive Plotly chart with scrolling
   • Summary statistics cards
   • Monthly breakdown table
   • Category totals with percentages
   • Responsive design for all screen sizes

Console Output

Loading data...
Processing rental platform data...
Processing utilities data...
Combining data...
Calculating statistics...

Total charges by category:
  Sewer: $919.88
  Electric: $917.94
  Water: $603.71
  Laundry: $504.75
  Trash: $411.28
  HVAC-Gas: $140.29
  Insurance: $45.00
  Account Billing Service Fee: $36.00
  Pest Control: $10.26

Grand total: $3,589.11

Generating charts...
Chart saved as 'output/charts/monthly_charges_chart.png'
Generating HTML report...
HTML report saved as 'output/reports/utilities_report.html'

All reports generated successfully!

Data Format

Rental Platform CSV (data/raw/bilt.csv)

Expected columns:

• Date: Date in format "Mon DD YYYY" (e.g., "Jan 15 2025")
• Description: Charge description (Water, Trash, HVAC-Gas, etc.)
• Notes: Additional notes (used for Water/Sewer categorization)
• Charge: Amount charged (with $ and commas)

Utilities CSV (data/raw/utilities.csv)

Expected columns:

• Date: Date in format "MM/DD/YYYY" (e.g., "01/15/2025")
• Payee: Service provider name
• Outflow: Amount paid (with $ and commas)
• Category: Transaction category
• Cleared: Payment status

Category Mappings

The tool automatically categorizes charges based on keywords in the payee name:

• Laundry → Any payee containing "CSC", "Laundry"
• Electric → Any payee containing "PG&E", "PGE", "Electric"
• Insurance → Any payee containing "Insurance"
• Water → Rental platform charges with Description="Water" and Notes starting with "Water"
• Sewer → Rental platform charges with Description="Water" and Notes starting with "Sewer"
• Other categories from rental platform statements (Trash, HVAC-Gas, Pest Control, etc.)

Customization

Modifying Categories

Edit src/utilities_analyzer/config.py to customize:

• COLOR_MAP: Colors for each category
• EXCLUDED_CATEGORIES: Categories to exclude from analysis
• File paths and output settings

Modifying Visualizations

Edit src/utilities_analyzer/visualizations.py to customize:

• Chart dimensions and styling
• Label thresholds
• Color schemes
• Font sizes

Modifying HTML Report

Edit src/utilities_analyzer/report_generator.py to customize:

• CSS styling
• Report sections
• Table formatting

Development

Project Structure

The codebase is organized into modules:

• config.py: Constants and configuration settings
• data_loader.py: CSV file loading with error handling
• processors.py: Data transformation and categorization logic
• visualizations.py: Chart generation (matplotlib and Plotly)
• report_generator.py: HTML report creation
• cli.py: Command-line interface and main execution flow

Adding New Features

1. Add new constants to config.py
2. Add processing logic to processors.py
3. Update visualizations in visualizations.py
4. Modify report template in report_generator.py
5. Update CLI if needed in cli.py

Troubleshooting

Common Issues

Issue: FileNotFoundError: bilt.csv not found

• Solution: Ensure CSV files are in data/raw/ directory

Issue: No module named 'plotly'

• Solution: Install dependencies with pip install -r requirements.txt

Issue: Charts not displaying in HTML report

• Solution: Ensure you have internet connection (Plotly CDN is required)

License

This project is for personal use.

Contributing

Feel free to fork and modify this project for your own utility tracking needs.

License

MIT License - Free for personal and commercial use.