Automate Reports in Excel With VBA and Power Query

Automating Weekly Reporting With Excel and VBA

Errors were found in 94% of spreadsheets studied. Automate reports in Excel with Power Query and VBA and build in checks that stop errors.

Alexander Kiel

Business Analyst · Banking & Wealth Management

26.06.2025 · 6 MIN READ · PROCESS OPTIMISATION

ON THIS PAGE

1. Map the Weekly Steps

2. Separate Data, Logic and Output

3. Automate the Report Import in Excel

4. Build In Checks

5. Secure the Macros

6. Document It for Handover

Sample case: JPMorgan Chase

In short

FAQ

Much of reporting work is collecting and processing data. McKinsey estimates that 69% of data processing and 64% of data collection activities could be automated with technology that already exists. That makes it worth the effort to automate reports in Excel.

Manual spreadsheets also carry risk. Reviewing decades of field studies, Raymond Panko found that errors were found in 94% of the spreadsheets studied. Each manual step in a weekly report, from copying an export to pasting a total, is another chance for such an error.

Excel and VBA are already installed on almost every desk in a bank. Which of your weekly reports could run with one click instead of half a day of copying?

Flow of an automated weekly report in Excel: import, data, calculations, checks, report and distribution, with the checks step highlighted

STEP 1

Map the Weekly Steps

Before writing any code, write down how the report is produced today. List every step: which files are exported from which system, what is filtered, copied, calculated and formatted, who checks the result and how it is sent. Note the time each step takes.

The steps with the most time and the most manual copying are the best candidates for automation. Collecting and processing data are also the activities McKinsey rates as among the most automatable of all work activities. My article on mapping the process before you change it shows how to draw such a flow so others can check it. Often the map reveals steps nobody needs any more, which can simply be dropped.

✓

List every step, input file and recipient of the report

✓

Record the time and the manual touches per step

✓

Remove steps that no longer serve a reader before automating

How many of your weekly reporting steps exist only because "we have always done it this way"?

Keep in mind: Automating a step nobody needs saves nothing. Simplify the report first, then automate what remains.

STEP 2

Separate Data, Logic and Output

Many weekly reports grow into one sheet where raw data, formulas and the final layout sit side by side. Such workbooks are hard to automate and easy to break. Split them into separate sheets: one for raw data, one for calculations and one for the report itself.

The ICAEW Financial Modelling Code advises to segregate inputs (assumptions), calculations and outputs (results). Protect the sheets users should not change. Panko notes that cell protection, which prevents formulas from being overwritten, is used in half or fewer of spreadsheets.

✓

Keep raw data on its own sheet, as an Excel table

✓

Put all formulas on a calculation sheet that users do not edit

✓

Build the report sheet only from references to the calculations

Could a colleague tell at a glance which cells in your report are inputs and which are formulas?

Keep in mind: Hard-coded numbers in formulas are the most common hidden error. Move every assumption to a clearly labelled input cell.

STEP 3

Automate the Report Import in Excel

The import is usually the most time-consuming and error-prone part of a weekly report. Replace manual copy and paste with a repeatable process. Power Query can combine multiple files with the same schema stored in a single folder into one table and refresh it with one click.

Where Power Query is not enough, VBA can open source files, copy defined ranges and close them again. Microsoft's reference documents each object, such as the Workbooks.Open method, with its parameters. Use fixed folder paths and file names, so the macro knows where to look each week.

✓

Agree a fixed folder and file name pattern for each export

✓

Use Power Query for imports and cleaning where possible

✓

Use VBA for steps Power Query cannot handle, such as opening protected files

Does your team receive the same export in the same format every week, or does it change?

Keep in mind: Automation depends on stable inputs. Agree the export format with the system owner before you build anything on it.

STEP 4

Build In Checks

A macro repeats an error as reliably as it repeats a correct step. Add checks that compare what came in with what came out: record counts, totals per source and key balances. Show the result on a check sheet, so a failed check stops the report before it is sent.

The ICAEW code recommends to include an equality check for each set of values that are calculated separately but ought to be equal. Review by a second person also helps, but it is not enough on its own: Panko found that individual inspectors catch only about half of errors, while group inspection catches about 80%.

✓

Reconcile record counts and totals between source files and report

✓

Flag blank inputs, duplicates and values outside expected ranges

✓

Stop the macro and show a message when a check fails

What would happen if this week's export arrived half empty and nobody noticed?

Keep in mind: A check that is always green is often not checking anything. Test each check by deliberately feeding it wrong data.

STEP 5

Secure the Macros

Macros can do almost anything on a computer, which is why IT departments restrict them. Microsoft now blocks macros in files from the internet by default because VBA macros are a common way for malicious actors to gain access to deploy malware and ransomware.

Work with IT from the start. The UK National Cyber Security Centre advises to only enable macros for staff that rely on them every day and to allow them only from trusted locations. Microsoft also recommends signing macros with a certificate from a trusted publisher.

✓

Store the workbook in a trusted location agreed with IT

✓

Sign the macro with a certificate if your IT policy requires it

✓

Never ask users to change their own macro security settings

Does your IT department know which macros your team runs every week?

Keep in mind: A macro that only runs because security settings were lowered is a risk, not a solution. Agree the setup with IT before roll-out.

STEP 6

Document It for Handover

Many weekly reports depend on one person who built the macro. When that person is absent or leaves, the report stops or is rebuilt by hand. Document what the workbook does, where its inputs come from, which checks it runs and what to do when one fails. When I automated reporting in Excel and VBA, most of the work was cleaning the inputs, not writing the code.

The ICAEW advises that explanatory documentation should not be stored separately from the model. In the code, start each module with Option Explicit, which Microsoft describes as a way to force explicit declaration of all variables, so a mistyped name causes an error instead of a silent new variable. Name one owner and one deputy. My article on desktop procedures that survive a handover shows what the documentation should contain.

✓

Add an instruction sheet to the workbook itself

✓

Comment the code and use meaningful names for variables and ranges

✓

Train a deputy to run and fix the report

Could someone else produce this week's report if the author were on holiday?

Keep in mind: An Excel tool that the business depends on every week is an application. Register it with IT and treat it like one.

You Suck at Excel | Joel Spolsky (Former Excel Program Manager @ Microsoft)

SAMPLE CASE

JPMorgan Chase

In 2012, JPMorgan Chase's Chief Investment Office lost at least $6.2 billion on a large credit derivatives position, known as the London Whale trades. The bank's own task force later examined the new value-at-risk model the office had used.

According to the report of the bank's management task force, the model ran through a series of Excel spreadsheets that had to be completed manually by copying and pasting data from one spreadsheet to another. One formula divided by a sum instead of an average, which likely muted volatility by a factor of two and lowered the reported risk.

The bank's model review group had noted that the process should be automated. The error was found only after the losses had occurred.

Key takeaway: The problem was not Excel itself but a manual, unchecked process around it. Automation, built-in checks and an independent review of the logic would have made the error far harder to miss.

"Our biggest need, based on empirical data, is to do massively more testing than we do now."

Raymond Panko (Spreadsheet Error Researcher @ University of Hawaiʻi)

In Short

Automating a weekly report saves hours every week. The larger gain is a report that is produced the same way every time, with checks that catch errors before anyone reads the figures.

Teams that map their steps, structure the workbook, automate the import, build in checks and document the result turn a fragile spreadsheet into a reliable process. The time saved can go into analysing the figures instead of assembling them.

Frequently Asked Questions

Should I use VBA or Power Query to automate reports in Excel?

Use Power Query for importing, cleaning and combining data, as it needs no code and is easier to maintain. Use VBA for tasks Power Query cannot handle, such as formatting, creating files, sending emails or controlling the order of steps. Many weekly reports work best with both: Power Query prepares the data and a short macro refreshes it and produces the output.

How much time can automating a weekly report save?

It depends on how many manual steps the report has today. Map the current process and record the time per step before you start. Steps that consist of copying, pasting and reformatting data can often be reduced to a refresh. Time is still needed for reviewing results and handling exceptions, so plan for this instead of expecting zero effort.

When should a report move from Excel to a dedicated tool?

Consider a move when the report draws on many systems, when many people need it at the same time or when it feeds regulatory or financial figures. Frequent breaks and dependency on one person are further warning signs. In these cases a reporting or business intelligence tool with central data, access control and audit trail is often safer than a growing workbook.