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:
Then you can import your .OFX files in YNAB!
UPDATE 20160827: Added quotes for spaces in the file path, Thanks Jeroen in the comments!
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!
Hi Jasper!
ReplyDeleteI made a convert.bat file in Notepad, but it's not working.
Any tips on how to create a BAT file?
Kind regards,
Paul
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)..
DeleteSo 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!
Hi Paul,
ReplyDeleteWhen 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.
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?
DeleteCould be spaces in the path. I just slightly edited the code with quotes for compatibility with spaces:
Deletefor /r %%i in (in\*.sta) do (
echo %%~ni
java -jar mtconverter-0.01.jar "%%i" out\%%~ni.ofx
del "%%i"
)
Nice catch, thanks Jeroen!
DeleteCool post Jasper! It's great that we can use abn amro this way.
ReplyDeleteThanks for creating this Jasper!
ReplyDeleteFor bash users:
#!/bin/bash
for staFile in ./*.STA; do
echo $staFile
java -jar mtconverter-0.01.jar "$staFile" "out/$staFile.ofx"
done
This comment has been removed by the author.
ReplyDeleteSounds like you need programming skills to work around the ABN non-OFX/QFX transfer facility... :-(
ReplyDeleteThis is awesome, thx a lot!
ReplyDelete