HOME / COMMUNITY Switch to knowledge base

Statement Uploads - [Co Op Bank] File Schema Errors


#1

Since I last tackled bulk banking imports via statements (I usually create by hand/copy paste etc.), the lovely folks at the Co-Op bank have updated their site and now have a basic, but much easier (.csv) export option…including filters. (Yippe!!)

So, I thought I would revisit the option and attempt an upload for Q4 2016. I was also doubly pleased to see that ‘Co-Operative’ is now also listed as its own option on the import list - yay no more ‘Other’ field matching!

Off it went and successfully generated 114 x new transactions with zero errors or duplicates.

Awesome… or so I thought… :fearful:

  • Credit transactions appear to have had their value listed as description/reference
  • Debit transactions have been set as ‘Generic payments’ + {date}
  • The statement ‘running balance’ figure has been inserted against each transaction entry as a debit figure. So in the screenshot above that account should be showing a final balance of +£512.20…rather than -£69k! :cry:

To resolve am I right in thinking it’s simply a case of deleting all the affected entries from the [Bank Account] screen and that this shouldn’t have any effect on anything else in the system?
Where is the offending .csv file kept after upload - is it deleted or do I need to clear this also?

Finally - can I help adjust the system schema used to match the fields for Co-Op Bank statements, as it’s clearly importing the wrong data fields into QF.

John.


Co-Operative Bank Statement not uploading properly
#2

If they are all untagged (which they should be if you just imported them, assuming no auto tagging occured) then deleting them will not affect any other part of the system.

We’d happily work with you on this, if you can please send either @QFSupport or I a private message and attach an example CSV file from co-op, we’ll take a look.


#3

Morning - marvelous thank you :relaxed:

A freshly squeezed csv incoming!

John.


#4

That is very helpful thank you!

We have updated our Co-op statement parsing logic to handle both legacy and new CSV formats.

Give that a go and let me know if that works.


#5

Morning Glenn - almost there!

Have re-run my original report and it is looking much more promising as the descriptions and dates all seem to have been pulled through correctly.

The only glaring issue I can see is that the Credits/Debits need reversing;

The .csv outputs from the Co-Op have the Credits [Money In] first, with the Debits [Money Out] second = QuickFile it is the reverse so the fields need to be swapped over:

John.


#6

Sorry it looks like debit and credit mappings were reversed, please retry now.


#7

That’s got it - smashing!:grinning:

Strange…my wife seems to struggle with the same affliction…if only fixing that was as easy!:laughing:

John


#8

…This is odd.
I’m a newbie to Quickfile and keen to move on from our Community Shop based spreadsheet to this application.

I’ve downloaded the .csv file [removing the extraneous preamble] from the Co-operative Bank and, selecting that bank from the drop-down under Banking>> Current Account>> More Options >> Upload Statement, I uploaded a small [16 line] selection of that statement:

That resulted in just 8 Transactions!!
… These appeared to be just those lines that had text entries in the “Credit” column … which showed in the ‘Money out’ Quickfile column.

Is there a recommended .csv format I should be following?

Regards,
Duncan


#9

Hi @LlandeglaCSFinance

When you uploaded the statement, did you select “Co-op” as the bank?

It should work all OK - I’ve checked the fields we’re expecting and it looks like the example you’ve provided.


#10

We find the simplest option with Co-op is to Accounts Information -> Transactions -> (specify the date range) -> Reports (bottom right hand corner) -> csv. Then upload that as a statement in QF.

I think I tried the statements report option a while back but it didn’t work. Haven’t tried it since.


#11

Thanks for the suggestions.
All fixed now… The error [on my part] was that I’d helpfully removed the preamble text prior to the column headings. [As well as removing the rather annoying ‘page x of y’ stuff the Co-Op puts in]. This is what I’d been doing for ages when importing to my spreadsheet.

Uploading a ‘raw’ downloaded statement did the trick.
Thanks again…

Regards,
Duncan.