Behind on accounting: Speeding it up with python and sqlite
Well, as usual, I’m behind on last years accounting and tax time is rapidly approaching. I finally sat down and at least sorted my receipts saved from last year. Now I’m faced with the horrible data entry phase.
Now, I was able to download all the transaction records from the bank. That’s all well and good, but it doesn’t have any of the information I really require except the amounts. Even the payees are primarily transaction number that are barely readable.
As usual, I decided to see what’s out there and available to help with taking a QIF file and make it useful. I found one or two things for the Mac, but they were pricey for what they do. However, I suddenly remembered that last year I made a solution to do exactly what I needed (How could I have forgotten??).
If you poke around the net, you can find a python script to read and write QIF files (thanks to whoever wrote that!). Thus, that is part one of the solution, the ability to read and write QIF files.
Part 2 of the solution is SQLite. Sqlite is a great low-overhead SQL database system. Works great with python. In sqlite, then you can build a list of expected transaction information. My table is fairly simple:
Name: the name of the transaction as I’d like it to appear
qifname: the partial name of the transaction as it appears in my QIF file. For example, Netflix appears as “WWW.NETFLI”
Category: the optional assignment of a category if it’s fairly consistent.
Part 3 is a custom python script that incorporates the QIF read and writes, checks the database for each transaction against the SQLite database for the qifname, if it’s found, replaces the transaction name with name and addes the category when present.
Simple, free, and powerful. Hours of work saved.