I do the bookkeeping for my companies myself. An old habit, started when I did the necessary training to be able to poke around in a client’s financial system over 20 years ago.
For my companies I’ve built a spreadsheet that does all the necessary things for me.
My banks provide statements in MT940 format. They assume you have some way to import them into your bookkeeping system. As I run my own spreadsheet I don’t have import functionality for that. Doing the third quarter bookkeeping recently I realised it would be good to automate adding the bank transactions. Yesterday I wrote an Applescript that takes a MT940 file and transforms it into CSV, which I can easily add to my spreadsheet.
I export the MT940 file for the period after my last download from the online banking environment, and save it as .txt. The scripts takes it, and results in a .csv file of the same name.
MT940 presents the transactions per day like
mt940 source file
The lines :60F: and :62F: show the balance at the start and end. In between are the transactions.
Every :61: line contains an amount debited or credited. The first character after an amount is N.
These lines are followed by :86: lines that provide the description. This description can be multiple lines long, with the actual description shown between /NAME/ and /REMI/.
In Applescript I read through the lines, and create a CSV line for every transaction in the way I need it in my spreadsheet (in the form of date;month;description;amount debit;amount credit), and adding a final line of the balance at the start and end of all transactions (across the entire period in the source file). That last bit I can use as a control.
csv output from the mt940 source file above
The csv I can easily import in my spreadsheet and then copy over into the bank account journal.
Now I am curious to see if my other bank uses MT940 in the same way, or has implemented the standard differently. (I learned from my EDIFACT days that even if things are standardised there will be big differences between implementations due to interpretation and mapping internal processes and data structures on a standard.)
The applescript is available on GitHub.