How to convert a year of bank statements into one spreadsheet

You have twelve statement documents in a folder and your accountant wants one spreadsheet before the deadline. The conversion itself is the easy half. The hard half is proving that the twelve files are actually the whole year, with nothing missing at the join between two of them and nothing counted twice.

That check exists, it is arithmetic, and it takes about a minute for the whole year once the files are converted. It is also the step most people skip, which is why a year assembled by hand so often turns out to be eleven months and a bit.

The calendar is not your period boundary

Statement cycles are set by the bank, not by the calendar. An account whose cycle ends on the 18th produces statements running from the 19th of one month to the 18th of the next, and twelve of those files cover 19 December to 18 December. They do not cover 1 January to 31 December, and no amount of naming the files after months will make them.

Two consequences follow. A calendar year needs thirteen statements, not twelve, with the first and last each straddling the year boundary. And the year has to be cut by transaction date once the data is combined, never by file, because the first and last files each contain rows on both sides of the line.

This is also why a folder listing is a poor completeness check. Twelve files with twelve month names in them look complete and can still be missing a period, because the file name was written by whoever downloaded them.

The closing-to-opening chain

Here is the check that does the real work. Sort the statements by period start. Each file's closing balance should equal the next file's opening balance, because a statement normally opens where the previous one closed. Follow that chain from the first opening balance to the last closing balance, and any break in it is something that needs an explanation before you go further.

Be precise about what a clean chain tells you, because it is easy to claim too much for it. An unbroken chain says the run you are holding has no hole and no repeat in it: every closing figure hands off to the next opening figure, so no period between the first and the last is missing or counted twice. It does not tell you that the run starts and ends where you meant it to, that you have the right account, or that the amounts inside any individual file are right. Those are separate questions, answered by the period inventory, the account number on the statements, and the per-file balance check respectively.

Worked across a partial run, a break looks like this:

In plain text: the first file opens at 5,102.44 and closes at 6,318.90, which is exactly where the second opens; the second closes at 5,947.12, where the third opens; the third closes at 7,204.63, where the fourth opens. The fourth closes at 7,660.55, but the next file in the folder opens at 8,110.02 and starts on 19 May. Both the dates and the balances have jumped, and the statement covering 19 April to 18 May was never downloaded.

How you read a break depends on what the dates are doing at the same time.

• A date gap and a balance break together points to a missing statement. Download the period between them and the chain closes.
• Contiguous dates and a balance break points to an extraction error rather than a missing file, and this is the likeliest reading by a wide margin. Check both files with their own verification pass first. If both come back clean against their own printed closing balances, the discrepancy is on the documents rather than in the extraction, and you are looking at a re-issued or corrected statement, or an adjustment the bank posted between the two periods. Ask the bank rather than editing a figure to make the chain close.
• Overlapping dates means a duplicated period, usually an interim statement or a re-issued document, and you should expect duplicate rows in the overlap.

Overlapping periods and duplicate rows

Overlaps are common and they are not always the bank's fault. Interim statements, re-issued documents and downloads taken with the date range set by hand all produce files that cover ground twice. Some layouts also reprint the closing transaction of the previous period as a context line at the top of the next one.

Resist the temptation to deduplicate on date plus amount. Two identical purchases at the same merchant on the same day are ordinary, and a rule that removes one of them destroys real data with no way to recover it. Use the bank's own transaction reference where the statement carries one, since that is the only field designed to be unique.

Where the statement carries no reference, add the running balance to date, amount and description. It is a much stronger discriminator than date and amount alone, because two distinct transactions of the same value on the same day sit at different points in the chain and therefore carry different balances. It is not a guaranteed key: a balance figure can recur later in a period once movements in both directions have netted out, so a match on all four fields is a strong candidate for review rather than a licence to delete automatically. A duplicate that survives the pass shows up afterwards as a converted balance that does not reconcile, which is the cheapest way to catch one.

Make the twelve files concatenate

Combining is only trivial when the files are structurally identical. Before you stack them, confirm four things across all twelve.

• The same columns in the same order, with identical header text, so a stacked sheet has one header rather than twelve.
• Dates stored as real dates, not text, so sorting the combined year works and a 03/04 does not mean March in one file and April in another.
• One amount convention throughout, either a single signed column or a debit and credit pair, applied identically to every file. The choice matters downstream, and the format the file is being exported for is worth settling before you convert rather than after.
• A source file column and a statement period column, added on every row, so any line in the combined sheet can be traced back to the document it came from.

That last one is not bureaucracy. The moment a figure is queried, whether by your accountant, a lender or a reviewer, the first question is which statement it came from, and a combined sheet without provenance cannot answer it.

The sequence

1. 1

   Inventory the periods

   List every statement with its period start and end, sorted by start date, before converting anything.
2. 2

   Convert each file

   Convert all twelve statement documents, keeping one converted file per source statement rather than merging as you go.
3. 3

   Verify each file on its own

   Confirm that each file's opening balance plus credits minus debits equals its printed closing balance.
4. 4

   Chain the files

   Check that every closing balance equals the next opening balance, and resolve any break before combining.
5. 5

   Stack and total the year

   Concatenate into one sheet, then confirm the year-level totals against the first opening and final closing balance.

The check that closes the year

Once the twelve are stacked, one final total confirms the combined sheet rather than the individual files. Sum every credit and every debit across the whole year, and the difference must equal the final closing balance less the first opening balance.

In the example above, the year takes in 118,540.26 in credits and pays out 114,210.93 in debits, a net movement of 4,329.33. The account opened the year at 5,102.44 and closed it at 9,431.77, and 9,431.77 less 5,102.44 is 4,329.33. The two agree, which is the strongest automatic evidence that nothing was lost, duplicated or mangled in the stacking. If they had disagreed, the per-file checks would already have told you which file to look at.

Pair it with a count. A net movement can also agree when two errors offset each other, most obviously a row lost from one file and a row of the same value duplicated from another, so total the rows in the combined sheet and compare that against the sum of the twelve per-file transaction counts. Amounts and population together are a much harder pair to fool than either alone, and running both takes one extra formula.

Combining into a single timeline raises its own ordering questions when more than one account is involved, so keep an account column and decide up front whether the combined sheet sorts by date across every account or keeps each account contiguous.

Twelve verifications is the actual work

Read back through that sequence and notice where the time goes. The conversions are quick. What is slow is running a full balance check on twelve files, keying twelve sets of opening and closing balances into a control sheet, and chasing the one file that disagrees. Multiply that by a client roster and it is the largest single cost in a year-end engagement.

Convert·Into removes the expensive half. The running balance is recalculated line by line on every statement and compared against the balance printed on the document, so each file arrives with its own arithmetic already settled and any row that breaks the chain flagged. Extraction accuracy is 99.6%. What is left for you at the year level is the closing-to-opening chain across twelve numbers, which is a minute of work and cannot be automated away because only you know which statements should exist.

Hold any alternative to the same standard. Keying twelve statements by hand produces no control totals at all. A generic table extractor, unless it has been configured with statement-specific validation rules, has no concept of an opening balance, so it cannot tell you that it dropped a page or misread a figure, and none of them can tell you that April is missing from your folder. A year of statements that has not been chained is not a year of statements; it is a folder you are hoping about.