System Requirements:
- Windows 95, 98, 98SE, Millennium, NT 4, 2000, XP, Vista, 7, 8, 8.1, 10
- Windows NT 4 Server, Windows 2000 Server, Windows Server 2003, 2008, 2008 R2, 2012, 2012 R2, 2016
- Windows Scripting Host 5.1 or higher
The Problem:
Having just moved banks, my previous provider offered downloadable QFX files which allowed me to import them into Microsoft Money. The new provider – Metro Bank UK – does not offer a QFX or QIF download service to automate the transaction import process. Instead that only offer a very limited snapshot view which downloads in a simplified CSV structure.
If like me, you are put out by the idea of having to manually enter your transactions into your finance package then this could help.
More Info
The Metro Bank UK CSV structure is extremely simple [as of September 2016]. The header format is as follows:
| Date | Reference | Transaction Type | Money In | Money Out |
The file does not contain sufficient information to permit the use of the QFX format, which is required to track the ledger date range and starting and end totals. While it is possible to track the date range, there is no information on the ledger start and end total to validate the import process against.
This means that the best that we can hope for is to use the far less intelligent QIF format. The principle issue that this causes is that your financial programme will likely attempt to import duplicates off of the file and you will need to be vigilant of controlling or editing the on-line bank export date range to minimise duplication. This is the case with Microsoft Money 2005 anyway.
The Fix
I have created a script that converts from the Metro Bank UK CSV download file and into the QIF format.
- No warranty is offered or implied for the use of this script. No liability is inferred for damages caused as a result of its use. Use it at your own risk
- You may use the script for free for personal use
- You may not redistribute this script. If you want to link to it, please link to this page.
Download: CsvToQif version 1.0.0 (2.57 KB)
Usage Instructions
You can use the script in one of two ways:
- Using the mouse, drag and drop the .CSV file onto the script file icon.
- Using the command prompt:
“<PathToFile>\CsvToQif.vbs” “<PathToFile>\Transaction_24.09.2016.csv”
e.g.
“C:\Users\MyName\Documents\QiFConverter\CsvToQif.vbs” “C:\Users\MyName\Desktop\Transaction_24.09.2016.csv”
In both cases, after a few moment, a .QIF file of the same name will appear in the same folder. Hit F5 to refresh if you do not see it. Import it either by selecting the import function in your finance program or if applicable double click the QIF file and follow the on-screen instructions.
If you want to use it with a different CSV layout, for example your own file or that of a different online bank provider. You will need to adjust the zero-based array indexes at around line 148 to reflect the correct columns.
For example, arrCsv(0) is the first column in the CSV, arrCsv(4) is the fifth column in the CSV and so on.