Automating reconciliations and reports with VBA (Visual Basic for Applications) in Excel and SQL

Cambridge University Hospitals NHS Foundation Trust, East of England, Acute

The problem

Due to the Trust’s size (and associated data volume) and complexity, reconciliations were very time consuming, we needed to work smarter, otherwise we had very little time for the important elements of critical review and correction. We automated the following using VBA within Excel:

  • accruals – NHS
  • accruals – commercial
  • prepayments – NHS
  • prepayments – commercial
  • deferred income – NHS
  • deferred income – commercial
  • income received in advance – NHS
  • income received in advance – commercial
  • bank – general account
  • bank – payments account
  • the monthly board report
  • analysis of trial balance (TB) against budgets.

The challenge

How we might create a one click system for each of the above reconciliations and reports so we could save time for finance staff and ensure focus on critical review and correction? How we might ensure teams could maintain these reports and reconciliations even if staff changed roles so that they are not person dependent?

The outputs

Faster, effective reconciliations with instructions that could be used by anyone needing to run them. On-demand Board reporting tool. Improved ability to identify errors and more time for error resolution and analytical review.

The outcomes

The outcomes include better quality reconciliations with highlighted anomalies, allowing for more time to investigate these anomalies and conduct analytical reviews. Consequently, this provides the financial accounts team with the opportunity to provide feedback to those responsible for journal postings, thereby reducing the error rate in the future. Additionally, the Board reporting process has become more effective and efficient, empowering users to carry out tasks independently without relying on others who might not be available. The enhanced ability to identify and resolve anomalies further contributes to improving the overall quality of the produced accounts.

The Innovation

Read the full innovation on automating reconciliations and reports with VBA (Visual Basic for Applications) in Excel and SQL

All documents and resources relating to this innovation can be found in the downloads section at the bottom of the page.

Read now

What the peer-reviewers said

“The standardisation brought about by automation ensures consistency and accuracy in the financial data, reducing the risk of errors associated with manual processes.”

“A really useful innovation that has scope for further roll out.”

“It has improved the quality of reconciliations and sped the process up by saving 1 week per month. This has allowed more time for investigation of anomalies and reviewing the data.”

Get Involved Today


Innovation O6 – Final Full Submission