We help IT Professionals succeed at work.
Get Started

Bank transaction matching between two bank accounts

597 Views
Last Modified: 2013-11-05
Scenario is download csv transaction data from a bank account and (say) a credit card for a period of time.

There are payments from the bank account to the credit card every now and then.
Sometimes the payments appear in the credit card csv dated the same day, often not and dated a day or two days later.

Both csv files import/open directly into Excel with no problem.

There are payment references in both, but none of these match, ever.

I could list the things I have tried, but do not want to limit the inspiration of possible answers.
I have struggled with this problem for many years.
There are various similarities between the transactions to be matched, for example the amounts are the same, the dates are close to each other when not the same.

I am interested to hear any ideas to solve this intractable problem.


MORE DETAILS

Second level solution involves adding a column to each csv and manually adding some sort of index column - I have been thinking of giving each relevant payment a numeric sequence within its month, so payment 01 02 03 etc which IF these are coming from only one source which is also tagged as such, might work - but I get stuck with what to do when a month end is interspersed between payment out by bank and payment in date by credit card - I suppose I could make the index sequence annual.

Current method is, with both csv files loaded into one sheet, as one table, payment in are negative,  create an abs(olute) value column and sort on that, which draws together all the similar amounts, which at this point are "so close but so far" from being tallied each with their partners. I quote this just to open a door to additional columns, sorting (ordering), formulae and so on, as well as the csv files being combined into one with column heads (fields) suitably matched.


CONCLUSION to my question

I am not looking for a VBA solution.

I think this is more of an Excel (or even Access) logic problem.

However, for me it is a long time problem I have always had to solve manually, using the human brain provided to me.

My first real question ever and why I have signed up to Experts Exchange, I have this question and one other similar, but the other one is more complicated as the amounts do not match so it can wait.

This first post is aimed at those who do not need examples of the data to understand the problem - which may be more about indexing than anything else.

I have simplified the problem, which in real life involves multiple accounts cross paying each other, and paypal which is another kettle of fish.

I have looked for cross references within each source but never found any.

Having in the past seen the level of genius illustrated in here I will not be surprised to be amazed.

Speed of answer is NOT of the essence here.

Anthony
Comment
Watch Question
Principal
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 7 Answers and 26 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE