Accounting Journal Entry Automation
Replacing 4+ hours of manual copy-paste work with a 12-minute Power Query refresh — built for real accounting teams.
Overview
A Canadian accounting firm processed monthly journal entries by manually copying data from SAP exports into Excel, applying formulas, and reformatting for submission. This 4-hour monthly task was error-prone and consumed senior accountant time. This project replaced the entire manual workflow with a Power Query pipeline that refreshes in 12 minutes with zero copy-paste.
The Problem
The manual process involved 7 separate copy-paste steps across 3 Excel workbooks, with no validation — meaning errors only surfaced during audit review, often weeks later. The goal was to eliminate the manual steps entirely, adding automated validation that flags mismatches before submission.
Questions Addressed
- 01
Can the entire 7-step manual process be replaced by a single "Refresh All" click?
- 02
How can we implement automatic debit/credit balance validation without changing the accountant's existing output format?
- 03
What is the minimum intervention required from non-technical accounting staff to maintain the system?
Methodology
Process Mapping & ETL Design
Shadowed the accounting team for two sessions to document every manual step, formula, and data source. Mapped the full data flow from SAP export → Excel transformation → submission format. Identified 7 Power Query queries needed to replicate the logic without VBA dependencies.
Power Query Pipeline
Built 7 chained M Language queries to: import SAP CSV exports, normalize account codes, apply mapping tables for cost center labels, compute running balances, and output to the exact submission format. Added a validation query that returns a red/green status cell — green only if debits equal credits to the penny.
VBA Wrapper & Documentation
Added a one-button VBA macro that triggers "Refresh All", waits for completion, then copies the validated output to a dated archive folder. Wrote a one-page user guide with screenshots so any staff member can operate the system without training.
Key Results
Key Findings
All 7 manual steps were successfully replaced by a single Refresh All action — zero copy-paste required post-implementation.
The automatic debit/credit validation caught 3 balance mismatches in the first month of use that previously would have required manual audit review to find.
Non-technical staff were able to operate the system independently after a 20-minute walkthrough — no ongoing support required.
The M Language approach is more maintainable than VBA for data transformation: changes to account mapping require editing one lookup table, not hunting through formulas.
Conclusion
Power Query is underutilized in accounting workflows. This project demonstrates that even complex multi-source reconciliation processes can be fully automated without custom software — just well-designed M Language queries and clear documentation. The time savings alone justify the investment within the first month of use.