How do I turn my bank statement CSV into a monthly expense summary?

What happens if you import the statement as it is?

We tried a sample statement in the Expense summary generator tool with the columns Date, Description, Debit, Credit and Balance, a common layout for bank exports. The tool matched Date as the date and Description as the note on its own, and left amount and currency unmapped. Result: all four rows appeared in the list of rows that were not imported, each with 'the amount is missing' and 'the currency is missing'.

That is deliberate. The tool does not assume Debit is the amount, since a file can have several numeric columns, and it does not assume a currency for a row that has none, because assuming AED for a file in SAR would give you a wrong total without a word. Every incomplete row is listed by number and left out of the totals.

Preparing the file in Excel: three steps

1. Delete the credits and incoming transfers, such as marketplace payouts or customer transfers; they are not expenses.
2. Add a column headed Currency and fill it with your code, such as AED or SAR, down to the last row. The tool recognises that header automatically.
3. Add a column headed Category and give each expense one, such as 'Suppliers', 'Ads' or 'Shipping'. Without it everything lands under 'Uncategorized'.

Save the file as 'CSV UTF-8', since that is the only encoding the tool reads.

Importing and mapping

Once you choose the file, a 'Map the columns' panel appears. Make sure Amount points to the Debit column; that mapping is manual every time. Watch one more thing: if your statement has a column called Type, the tool maps it to Category automatically, because that word is one of the names it knows, yet in a bank export it may hold 'Purchase' or 'Transfer' rather than your expense categories. Point it to the right column or to 'none'.

In our example after preparation: three January expenses, 450.00 to a supplier, 89.99 for ads and '1,250.00' for a courier invoice written with a thousands separator. All were read without errors, and the total came to 1,789.99 SAR from 3 entries.

Statements with one signed Amount column

Some banks put every movement in a single Amount column, with debits negative and credits positive. The tool reads a negative amount as a refund that lowers the total. We tried two debits of -450.00 and -89.99 and a credit of 1200.00, and the total came to 660.01 SAR, a figure that means nothing as spending. The fix is to delete the credits, then flip the sign of the rest in Excel with a formula such as =-C2 in a new column that you map to Amount. If you want to see the column names exactly as a program reads them before importing, open the file in the CSV and JSON converter.