Financial Report Generator

Short Description:

Built an automated financial report generator in Python that reads raw transaction data, cleans inconsistencies, and produces a formatted 3-sheet Excel report with KPI dashboard, categorized expense breakdown, and bar chart — replacing a manual 2-hour process with a 3-second script.

Automated Financial Reporting Dashboard

Transforming Raw Data into Actionable Insights and Formatted Workpapers

Overview

As the capstone project for my completion of the Python for Accountants series, I developed a full-stack web application designed to eliminate the manual, repetitive tasks associated with end-of-month financial reporting. This tool allows users to upload raw, unformatted transaction data and instantly receive an interactive business dashboard alongside a fully formatted, multi-sheet Excel report.

The Problem

In the accounting and finance sectors, professionals spend countless hours manually cleaning CSV exports, categorizing transactions, calculating key performance indicators (KPIs), and formatting Excel workpapers for management review. This manual process is not only tedious but highly prone to human error. I wanted to build a solution that proved these traditional workflows could be entirely automated using Python.

The Solution

I built a live, interactive web application using Streamlit. The user experience is simple: drop a raw csv file (e.g. transactions.csv) file into the browser. Behind the scenes, the application utilizes the Pandas library to handle the heavy lifting of data engineering—cleaning messy text fields, filling missing values, and dynamically calculating income, expenses, net profit, and profit margins.

Key Features

• Dynamic Data Cleaning: Automatically standardizes date formats, title-cases descriptions, and categorizes unstructured data based on transaction types.
• Interactive Dashboard: Generates a real-time web interface displaying top-line KPIs, monthly summary tables with conditional formatting, and a categorized breakdown of expenses.
• Automated Excel Generation: Bypasses basic CSV exports by using the openpyxl library to programmatically build a management-ready .xlsx file from scratch.
• Multi-Sheet Architecture: The downloadable report includes a styled Dashboard sheet with calculated totals, a Cleaned Transactions ledger, and an Expense Breakdown complete with dynamically generated bar charts.

Technical Stack & Skills Applied

• Language: Python
• Data Manipulation: pandas (DataFrames, aggregations, masking, datetime transformations)
• Web Framework: streamlit (UI design, state management, metric cards)
• File Automation: openpyxl (Cell formatting, pattern fills, dynamic formulas, chart generation, in-memory byte buffers)
• Version Control & Deployment: Git, GitHub, and Streamlit Community Cloud

The Impact

This project bridges the gap between traditional accounting and modern programming. By applying DRY (Don’t Repeat Yourself) principles, loops, and conditional logic, a reporting process that normally takes an hour of manual Excel manipulation is reduced to a single click. It serves as a practical demonstration of how Python can be leveraged to drive efficiency and accuracy in real-world business environments.

Libraries and Main Functions

1. Pandas (import pandas as pd)

The data engine. This library replaces the manual work you would normally do in Excel (filtering, sorting, pivot tables).

• pd.read_csv(): Reads the raw CSV file the user uploads and converts it into a DataFrame (a table of rows and columns).
• pd.to_datetime(): Converts the raw text in your “Date” column into actual Python date objects, allowing you to sort chronologically and extract months.
• .dt.strftime() & .dt.to_period(): Extracts specific date formats (like “Jan 2024”) from your datetime column so you can group your data by month.
• .where(): Acts like an IF statement in Excel. You used this to check if a transaction was “Income” or “Expense” and place the amount in the correct column.
• .groupby() & .agg(): Acts exactly like an Excel Pivot Table. You used this to group all transactions by month (or by category) and calculate the total sum.
• .isna() & .fillna(): Finds empty/blank cells in your data and fills them with a default value (like replacing blank amounts with 0).

2. Streamlit (import streamlit as st)

The web framework. This library turns your Python script into a live, interactive website without needing to know HTML or CSS.

• st.set_page_config(): Sets up the web page tab title (showing the 📊 icon) and makes the layout wide.
• st.file_uploader(): Creates the drag-and-drop box where users upload their transactions.csv file.
• st.columns(): Splits your web page into side-by-side columns (used for your KPI cards and side-by-side tables).
• st.metric(): Creates the clean, professional-looking KPI cards (Total Income, Total Expenses, Net Profit).
• st.dataframe(): Takes your Pandas DataFrames and displays them as interactive, scrollable tables on the web page.
• st.sidebar: Creates the left-hand panel where you added your “About This Project” portfolio text.
• st.download_button(): Creates the button that takes your generated Excel file and pushes it to the user’s browser to download.

3. Openpyxl (from openpyxl import ...)

The Excel automator. This library creates, writes, and styles .xlsx files from scratch.

• Workbook(): Initializes a brand new, empty Excel file in the background.
• ws.cell(): Targets a specific cell in the Excel sheet (like A1 or B5) to insert a value.
• ws.merge_cells(): Merges multiple cells together (used for your big, clean dashboard headers).
• Font(), PatternFill(), Alignment(), Border(): The styling classes. These are used to make your headers bold, color the backgrounds blue or green, center the text, and draw gridlines.
• BarChart() & Reference(): Reference highlights a specific range of data in your sheet, and BarChart uses that data to dynamically draw the “Expenses by Category” chart directly inside the Excel file.

4. Built-in Python Libraries (import io, import warnings)

Standard tools that come with Python to handle system-level tasks.

• io.BytesIO(): This is a very clever trick used in web apps. Instead of saving the generated Excel file to your computer’s physical hard drive (which doesn’t work well on cloud servers), this creates a temporary “in-memory buffer.” It saves the Excel file to the server’s RAM just long enough for Streamlit to pass it to the st.download_button.
• warnings.filterwarnings("ignore"): Sometimes Pandas throws annoying red warning text in the terminal about future updates. This function simply tells Python to mute those warnings so your logs stay clean.