Spreadsheets

Combine monthly bank statement CSV files with Power Query

Combine monthly bank statement CSV files into one Excel table with Power Query, fix dates and amounts once, and refresh when a new file arrives.

Editorial illustration for Combine monthly bank statement CSV files with Power Query

Put every monthly statement CSV in one folder, then in Excel use Data, Get Data, From File, From Folder and choose Combine and Transform Data. Fix the date and amount types once in the editor, load the result to a sheet, and from then on a new month is just a new file in the folder plus Refresh All. Power Query is built into recent Excel for Windows; Excel for Mac has a more limited version, so check what yours offers.

How should I set up the folder first?

  • Use a dedicated folder that holds only statement CSV files, for example one named Statements 2026-27.
  • Name files so they sort sensibly, such as Savings-2026-04.csv, Savings-2026-05.csv. The file name is carried into the combined table, so it doubles as a label.
  • Keep the Excel workbook itself outside this folder. The folder query lists every file it finds, and you do not want it reading your own workbook.
  • Download all files from the same account in the same format. Power Query matches columns by name, and a statement with extra columns will give you messy output.

What are the steps to combine the files?

  1. In Excel go to Data, Get Data, From File, From Folder, and browse to your statements folder.
  2. A preview lists each file with its name and date modified. Click Combine, then Combine and Transform Data.
  3. In the sample-file dialog leave the first file selected and the delimiter as comma, and press OK. Power Query opens its editor with all rows stacked and a Source.Name column added at the left.
  4. If your statements start with a few lines of account details before the real header row, open the query named Transform Sample File in the left pane and use Home, Remove Rows, Remove Top Rows there. Do the same with Use First Row as Headers. Steps placed in that sample query run on every file before they are stacked.
  5. Set the column types, as explained in the next section.
  6. Choose Home, Close and Load. The combined table lands on a new sheet and keeps its link to the folder.

How do I get the dates and amounts right once?

Types are where most bank CSVs go wrong. A date such as 05/04/2026 can be read as 4 May on a computer set to US formats. In the editor, right-click the date column, choose Change Type, then Using Locale, set the type to Date, and pick English (India). Test it on a row you know: a transaction on 5 April must show as 5 April, not 4 May.

For amounts, choose Decimal Number. If a column still shows errors, the amounts probably carry a comma or a trailing Dr or Cr. Strip the comma with Transform, Replace Values. Because these steps are recorded, you do this work once, not every month.

StepWhere to do itWhy there
Remove account-detail lines at the topTransform Sample File queryRuns on each file before stacking
Promote first row to headersTransform Sample File queryEach file has its own header row
Set date and amount typesMain combined queryApplies once to the stacked table
Filter out blank or total rowsMain combined queryEasier to see across all months
Add a signed Amount columnMain combined queryOne rule for every file

What if my statement has separate debit and credit columns?

Add a single signed amount. Choose Add Column, Custom Column, name it Amount, and enter this formula: =(if [Credit] = null then 0 else [Credit]) - (if [Debit] = null then 0 else [Debit]). Say a debit of 1,250 with a blank credit becomes -1,250, and a credit of 40,000 becomes 40,000. Now every row has one number that is negative for money out. Set its type to Decimal Number too.

How do I add next month's statement?

Save the new CSV into the folder and choose Data, Refresh All. The new rows appear in the table below the old ones. If you built a pivot table on top of the loaded table, Refresh All updates that as well.

Watch for overlapping downloads. If you pull 1 to 15 April and later 1 to 30 April, the first half arrives twice. Resist Remove Duplicates as a quick fix, because two genuine purchases of the same amount at the same shop on the same day would be deleted. Instead check the Source.Name column, and keep date ranges clean at download time.

When does a folder of CSVs stop being enough?

This setup works well with one or two accounts and consistent files. It strains once you add credit cards, wallets and cash, because each source needs its own folder and clean-up steps. At that point an app can take over the plumbing: SpendixAI lets you upload statements as PDF, CSV or Excel, choose the account first, review the rows before saving, and flags possible duplicate imports. Layouts differ between banks, so review what it reads.

  • One folder, one account type, files named by month.
  • Date locale set to English (India) and one test row checked.
  • Signed Amount column built if debit and credit are separate.
  • Totals compared with the statement for one month.
  • Refresh All after every new file.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports