This is another pre-accounting post. Last time I talked about dealing with international invoices in Google Sheets.
This post is about processing credit card statements efficiently.
What do I mean by “processing credit card statements”?
The task at hand here is to compare the expenses you have in 1 table in a sheet, with the statements from the credit card expenses statement.
Why would you do this?
If you are doing pre-accounting tasks i.e. gathering all docs for your bookkeeper, this helps you to gather your documents.
I will look at every line item and then either validate that I already have the invoice, or be reminded to find the invoice.
The role of these documents vs transactions in double-entry bookkeeping
Your credit card has a final value that gets removed from your business account when it’s time to settle the bill. Every time you make a purchase with a business credit card, a line item gets added to the statement.
Your accountant will have to match the line items against the corresponding documents to have supporting documentation when doing the quarterly VAT return.
If you accidentally make a private purchase, this will have to be marked so a private purchase doesn’t end up in your business expenses.
The problem
My accountant has complained in the past that I didn’t find every document, and then I was presented with the dreaded “wachtrekening”, a document that lists all the missing documents.
This list always contained 10-15 documents… after a while, I got complaints from my bookkeeper about my accuracy when doing the pre-accounting, so I decided to be more diligent about it.
The process
Every month, I get my business credit card statements from KBC via Doccle. There is a feature within Doccle where you can add an accountant to a connection. Within that feature, you can add an e-mail address. I added my own e-mail address, so this way, I get the statements via e-mail and do not have to enter Doccle. This is what they look like:
This old document contains only 3 transactions, but at this point in time for Obra Studio, I have to process 30-40 transactions per month.
To make sure that I have an invoice for every line item on the statement, I started adding the line items from the credit card statement to what I call my “shadow bookkeeping” sheet.
This is a Google Sheet that contains income and expenses.
As a first step, I need the data. I’ve saved this prompt, which I put in ChatGPT along with the document:
Please give me a CSV of this PDF with the following columns.
date of transaction
date of settlement
description
amount in EURChatGPT then generates a CSV:
After downloading this CSV, what you’ll want to do is append the current sheet to your Google doc, via the “Import file” dialog.
This leads to having the entries in your Google doc:
The next step is unfortunately a manual step for me: look up the different documents and compare them against the statements.
There’s more things to be automated here: finding the documents in my mailbox, and match them automatically.
A task for an AI agent, maybe?
If you have any workflow tips, feel free to share them in the comments.