Why Reconciling Your Bank Accounts is Painful

It’s that time of the month for me to reconcile my financials. I’m using MoneyWell as my personal finance application and it has been doing an OK job so far. I’ve been doing this for quite some time now, originally with Quicken for Windows and its companion app for PalmOS.

One of my big complaints that haven’t been solved by these personal finance applications – Quicken or MoneyWell – is reconciling transactions with the banks that I use. These applications are programmed with US banks in mind and rarely makes it easy to use financial institutions outside the US.

The Problem

Almost all personal finance applications that I see expects a QIF import or need to talk to the bank’s Internet Service directly via DirectConnect or OFX protocols. That’s fine if your bank provides these interfaces. What’s bad is that I haven’t seen any bank that offer these. Neither DBS/POSB (Singapore) nor BCA (Indonesia) – the two banks that I use – supports DirectConnect nor provides QIF/OFX export of transaction data. Perhaps I was born in the wrong side of the world?

DBS Bank’s pseudo-CSV format

DBS Bank provides CSV export of transaction history of savings account. They say it’s CSV but it’s not really CSV. The screenshot below shows DBS’ CSV dump when imported to Microsoft Excel. As you can see, it’s not a pure CSV because:

  • There are other headers besides just the column header – range A1:B4.
  • Rows 5 and 7 are empty – CSV aren’t supposed to have empty rows.

DBS Bank's CSV output - shown in Excel

Because of this data format problem, I had to manually clean up all the CSV “errors” of the file that DBS provides before feeding it to MoneyWell. What’s worse is that I had to do it every time I reconcile my savings account.

What’s worse is that DBS doesn’t provide any plain-text export of my credit card transaction history. Thus I had to manually enter every transaction that I made – which is quite a bummer. In this respect, my credit card doesn’t have any value-add than the cash that I have in the bank and I’d rather use NETS debit card instead so that the transaction shows up in my savings account’s CSV dump.

Getting transaction data from BCA

Bank Central Asia (BCA) is even worse such that it doesn’t have any kind of CSV export at all. Because of this I had to save the web page and key-in every transaction in that web page, manually copying each line item from the table. This is a real bummer.

BCA Account Statement

MoneyWell’s CSV Import

MoneyWell provides a CSV import which does help a bit. But a major issue is that it needs to be set-up properly and can’t “remember” the setting that I’ve made. That is since different banks have their own way of representing data MoneyWell should be able to retain such “import profile” for each bank. But currently it does not, which can cause quite a problem as I tend to forget which column in the CSV maps to which field in MoneyWell and cause the fields to be mapped inconsistently.

 

MoneyWell CSV Import Dialog

 

Proposed Solution

Rather than regretting the sadness of in the unsupported part of the world, I have a solution in mind. It’s an app that runs on your computer that does four things:

  1. Helps you login to your bank’s Internet Banking site.
  2. Captures the web page (or export file) that contains your transaction history.
  3. Converts that data into a format that your favorite personal finance application can read and launch the app to import your transaction history.

The app sits between your Internet Banking site and manages the data conversion between what your bank provides with what your personal finance app expects. It will even be more useful if you have multiple accounts in different banks since the application will remember each bank’s output format and will convert them appropriately. This will save you about two hours per month for each account since you no longer need to key-in every transaction from the bank’s site or massage the data yourself and fiddle with your financial app’s import settings.

Bank Data Converter

You can see an illustration on how it work in the diagram above. The Bank Data Converter app (name yet to be finalized) have a web browser that you use to login to your bank and capture your transaction data from the bank’s website. Then the app converts the data and writes out a data file specially formatted for your favorite personal finance application. This data file is already pre-configured so that the app can load it directly into its database without requiring much work from you.

Why not a web app? Think of it: do you feel safe to trust your financial records to small indies like us? Not only that, _we_ don’t feel safe of keeping other people’s transaction data. We’re going to have to harden our servers considerably – that amounts to a lot of costs. Not to mention the lawsuits that may happen should – heaven forbid – we accidentally leak your bank records. If this is made a web app, your privacy may be compromised as your financial data is funneled through a 3rd party server (which is the app developer’s server).

Having the app running on your computer solves all these security issues. Your data is safe since it never leaves your computer in the first place. No, we won’t transmit your bank’s data file to us without your consent – we respect your privacy and really don’t want to be entangled in that kind of issues.

Now here’s the million-dollar question: do you have this problem? Is your #1 cause that you slog in balancing your checkbooks is because it’s just pure drudgery to get the data into your finance app? Please let us know in the comments and we’ll work together to solve your problem.

Tags: , , , , , , , , , ,

  • http://twitter.com/kevinhoctor Kevin Hoctor

    MoneyWell 2.1 allows you to save different delimited file import formats and remembers which one you used last for each account. This should save you a bit of trouble with importing. This update should be available in early July, 2012.

  • basilsalad

    Thanks – I’ll give it a spin and see if it’s more seamless.

  • Ren Nakanote

    Here in US, I was turned on to Mint.com, a product of intuit.  However, they didn’t seem to do very well reconciling updates, a lot of errors and glitches as well.  I pretty much gave up on them and laugh when I see the email reminders and updates.  

    Will your system be specifically for Singapore and Indonesian markets?

  • http://arifandi.com/ Arifandi

    This would be great for BCA user or any other non standard csv bank records. Expand for windows user, great marketing and advertising you have a winner here.

  • Deni Kurniawan

    Use google refine to clean up. It’ll be better if someone create webservice for this. :-)

%d bloggers like this: