0

I'm not sure if this is the right stack exchange site for this, or maybe this is more of a reddit question, but don't rake me over the coals for asking. Happy to go post on another corner of the internet if need be.

I'm wondering what would be the best approach to identifying duplicate transactions within an accounting system. If every transaction has a date, a vendor name, and an amount. I think any 2 of the 3 data points could indicate a possible duplicate, but probably relying on that approach would produce a high number of false positives.

My thinking currently would be the dollar amount and vendor would have to match, and the date would have to be within the same month? But also it seems like checking for a matching amount and date, but mismatched vendor might indicate a reasonable probability of being a duplicate.

Has anyone done this type of thing before and what criteria did you use that proved successful?

UPDATE

The purpose of identifying the duplicates would be to have an end user manually review them to determine if they should be deleted or if they're legitimate transactions.

My concern is the end user may get frustrated with reviewing a bunch of false positives, so I want to try and find the sweet spot where there aren't a ton of false positives OR negatives.

UPDATE 2

I could potentially use the invoice/reference number, so that's another possible point of comparison.

In other matching situations, I take 2 invoice numbers and "clean" them, by exchanging any Os for 0s and Is or ls for 1s, trimming white space, and then comparing the resulting value. I could potentially employ the same logic here, but that's proven problematic at times mainly because one invoice number could have a 3 digit prefix on one side and the other doesn't have the prefix. Same with a suffix. I'm sure people way smarter than me have figured out some pretty elegant and sophisticated ways to do this type of thing.

Again, trying to find that sweet spot is what I'm after.

12
  • 1
    A recurring amount and vendor does not look at all like a duplicate to me - much more likely something like a subscription. Commented Jul 11, 2023 at 16:52
  • 1
    "but don't rake me over the coals for asking" - I like the derring-do of this question! +1
    – Steve
    Commented Jul 11, 2023 at 17:23
  • 2
    @Steve The OP didn't say 'please' though. Let the raking begin!
    – JimmyJames
    Commented Jul 11, 2023 at 17:31
  • Joking aside, this is really something you should be discussing with your requirements owners. There's no universally correct answer to this.
    – JimmyJames
    Commented Jul 11, 2023 at 17:33
  • 1
    have an end user manually review them to determine if they should be deleted or if they're legitimate transactions. -> So "the man in the middle" means the computer-selections do not have to be perfect. Err on the side of minimizing false negatives which means you may flag lots of false positives, at first. Refine the process as you get user feedback. Mostly resolved in testing, At release be like MS & Apple: "Hey users! Help us make it better for YOU! Let's do 'operational testing' as it were. Be kind, unwind.
    – radarbob
    Commented Jul 13, 2023 at 23:56

2 Answers 2

1

You'd have to concentrate on analysing why the duplicates arise, and what effects they typically have in the data.

For example, you seem to be saying transactions can be "duplicates" where even the recorded vendor or other counterparty of the transaction is not the same. That sounds potentially like an extremely radical error (duplicate and misattributed to a different party), which would require fundamental improvements in record-keeping.

You'll probably also find that very few instances can be identified as duplicates from the data alone, but only identifies candidates for further investigation (such as contacting the counterparty, or cross-checking with other records and audit trails).

3
  • This is great feedback, thanks for that. I think you're right about needing to manually investigate the supposed duplicates. Duplicates arise because of incorrect data entry. I'm trying to add functionality to our software to identify these and make it as easy as possible to correct. Sometimes, although not incredibly often, there will be 2 of the same vendors in the accounting software with slightly different names (one got created by mistake) and then then transactions were inadvertently recorded under both vendors.
    – hyphen
    Commented Jul 11, 2023 at 17:40
  • @hyphen - could you please edit the original question to add this contextual info? It clarifies your question tremendously. For example, before I read this, I assumed your goal was to have the system automatically reject or correct these duplicates, but what you actually need is that they be flagged, so humans could take a look. (Your comment here might be moved up or down if other answers are posted, so it might not be visible to people) Commented Jul 11, 2023 at 18:00
  • @hyphen, it sounds like you're trying to put your cart before the horse - assuming the computer knows the correct answer, and is in a position to tell the clerk that they have entered it wrong. The reality is that the clerk may be the only source of truth - if the computer already knew the answers, you wouldn't need the clerk to enter them. The only hope is that you find some kind of contradiction between two entries. But there's no general principle for where you will find these - the situation has to be analysed in detail and possibilities searched.
    – Steve
    Commented Jul 11, 2023 at 18:09
0

I think you're too far in the weeds already with your approach. This is first and foremost a business problem, not a technical one and you should treat it as such. I would start by gathering requirements as to what kind of indicators should be taken into account. For example, there are a few kinds of duplicates here that could be legitimate.

  • Same item, same invoice, same cost - I ordered it in that way (think scanning 2 cans of soup at a self service checkout).
  • Same item, different invoice - a repeat, or subscription.

From your description, there could be all sorts of issues re-typing which could actually hide duplicates rather than reveal them, so it's a complex problem.

So...I'd push back on the people who asked you to do this and, while you can offer suggestions, ask for the rules they'd like you to apply.

One thing I'd definitely draw to your attention is that (I assume) your organisation has actually asked for the stuff on the invoice. Your invoices should be reconciled to a purchase order or some kind of shopping list. You should really be using that as in input to your process, to ensure you're getting what you paid for and not paying for something you didn't ask for. Duplicates will naturally fall out of that process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.