UK Banks & OFX downloads


Money and UK Banks

Availability of OFX connection is quite common in the US, but seems to be very rare in Europe, even for business accounts and since there seems to be many questions regarding downloading UK Bank data, I thought I would start this thread that I’d be happy to keep up-to-date with feedback from my Blog readers.

Chances are that the OFX download solutions promoted in my Blog won’t work with any of the institutions listed and having a N in the OFX or QFX columns, which is a good many of them. 😦  but this can serve as a forum to discuss this further.   The initial table is courtesy of Moneydance

Bank  OFX  QFX  QIF  OFC  CSV
Abbey Bank Plc. N       Y
Alliance & Leicester Y   Y   Y
BarclayCard Y    Y Y
Barclays Personal Y       Y
Barclays Business Y       Y
Clydesdale   Y    
Coutts & Co Y   Y    
Egg N        
Fidelity UK N        
First Direct N      
Halifax Y   N    
HSBC N Y      
Lloyds TSB N N Y   Y
Nationwide N N N N Y
NatWest Y        
Royal Bank of Scotland  Y   Y    
Smile.co.uk N  N
Tesco Y      
Yorkshire N      

 


Hung Le is putting significant effort into coming up with a Java app version that is tailored to UK needs:

Join in on the  Java app Blog page   for more…

  • has support for two additional quote sources: ft.com and Google Portfolio. Both have good coverage for UK fund (in particular ft.com).
  • has support for mapping a quote source symbol to MM symbol.
  • both ft.com and Google Portfolio provides currency in its data so we have enough information to write out symbol’s specific currency in the generated *.ofx file. It is unclear if MM honor that. I don’t use multi-currency to be able to test that area.
  • there is an outstanding issue with Money05UK, in that import statement with zero share does update the quote price. We are looking into a work-around: generating fake share count to force MM into creating transactions.

In general, most of us US developer have no access to Money05UK and are likely having zero knowledge on multi-currency usage. So we can use some testing and feed back on above issues 



-ameridan

Advertisements

21 Comments »

  1. John said

    Hi!
    Thanks for moving this forward, really helpful.
    Just an observation, I’ve been downloading statements from First Direct into Money for 5+ years – so I assume there is a format of some sort that they support which works with Money.
    As an aside, Barclaycard UK supports OFX, OFC, CSV and QIF.
    If I may widen the topic slightly, what I’m looking for is an OFX source or UK mutual funds. Yahoo.co.uk supports fund prices and APIs which extract the prices, but alas the feed returns zeros for prices!
    Does anyone have clever ideas as to how us UK Mutual Fund Holders might get prices?!
    21 days and counting…
    Thanks!

  2. ameridan said

    Thanks John,

    I’ve incorporated your feedback into the table. I don’t have any experience with UK institutions but thought I’d try to provide a source of help for those trying to use the OFX scripts. Have you actually opened up Manual Update to verify the prices = 0.00? You don’t want to simply look at Portfolio View, as it may simply be a means of unmatched security symbols.

  3. John said

    Hi Ameridan!
    Thanks for the prompt reply.
    Alas, all .csv outputs from yahoo.co.uk reveal that the prices are zero. I was advised by someone who has looked into this that the APIs will also return zeros!
    So, for us UK mutual fund holders, no solution readily in sight.
    Starting to panic…

  4. ameridan said

    Sounds like this is Steve’s issue as well.

    Just tried PocketSense – I must [be] so close but…PocketSense only picks my stock prices – not the funds e.g. Fidelity GB0032346800.L.

  5. John said

    Yep! Alas, I can’t find a solution for UK Mutual Funds anywhere!

  6. ameridan said

    Hung Le,

    Thank you very much for the generous contribution to my Coffee Fund!!

    I think it’s great that you’re developing another Java alternative for obtaining quotes. A couple of thoughts come to mind (you may have already had the same thoughts 🙂 )

    1. A SUBMIT button to work with the SAVE button that has Java code that does the equivalent of the Python code that “executes” the ofx file right after SAVING the file, so that the user doesn’t have to look for the file and double-click it to import it into Money (or use the File Import button).

    FOR %%D in (*.ofx) DO START %%D

    2. “Scrub” the quotes from Google when creating the OFX file such that 0 units of each investment exist, rather than 1 (needed to obtain the quote), so as to not have any effect on the Portfolio.

    Again, thank you and I hope your efforts lead to a very useful “International” Quotes tool!

  7. Hung Le said

    John,

    I have some test code for you and others to try – I would appreciate feedback.

    Best,
    Hung Le (hle999 at gmail dot com)

    http://www.le.com/~hle/out/GooglePortfolio/readme.txt

    http://www.le.com/~hle/out/GooglePortfolio/

    [edited by ameridan 1/21/2011 by merging 2 comments as 1 of them got messed up]


    I also had email exchange with Steve who mentioned another possible quote source: ft.com. It appears for UK investment data, ft.com is one of the preferred sources. For ft.com, we will need a CSV to OFX which actually is kind of there already: hleOfxQuotes.jar does just that: converting Yahoo’s CSV to OFX. I just [had] to re-factor the codes to make it a bit more general so that the tool can parse a user-mapped CSV file. If you feel one is better than the other (Google’s portfolio vs. FT’s portfolio), please chime in.

  8. ameridan said

    I’m hoping that anyone trying out the Beta Java app that Hung Le has written will provide feedback via this thread. It installs the same as his original Java app, but rather than going thru Yahoo, it uses Google Portfolio. I also added the tag “Google Portfolio OFX Java” to this thread to help lead others here and will be glad to create a Blog page for the app after some feedback – right now this seems like the ideal thread for discussion as it was written with the readers complaining that UK funds weren’t working via Yahoo in mind.

  9. John Hennessey said

    Hung Le,
    A quick tour of Google v FT.Com indicates, to me at least, that FT.Com coverage is much better.
    More than happy to be guided by others who are better acquainted with the UK price sources, but that’s how it seems to me.
    Initial feedback on your code sent separately.
    Thank you!!

  10. John Hennessey said

    Hung Le,
    Have taken a good look at google finance, FT.Com and Morningstar UK.
    The last 2 appear to be the very best in terms of coverage – alas the latter has no export function I can see.
    In my view, FT.com would be a sensible price source to code for. The Fund coverage is incredibly extensive – both UK and international funds so would be useful to non-UK investors too. As you know, the output is in CSV format.
    Thanks!
    John

  11. Hung Le said

    I’ve update hleOfxQuotes.jar to support two additional quote sources: ft.com and Google Finance Portfolio. Hopefully, that will give our UK/Canada users additional sources for their quotes.

    Please read carefully file: http://www.le.com/~hle/out/hleOfxQuotes/test/Build_20110124_001/readme.txt
    Check out the screen dump: http://www.le.com/~hle/out/hleOfxQuotes/test/Build_20110124_001/

    then download the new jar file at: http://www.le.com/~hle/out/hleOfxQuotes/test/Build_20110124_001/hleOfxQuotes.jar

    Best,

  12. Chris Willsher said

    I use Fidelity UK to hold a range of mutual funds for my PEPs and ISAs (apologies to the watchers on this blog outside the UK if those terms are unfamilar). Fidelity UK offers the downloading of a .csv format report showing all the main holding information. I contacted Hung Le direct to see if I could use this as a source of information to convert into .ofx format. He has very kindly guided me through the process using the FT.com template on build 20110205_001 of hleOfxQuotes.jar. I now have this working to update MM successfully for mutual type funds (Unit Trusts and OEICs). Hung Le has told me that he is going to modify the FT.com template so that MM also shows the update date and time details.

    The principles would allow any .csv format file to be converted to .ofx format and used to update MM. If this is of interest to anyone else then I will finish testing and then try to document the procedure and post here.

    The key for me was the ability to check prices live on my actual Fidelity UK holding account rather than to try and identify the correct funds and codes in a third party source of data.

    No doubt someone more knowledgeable than me would be able to automate the whole process including logging into my Fidelity account, downloading the csv file, reformatting the information to match the requirements of Hung Le’s Java script and exporting direct to MM – just as used to happen before Microsoft abandoned us!

    For the record, although I am in the UK, I am now using Money Plus Deluxe Sunset Edition. I bought a licence for Money Plus some time ago from an online US source and have no desire to try and go back to the earlier UK version.

  13. Hung Le said

    Sounds like MM05UK online/quote service is still working so the need might not be there but I’ve gotten some reports of UK users having success using the “randomized share count” scheme to force price update.

    Current state of of UK support: http://code.google.com/p/hle-ofx-quotes/wiki/UKUsers

  14. Sue Bailey said

    I am in the UK. I have painfully exported and imported all my financial data from Money 2005 into Money Plus Sunset as I have now a 64bit Windows laptop.

    I have tried both Pocketsense and Hung Lee’s java script (which gives me more of my prices from ft.com), to download investment prices but with both I can only get the stocks to link to my existing investments in Money. None of my ISAs appear in the dropdown when I am processing the files, and I am forced to add a new investment (it then makes me change the name as it recognises at that point it is there), and to delete the symbol.

    Any suggestions would be welcome!

  15. Sue Bailey said

    Thanks for the reply – I had tried various combinations in mapper.csv. But your comments made me think again!

    I’ve now got it working!

    My problem was that, being unfamiliar with the terminology in the US version of Money, I chose ‘other’ when setting up my investments. This meant they did not appear when importing the ofx file. Added to that, I did not then have the option of changing from ‘other’ to ‘mutual fund’, so I had to create new investments and change all the transactions to move them into the new investments (more tedious clicking!!) . After that I was able to import the ofx file and associate it with my existing investments!

    At last I am set up! I await tomorrow to see if new prices gest picked up!

    Thanks for the program and help. Just one comment – I am only getting prices to 2 decimal points, and could do with 4.

  16. Hung Le said

    Great to hear that importing is working.

    Regarding precision, there was a bug relating to that. Will be fixed in next build schedule for this week-end:
    https://code.google.com/p/hle-ofx-quotes/issues/detail?id=6

  17. Mark said

    HI,

    From my experience as a customer of Halifax plc in UK

    – Allows download of OFX files & CSV Files – The previous availability of QIF files has been withdrawn.

    – Also as at Jan 2011 – their credit card account has temporarily withdrawn the ability to download statements/transaction in any format at all. They plan to return this service in due course, but as at 11th march it is not there.

    Regards

  18. Mark said

    About Barclays Personal bank accounts – March 2011

    A Telephone enquiry & study of their web site indicate that their online-banking-service will allow me to export statement data in ofx & csv formats –

    Regards

  19. ameridan said

    Mark,

    Thanks for your input. I think I incorporated it into the table properly.

  20. Kevin Williams said

    Mark, I can download OFX from my Halifax Credit Card but Money 2005 wont inport it – the error is the file is corrupt? Have you successfully managed this.
    Nationwide also recently changed their online download ability to OFX and CSV but I cant get Money 2005 to import the OFX file 😦

RSS feed for comments on this post

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: