Financial Documents

Co-operative Bank Statement to Excel: CSV, XLS and Paper Copies

Co-op business online statements stop at 25 months and are not the record, and its CSV has no balance. How to get Co-op statements into an Excel sheet that ties.

Published
Updated
Reading time
14 min
Topics:Financial DocumentsBank StatementsUKCo-operative BankPDF conversionNon-profitsbank reconciliationsmile

Getting a Co-operative Bank statement to Excel starts with one fact about the bank's own export. Co-op business online banking downloads transactions from Search transactions as CSV, XLS, PDF or TXT, but the CSV puts credits and debits in a single amount column and carries no balance. The XLS splits credit and debit amounts into their own columns and includes a balance.

The second fact matters as much. The bank says its business online statements are for transaction information only, can be viewed for up to 25 months from the date you started using online banking, and do not replace the paper statement, which remains the actual bank statement for the account.

That leaves two paths:

  • You are a live business customer and need only periods inside the 25-month window. Download the XLS. It has money in, money out and a balance, and for recent bookkeeping it does the job without anything else.
  • Everything else. Periods older than 25 months, paper statements, scanned copies, copy statements ordered from the bank, personal Co-op or smile accounts, or a CSV that will not reconcile: here you convert the Co-op Bank statement to CSV or Excel from the PDF or scan itself.

That second group is typically a charity treasurer or independent examiner closing a financial year, an accountant catching up a client's books or preparing year-end accounts, or a landlord pulling together a Self Assessment.

What Co-op online statements are, and why the paper statement is still the record

The Co-operative Bank puts it plainly. Its Business Online Banking User Guide and its FAQ on viewing and printing statements both say online statements are "for transaction information only and not a replacement of your paper statement which remains your actual bank statement for your account." They are available "for up to 25 months from the date you started to use online banking."

Read the second sentence closely. Online statements begin when online banking started, so an organisation that moved onto online banking eight months ago has eight months of online statements, however long it has held the account, and no business account has more than 25 months of online statements. Personal and smile accounts follow different rules, set out further down.

Paper statements follow their own rule. The bank produces one "once 25 transactions have occurred or a debit has left your account, regardless of the frequency you may have selected." A charity that takes card donations, pays a few suppliers and banks cash weekly can generate a paper statement every couple of weeks. A year's Co-operative Bank statements for a busy account is a stack, not a tidy run of twelve.

Online, a statement opens as a report on screen, and you can save it as TXT or download it as PDF or XLS. Those downloads are useful working copies. For an independent examiner, an auditor, or anyone preparing accounts that someone else will check, the paper statement is what the figures have to agree with. The same split between a quick download and the statement record runs through other UK banks, and it shapes the advice on Barclays statements to Excel too. For any period older than the online window, paper statements, or copies ordered from the bank, are the only statements there are.

One change of ownership is in progress. Coventry Building Society completed its acquisition of the bank on 1 January 2025, and the bank's business is proposed to transfer into Coventry Building Society on 1 January 2027, subject to the court. The bank says there is no change to the way customers manage their accounts day to day: The Co-operative Bank and smile continue as trading names, and statements keep the same branding.

Exporting transactions from Co-op business online banking: the CSV and the XLS

The transaction export sits under Search transactions, and the bank's FAQ and user guide describe the same route:

  1. Open Search transactions.
  2. Choose All accounts, or Select account for one account.
  3. Search by date range, amount or customer reference.
  4. Click Apply search.
  5. Click Download and pick CSV, PDF, TXT or XLS.

The two spreadsheet formats are not the same file in different clothes.

The CSV carries transaction date, account number, bank reference, type, customer reference, amount and additional information. The amount column holds, in the bank's words, "credit and debit in the same column". There is no balance column. The user guide then tells customers how to tidy the file before using it: delete column A, which is blank; delete the account-number column if you do not need it; delete header rows 1 and 2 and the footer row. When the bank's own instructions start with deleting parts of its output, expect to do some work on it.

The guide also warns that exports "connected to automated processes" should be updated "as the file formats have changed." If a spreadsheet macro or an accounting import was built around the earlier layout, it may now be reading the wrong columns without telling you.

The XLS has Transaction date, Bank reference, Customer reference, Type of payment, Credit amount, Debit amount and Balance. That is the layout a reconciliation needs. What it lacks is an account-number column, so rather than downloading an All accounts search, download one account at a time and keep each account's rows identifiable.

There is a second, shorter route. From an account's View details, Download last 30 days transactions gives the recent month as PDF, TXT or XLS.

None of the options is an accounting-package file. Where NatWest's exports and statements include an OFX download for Sage alongside Excel and CSV, the Co-op business export stops at CSV, XLS, PDF and TXT, so any import into Xero, Sage or QuickFile goes through one of those.

Why the Co-op CSV breaks a Xero or QuickFile import

Two failures account for most of the trouble people have with this file.

The first is the amount column. Most import screens expect either separate money-in and money-out columns or a single signed amount, and the mapping has to say which. Map the Co-op's single column as though it were the money-in column and every payment arrives as a receipt, while the credits vanish or land somewhere unexpected. The personal account layout differs from the business one, so an import template set up for a business CSV can misread a personal file, and the other way round.

The second is the furniture around the data. The blank first column, the two header rows and the footer row are not transactions, and an import either rejects the file or turns them into junk lines dated nowhere.

The fix for the CSV is manual but quick once you know it:

  • Delete the blank column, header rows 1 and 2 and the footer row, as the bank's guide describes.
  • Map the columns yourself rather than picking a bank template: transaction date as the date, the customer reference or additional information as the description, and the amount column as a single signed amount.
  • Before importing a year, import a week. Check three or four transactions you know (a payment out, a deposit, a bank charge) and confirm each landed on the right side.

The walkthroughs for importing bank statements into QuickFile and for importing a bank statement CSV into Xero cover the mapping screens for each package.

Even a clean import leaves one gap. The CSV has no balance, so nothing in the file lets you check it against a statement's closing figure. To reconcile, you have to start from an opening balance and rebuild the running balance yourself, which is the column the XLS and the statements already carry.

A bank feed avoids the file altogether for new transactions. The Co-operative Bank offers Open Banking integration with accountancy software such as Xero and Sage. It says nothing about how far back a feed reaches, so treat a feed as covering transactions from when it is connected; the history before that still comes from statements.


Statements older than online banking holds: copies, audit letters and certificates of balance

When a period falls outside the 25-month window, or the paper statements for part of the year have gone missing, the bank will send copies. Its Business Bank Account Plus Tariff, effective from 1 October 2025, prices them as follows:

ItemCharge
Copy statement£2.50 per statement
Duplicate statements£5
Audit letterfrom £30 plus VAT
Certificate of balance£15 plus VAT

The per-statement charge adds up faster than it looks. Because the bank produces a statement once 25 transactions have occurred or a debit has left the account, a busy account can generate thirty or more statements a year. Thirty copy statements at £2.50 is £75 for one account's year, so check what the organisation already holds before ordering a full run.

The audit letter and the certificate of balance are different from statements. They are the bank's own confirmation of the balances on the accounts at a date, and an auditor or independent examiner may ask for one alongside the statements when the year-end figure needs confirming by a third party.

Copies arrive as statements, not as rows, and they go into the conversion step later on this page like any other statement.

Personal Co-op and smile accounts: PDFs only for the paperless years

Personal and smile customers get a different set of rules. Paper statements are the default. Paperless PDF statements go back up to 7 years in online banking, but only for the periods you were opted in to paperless: switch to paperless today and the PDFs start today, with the years before it on paper. Credit card statements are available online for 6 months.

Copy statements on a personal account cost £2.50 per statement, capped at £10 per request, for statements from the last 7 years, and arrive within 7 business days. Copies of mortgage statements cost £25.

The bank documents a transaction export for business online banking only. Personal customers do download CSV files, laid out differently from the business one, but the bank publishes no column list for them, so there is none here to plan an import around.

For a landlord or sole trader who needs a smile or personal Co-op bank statement in Excel for a tax year, that settles the inputs: the PDF statements for any paperless years, and paper statements or ordered copies for the rest. Both need converting, and they convert the same way as the business statements below.


Worked example: a charity's year of Co-op statements in one sheet

Take a small charity with a Co-operative Bank community account and a financial year running April to March. The charity moved onto online banking in January, so online banking holds only the last three months; the nine months before that exist only on paper. The treasurer kept those paper statements, two or three a month under the 25-transaction rule, and has scanned them to PDF. The last three months were downloaded from online banking as PDF statements, and the examiner will still check their figures against the paper statements as they arrive. The independent examiner is due in May.

The examiner's starting point is the statements. The Charity Commission's guidance for independent examiners (CC32) says that where manual records are kept, the examiner should carry out a direct comparison with them, including bank statements, and that for receipts and payments accounts the records may amount to bank statements, a file of receipts and invoices and a simple listing of transactions. Where the charity uses accounts software, it recommends a review of bank reconciliations as a check on completeness (paragraphs 6.4 and 6.5). Either way, the spreadsheet has to agree with the statements, and an export that covers only part of the year and drops the balance cannot show that it does.

So the statements themselves are the source, and the job is to convert Co-operative Bank PDF and scanned statements to Excel in one pass. With Invoice Data Extraction, the treasurer uploads every PDF, scanned and downloaded, together as one batch. The prompt can be the job in plain words, for example "I'm reconciling bank statement transactions against our invoices", or a field list:

"One row per transaction: date, description, money in, money out, balance. Format dates as DD/MM/YYYY."

If the examiner's workbook or the accounts package expects a fixed layout, Structured Prompt sets the exact column headers in the exact order, with an instruction on any column that needs one. The ready-made bank statement prompt in the empty prompt box is a general one: it fills the box with a short prompt for one row per transaction, which the treasurer can change before running it.

What comes back is a single Excel, CSV or JSON file with one row per transaction across the whole year, in the columns the prompt named. In Excel the amounts arrive as numbers and the dates as dates, so a running-balance formula works straight away. A value or row the panel of AI agents cannot agree on is flagged as Review Needed, with what to check, and in the results viewer one click on a row's file name opens the statement page it came from beside it. Every row also carries the file and page it came from in its Source File column, which stays in the downloaded sheet, so the examiner can trace any line back to its statement.

Checking the sheet against the Co-op statements before import

Before the sheet goes to the examiner or into Xero, Sage or QuickFile, make it prove that it ties. Add a check column beside the extracted balance and work through these in order:

  1. Opening balance. Take the balance brought forward on the first statement of the year. The first row's printed balance should equal that figure plus its money in, or less its money out.
  2. Running balance. In the check column, calculate each row as the previous row's balance plus money in less money out. It should match the printed balance on every row. The first row where it stops matching is the one to open beside its statement page.
  3. Closing balance. The last row's balance should equal the closing balance on the last statement of the year.
  4. Totals. Total money in and total money out for the year should agree with the receipts and payments totals in the accounts.

Two places deserve a look before the rest. Rows flagged Review Needed come first: open each from the results viewer, compare it with the page, and make any correction in your own file. Then look at the joins between statements. A busy account's year can run to dozens of statements, and each join is a balance carried forward that can be mistaken for a transaction, or a line at the foot of one page that needs checking against the top of the next.

One timing point comes from the bank's own guide: "Your most recent transactions may not be on your most recent statement." The last statement issued before the year end can stop short of 31 March, with the final days' transactions appearing on the next statement. Include that next statement in the batch and take the 31 March balance from it, or order a certificate of balance at that date. Items in the cash book that have not yet reached the bank by then are the ordinary timing differences of any year-end reconciliation. The wider method, outstanding items and all, is in the guide to reconciling the cash book to the bank statement.

Once the balances tie, reshape the columns to the layout your accounting package imports, and the column-mapping cautions above still apply.

Extract invoice data to Excel with AI

Upload your invoices, say what you need in plain words, and download the spreadsheet.

  • A panel of AI agents has to agree on every value
  • What the panel cannot agree on is flagged as Review Needed
  • 50 free pages every month, no subscription
  • Any layout, any language, scans and phone photos
  • Numbers come through as numbers and dates as dates
  • Files encrypted and deleted within 48 hours