• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3782
  • Last Modified:

Excel - auto analyze/organize Bank statement csv

I am attempting to analyze my bank statement which I can down load as a csv file. I am not necessarily looking for a "swiss army" knife approach, just simplicity.  This does not have to be a finished product, just some directions in how to capture the data and display it.

I would like to drop the raw csv file in one worksheet and in another have it set up to  look at the transaction description column and search on key words that are listed in category columns, capture the debt or deposit for that description and then sum up these categories so that it gives a picture of where expenditures are going.

This needs to accommodate varying number rows since the number of transaction can vary month to month. SampleBnkStmnt.xls
  • 2
1 Solution
TracyVBA DeveloperCommented:
Sounds like you can use the sumproduct formula to do this.


See attached example.

TracyVBA DeveloperCommented:
Here's a more specific example from what you attached:

=SUMPRODUCT((bankstatement!$B$9:$B$23='Summary of Expenditures'!B4)*(bankstatement!$C$9:$C$23))

rpelfreyAuthor Commented:
This is a good beginning approach, but doesn't take in account that the description is not that "clean".  Those words in your example would appear in the transaction description but additional transaction info is in there like store name and #, trans #, sometimes location address, and or phone number.  
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now