PORTFOLIO PROJECT USING SYNTHETIC DATA
ACH Returns Reporting
Complete
View project on GitHub
Open the source code, synthetic input files, monthly summary, and exception report. The README explains how to reproduce the analysis with one command.
A portfolio recreation inspired by my monthly reporting work at PayArc. The project uses fictional sponsor-bank files to demonstrate how I would standardize different layouts in Power Query, validate and summarize returns in SQL, and present monthly trends in Excel and Power BI.
Python · SQL · SQLite
Business Question
How can monthly ACH returns be standardized and reconciled across sponsor banks to support a reliable operational review?
Data and Scope
Fictional sponsor-bank files only. This recreation contains no PayArc customer, payment, or proprietary data.
Validation
Planned evidence: investigate duplicates and missing values, test joins, reconcile counts and amounts to each source, and document every exception before publishing a monthly summary.
Investigation log template · populate with actual checks and resolutions
Issue
How I checked it
How I handled it
Duplicate
Add the key, query, and affected records.
Add the decision and audit trail.
Missing value
Add completeness checks and source review.
Add the recovery or exclusion rationale.
Reconciliation discrepancy
Add source-to-output count and amount checks.
Add the root cause and resolution.
Workflow
Planned workflow: standardize layouts in Power Query; join and summarize returns in SQL; build Excel formulas and PivotTables; compare monthly trends and variances in Power BI.
Documentation
Planned documentation. Supporting files will appear when the project work is ready.
Data dictionary
KPI definitions
Business requirements
Source notes
Query notes
Assumptions and limitations
Refresh procedure