Script to convert from an online bank downloadable CSV file to Microsoft Money to Quicken compatible QIF (Specifically Metro Bank)

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:

  1. Using the mouse, drag and drop the .CSV file onto the script file icon.
  2. Using the command prompt:
    “<PathToFile>\CsvToQif.vbs” “<PathToFile>\Transaction_24.09.2016.csv”
    “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.

Adafruit USB + Serial RGB Backlight Character LCD Backpack VBScript API

System Requirements:

  • Adafruit USB Serial RGB Backlight Character LCD Backpack

The Problem:

I recently needed a fast, cheap and modular way to output small amounts of information from a headless Windows 7 appliance, something that didn’t require a large amount of electrical engineering work or assembly while at the same time working over USB.

I accidentally stumbled upon a product by Adafruit, who seem to be tightly aligned with the Raspberry Pi/Arduino, however USB is USB which means that making it cooperate with Win32 wouldn’t be particularly challenging.

Being the lazy person that I am, I never much care for the idea of having to repeatedly type out control commands through a command line parser to get what I want and quite frankly, getting a NT Command Prompt to reliably pass anything out to a Serial Port is more or less a futile exercise unless you are using Plink or replace the shell entirely with something more robust.

My solution was quite simple, create a O-O VBScript API for running it via CScript that can in turn be called from the NT command line where required.

Buy Adafruit USB Serial RGB Backlight Character LCD Backpack, and other Adafruit components or & help support this site.


More Info

This API does what I require it to do. I have implemented all of the main command syntax from the Adafruit documentation (links below). It is classful and self contained. As long as you can create a FileSystemObject and can find the COM Port, you can make use of it.

[Update 25/04/2019] You can now obtain this code directly from Github.

View: AdafruitUsbSerial on GitHub

' AdafruitUsbSerial Application Programming Interface v1.0.4

' © C:Amie | 1996 - 2014

' Not for commercial reproduction without the express permission of the author

' No warranty is offered or implied as a result of downloading or using this APIClass AdafruitUsbSerialprivate m_ForReadingprivate m_SCREEN_OFF

private m_SCREEN_ON

private m_AUTO_SCROLL_ON


private m_CLEAR_SCREEN








private m_SET_BLINK_ON

private m_SET_BLINK_OFF

private m_SET_RGB

private m_SET_CONTRAST

private m_SET_BRIGHTNESSprivate m_iPortNumber

private m_byteCharacterLength

private m_bolDebug

private m_bolAutoScroll

private m_bolUnderlineCursor

private m_bolBlinkCursorprivate m_fso private

sub Class_Initialize
m_ForReading = 1
m_SCREEN_OFF = chr(254) & chr(70)
m_SCREEN_ON = chr(254) & chr(66)
m_AUTO_SCROLL_ON = chr(254) & chr(81)
m_AUTO_SCROLL_OFF = chr(254) & chr(82)
m_CLEAR_SCREEN = chr(254) & chr(88)
m_SET_STARTUP_SPLASH = chr(254) & chr(64)
m_SET_CURSOR_POSITION = chr(254) & chr(71)
m_SET_CURSOR_HOME = chr(254) & chr(72)
m_SET_CURSOR_BACK = chr(254) & chr(76)
m_SET_CURSOR_FORWARD = chr(254) & chr(77)
m_SET_UNDERLINE_ON = chr(254) & chr(74)
m_SET_UNDERLINE_OFF = chr(254) & chr(75)
m_SET_BLINK_ON = chr(254) & chr(83)
m_SET_BLINK_OFF = chr(254) & chr(84)
m_SET_RGB = chr(254) & chr(208)
m_SET_CONTRAST = chr(254) & chr(80)
m_SET_BRIGHTNESS = chr(254) & chr(153)

m_iPortNumber = 1
m_byteCharacterLength = 32
m_bolDebug = false
m_bolAutoScroll = true
m_bolUnderlineCursor = false
m_bolBlinkCursor = false

set m_fso = CreateObject("Scripting.FileSystemObject")
end sub

private sub Class_Terminate
set m_fso = nothing
end sub

public property get PortNumber
PortNumber = m_iPortNumber
end property

public property let PortNumber(ByRef iIn)
m_iPortNumber = iIn
end property

public property get CharacterLength
CharacterLength = m_byteCharacterLength
end property

public property let CharacterLength(ByRef byteIn)
m_byteCharacterLength = byteIn
end property

public property get Debug()
Debug = m_bolDebug
end property

public property let Debug(ByRef bolIn)
m_bolDebug = bolIn
end property

public property get AutoScroll()
AutoScroll = m_bolAutoScroll
end property

public property let AutoScroll(ByRef bolIn)
if (bolIn) then
end if
m_bolAutoScroll = bolIn
end property

public property get Underline()
Underline = m_bolUnderlineCursor
end property

public property let Underline(ByRef bolIn)
if (bolIn) then
end if
m_bolUnderlineCursor = bolIn
end property

public property get Blink()
Blink = m_bolBlinkCursor
end property

public property let Blink(ByRef bolIn)
if (bolIn) then
end if
m_bolBlinkCursor = bolIn
end property

public sub clearScreen()
end sub public sub screenOn()
end sub

public sub screenOff()
end sub

public sub changeSplashScreen(ByVal strIn)
strIn = Left(strIn, m_byteCharacters)
' Force it to be exactly 32 characters by padding
do while (Len(strIn) < m_byteCharacters)
strIn = (strIn & " ")
end sub

public sub backlight(ByRef byteR, ByRef byteG, ByRef byteB)
end sub

' Valid Range 0 - 255. Values between 180 and 220 are suggested
public sub contrast(ByRef byteIn)
end sub

' Valid Range 0 - 255.
public sub brightness(ByRef byteIn)
end sub

public sub setCursorPosition(ByRef iX, ByRef iY)
end sub

public sub home()
end sub

public sub back()
end sub

public sub goBack(ByRef iIn)
Dim i
for i = 1 to iIn
end sub

public sub forward()
end sub

public sub goForward(ByRef iIn)
Dim i
for i = 1 to iIn
end sub

public sub delete()
me.write(" ")
end sub

public sub write(ByRef strIn)
Dim serialWriter
if (me.Debug) then
wscript.echo strIn
end if
set serialWriter = m_fso.CreateTextFile("COM" & m_iPortNumber & ":",True)
set serialWriter = nothing
end sub

public sub teletype(ByRef strIn, ByRef iDelayMs)
Dim i
Dim iLen
iLen = Len(strIn)
for i = 1 to iLen
me.write(Mid(strIn, i, 1))
end sub

public function testComPort(ByRef byteNumber)
Dim serialWriter
if (me.Debug) then
wscript.echo "Attempting communications with COM" & byteNumber
end if
On Error Resume Next
set serialWriter = m_fso.CreateTextFile("COM" & byteNumber & ":",True)
set serialWriter = nothing
if (err.number = 0) then
testComPort = true
testComPort = false
end if
On Error Goto 0
end function

End Class

Copy it into your VBScript project file or into a dedicated class file and include it. Once it is in scope, the example below shows a general usage pattern for the main API.

It is recommended that all projects include and set the first 8 lines shown below, just so that you can ensure that you are tuning your project in the way that you want it. The remainder of the code shows examples of how to use the functions.

As a result of the USB driver allocating COM ports in a fairly dynamic way under Win32, you cannot expect to hard code your COM Port inside the project – particularly if the physical USB port that the backpack is connected to changes. Consequently, you can use testComPort() to attempt to locate the correct port as shown below. The function will terminate on the first port that it finds with an active serial output line available, if you have multiple active serial ports available on your project, the function may find the wrong port.

Finally, Adafruit recommends – at a minimum -adding a 10 millisecond delay between each command, which is not shown below. You should use WScript.Sleep(10) to achieve similar under VBScript. If you don’t, everything shown below with the exception of the executing of the Teletype macro will occur in well under a second.

Dim usbSerial

set usbSerial = new AdafruitUsbSerial

    usbSerial.PortNumber = 3              ' Set to COM3

    usbSerial.Debug = true                ' Inputs will be written back to WScript

    usbSerial.CharacterLength = 32        ' 32 is the default

    usbSerial.AutoScroll = true           ' Enable/Disable Auto Scroll

    usbSerial.Underline = true            ' Enable Cursor Underline

    usbSerial.Blink = true                ' Enable Cursor Blink' Find the first live COM Port if you don't know where it is

Dim iComPort

for iComPort = 1 to 30

    if (usbSerial.testComPort(iComPort)) then

        usbSerial.PortNumber = iComPort

        Exit For

    end if

next' Write Text

usbSerial.write("some text")' Write on both lines

usbSerial.write("line one" & vblf & "line two")' Clear the screen

usbSerial.clearScreen()' Screen Off


' Screen On

' Change the Backlight Colour
call usbSerial.backlight(255, 0, 255) ' Sets the RGB values (Fuchsia in this case)

' Set the screen brightness
usbSerial.brightness(180) ' 0 - 255

' Set the screen contrast
usbSerial.contrast(180) ' 0 - 255

' Set the Cursor Position
usbSerial.home()                  ' Moves to character 1, row 1
usbSerial.back()                  ' Moves the cursor back 1 character
usbSerial.forward()               ' Moves the cursor forward 1 character
usbSerial.goBack(5)               ' Steps the cursor back 5 characters
usbSerial.goForward(6)            ' Progresses the cursor forward 6 characters
usbSerial.setCursorPosition(5,1)  ' Sets the cursor to Character 5 on Row 1
usbSerial.delete()                ' Moves the cursor back 1 and clears the previous character

' Teletype (Macro)
call usbSerial.teletype("this will teletype out", 100) ' Write the text, with a 0.1 second character delay

' Change the Adafruit Splash Screen (Auto truncated/padded to usbSerial.CharacterLength)
usbSerial.changeSplashScreen("This is a splash screen message")

' Clean up and free resources
set usbSerial = nothing

Thanks to a structured API it is as easy as that!

View: Adafruit: Command Reference
View: Adafruit: Sending Text

See Also

View: Adafruit

Buy Adafruit USB Serial RGB Backlight Character LCD Backpack, and other Adafruit components from Amazon & help support this site:

Exploration of which request headers you need, should and cannot set when using MsXml2.XmlHttp, Microsoft.XmlHttp and MsXml2.ServerXmlHttp

System Requirements:

  • Windows NT 4.0 Server SP4+
  • Windows Server 2000
  • Windows Server 2003
  • Windows Server 2008, R2
  • Windows Server 2012, R2
  • Windows 2000 Professional
  • Windows XP
  • Windows Vista
  • Windows 7
  • Windows 8, 8.1
  • IIS 4.0, 5.0, 5.1, 6.0, 7.0, 7.5, 8.0
  • ASP 3.0 (Classic)
  • VBScript

The Problem:

I was browsing around the web earlier looking for inspiration on making PowerShell send asynchronous XmlHttp requests when I came across a block of VBScript examples. In them, I observed that the coder was religiously attempting to set the Content-Length request header, something that when using MsXml I don’t usually do – it’s a different story with PHP.

Equally, I observed that the programmer was using Len() to define the length of the content body for text, but ASP usually operates using an internal UTF-16 encoding to represent strings, thus does it follow that Len() will give the correct value?

So I thought that I would explore the issue to see for my own amusement, what was actually going on.

More Info

Let us take a standard code block to use for our tests and use the services of the good people at for a public message echo system.

Option Explicit
Dim x
Dim strRequest : strRequest = "a=1&b=2&c=3" ' <- 11 charactersset x = CreateObject("MsXml2.XmlHttp")
call"POST", "", false)
call x.setRequestHeader("Content-Length", 11)
call x.setRequestHeader("Accept-Language", "EN-GB")
call x.setRequestHeader("Connection", "keep-alive")x.send(strRequest)

The above gives us a boiler plate for what we believe the correct header should looklike written in VBScript. When executed, HttpBin responds with:

"args": {},
"data": "a=1&b=2&c=3",
"files": {},
"form": {},
"headers": {
"Accept": "*/*",
"Accept-Encoding": "gzip, deflate",
"Accept-Language": "EN-GB",
"Cache-Control": "no-cache",
"Connection": "close",
"Content-Length": "11",
"Host": "",
"User-Agent": "<omitted>",
"X-Request-Id": "<omitted>"
"json": null,
"origin": "<ommitted>",
"url": ""

HttpBin provides us with an echo of the header and WireShark provides us with a wire capture of the transport packet being sent between the XmlHttpResponse provider and the HttpBin server. With these two tools we can analyse the header states.


If you take out the line call x.setRequestHeader(“Content-Length”, 11) and do not specify a content-length, both HttpBin and Wireshark’s trace of the connection report Content-Length: 11\r\n, which is correct. So logically, this has been inserted by the XmlHttp parser during the request to .send().

So what happens if we attempt to override it. If we inject call x.setRequestHeader(“Content-Length”, 15), which is 4 characters too long, again both WireShark and HttpBin report

Content-Length: 11\r\n

The same occurs if we under-report the content length, i.e. 10 characters. Therefore it is safe to conclude that MsXml2.XmlHttp’s send() operator handles this for you and any processing of the Len() of the message body before sending is just wasting CPU time, memory and lines of code as it is ultimately an action that will be duplicated.

To further test the theory to see if it is required in down-level versions of MsXml, I repeated the experiment using HttpBin and Wireshark with the following COM objects:

MsXml2.XmlHttp            <-which is logically v3.0
MsXml2.ServerXmlHttp      <-which is logically v3.0

None of them could be coerced to include an incorrectly sized Content-Length and all of them appended the correct 11 character content length on their own when no attempt was made to manually provide it.

We can therefore categorically conclude that with MSXML 3.0 and higher, you do not need to waste time calculating the content length.

A an aside, when dealing with character data, the use of Len() appears to be correct for UTF-8 encoding. LenB() should be used when passing in binary data i.e. ADODB.Stream data, FileSystemObject data etc.


If you do not manually specify a Content-Type header, all MSXML providers appear to transmit POST encoded data as

Content-Type: text/xml; charset="utf-8"\r\n

As we were sending POST data (i.e. a HTML form), this is incorrect as the content-type should be “application/x-www-form-urlencoded; Charset=UTF-8”. Thus is is appropriate and necessary to include the Content-Type header manually as shown below.

call x.setRequestHeader("Content-Type", "application/x-www-form-urlencoded; Charset=UTF-8")


Accept language was decidedly different depending on which provider you are using. You can safely override Accept-language by manually specifying it using setRequestHeader(“Accept-Language”, “EN-GB”).

Microsoft.XmlHttp         <- NOT SET
MsXml2.XmlHttp.3.0        <- NOT SET
MsXml2.XmlHttp.6.0        <- Derived it from OS i.e. Accept-Language: en-gb\r\n
MsXml2.ServerXmlHttp.3.0  <- Derived it from OS i.e. Accept-Language: en-gb\r\n
MsXml2.ServerXmlHttp.6.0  <- Derived it from OS i.e. Accept-Language: en-gb\r\n

Thus, if you are using the legacy Microsoft.XmlHttp or the client MsXml2.XmlHttp.3.0, it is necessary to manually enter the Accept-Language if of course it is needed.


All examples set the connection value to Connection: Keep-Alive\r\n when communicating with the HTTP/1.1 protocol. If you need to force the remote server to close the TCP connection immediately after the response has been received, you must manually set Connection: Close\r\n using setRequestHeader(“Connection”, “Close”).


Cache-control is only set by the client providers and can be added manually to the server providers.

Microsoft.XmlHttp         <- Cache-Control: no-cache\r\n
MsXml2.XmlHttp.3.0        <- Cache-Control: no-cache\r\n
MsXml2.XmlHttp.6.0        <- Cache-Control: no-cache\r\n
MsXml2.ServerXmlHttp.3.0  <- NOT SET
MsXml2.ServerXmlHttp.6.0  <- NOT SET


Accept-encoding is only set by the client providers and can be added manually to the server providers.

Microsoft.XmlHttp         <- Accept-Encoding: gzip, deflate\r\n
MsXml2.XmlHttp.3.0        <- Accept-Encoding: gzip, deflate\r\n
MsXml2.XmlHttp.6.0        <- Accept-Encoding: gzip, deflate\r\n
MsXml2.ServerXmlHttp.3.0  <- NOT SET
MsXml2.ServerXmlHttp.6.0  <- NOT SET


Accept is set to Accept: */*\r\n by all providers and can be overridden if required.

0x80090020 when attempting to load a .PFX Private Key Certificate into a CAPICOM_MEMORY_STORE using Store.Load() or Certificate.Load() using CAPICOM

System Requirements:

  • Windows Server 2008, R2
  • Windows Vista
  • Windows 7
  • Windows 8, 8.1
  • 7.0, 7.5, 8.0
  • ASP 3.0 (Classic)

The Problem:

Ah encryption, that most noble of things. One thing that is sure to drive every developer close to the brink on the odd occasion. The one time where clear, concise API documentation should be considered mandatory – and the one place where good API documentation it seems is an obligation itself not to provide. Be it Microsoft, Java, BouncyCastle, PHP it would seem they are all blighted with the same issue.

Attempting to use legacy API on an unsupported platform should seem like an exercise in masochism, however, you know how much I like to avoid using .net whenever I can.

If you attempt to do this

Dim cert
set cert = Server.CreateObject("CAPICOM.Certificate")
call cert.load("c:\myPrivateKey.pfx", "test", CAPICOM_KEY_STORAGE_EXPORTABLE)

or this

set store = Server.CreateObject("CAPICOM.Store")
call store.load("c:\myPrivateKey.pfx", "test", CAPICOM_KEY_STORAGE_EXPORTABLE)

you will get back

error '80090020'
/file.asp, line ###

If you send in a .cer file instead of a .pfx, it works without error but doesn’t allow you to access the Private Key.

More Info

Taking the two code samples in order

Dim cert
set cert = Server.CreateObject("CAPICOM.Certificate")
call cert.load("c:\myPrivateKey.pfx", "test", CAPICOM_KEY_STORAGE_EXPORTABLE)

Should you be getting a 0x80070056 error, your password is wrong. If the file doesn’t have a password, only send parameter 1 (which is about to cause you a problem). To resolve the 0x80090020 error while using a CAPICOM_MEMORY_STORE, you need to stop CAPICOM from attempting to insert the certificate as a resource for a user. If the IIS worker process that you are using doesn’t connect to a user account and has no permissions, the default parameter CAPICOM_CURRENT_USER_KEY or 0 will throw 0x80090020.

To change the scope, ensure that you use the fourth parameter and set the value to CAPICOM_LOCAL_MACHINE_KEY.

set cert = Server.CreateObject("CAPICOM.Certificate")
call cert.load("c:\myPrivateKey.pfx", "test", CAPICOM_KEY_STORAGE_EXPORTABLE, CAPICOM_LOCAL_MACHINE_KEY)

To resolve the second issue, modify the original code to make use of the now fixed certificate.load() call and import it vie the long route.

Dim store
set store = Server.CreateObject("CAPICOM.Store")
call store.Open(CAPICOM_MEMORY_STORE, "MemoryStore1", CAPICOM_STORE_OPEN_READ_WRITE)set cert = Server.CreateObject("CAPICOM.Certificate")
call cert.load("c:\myPrivateKey.pfx", "test", CAPICOM_KEY_STORAGE_EXPORTABLE, CAPICOM_LOCAL_MACHINE_KEY)

call store.add(cert)

If you receive 0x80070005, you are either getting an Access Denied error to the MEMORY_STORE or you are attempting to import a certificate into the instantiated store which already exists. Similarly, if you receive 0x80070056, your password is wrong.