Lloyds TSB - QIF Statement Download to MS Money not working?

mbailey

Shocking that this still isn't fixed.

I now have a Halifax credit card and as it uses the same systems as Lloyds it has the values swapped around as well.

For the past few years I have been manually editing the QIF file before I import into Microsoft Money. A real pain, having to go through each of the transactions and change the polarity.

I got annoyed doing it tonight and did a quick google and this article came up and I realised I first started the post in to 2010!!!

I have other credit cards which use QIF and they are fine. (Barclays and Tesco.)

Browser1

Dear mbailey,
I have come across your thread in my search trying to resolve a different issue with my Lloyds Trustcard account to yours, and noted I have had no problems importing my .QIF statements manually in to the Microsoft Money 2005 UK in the way you and others are describing here, so was puzzled by your difficulties. While playing around in Microsoft Money and accessing my Lloyds account from within the Money program I experienced exactly the same problem you talk about. I am no expert at all in Computer matters, so this is only a novices guess as to what the problem could be. But I noticed that in accessing the Lloyds Bank website Microsoft Money automatically switched browser from my default Google Chrome back to IE. I use Windows XP and some years ago found increasing problems when accessing the web with the IE browser, as Microsoft had stopped upgrading IE for Windows XP, and thus it was often was very slow, and had other erratic problems with more recently built websites. Google Chrome never stopped upgrading their Web browser for Windows XP, and once I changed to them, all my Internet Explorer wows went. Of course with Windows 7 this does not apply as the IE for this OS is still being upgraded. Thus I think it is the Web browser that is the problem, so when Lloyds Bank rebuilt their website it was optimized for using the latest HTML version at the time, and thus Internet Explorer in Windows XP, which is probably your Microsoft Money mandatory browser, is no longer able to download the .QIF file correctly. However if you download your Lloyd Credit card statements via Google Chrome, outside Microsoft Money, but with the program running, when you then click on your .QIF file in the Google window download bar in the lower left corner, Microsoft Money still picks it up automatically, and incorporates it in to the account you specify, though this has to be specified with each statement download.

I hope this helps, though I do stress I am only a rookie with computers.

mbailey

Browser1 wrote: »

Dear mbailey,
I have come across your thread in my search trying to resolve a different issue with my Lloyds Trustcard account to yours, and noted I have had no problems importing my .QIF statements manually in to the Microsoft Money 2005 UK in the way you and others are describing here, so was puzzled by your difficulties. While playing around in Microsoft Money and accessing my Lloyds account from within the Money program I experienced exactly the same problem you talk about. I am no expert at all in Computer matters, so this is only a novices guess as to what the problem could be. But I noticed that in accessing the Lloyds Bank website Microsoft Money automatically switched browser from my default Google Chrome back to IE. I use Windows XP and some years ago found increasing problems when accessing the web with the IE browser, as Microsoft had stopped upgrading IE for Windows XP, and thus it was often was very slow, and had other erratic problems with more recently built websites. Google Chrome never stopped upgrading their Web browser for Windows XP, and once I changed to them, all my Internet Explorer wows went. Of course with Windows 7 this does not apply as the IE for this OS is still being upgraded. Thus I think it is the Web browser that is the problem, so when Lloyds Bank rebuilt their website it was optimized for using the latest HTML version at the time, and thus Internet Explorer in Windows XP, which is probably your Microsoft Money mandatory browser, is no longer able to download the .QIF file correctly. However if you download your Lloyd Credit card statements via Google Chrome, outside Microsoft Money, but with the program running, when you then click on your .QIF file in the Google window download bar in the lower left corner, Microsoft Money still picks it up automatically, and incorporates it in to the account you specify, though this has to be specified with each statement download.

I hope this helps, though I do stress I am only a rookie with computers.

Interesting idea, but it is nothing to do with Internet Explorer or Chrome.

I can download the QIF file perfectly ok with Internet Explorer, it is just the format of the file which is wrong as Lloyds haven't implemented the file format correctly.

As stated above, the only way I can use the QIF file is to download and save the file to my computer, edit the file and change the polarity of the transactions, and then double click on the QIF file and it imports into Microsoft Money.

Having tried speaking to Lloyds and Halifax they have acknowledged they are "different" from other banks, but they try to argue that every other bank is wrong and they are correct!

Browser1

Dear mbailey,
Thanks for replying so quickly. Strange. My setup is Windows XP running Microsoft Money UK 2005 deluxe with the obligatory patch. Using the Google Chrome Browser I downloaded 6 months worth of my Lloyds TSB Trustcard (Mastercard) transactions using the Export facility on the Lloyds on-line banking site 2 days ago, as well as the Platinum Current account transactions for the last 18 months. Once the downloads appeared in the Google Chrome Window in the (L) lower corner, on clicking on it Microsoft Money automatically imported it, once I had specified the account in to which the statements should go. The Trustcard account IS specified as a Credit Card account in Microsoft Money. Following your reply I double checked the two Microsoft Money accounts in question this am and can assure you that all the Transactions have imported in to the accounts absolutely correctly. I can also confirm that on accessing my Lloyds bank website through Microsoft Money nothing happens. I think it highly possible there is come kind of browser issue, it seems logical in the context outlined. May I suggest you try doing it through Google Chrome just to see what happens. Now, though strictly speaking not quite relevant to this thread, I wonder if you would be kind enough to help me a little bit by confirming or refuting my suspicions. I am aware that Microsoft Money used to be able to import your statements automatically, and PocketSense appears to offer the possibility of restoring this facility, even though the Microsoft Servers are now off line. Though it seems to work for the USA, I am beginning to think this is not possible with UK Banks because of the upgraded log-in requirements that seems to have recently been implemented, preventing automated log-on, over and above the fact that Lloyds does not provide OFX transaction exports anyway. Do you have any knowledge on this?

Yours sincerely.

Andy6619

Having Used MS Money since 1995, I am not about to give up even if MS has. I have just enrolled for a Lloyds Credit Card and faced the same issues as some above. I can create a QIF file but when it is loaded into MSMoney, the debits are credits and vice versa. If only this were true...

Mbailey certainly has a solution which I tried and it works. This has identified that a minus sign is required in front of the debits in order for it to be imported correctly. In my tests, It did not seem to matter if the type was changed as referred to above by DurhamBen.

As an alternative solution, for anyone with Excel - If you are using MSMoney I am sure you will have Excel - is a file converter I located xl2qif.chez-alice.fr/xl2qif_en.php or XL2QIF if you want to google it. The writer of this freeware deserves much praise and a donation if you appreciate it as much as I do.

This is an Add In (Program/Macro) that sits within Excel on the Add Ins tab

I downloaded the statement as csv file,
It is probably a good idea to play around with the converter first with a blank money file or account until you confirm your settings.
Open the Add In - Select and Open the Add In using "Save to QIF"

Select your Input data
I Deleted one of the date columns before I opened the Add-In (you can just not select it or choose - "Don't Care" when selecting the columns to map.)
You can leave the headers in the excel file but just do not select them as part of the input data - or you can delete them
You need to select (tick) columns in the order they appear in the spreadsheet to ensure they are in the right order (See Columns order in Input Data section of input screen)
I chose <Date>, <Check Number>,<Payee><Amount>
Select account type although I saw no difference with this on import - I may be wrong
Choose Date Format
Specific Processing - leave as "None"
tick "Invert amounts" box (This is the problem with the Lloyds QIF file remember)
leave the other tick boxes (Separate Debit/Credit and AutoImport) blank

Choose a filename so you know where you are saving it to.
Press Convert button

Close it all down when QIF has been reported to be made.

Navigate using Windows Explorer to the QIF file and open it. MS Money should then run its usual import routine. - It works

I had to experiment a bit as I am sure you will

Using this method also allows you to edit some of the longwinded descriptions before you import. Add a memo maybe (not tried)

It may read longwinded but it is quite simple after you have done it a few times.
Now to move onto those banks that don't download QIF files

And for those that are concerned that this refers to a macro last updated in 2005. I am running it in Excel 2013 on Windows 10 Operating System - Timeless

Thank you XL2QIF

mbailey

It's rather depressing that 5 years later and Halifax/Lloyds have all refused to look into the problem and fix it.

I'm still using Microsoft Money and still using the method I described above where by I change the polarity of transactions after I have downloaded the QIF file and before loaded into Money. It's a pain, but I guess I've got used to doing it now.

No_6

Just do mine the old way....look at the online statement,
then balance the transactions on MS money to see if they match
only once a month though, so not to bad.


6

deeyem

Encountered an issue with this today with the Post Office credit card and was looking for solutions to see if there was a fix before I started tinkering and found this post. Posting a reply in case it helps anyone.

I looked at the downloaded QIF file with a hex editor and compared it to another credit card QIF file and saw that there were extra characters at the start of the file and between the '-' and the amount. They don't show in Word if you allow it to open using unicode encoding so I tried a DOS file format and the errant characters appeared. I used search-and-replace to get rid of them, saved the file and imported it successfully.

(I tried to post some screenshots but I can't create a post with links as I have only just joined the site.)