When you have the slightest care about your money but you can’t hire an accountant just yet, you’ll likely use a personal finance software to keep your budget in check. If you’re lucky enough that your software can communicate directly with your bank then half of your monthly reconciling work is done already. However the rest of us aren’t that lucky – including myself. If you find yourself juggling CSV files whenever you need to update your financial records, I might have a solution for you.
I have a number of bank accounts and a few credit cards to go with them. Yes, it’s probably a good idea to consolidate these into one, but in the course of my life I needed to open some bank accounts because they offer a good deal but there are some medium and long-term commitments (e.g. installment payments and mortgage) that are a big hassle to modify, hence I still keep the old accounts around. Some of these accounts are in my home country and that makes it easier if I need to send money to myself for use there or make payments to people in that country. Moreover the Singapore government has a pretty small limit of the money that they guarantee, hence it’s probably useful to split the money to mitigate the risk of the bank going under.
Every month or so I reconcile the balances of these accounts with the records in my personal finance software. None of these banks supports Open Financial Exchange (OFX) or Direct Connect – those are methods for personal finance applications to talk directly to the banks’ computers via the Internet and tend to be the domain of US banks. However being half a world away from the land of the free and home of the brave, none of the financial institutions that I use supports these protocols. I have to make do with their “export to CSV” functions, which are supposed to provide my transaction data that I can use in other applications – most notably personal finance software.
Whenever I use the “Download to CSV” function from my bank’s website, the resulting file can’t be used directly by my personal finance software. The reason is that none of these banks follow the CSV standard. That is, every line must contain a transaction record except the first line which may be the header row. Also every value needs to be separated by a comma (hence the term comma separated values – CSV) or otherwise enclosed with quotation marks. Furthermore the number of values in every line needs to be the same and follows the header declared in the first line.
However what I receive from these banks “export to CSV” is often quite different. There are always multiple header lines that are not comma separated. Some have blank lines between the records for no reason at all. Even worse, some others have transaction records that are unevenly split in multiple lines – some transactions are in two lines whereas some others are expressed in one line. This tend to choke my personal finance software that expects to import CSVs in a standard format. Because of this every month I need to edit these banks’ data dumps and massage them into a standard CSV format.
After a while solving this problem manually, I grew wary and decided to invest a Sunday to write some programs that automatically does this for me. These scripts are to take in each account’s export dump for the month and converts it into a standard CSV format ready for import to my personal finance software. I’ve also made it open source so that you don’t need to write these scripts yourself and just use those scripts to help your monthly reconciliation. I name the project BankConverter and host it on Github.
As of this writing these are the banks and account types that the scripts support.
There’s nothing special why only these Singapore banks are supported – they are merely the ones that I need to reconcile the most. You’re welcome to add support for your own banks and contribute to the code repository.
Follow the following steps to install the scripts. These steps are meant for the Mac or other Unix derivatives
- Install R along with dplyr and lubridate packages
- Download the BankConverter project from Github
- Copy the scripts into a place in your PATH and activate the execute pemission.
Installing R and packages
BankConverter is written as a set of R scripts. R is a scripting language geared towards statistical computing. One of R’s strengths is dealing with tabular data and handling CSV files goes with it. Hence it’s pretty straightforward to write BankConverter in R.
You’ll need to go to download R from CRAN and follow the installation instruction from there. Please install the latest stable version of R, version 3.1.2 or newer. Optionally you can also download R Studio, which is the preferred graphical interface for R as of this writing. R Studio would be useful if you need to tweak these scripts to your own needs.
Then you probably going to need to install some optional R packages that are required by the script:
lubridate. To do this, simply start R from the Terminal and use the “install packages” command, which roughly look like the following command sequences:
$ R > install.packages("dplyr") > install.packages("lubridate")
Note that “$” indicates that you are in your shell whereas “>” shows that R is processing your commands instead. Don’t type “$” or “>” – instead type the commands that comes after it. You should see “bash” or “R” in your Terminal window’s tab bar title, respectively. When you’re done, press Ctrl-D to quit R.
If you are prompted to select which a CRAN mirror, you will need to configure the mirror in your R configuration file. Exit R (you can press the Ctrl-D key combination) and then type the following command in the Terminal:
cat options(repos=structure(c(CRAN="http://cran.stat.nus.edu.sg/"))) >> ~/.Rprofile
Then go back and try to install those two R packages again. The command above will set your default R mirror to one in Singapore (assuming that since you’re reading this, there’s a good chance that you also have a bank account there and probably located within the region). For further information on CRAN mirrors, please refer to this topic on Stack Overflow.
Just go to the BankConverter project on Github and then click the “Download ZIP” button. Extract the archive and locate these three R scripts inside:
csv-dbs.R– Massages DBS Savings CSV export into a standard CSV format file.
csv-ocbc.R– Massages OCBC 360 CSV export into a standard CSV format file.
csv-ocbc-frank.R– Massages OCBC Frank CSV export into a standard CSV format file.
Installing the Scripts
To install these R scripts, simply copy them into your system’s /usr/local/bin folder and then set the execute bit. Thanks to the magic of Unix scripting, you can leave out the “.R” extension which should save some typing later on as you use them. Open a Terminal window and have it start within the extracted BankConverter folder and then type in the following commands:
sudo bash cp csv-dbs.R /usr/local/bin/csv-dbs cp csv-ocbc.R /usr/local/bin/csv-ocbc cp csv-ocbc-360.R /usr/local/bin/csv-ocbc-360 cd /usr/local/bin chmod a+rx csv-dbs csv-ocbc csv-ocbc-360 exit
Note that the first command will require you to enter your password. You will also need to be an administrator in your system. If you are not an administrator, you can create a “
bin” folder inside your home directory and copy these scripts there. Note that you will need to add your own “bin” folder to
How to Use it
- Login to the bank’s (either DBS or OCBC) e-banking site.
- Locate the Download to CSV functionality and download a copy of your transaction history. This will be slightly different with each bank, but generally it’s somewhere near to where you view your account’s transaction history.
- Run the script from the Terminal, giving the file that you got from your bank as the first parameter and the output file as the second parameter. For example, if you just downloaded a CSV transaction history file from DBS, you can run the following command in Terminal:
csv-dbs ~/Downloads/input.csv ~/Downloads/output.csv
- Use your favorite personal finance software to import the resulting CSV file.
Please let me know what you think. I’d like to know whether these scripts help you and is there anything better that we can do. We’d also like to know how often do you reconcile your software and what personal finance software do you use.