How do you combine 12 bank statements into one spreadsheet without double rows?

Put every month in one sheet, then check the seams: each statement's closing balance must equal the next statement's opening balance, and any transaction dated on the last day of one statement must not appear again on the first day of the next. ZeraBooks does both when you download several statements together as one file, with an Account column so different accounts don't mix.

Where double rows come from

Statement periods don't always line up with calendar months. A statement that runs Jan 22 to Feb 21 and the next one from Feb 22 to Mar 21 is clean. But people often mix sources: a PDF statement for January and a CSV export for "February" that actually starts Feb 1. Now Feb 1 to Feb 21 is in the sheet twice.

Here's a seam check that catches it, for a cafe's account:

  • January statement closes Jan 31 at 6,240.10
  • February statement opens Feb 1 at 6,240.10
  • Transactions in the sheet dated Feb 1 to Feb 28: total net +1,880.45
  • February printed closing: 7,120.55

6,240.10 + 1,880.45 = 8,120.55, not 7,120.55. The sheet has 1,000.00 too much. Search February for anything that appears twice. Here it was a 1,000.00 transfer that came in once from the PDF and once from a CSV.

The checklist for a year in one sheet

  1. One source per month. All PDFs, ideally.
  2. Line up opening and closing balances month to month. Any break means a missing statement or a double.
  3. For each month, opening plus that month's rows must equal its closing.
  4. Add an Account column before you combine more than one account.
  5. Sort by date only after the checks pass.
A year of statements listed with each one's opening and closing balance

ZeraBooks shows every statement's opening and closing balance in one list so the seams are easy to read: https://www.zerabooks.com/combine-bank-statements

Disclosure: I founded it. The checklist works in plain Excel too.

Comments

Post a Comment

Popular posts from this blog

How to clean up messy books without redoing the same month twice

Image
Clean up books in this order: collect every statement , set opening balances from the last period your accountant signed off, then reconcile each account one month at a time, oldest first . Only once a month ties do you pair transfers, remove duplicates, place uncategorized lines and move owner spending out. A different order means categorizing lines you later delete. A month that doesn't tie, with the gap shown. During a cleanup you stay on that month until the gap is zero. The worked figures below belong to a fictional design studio whose books stopped being kept in February. They are illustrative only. What has to happen before any categorizing? Step 1: Build a statement grid Accounts down the side, months across the top. Tick a cell when you hold that month's statement as a PDF from the bank. Our studio has checking, savings and one card across eight months, so the grid has 24 cells. Any blank cell is a month you can't prove yet, so request it today. Activity...
Read more

How to convert a credit card statement PDF to Excel, step by step

 You need last quarter's card charges in a spreadsheet by Friday, and all you have is three PDFs from the issuer's website. To convert a credit card statement to Excel, download the PDF statement (not the activity export), run it through a converter or copy the tables out by hand, then fix three things: the signs on payments, the foreign currency detail lines, and the summary blocks. Finish by checking that your rows rebuild the printed new balance. Here's each step. Step 1: Download the statement PDF Go to the card's statements page on the issuer's site and download the monthly statement. Many issuers also offer a "download activity" CSV. Skip it for this job. It usually has no previous or new balance printed anywhere, and those two numbers are how you'll prove the spreadsheet is complete at the end. Watch the dates. A card statement ends on a closing date, not the end of the month. If you need the full quarter, you may need the statement after it too...
Read more

How to scan a bank statement so it converts to Excel cleanly

Image
To scan a bank statement so it converts to Excel cleanly, use 300 dpi and grayscale , keep pages straight, save each account's pages into a single PDF , and include the last page even if it looks empty. Then convert it and check that the opening balance plus the rows equals the printed closing balance. Most conversion problems with scanned statements are decided before the conversion starts, at the scanner. Here is how to get it right, step by step. Where a scanned PDF goes in ZeraBooks Convert: the Upload button, or drop the file on the page. Do you need to scan the statement at all? Step 1: Check online banking first Log in and look at the statements section. If the month is there, download the PDF. A downloaded statement carries the bank's own text and will always beat a scan. What scanner settings work best for bank statements? Step 2: Set the scanner Setting Use Why Resolution 300 dpi Lower loses faint digits; higher only makes bigger files Colo...
Read more