ABN Amro bank statement export to .OFX

I recently started to use YNAB 4 (You Need A Budget) software to manage/monitor my spendings. You can find it here (referral + $6 discount alert). Great and easy to use software to budget your expenses. But for Dutch users I found a big disadvantage: you cannot import files from ABN Amro.

YNAB only imports .OFX (Money) or .QFX (Quicken) and to some extend (they say they do not prefer it) .QIV or .CSV files. On the other end, ABN Amro only exports PDF, Excel (.xls), MT904 (.STA), TXT (.TAB, tab delimited) and CAMT. 053 formats.

To get your transactions into YNAB, you need to download a MT904 converter. You can find it here: http://sourceforge.net/projects/mtconverter/ You need to have Java installed for it to work (www.java.com).

To use the converter, I created a batch file with the following lines:
for /r %%i in (in\*.sta) do (
    echo %%~ni
    java -jar mtconverter-0.01.jar "%%i" out\%%~ni.ofx
    del %%i
)
Save it as convert.bat in the same directory as the .jar file. In this directory create two new directories called 'in' and 'out'. Copy the .STA files from ABN Amro in the 'in' folder and doubleclick convert.bat. The program finds all the .STA files in the 'in' folder, creates a corresponding .OFX file in the 'out' folder and deletes the .STA file from the 'in' folder.

Then you can import your .OFX files in YNAB!

UPDATE 20160827: Added quotes for spaces in the file path, Thanks Jeroen in the comments!

Comments

  1. Paul2/2/16 20:01

    Hi Jasper!

    I made a convert.bat file in Notepad, but it's not working.
    Any tips on how to create a BAT file?

    Kind regards,
    Paul

    ReplyDelete
    Replies
    1. Jasper Timmer2/2/16 20:16

      I believe my previous message was not a reply to your comment (I never got a comment before, so I have to get used to this haha)..

      So please find one reply on the blog.

      Another tip is to make sure you have the .bat file and the .jar file in the same folder, and you created the 'in' and 'out' directories in teh same folder, if anything is missing, it might not work.

      And make sure you have java installed, else the converter won't work.

      If nothing helps, comment again with what does happen!

      Delete
  2. Jasper Timmer2/2/16 20:12

    Hi Paul,
    When you name a file 'convert.bat' in notepad and press save, notepad adds .txt to it: convert.bat.txt. This is wrong. You can prevent this by putting the filename in doublequotes in the filenamebox: "convert.bat" and put the file type to 'any file type' option. I hope this helps.

    By the way, the offline YNAB is going to retire soon, they have a new cloud version. Just for your info. They claim it can import more types of file than the offline version.

    ReplyDelete
    Replies
    1. Paul2/2/16 21:18

      Actually did that already, so it's something else that's messing it up. I do see a CMD window pop-up when I double click the BAT file. All the files (and directories) are in the same/correct folder. Could it be a Windows 10 issue?

      Delete
    2. Unknown25/8/16 20:19

      Could be spaces in the path. I just slightly edited the code with quotes for compatibility with spaces:
      for /r %%i in (in\*.sta) do (
      echo %%~ni
      java -jar mtconverter-0.01.jar "%%i" out\%%~ni.ofx
      del "%%i"
      )

      Delete
    3. Jasper Timmer27/8/16 10:43

      Nice catch, thanks Jeroen!

      Delete
  3. Unknown19/8/16 16:59

    Cool post Jasper! It's great that we can use abn amro this way.

    ReplyDelete
  4. Unknown2/1/17 15:29

    Thanks for creating this Jasper!

    For bash users:

    #!/bin/bash
    for staFile in ./*.STA; do
    echo $staFile
    java -jar mtconverter-0.01.jar "$staFile" "out/$staFile.ofx"
    done

    ReplyDelete
  5. Anonymous10/3/19 12:32

    This comment has been removed by the author.

    ReplyDelete
  6. Anonymous10/3/19 12:33

    Sounds like you need programming skills to work around the ABN non-OFX/QFX transfer facility... :-(

    ReplyDelete
  7. FinnFiana24/8/19 12:18

    This is awesome, thx a lot!

    ReplyDelete

Post a Comment

Popular posts from this blog

Microk8s cluster on a homelab (Proxmox) local network with a Fritzbox and Metallb

Homelab/server with Proxmox connected to my Fritzbox 7590 (XS4ALL NL) configured on home network 192.168.178.0/24  Inside Proxmox I have 3 vms (ubuntu server 20.04 LTS) that I named k8s-{1,2,3} During setup of the Ubuntu server vms I selected install openSSH and microk8s In the fritzbox I selected the option to give proxmox host and k8s-1,2,3 always the same IP After boot of servers: On k8s-1 I did sudo microk8s add-node and ran the output on k8s-2, then I repeated that for k8s-3. I enabled addons on k8s-1:  sudo microk8s enable dns metallb It will ask for an IP range, I opted for 192.168.100.0/24 Then in the Fritzbox, I went to network, network settings, more network settings, static routing tables I added the route 192.168.100.0, 255.255.255.0, 192.168.178.x (ip of k8s-1), active, and I saved it. Now I could test the Metallb: wget https://raw.githubusercontent.com/charmed-kubernetes/metallb-operator/master/docs/example-microbot-lb.yaml kubectl apply -f example-microbot-lb.yaml kubec
Read more

Using Flying Saucer and iText in Java to convert XHTML to PDF

The situation Where I work, we were generating reports in XHTML, for printing. The styling should be easily configurable. The problem with this approach is the cross-browser look of your report. IE and Chrome print two completely different reports, so to speak. To prevent the difference between browsers, and to re-use the existing report generation, I decided I needed to render the print at the serverside. So I had html, and I wanted it to look the same on every computer. Pdf is a good medium for this purpose, so I needed a html to pdf library, for our Java system. I first tried iText by itself, but this did not apply the css. Browsing the web a bit further I found the combo Flying Saucer and iText , and this was a winning combination for us. Flying Saucer is a Java library that renders XHTML/XML + CSS to screen/image/PDF. For PDF there is a dependency on iText, a library to create pdf files. The code In this blogpost I provide some codesnippets, not a full working example.
Read more