Monthly Banking Performance & Risk Report

I built this Excel project to make monthly banking reporting easier to update. The workbook takes banking data, cleans and summarizes it with Power Query, and turns it into a reusable monthly report with formulas, PivotTables, charts, slicers, and VBA.

Dashboard

The dashboard includes:

• total transaction value
• transaction count
• total card spend
• fraud rate
• total loan payments
• average satisfaction score
• monthly transaction value trend
• card transaction value by merchant category
• fraud rate by merchant category

The Month slicer changes the selected reporting month. The transaction trend keeps the full available history so the larger pattern is still visible.

How to Run It

This project is meant to be opened in Excel desktop.

Download the project

On GitHub, click the green Code button near the top of the repository.

You can either:

• choose Download ZIP, then unzip the downloaded folder, or
• clone the repository with Git:

git clone https://github.com/ryanleem/banking-performance-report-analyzer.git

Make sure you download or clone the entire repository, not just the Excel workbook, because the workbook uses the other project folders when it refreshes data.

Use the report

1. Open the downloaded banking-performance-report-analyzer folder.
2. Open the input folder.
3. Put one CSV file in that folder. The filename does not matter.
4. Open report/Monthly Banking Performance & Risk Report.xlsm in Excel desktop.
5. Enable macros/content if Excel asks.
6. Open the DataMapping sheet.
7. Match the incoming CSV column names to the report's standard fields.
8. Go to the Dashboard sheet.
9. Click Refresh Report.
10. Use the Month slicer to change the reporting month.

Keep only one CSV file in input at a time. To use a different file, replace the current CSV, update DataMapping if the headers changed, and refresh the report again.

Data Mapping

The DataMapping sheet lets the workbook accept CSV files even when the headers do not match the names used inside the report.

Report Field	Required?
transaction_id	Yes
account_id	Yes
txn_date	Yes
txn_type	Yes
amount	Yes
channel	Yes
merchant_category	No

For example, a source file could map OrderNumber to transaction_id, ProcessedOn to txn_date, or GrossValue to amount.

What I Built

Power Query handles the data cleaning and summarizing. The original transaction and card transaction data can contain millions of rows, so I aggregate it before loading the report instead of trying to place every row on an Excel worksheet.

The workbook also uses formulas such as SUMIFS, COUNTIFS, AVERAGEIFS, IFERROR, and EDATE, along with PivotTables, charts, conditional formatting, slicers, and VBA.

The Refresh Report macro looks inside the project's input folder, finds the CSV file, updates the transaction source, and refreshes the workbook. This avoids having to edit a Power Query file path manually each time.

The main user-facing sheets are:

• README
• DataMapping
• Dashboard
• Monthly_Summary

Helper sheets used by queries and PivotTables are hidden in the workbook.

How the Report Works

CSV file in input/
        ↓
DataMapping
        ↓
Power Query cleaning and summarizing
        ↓
Monthly calculations and PivotTables
        ↓
Dashboard

The flexible CSV input and DataMapping setup currently applies to the transaction data. The workbook also contains simulated card, fraud, loan, account, and support data used by the other dashboard metrics.

Project Structure

banking-performance-report-analyzer/
├── input/                                  # put one CSV here
├── report/
│   └── Monthly Banking Performance & Risk Report.xlsm
├── sample_data/
├── documentation/
├── dashboard.png
└── README.md

A small example transaction file is included in sample_data/transactions.csv.

Data

The project uses simulated banking data. No real customer banking information is included.

Tools Used

• Microsoft Excel
• Power Query
• PivotTables and PivotCharts
• Excel formulas
• slicers and conditional formatting
• VBA

What I Learned

The biggest challenge was working around Excel's worksheet row limit without losing the information needed for the report. Aggregating the source data in Power Query made it possible to keep the workbook usable even when the original files were much larger than a worksheet can hold.

I also wanted the report to be reusable. The DataMapping sheet and Refresh Report macro let someone swap in another transaction CSV without rebuilding the workbook or manually changing the Power Query source path.