Bank Transaction Categorisation: Keyword Rules, Vendor Maps, Amount Rules, Split Transactions & an Exceptions Queue

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Read a raw bank export like a bookkeeper: label every line as money in or out, pull the type code and the reference out of the description text, and prove that the export is complete by rebuilding the closing balance.
• Build a small rule table of keywords and categories, flag every description that contains a keyword with IFERROR(FIND(…)), return the category of the first matching rule and see what the rules still miss.
• Count how many rules match each line, give every rule a priority, let the highest-priority matching rule decide the category and spot the false positive that still slips through.
• Cut a clean vendor key out of each description, look it up in a vendor map with XLOOKUP, fall back to the keyword rules with IFNA when the vendor is unknown and measure how much of the export the map already covers.
• Write one IFS rule chain that sorts lines by type code, sign and size – transfers, supplier refunds, sales income, bank charges and small sundries – and see why the order of the rules decides the result.
• Split one hardware purchase into four categories by share, let the last line take the rounding remainder so the cents add up, and check a typed split of the rent payment against its bank line.
• Flag vendors that were also paid in the previous month, measure what share of lines the rules categorise on their own, size the exceptions queue and make the uncategorised lines stand out for review.
• Turn three months of categorised lines into a category × month summary with SUMIFS, measure the uncategorised remainder of a month and chart the spend so the owner sees where the money went.