Automated settlement-to-invoice ledger

Background

The same work came around at the start of every month.

Open the mailbox, find each partner’s settlement statement, download the attachment, open the Excel file, copy the amounts one by one, then issue dozens of tax invoices from the result. It took one person a full day.

The statements do not arrive in one format, either. All 26 partners send Excel files in their own layout. The layouts fall into four broad families, and each names and places the same items differently. One family does not even use the term “supply value”.

And the amounts taken from these files become tax invoices as they are.

The problem

The program runs without errors, and the results can still be wrong.

• Wrong numbers are collected just as smoothly as right ones. Gathering 26 partners’ Excel files on one screen does not tell anyone whether the values in them are correct
• No error appears, so problems surface later. An amount that is one cell off does not turn red on screen. It only shows when someone opens each file and compares
• And that amount becomes the tax invoice. An incorrect invoice incurs a penalty and has to be reissued as an amended invoice

We find gaps like these and turn the checks people repeat every month into a system. So the goal was not just to collect, but to verify that the numbers are right.

What we built

• You choose what happens to processed emails — mark them as read, move them to a folder you choose, or leave them untouched. The default is to leave them untouched. Emails stay unread even after the body is read, and nothing is deleted or moved. Staff can keep using their mailbox as usual from day one, and change the setting whenever they want
• The system recognizes every format on its own — the 26 partners’ layouts fall into four families. A system that reads only one family would silently record zero for the rest
• Every extracted amount is checked again by a separate program — reading the amounts and checking them are done by separate programs. The checker opens each Excel file and takes the values exactly as staff see them on screen for comparison. We chose to add that extra layer
• Done by 07:30 every morning — statements do not all arrive on the 10th; they come in between the 1st and the 9th, so the job runs daily. If anything stopped overnight, it appears in the summary email sent at 8 a.m.
• 16 automated tests run on every change — a fix in one place cannot quietly break another

Where the screen looks fine but the amounts are wrong

These happen at any company that receives settlement statements from partners.

• A single decimal point throws off the supply value, VAT and total. Some partners send amounts with decimals, and on those statements the supply value plus VAT does not equal the total. In our first full check, 76 of 427 statements had this problem. We recheck every statement as it arrives, so a tax invoice with a mismatched amount cannot reach the issuing stage.
• An Excel file from a partner looks blank to the program. Some files show the amounts normally in Excel, yet the program cannot read them. That partner then silently shows zero statements. Because the file looks perfectly normal to a person, staff who open it to check see nothing wrong. When a file cannot be read, we have a spreadsheet program calculate it and read it again. No partner gets written off as “probably didn’t send one this month.”
• There is a way to issue the same invoice twice. When a partner resends a statement, the “issued” mark staff had set is cleared, with no error or warning. Once staff mark an item as issued, we keep that status and update only the amount. Automated collection cannot override a decision your staff made.

What we caught

Calculation errors were not the only risk. Each case below would lead to issuing something that should not be issued, or missing something that should.

• Statements arrive with negative amounts. This means the partner will offset the amount next month. Read as a positive number, it would produce an invoice that was never needed. These are now flagged separately as “no invoice required”
• “No need to issue an invoice” appears only in the email body, not in the Excel file. A system that reads only attachments misses this, so the body text is read as well
• One partner’s file contained another company’s name. Trusting that name would have added someone else’s amounts to the wrong account. The company is identified by the file name only
• Some partners send to a folder other than the inbox, and some statements land in spam. Looking only at the inbox, those partners appear to have sent nothing for two months. Reading the entire spam folder, however, would pull in attachments from unknown senders. The system now searches only for partner addresses registered in advance. Attachments from unregistered addresses have no way into your invoice ledger

Results

When an email arrives, the ledger fills itself in, and every value is checked each time. When the 46 statements for July 2026 were opened in a spreadsheet program and compared, there were zero mismatches.

Work that took one person a full day now takes an hour. Opening the emails, telling apart four format families from 26 partners, extracting and checking the amounts, cross-checking them with a separate program, and filling the ledger with who to invoice, how much and by when is all done by 07:30 every morning. What staff do is press the issue button.

The system also flags what needs attention. Items with negative settlements or errors are set aside, and partners silent for more than two months are reported too. No one has to rely on memory each month to check whether everything has arrived.

What this means for you

If your company receives settlement statements, statements of account or bills by email and issues tax invoices or bills from those figures, this applies directly. We build the same structure whether you have 26 partners or three. Different formats for every partner, amounts with decimals, conditions written only in the email body: we have already dealt with and solved each of these. What is left for your staff is to review and press the issue button.

See more work on this topic under Data analysis.

See the other systems we use to run our own company under Internal operations automation.