OFX / QIF File Format - No Longer Supported

  • jayneandallan's Avatar
    Just looking
    Hi Folks.

    I too found this was a nightmare, especially as a Microsoft money geek. I am also a bit of a spreadsheet geek, so wanted to find a solution (for free of course :)

    I have successfully used this solution a couple of times now, and once you have done it once, it is really quite easy. (using a spreadsheet and a 'Notepad++" piece of software which is free to download on Microsoft store)

    Basically, download the CSV and open in your normal spreadsheet software. I use the OpenOffice Calc software, but will work on Excel too. If you need to convert the file when opening, uncheck all default 'delimiting' options, then select the 'Comma' delimit option. Once opened, the fields will be in columns from A through to J, in descending date order.

    That is when you copy and paste the following text into cell K1

    !Type:CCard

    and the next line of text to K2 (including the equals sign)

    ="D"&A2&"|"&"T"&TEXT(-1*VALUE((SUBSTITUTE(C2;"£";"")));"£0.00")&"|"&"P"&D2&"|"&"A"&E2&"|"&"A"&F2&"|"&"A"&G2&"|"&"N"&TEXT(H2;0)&"|"&"^"

    This formula can be copied all the way to the oldest record you want to import.

    Highlight the cells from K1 to the final entry you want to use for import and click the copy button.

    Open up the Notepad++ software and select File then New.

    Click the Paste button to paste the rows of data into the 'notepad' document

    Click on search on the menu bar, and click replace (alternatively use Ctrl+H).

    In the search mode, select the option for 'Extended (\n,\r,...)'

    Type in a pipe character (|) (using shift key and button next to the 'z' key) into the 'Find What' box.

    Type in \r\n into the 'Replace with' box

    Click Replace all.

    This will make each record split into the individual rows ready for import.

    Now select the "Encoding" menu, and click on 'Convert to ANSI'.

    Final step is to select the "FIle" menu, and click 'Save a copy as' and navigate the save menu to the location you want to put the file. Then change the 'Save as type' to be 'All Types (*.*)'.

    Give the file a name such as "Tesco1.qif"

    Once saved, navigate to that location, and double click the file and it will open and give you the normal import message in Money.

    The stages in Notepad++ will be easier the second time you do it as a number of the options you used will be remembered from the previous time.

    Experiment with one transaction first until you are confident in the process. I have attempted to upload a sample sheet which can be used to paste your CSV output onto if this makes things easier - converted using OpenOffice to .xls as their native format is not supported on the forum.

    Good luck, and If anybody has questions, happy to help where I can.

    Allan C
  • Traversmitg's Avatar
    Just looking
    I have used Microsoft Money for 15 years and what I liked about Tesco’s Downloading was that it was quick and with no human mistakes. Now with only Excel as a download option, entering and balancing is taking 3times as long, it makes my Lloyds Visa card more appealing!
  • Khuprius's Avatar
    Just looking
    For those using YNAB, here are a couple of macros that might help to turn the Tesco CSV into a file that can be used. I've kept the two macros separate so that it is easier to see,

    Create a new workbook with the macros in it and save it as a macro enabled workbook.
    Copy the Tesco csv into the new sheet in your macro workbook.
    Run CopyOutRequiredColumns
    Run WriteOutTheCSVDirectly

    Then import the csv file that you have created.

    It's abit quick and dirty and could be written better with someone with more skill than I, but it seems to work.


    Sub CopyOutRequiredColumns()

    'This macro needs to be in the workbook that has the downloaded Tesco CSV in it.
    'It will copy the data from a sheet marked tesco.xls to a new sheet called tescoYNAB that has only the columns needed

    Dim intLastRow As Integer
    Dim intRowNumber As Integer


    intRowNumber = 1
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "TescoYNAB"

    'go back to the first sheet

    Worksheets(1).Select ' re-select the source worksheet

    Range("A:A").Copy Sheets(2).Range("A:A")
    Range("D:D").Copy Sheets(2).Range("B:B")
    Range("E:E").Copy Sheets(2).Range("C:C")
    Range("C:C").Copy Sheets(2).Range("D:D")

    End Sub

    Function DateToText$(dbl)
    DateToText = Format(dbl, "DD/MM/YYYY")
    End Function

    Sub WriteOutTheCSVDirectly()
    'This macro comes second and will set the formats of the date and switch the cash values around to the correct format. It will then write out a csv in the correct format for YNAB.
    'you will have to create the folder that you use


    Dim filePath As String
    Dim intDateRows As Integer
    'we know there will only be 4 columns
    Dim thisLine As String 'we will use this to construct the line
    Dim intColumns As Integer

    filePath = "D:\Onedrive\temp"
    Open filePath & "TescoYNAB.csv" For Output As #1
    'Now each line
    'first line is the titles
    Print #1, "Date,Payee,Category,Memo,Amount"

    Worksheets(2).Select
    intLastRow = ActiveSheet.UsedRange.Rows.Count ' count the rows used in the worksheet
    'now we have to convert dates to text and money to text - copy and paste with no formatting.

    For intDateRows = 2 To intLastRow
    For intColumns = 1 To 4
    cellVal = Cells(intDateRows, intColumns).Value
    'if it is a date row, we need to convert it to text first

    Select Case intColumns
    Case Is = 1
    thisLine = DateToText(cellVal)
    Case Is = 2
    thisLine = thisLine + "," + CStr(cellVal)
    Case Is = 3
    thisLine = thisLine + ",," + CStr(cellVal)
    Case Is = 4
    thisLine = thisLine + "," + CStr(-1 * cellVal)
    End Select
    Next
    'now we hae 4 entries we can write the line
    Print #1, thisLine


    Next


    Close #1


    End Sub
  • JK64's Avatar
    Fresh Eyes
    Why have Tesco Bank removed the export option for transactions older than 90 days? This facility now next to useless and PDF's are very unfriendly. I'm very unhappy with this and will be looking for an alternative.
  • JoeC's Avatar
    Your Community Expert
    Hi @JK64, thanks for getting in touch. Transactions have only ever gone back 90 days on our online banking platform. Any transactions older than this would need to be downloaded as PDF statements. I'm sorry if this is inconvenient for you.
  • JK64's Avatar
    Fresh Eyes
    Hi @JK64, thanks for getting in touch. Transactions have only ever gone back 90 days on our online banking platform. Any transactions older than this would need to be downloaded as PDF statements. I'm sorry if this is inconvenient for you.

    Sorry that's not true. - up until recently I could select a statement period and then download it as an excel - I've been doing it for years. Where's the thumbs down icon...
  • mikeab28's Avatar
    Window shopper
    I agree. This is actually a second thread on exactly the same topic - 10's of thousands of views. I think a system analyst slipped up some where!! The other post is called

    OFX / QIF File Format - No Longer Supported

  • SueIan04's Avatar
    Fresh Eyes
    Hi @Norfy, due to recent changes we've made to our Online Banking platform, we're no longer able to support this format. This being said, we're continuing to review all customer feedback received on our Community and I'll pass your comments on to our Customer Insight Team for further consideration. I've added your comments as feedback too @JeffWoodend, I'm really sorry for any inconvenience this has caused.

    I would like to add my voice to those complaining about the loss of functionality. Please reinstate Quicken and MS Money download of transactions.

    And: let me get straight to my transactions rather than clicking through a range of screens and menus to get there.
  • RossM's Avatar
    Former Community Manager
    Please note that we had a couple of threads for this subject so I have merged them all into this one thread.
  • MPB1404's Avatar
    Just looking
    Please note that we had a couple of threads for this subject so I have merged them all into this one thread.
    There was a promise that there may be good news on return of Qif downloads. Are Tesco still claiming this as an improvement!