Bank statement import duplicate detection

Hi,

I’m having a few problems with the duplicate detection function when importing bank transactions. I see there was another topic about this a few years ago:

So we pay lots of guys’ salary, and quite often, they earn the same amount as one another. Normally, when importing, we only import date, description and amount fields. The date and amount for these transfers will be identical, but the description will include each guy’s name etc.

Unfortunately, this isn’t enough for Manager to detect non-duplication of line item, and so immediately doesn’t import those items. I understand the rationale behind avoiding duplicate items, but if I’m importing 200 transactions, and the only information I receive is that 197 were imported correctly but 3 were duplicates, I then have to go through each item one by one to find which were not imported, and input them manually via the “add new payment button”, which is obviously not ideal.

With that in mind, would it be possible to request that the import statement section include some sort of a review screen, so items detected as duplicates could be checked to see if they really are duplicated or if they are genuine?

Many thanks!
Rob

Something like this would acheive what you want


However I think it is likely to be beyond NGSoftware’s current resources / priority allocation

I assume you have bank rules to allocate each employees pay to their respective “Employee clearing account”. If so, in this case you could find the missing transaction by

  1. Go to the “Employees” tab, employees with a positive “Amount to pay” can be found by clicking on that heading. Which should list the employees wage transaction not imported

  2. Reconciling the bank account would confirm the amount missed.
    Reconcile

Yes, that idea would be great - I’m not sure something quite so sophisticated would be necessary, just a basic review of the duplicates would be enough in my case!

Sadly, using the Employee’s tab isn’t enough as a work-around, as we have lots of residual amounts cluttering that up, guys that have payments on hold, etc. Currently, I do it in excel, exporting the recently imported line items, then index matching against the original import file. It’s not that there aren’t work-arounds, it’s more that they aren’t very efficient, if you know what I mean.

To ease your particular case of clustering due to salary withheld, etc; what if at the end of each month all unpaid salaries are transferred to a salary holding account with appropriate comment to show names/salary withheld? This will zero the account each pay circle, making the bank import more efficient.

This is confusing. Are you saying you modify the statement file exported by the bank before importing it? If so, why? Statements should include the name of the payee. This would allow you to accurately allocate transactions using bank rules. All the rest of this discussion seems irrelevant in light of that.

If I understood you correctly, you are complaining that a function of Manager does not work after you deliberately deleted information from the imported statement for some reason. If I misinterpreted your comment, please clarify.

The bank statement xlsx format our bank offers does not match exactly the Manager import csv. I can’t imagine this is an unusual case. So the data in the import csv has to be altered to match what Manager requires.

The fields Manager imports are:
Date, Payee, Reference, Description, and Amount

Date field is self explanatory
Payee transfer beneficiary or origin
Reference is not actually the transfer reference, rather a numerical reference number which our bank does not provide
Description - the item description - here the name of the individual would appear
Amount, again, self explanatory.

So, discarding Reference as not applicable, the other fields are all filled by copying and pasting from the bank xlsx. However, the issue is that the duplicate detection seems to be based on ONLY date and amount, hence the problem. Apologies if my explanation wasn’t clear.

Hmm, thanks for the suggestion, but would cause lots more work, I think. We have multiple currencies, and it’s far more meaningful to have currency amounts stored for individual employees than in holding accounts. Plus the employees tab also allows tracking of who is owed what, which otherwise would be lost until the data re-entered.

The employees tab works really really well, it’s just this little issue with duplicate detection of statement lines that’s causing the issue.

CSV imports are always the most problematic. And .xlsx exports from banks are the nearest thing to useless. If your bank offers any other format, try it.

Meanwhile, leaving payees’ names in the Description field is going to automatically deprive Manager of potential duplicate detection information. (I don’t know the exact process used, but I would expect it to look at more than date and amount, because many high-volume businesses sell and purchase identically priced items more than once per day. And these are exactly the types of business the import function was designed for.)

If the Excel file is all you can get, you should add a column for payee and separate data from the export file as you build your CSV file. It may be tedious, but you might be able to develop a script to do it.

Yes, I don’t know if the other import formats allow for more fields to be imported, but our bank (BancoSabadell) only offer xlsx or something called n43. So xlsx and csv it is.

I’ve just tested it with another example (bank commissions in this case) I have just had right now. You can put any amount of different data in any or all of the other fields (Payee, Description, Reference), but if Date and Amount match a previous entry, they are reported as duplicate and are not imported.

I think this probably qualifies as a bug at this point as, as you say, these kind of high volume items are just what the import is designed to deal with.

Generalche
Your description of how the bank import works is different to my understanding.

I thought the duplicate detection was really only designed to provide some user protection when all bank transactions are entered by importing them (ie no transactions are entered manually) but occasionally overlapping date ranges are imported in error. I suspect it does only look for matches on date and amount as the other fields are often edited by users or not consistently used in imported data.

As such the duplicates detected are probably not within your imported data but due to bank transactions already in Manager. I assume you enter those manually for other reasons. Which is why the solution in the other thread appears more complex than you thought you needed (it enables the user to use both bank imports and manual entry by using bi-direction transaction matching and showing the match / mismatches).

A work around is to create a payroll importing bank account. Use it just to import the payroll payments (so no other transactions will exist on each date prior to importing). Then do one inter account transfer for each pay to zero the payroll importing account and transfer the cost to your actual bank account