gbonnema/rabo2ofx

A python script to convert Dutch Rabobank CSV files to OFX files for processing in GnuCash.

Rabo2ofx

This program converts the Dutch rabo csv file for non-business customers to the financial records of OFX for GnuCash or for HomeBank. The csv file is available for download for each customer.

I release this software under Copyright and with the user license of GPL version 3 of which the text accompanies the software.

Use

Open a terminal in the directory where you downloaded the csv file. Then use the program on that file. It will create an OFX formatted file in a subdirectory with the same filename and extension of csv replaced by ofx. The directory can be explicitly specified through the argument -d or --directory or it has ofx as default. For HomeBank it has ofx_hb as default. Example (GnuCash):

           Output to ofx (GnuCash version)

TRANSACTIONS: 248
IN:           2018-008-transactions-30-12-to-26-09.csv
OUT:          2018-008-transactions-30-12-to-26-09.ofx

	accountnumber     processed  skip   sum
	NL01RABO0123456789        1    16    17
	NL02RABO9876543210      231     0   231
	-

The output file is an OFX compliant xml file that GnuCash or HomeBank can process.

Difference between GnuCash and HomeBank

HomeBank needs all transactions, including the "internal transfers". It then concludes which transactions are internal transfers.

GnuCash will not automatically recognize internal transfers and these transactions will be included into the system twice. As this is undesirable, for GnuCash the system automatically skips transactions for the subordinate accounts to or from the main accounts.

Transfers: accounts in config

Remark: only for GnuCash

Since version 2.10 the program uses a config file denoting accounts to be downloaded. An example file is available. Be sure to register your accounts in the config.

The files you download will have transactions for both accounts. Where a transfer from config.account1 to config.account2 is present, there will be 2 transactions:

- One from the perspective of config.account1
- One from the perspective of config.account2

Without measures, these transactions will be registered in your ofx file twice, as different transactions, causing havoc in your bookkeeping systems.

To prevent double transactions, for all transfers between config.account1 and config.account2, the program will only translate the transactions from the viewpoint of the first account in config. This is indepedent of whether they are in the same download or not.

It means that transfers between config.account1 and config.account2 will only show up if and when config.account1 is downloaded (config.account1 precedes config.account2 in the config file). In a file with only config.account2, the transfers will be skipped, i.e. not transferred to the ofx file.

Be sure to create a config file. An example file is present in the distribution. Remember: order is important.

Transfers: accounts not in config

Remark: only for GnuCash

For accounts not in config, if they are in the same download file, transfers will only produce one of two transfer transactions. These unknown accounts will never transfer to a known config account. For multiple unknown accounts, transfers between the unknown acounts will register only the first account processed.

This is a risk, and thus the program warns if an account is in the download file, that was not in the config file. Please make sure to always have the accounts you download in the config file.

Information and warnings

• The program was developed for a checking account.

• The program can process multiple accounts per file. If processing gets slow, you could choose to download only one account per file. It reads the input once per account. But beware of transfers.

• The FITID up until 2018 is a construction of transaction data (amount, date etc). From 2018 the Rabobank starts using a serialnumber that is unique per account.

• The file assumes each account is a checking account. This may not be true for all your accounts, but the program has no way of knowing this.

• The default directory is ofx. You can specify a different directory in the argument '-d'. The default for HomeBank is ofx_hb.

• Some of the OFX entered data is fake, like signon-data and at the end of each account balance amount or balance date. There is no question of any logon session going on, but the OFX file needs the info.

Date semantics: why we use interestdate in stead of date

The Rabo has decided to present non-business users with an unusual and unnecessary problem. For business users the field "datum" (date) is filled with "boekdatum" (the bookingdate). This is what one expects. However, for non-business users the Rabo fills "datum" (date) with "verwerkingsdatum" (the date the Rabo processed the transaction). These dates often differ and is not what one expects or would want for a bookkeeping system.

There is no rational explanation for this difference, but there are consequences for users with a non-business account. The first consequence is that selection is no longer based on the bookingdate, but on the processing date. For end of year payments, the processingdate is usually the next year. So selecting payments for a strict period (month, quarter, year), no longer works as expected.

The second consequence is that your bookkeeping will show the wrong date had I used 'datum' instead of 'rentedatum' (interestdate). Again, think of end of year payments. The bookkeeping program would attribute them to the wrong year.

For that reason I use interestdate in stead of date. It is not what I want and it does not make sense, but the Rabo forces this anomaly. I have contacted them through email april 2018. I have saved the email correspondence to date as a pdf file but have had no further response, neither a correction of the csv-file contents. I don't expect the Rabo bank to care enough to change anything unless more people complain or the problem becomes public. Public image sometimes can sway corporations where customers can not.

Development

If anyone has remarks, please create an issue on the github repository gbonnema/rabo2ofx. If anyone feels like improving the code, please fork the repo and issue a pull request.

Installing on Linux

I usually just copy the python script rabo2ofx.py to /usr/local/bin and the man page in the man subdirectory rabo2ofx.1 to /usr/local/man/man1 and then I am done with it.

There is no need to compile or build anything as python is a script interpreter.

Installing on Windows

I have no idea whether this program will run on ms windows. It should, but I haven't tested it. Also, I guess the man page is irrelevant to ms-windows.

If you are running linux on ms-windows, in theory you should be able to use the linux instruction.

Sept 2019, Guus Bonnema.

rabo2ofx's Issues

ImportError: No module named configparser

On ubuntu 20.04 running the script throws this error

ImportError: No module named configparser

Processing/value date switch

Testing the script I ran into a problem with the reconciliation. In my current workflow I import a month of transactions and reconcile the account to match the account statement of the Rabobank. This runs smoothly. After import the new balance amount always equals the ending balance of the statement. This workflow is in line with the format description of the Rabobank and their email response (01-datum-20180410-Uw-klacht.txt). It may not be 100% in line with accounting principles, but it works very well for me.

However, the reconciliation of my test run failed because there was a transaction with a different processing and value date. As documented, this script uses the value date (interest date) instead of the processing date. This unfortunately prevents matching ending balances in case these dates are in separate months.

It would be great if this script can be used to reconcile imports per month as described above. Is it possible to add an option to this script that enables the user to choose the processing date or the value/interest date as the date for the transaction in the ofx file (<DTPOSTED>)?

Colons followed by spaces

Many thanks for this python script, @gbonnema. Great work.
I have been testing it (in Windows 10), in order to kill my Excel-copy-paste workflow for importing Rabo transactions. I have a question about the second booking line as a result of the import. E.g.:
OFX ext. info: |Trans type:Transfer|Memo:Studietoelage Xxxx + Yyyy
This line is not recognized by GnuCash. Could it be caused by the fact that there is no space between Memo: and Studietoelage?
Is it possible to add spaces after the colons by default, so that GnuCash recognizes the imported bookings based on previous imports?

Conflict between main and savings account, when downloading both

Problem
The problem occurs when you download your main account and your savings account transactions and process them in the same bookkeeping program. Chances are your bookkeeping program will register the transfers between you main and savings account twice. The consequence is an incorrect status of both the main and the savings account. For my account management program (GnuCash) this is certainly true. I would like to hear from other users how their account management program processes these transfers.

Solution
There is no solution that would satisfy everyone and is watertight in its recognition of a transfer.

Having said that, there is a way to guess, but it depends on both transactions being available in the same file.

So:

• either both transactions are downloaded simultaneously and end up in the same file,
• or we store all transactions in a database, that can produce all ofx records and thus recognize transfers.
• or we exclude transfers from the download file (manually).

Except for the cumbersome manual solution, I do not know a simple way to solve this automatically. Both solutions require an overhaul and saving transactions in a database make for a much more complex program. Also it gets more dependencies.

The manual solution in the case of a savings account implies downloading only interest and bank cost transactions and leaving all other transactions out. For 2 different main accounts it may be more complex.

Rabo
The rabo could simply solve it, because the Rabo knows it is a transfer. Especially when transferring from main to savings account and back, they could prevent misunderstandings by making sure I can recognize transfers. Currently they don't. I will bring this issue to their attention, but don't expect them to respond any time soon with any significant response.

When dealing with the Rabo most responses include: "we have sent your request to the IT department" and "sorry, we do not track progress on the issue".

Decision
I am still to decide what to do.