Exporting to Excel is three separate claims, and we were only meeting two

Every bank statement converter says it exports to Excel. It is one line on a feature list, next to CSV and a logo grid, and it reads as a single yes or no question.

It is at least three.

1. Is the file a real workbook, or a CSV with .xlsx on the end?
2. Are the amounts numbers, or text that looks like numbers?
3. Are the dates dates, or text that looks like dates?

Our own export answered yes, yes, and no, from the day it shipped until this week. This post is partly about how to check all three on any converter in about a minute, and partly about the thing that matters more than all of them, which no file format can help with.

The three claims

A real workbook. An .xlsx is a zip of XML parts with a fixed structure. A file that is really a CSV renamed opens with a warning, or opens as one column with every field crammed into it. This is the easiest of the three to get right and the one people most often mean by "exports to Excel".

Numbers that sum. This is the one that costs you an afternoon. A number stored as a text cell holds the same characters, sits in the same place and is skipped silently by SUM. A column of 900 amounts stored as text totals to zero, and nothing on screen says so out loud. Excel left-aligns it and puts a small green triangle in the corner, both of which a converter can override and most people have stopped seeing.

The blunt version is the leading apostrophe. '-25.63 is Excel's way of saying "treat this as text", and a column written that way cannot be summed until somebody strips them.

Dates that are dates. This is the one we got wrong.

Our dates were text

Until this week, every date in our xlsx export went out as an inline string. 2024-04-02 arrived in the cell, looked exactly like a date, and was not one.

Text dates fail quietly rather than loudly. =MONTH(A2) returns an error. A pivot table cannot group them by month or quarter. Filtering "between 1 April and 30 April" does nothing. And Excel does not convert them on open, which is the part people assume: a column of text dates stays text until somebody selects it and runs Text to Columns.

They also sort. YYYY-MM-DD sorted as text comes out in chronological order by coincidence, which is why nobody notices.

The fix is not complicated. A date cell in a workbook is a number, days since 1899-12-30, with a date number format attached. Attaching a number format needs a styles.xml part, which the workbook did not have at all, so it was never one attribute away from working.

Three things came with it that were not in the plan:

• Column widths. A date that does not fit its column renders as #####, where text just overflows into the next cell. Real dates in a default-width column would have shipped as a screen of hashes.
• A fallback. A <v> element holding something that is not a number is not a bad cell, it is a workbook Excel declines to open. Anything that does not parse as a number now goes out as text instead, and so does any date before 1900-03-01, where Excel's serials and the calendar disagree by a day for reasons dating to Lotus 1-2-3.
• Control characters. OCR of a scanned statement can emit a stray byte that XML has no way to represent. One of those in a merchant name costs you the entire file. They are stripped now.

The reason this survived is worth more than the bug. There was a test. It read:

expect(xml).toContain("<v>-3.50</v>");

That asserts on the string that went into the file, not on the file. It passed the whole time the dates beside those amounts were text, because it was never looking at them. The tests now unzip the workbook and read the cells back out, so they assert about what a spreadsheet opens rather than about what we wrote. A 900-row export read back by an independent spreadsheet library comes out with every date a real date, every amount a number, and the amount column summing to the penny.

How to check any converter in a minute

None of this needs our tool. On the file you just downloaded, from anywhere:

1. Select the amount column. Look at the status bar at the bottom of the window. If it shows a sum, the column is numbers. If it shows only a count, the column is text and every total you build on it will be wrong by all of it.
2. Type =MONTH(A2) in an empty cell, pointing at a date. A number means a real date. #VALUE! means a string that looks like one.
3. Click one amount cell and read the formula bar. A leading apostrophe is invisible in the grid and plain there.

Three checks, one file, about a minute.

Sorting by date is the check everyone reaches for first and it is the one that does not work. Text dates written YYYY-MM-DD sort into the right order anyway, so the column passes the eye test and still cannot be filtered or grouped.

None of these tell you anything at all about whether the file is right.

The part no file format fixes

Here is the uncomfortable thing about getting all three claims right: a wrong number in a properly typed number cell sums perfectly.

If a converter misreads 455.13 as 45.13, the column still totals, still sorts, still pivots. Every check in the previous section passes. The file is a model citizen of a spreadsheet and the figure in it is not what the bank printed. You find out three weeks later when a reconciliation fails, or you never find out at all, which is worse and more common.

Any tool can produce rows. The question worth asking is whether the file tells you which of its rows nothing could prove.

A bank statement carries its own proof. Every row states the balance after it, so every row can be checked against the row above, and the chain either closes on the closing balance the bank printed or it does not. Most statements also declare their own totals: opening balance, sum of debits, sum of credits, closing balance. Those four numbers have to agree.

We run that check before you see the file, and then we put the result in the file, per row. Two of the eight columns in every export, xlsx and CSV alike, are the verdict:

Column	What it holds
Needs review	yes if nothing on the statement could prove this row. Empty if something did.
Review reason	Why, in a sentence.

The reasons are specific rather than a flag:

The running balance is off by 12.40 at this row.

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 likely misread.

Nothing on this statement could verify this amount, there is no running
balance and no declared total to check it against.

Filter that column for yes. On a statement that reconciled, the filter comes back empty and the file needs nothing from you. On one that did not, you have the exact rows to open the PDF against, often only a few out of several hundred, each with a sentence saying what does not add up.

That last reason is the one we are most attached to. It appears on every row of a statement that prints neither a running balance nor declared totals. Those rows may all be perfectly correct. Nothing on the page could establish it, so nothing claims it. The absence of a check is not a pass, and a converter that returns a clean-looking table for a statement it could not verify is telling you something it does not know.

What the verdict does not cover

Being precise about this matters as much as making the claim.

An empty Needs review column means the arithmetic holds. It does not mean the descriptions are right, the order is right, or the currency label on each row is right. We have shipped bugs in all three of those, and every one of them reconciled perfectly, because a balance chain proves the amounts and is silent on everything standing next to them. That is a whole post of its own, and it is the honest limit of what this column is worth.

Where that leaves the checkbox

"Exports to Excel" is worth checking rather than believing, on any tool, including this one. Three checks and a minute will tell you whether the file works.

What none of those checks can tell you is whether the numbers are the numbers the bank printed. For that the file has to carry its own answer, row by row, into the spreadsheet where you actually do the work, rather than leaving it behind on a web page you have already closed.

The full column list and what each one contains is in exporting to Excel.