The cash book goes through an Excel sheet with a macro right now. Can the
app produce that file, exactly the way the accounting expects it?
At a cafe the cash book ran through a spreadsheet for years. You fill in the week, you press a button, the macro makes a file and that file goes to the accounting. It worked. Nobody knew exactly why any more.
We built an app that produces that same weekly cash statement. Not "something comparable": the same file, byte for byte, so nothing has to change on the accounting side. It is built. The client stays anonymous and there are no amounts, prices or margins here.
That turned out to be a useful exercise, because a macro is an honest specification. It cannot be vague.
The format is the contract
The file is a CSV with nine columns: the cash account, a description of the cash, a transaction code, a contra account, its name, the date, a description, an amount and the cash balance after that line. A few lines per week, each with a description in the form "KIND WEEK number year", and the Sunday of that week as the date.
The formatting is where this kind of thing breaks: semicolon as the separator, decimal comma, no thousands separator, a Windows line ending, and the old Windows character set instead of UTF-8. So specifically no BOM. That is exactly the opposite of the import file we make for a different accounting package, where a BOM is required. One destination, one export, and write the differences down.
We also produce the file with the same name per week. That sounds like a detail until somebody has three weeks sitting next to each other in a folder.
The surprise: the column that does nothing
Here we nearly got it wrong. The file has a contra account column, with a tidy number in it. You would think that decides where the amount goes.
It does not. The accounting package looks at the transaction code and works out the contra account itself. The column in the file is, in practice, documentation for the human who opens the file.
We found that because the numbers in the spreadsheet did not match where the amounts ended up in the accounting. One code consistently booked onto a different account in the package than the sheet said. The macro was not wrong, and neither was the accounting. There was simply an outdated number in a column that is not read anyway.
The lesson: when you recreate an existing file format, do not check whether your file resembles the original, but where the thing reading it puts the amounts. Those are not the same.
That gave us the real destination per transaction code on paper: turnover at the high rate and at the low rate, with the package itself extracting the VAT part from the amount, card payments, gift cards and cash expenses. Five codes, five destinations, worked out once.
The sign
The nicest bit was in the gift cards. In the spreadsheet they sit in a column used as a deduction, and the cash is calculated as one minus the other. Practically that means: whoever fills in the sheet has to type returned minus sold there. Not sold minus returned.
That is not a bug in the macro. It is a convention somebody decided once and then never wrote down. And it is exactly the kind of thing you have to get right by hand every week.
Our app works it out itself, from the direction of the cash: what was sold, minus what was returned. There is no field where anybody can flip a sign.
This is the core of why this kind of integration is worth it. Not because the spreadsheet did not work. Because the same four decisions are taken by a human every week, and one of those decisions is a sign.
How you know it is right
A CSV that resembles the original proves nothing. The check we use is the only one that counts: a week's cash book has to end on the same cash balance as the accounting for that same week, to the cent.
That is a hard check, because it touches everything at once: the codes, the signs, the VAT split and the closing balance the next week starts with. If it is right for one week, you know the whole chain is in place. If it is a cent off, you have a rounding or sign problem and not a format problem.
Two things are deliberately not in the weekly statement, and you need to know that before you go looking:
The deposit to the bank. That arrives via the bank statement and still has to come off the cash afterwards. It does not belong in the cash statement, and if you put it in, your balance is wrong twice.
Loose receipts from earlier on. In this case those were booked straight from the receipt for a while and only later through the cash expenses line. So if you check an older period, you are looking at two different ways of working. That is not a fault in your export.
What we would take to the next one
Read the macro, not the manual. The spreadsheet is what actually happens. The manual describes what somebody intended.
Hunt for the conventions nobody wrote down. Signs, rounding, which column is unused, which field always stays empty. That is where the work is.
Do not copy a field because it is in the original. Ask per field who reads it.
Build one hard closing check. Not ten small checks on individual fields, but one number summing everything up.
Common mistakes
Making the file "modern". UTF-8 with a BOM and a comma as the separator is tidier and does not work. Copy the format exactly.
Trusting a column that looks official. Check where the amounts really land, not what the column says.
Copying the sign from the spreadsheet. Work it out from the direction you understand, so nobody can flip it.
Only testing the export. A valid CSV landing on the wrong accounts is still wrong.
Putting the bank deposit in the cash statement. That comes through the bank.
Using an old period as a benchmark without asking whether the way of working was the same then. Then you are hunting a bug in your own code that is not there.
What an integration on this package can do is on connecting Nmbrs. The quirks of that API are in the Nmbrs Accounting API. If you want to replace an existing spreadsheet with something that works it out itself, have a look at custom software.