Exporting to Excel

Every extracted statement can be downloaded as an .xlsx workbook: an ordinary Office Open XML file, with amounts stored as numbers and dates stored as dates. The amount column sums without anything being reformatted first.

Getting the file#

From the app. Each completed statement in the list has a CSV button and an Excel button beside it.

From the API. One request, with the document id:

curl "https://extractbankstatements.com/api/v1/documents/$ID/export?format=xlsx" \
  -H "Authorization: Bearer $KEY" \
  -o statement.xlsx

The file is named after the statement you uploaded, so march.pdf comes back as march.xlsx.

What is in it#

One sheet, called Transactions. One header row, frozen so it stays visible while you scroll. One row per transaction, in the order the bank printed them, which is not always oldest first: Monzo prints newest first and the export preserves that rather than quietly re-sorting a statement.

There is no summary sheet, no chart and no total row. The file is the transactions and nothing else, because a total we inserted would be indistinguishable in a spreadsheet from a total you calculated.

The columns#

Amount and Balance are real numbers. No leading apostrophe, no text formatting, no green triangle in the corner of the cell. =SUM(D:D) works on the file as it downloads. This is the one thing worth checking on any converted statement, whoever produced it: select the amount column and look at the status bar. If it says Count: 900 and no sum, the column is text and every total built on it is zero.

They carry a 0.00 number format, so -3.50 displays as -3.50 rather than -3.5. The format is display only; the value underneath is the exact figure.

Date and Value date are real dates, stored as dates and formatted yyyy-mm-dd. They sort chronologically, filter by month, and work in =MONTH(A2) and in a pivot table. The ISO format is deliberate: 04/05 is two different days either side of the Atlantic, and a file that crosses a border should not change meaning on the way. It is a date underneath, so if you want your own format, select the column and set one.

Empty cells are genuinely empty. A statement that prints no value date and no running balance leaves columns B and E blank rather than filling them with a guess.

The tax columns#

Columns K, L and M are for invoices, and they are empty on a bank statement, which charges no tax and states no rate.

They hold what the invoice printed on that line and nothing else. A rate with an empty Tax beside it means the invoice stated the rate and not the tax, and that blank is information rather than a gap we forgot to fill: multiplying the net by the rate would put a figure in your ledger that no supplier ever wrote down, and it would look exactly like one that they did. If you want it, the formula is =ROUND(K2*L2/100, 2) and it is yours rather than ours.

Tax rate is a plain number, not a percentage-formatted cell. 20 means 20 %. Excel's percent format multiplies by a hundred, so a cell holding 20 and formatted as a percentage would read 2000%, and a 0.00 format would render 5.5 as 5.50 beside a column of amounts written the same way and invite you to add it up with them.

Where the tax on the invoice as a whole is broken out per rate, the app shows that block above the table. It is not in the workbook, because a summary sheet in a file that is otherwise one row per line is a second thing to keep in agreement with the first. The API returns it as tax_bands.

The verification columns#

Column J is the part that is not just a table of rows, and G and H explain it.

Every figure is checked against the document's own arithmetic before you see it: the running balance from one row to the next, and the opening balance, declared totals and closing balance the document states about itself. Column J says which of three things that check found.

Every row in the file is yours to use whichever of the three it carries. The column is what we can say about a figure, not permission to use it.

not verifiable is not a warning and does not mean anything is wrong. Plenty of documents carry no self-check at all: an invoice lists what it lists, and there is no running balance to walk and no declared period total to sum against. The figures may all be perfectly correct. Nothing on the page could establish that, so we do not say it. That is the whole of what the word means.

When column H does have something to say, it is specific rather than a generic flag, and it says which side of the disagreement is in doubt. Usually that is our reading:

The running balance is off by 12.40 at this row, so a figure on it was
probably misread.

The balance figure on this row does not follow from the row above, and the
next row is off by the same amount in reverse, so the balance figure on this
row is the one we most likely misread.

This repeats the -25.63 transaction above it, and dropping it is exactly what
the statement's own totals need to add up, so it was almost certainly read
twice.

What to do with them#

Filter column J. contradicted gives you the exact rows to open the PDF against, often only a few out of several hundred, with a sentence each in column H saying what we found. Filtering column G for yes gets you the same rows.

Filter for not verifiable and you have the figures nothing on the document could confirm. Those are worth an eye if the numbers matter, and they are not errors we found.

A column of verified across the whole file means every figure was proved by the document's own arithmetic. It does not mean the descriptions are right, the order is right, or the currency label is right. Those sit outside what arithmetic can prove, and are covered in what reconciliation actually proves.

An empty column G is not the same claim, and this is the one thing on this page worth getting right: G is empty both on a row that was proved and on a row that nothing could reach. Column J is what tells the two apart.

Two things to watch#

Do not sum a mixed-currency column. A business account statement can hold several currency accounts in one PDF. Column F is the currency of that row, not of the statement, so filter by it before totalling anything. Adding euros to dollars produces a number that is not money.

Signs follow the account, not the column headings. On a statement, money out is negative and money in is positive, whichever way round the bank printed its debit and credit columns. The convention is worked out from the statement itself and checked against the running balance, so a bank whose "debit" column is really money in still comes out the right way up.

An invoice has no such convention, so its amounts are as printed. The same invoice is money out to whoever received it and money in to whoever sent it, and the page does not say which. Adding a sign would be our claim rather than the document's, and the line items would then no longer sum to the total it declares, which is the arithmetic that proves them.

CSV instead#

?format=csv gives the same thirteen columns and the same rows. It is RFC 4180 with a UTF-8 byte order mark, so Excel on Windows reads accented merchant names correctly instead of turning them into mojibake.

The difference is types. A CSV has none: every field is text, and what a date or an amount becomes depends on the import settings of whatever opens it. That is fine for a pipeline that parses the file itself, and it is the reason the xlsx exists for anyone opening it by hand.

For JSON, use GET /v1/documents/{id}/transactions, where amounts are exact decimal strings rather than JSON numbers.

Straight into an accounting package#

?format=ofx gives a bank statement in the format Xero, QuickBooks, Sage, FreeAgent, Wave, MYOB and GnuCash import, rather than a table you map columns onto. It carries less than these thirteen columns do, because OFX has no field for the three verification states, and exporting to an accounting package says exactly what travels and what does not.