Importing bank statements into a budgeting app by hand

The connection to your bank has stopped working. Perhaps the re-authorisation loop never completes, or your institution was never on the supported list, or it ran for eight months and then stopped delivering transactions without ever raising an error. Whatever the cause, the budget is out of date and the app is waiting for data it cannot fetch.

File import is the path that still works. YNAB, Monarch, Actual and a plain spreadsheet in Excel or Google Sheets all offer some way to bring transactions in from a file, and so do most of their competitors. What each one accepts today, and what its mapping step calls the columns, is stated on its own import screen and moves between releases, so read the screen in front of you rather than a walkthrough written a year ago. The rest of this covers the file you build, which has changed far less than any product's format list.

The two file families

Import formats fall into two families, and which one your app offers decides what you convert to.

• Delimited files. A comma or tab separated file with one row per transaction. This is the common denominator: an app that reads only one format almost always reads this one, and a spreadsheet takes it directly. There is a mapping step where you tell the app which column holds what.
• Banking interchange formats. OFX and its close relatives QFX and QBO are structured financial exchange files rather than plain tables. A transaction in one of these generally carries an account identifier and an identifier of its own, so a receiving app can recognise a transaction it already holds instead of adding a second copy. Whether your import screen offers them, and which of the variants it takes, is something only that screen can tell you.

The interchange formats are stricter and self describing, so there is no mapping step and no ambiguity about which column was the amount. A delimited file is more forgiving and more portable. If you are importing one account once, build the delimited file. If you are backfilling months into an account that already holds transactions from a feed, per-transaction identifiers are worth having, provided your import screen offers a format that carries them. Choosing between the export formats comes down to that dedupe question more than anything else.

The column shape that works everywhere

Build the file with these columns and most import screens ask for little beyond confirming which is which. The labels vary between apps, so treat the names below as the content each column has to carry rather than as text the app is looking for.

The choice between a single signed amount and an outflow and inflow pair is the only real decision, and it depends entirely on the import screen in front of you. Some apps offer both and let you pick at the mapping step. If yours only names one, build that one; converting between them afterwards means editing every row.

The description field is worth more attention than people give it. Whatever text lands in that column is what the app's rules will match on for the rest of the year, so a truncated description costs you every future automatic categorisation for that merchant. If your bank's own export cuts descriptions at a fixed width, the full narration on the statement document is longer, and converting the statement rather than exporting from online banking is how you keep it.

The sign convention that ruins an import

Statements are usually printed with two unsigned money columns, one for debits and one for credits. A conversion that carries that layout straight through gives you a file where a 62.40 grocery shop and a 3,250.00 salary credit are both positive numbers. Import that and the app books the grocery shop, the rent and the car insurance as income alongside the salary.

Fix it before importing, not after. Build one amount column: the credit value where the credit column has a figure, and the negative of the debit value where the debit column has one. Then run a single check. Sum the whole amount column. That total must equal the statement's closing balance minus its opening balance. If your statement opens at 1,240.50 and closes at 2,318.75, the amount column has to sum to exactly 1,078.25. Credits of 4,610.00 against debits of 3,531.75 give exactly that. If the sum comes out as 8,141.75, which is the two totals added rather than netted, your signs are wrong and nothing else you do will fix it downstream.

Importing without creating duplicates

The most common way a manual import goes wrong is not the file. It is the overlap between what you import and what the app already has.

If the feed stopped delivering on 14 March and you are backfilling from a statement covering the whole month, the rows from 1 March to 13 March are probably already in the register. Importing the full month puts them in twice. The app cannot always tell: with a delimited file there are no transaction identifiers, so all it has is date, payee and amount, and two identical purchases on the same day at the same shop are a real pattern rather than a mistake.

Trim the file to the gap. Filter to rows dated after the last transaction the app already holds, and import only those. When the connection comes back, check whether it lets you choose a start date. Some do. Many aggregator integrations simply pull whatever history the institution hands over, and then the overlap is unavoidable and the work moves to spotting duplicates after the first sync rather than preventing them. Either way it is the boundary date you are managing, and the same applies when a broken feed leaves months to backfill.

The import sequence

1. 1

   Convert the statement periods you need

   Convert the full statement documents for the months missing from your budget, keeping date, description, amount and balance.
2. 2

   Verify against the statement before shaping anything

   Confirm opening balance plus movements equals the printed closing balance for each period, then check every row against the balance printed beside it while the balance column is still in the file. Confirm consecutive periods join closing to opening as well.
3. 3

   Build the amount column your app maps

   Produce either one signed column or an outflow and inflow pair, matching what your app's import screen names. Confirm the signed column sums to closing minus opening.
4. 4

   Trim to the gap

   Remove any rows dated on or before the last transaction already in the register, so the import cannot duplicate what is there.
5. 5

   Normalise the dates

   Set one date format across the whole file, ISO where the app accepts it, and confirm no row fell back to text.
6. 6

   Import into the correct account and reconcile

   Import, then compare the account balance the app now shows against the statement's closing balance for the same date.

The check that closes the loop

After the import, your app shows a balance it computed from the rows in the register. The statement shows a balance the bank computed from the rows in the account. Those two figures should be identical on the same date, and the size of any difference narrows down what went wrong.

Suppose the statement closes at 2,318.75 and the app now reads 2,224.35. The register is low by 94.40. That is not a rounding artefact or a timing quirk; it is a transaction, or a set of them, present on one side and not the other.

Work the difference before rereading the register. A difference equal to one transaction amount points at a single row out of place. A 94.40 debit that imported twice leaves the register 94.40 low, and so does a 94.40 credit on the statement that never arrived, because an extra copy of a debit moves the balance by one more of that amount, not two. A difference equal to twice a transaction amount points instead at a sign flip. A 47.20 credit imported as a debit loses the 47.20 that should have gone in and adds 47.20 going out, so the register lands 94.40 low on a row worth half the gap. Halve the difference and check whether a row of that value exists before assuming a transaction is missing. A difference matching no single row is usually two rows, and sorting both lists by amount and comparing is faster than reading either one.

Be clear about what this comparison is, though, because it is much weaker than the check you ran on the converted file before shaping it. Comparing one final balance against another is a totals check. It tells you that something is wrong somewhere in the period and nothing about where, and a pair of errors that offset each other, one row imported 40.00 too high and another 40.00 too low, cancels out and leaves the two totals agreeing. Rebuilding the running balance row by row against the statement is the stronger version, because it localises the error to the row where the chain first breaks.

Even the line by line chain has limits, and this workflow walks straight into them. It reads amounts and nothing else. A row imported with the wrong date lands in the wrong month of your budget and reconciles perfectly, and a description mangled past recognition reconciles perfectly too while costing you every automatic categorisation that merchant would have picked up. A row for 0.00 satisfies the chain whatever else is wrong with it. And the file you actually import carries no balance column at all, because you dropped it at the mapping step, so the chain is something you run on the converted file beforehand rather than something the register can be tested against later. Run it there, treat the post-import balance comparison as the coarse confirmation it is, and read the first screen of imported rows with your own eyes.

Once the register is current, the categorisation work starts, and converted statement rows are a good base for it whether or not your app is doing the categorising. The technique for turning a year of rows into a category by category picture is the same in a budgeting app and in a spreadsheet: assign categories to merchants once, not to transactions repeatedly.