extracting data from multiple downloaded excel bank statements with different layouts

[cookienomnomnom]

Hi

Would it be possible to guide me to a video or some resource that anyone believes might address my problem? Or, does anyone have ideas for a solution?

My problem is: -

I want to extract information from downloaded excel formatted bank statements into my excel financial manager I've created.

I need to do this so I can proceed to analyse data based on one, workable record sheet. However, the bank statements come in varying layouts, which has created this problem I now face.

Background: -

There are several different bank accounts that I can download excel statements from, but they all come in different formats. E.g. one bank uses 3 columns in their transaction description, whereas another bank only has one column but with a lot of information (text and numbers) in it. I've gone through the downloaded transactions and filtered out duplicates and in some accounts I have found that there aren't many duplicates. However, there are key words that I've generally noticed that are used in most transaction records, which could be used to my advantage - I would think.

I've mentioned that I've created a financial manager: I'm ultimately trying to create a highly comprehensive financial model for the household and use this for planning and budgeting, among other things, as part of the manager.

So far, I've only recorded the transactions that have been made in person (including cash and electronically made transactions) by ensuring each person provides me with receipts. However, most of the automatically made transactions I've missed and it's difficult to keep up, mainly because there are several different bank accounts. To illustrate, in addition to cash-flow between 3 people, there is also a total of 15 accounts amongst 5 different banks (with very different layouts used in their downloadable excel-formatted statements).

I only use drop-down list menus using the data-validation tool to ensure consistency. The criteria I use (the columns from left to right) when inputting records into my manager include: year, month, day, name, cash or name of bank, account type (e.g. savings or credit card), money in/out, variable/semi-variable/fixed, category, sub-category, remark (remark is not subject to data-validation, so I can freely type other details).

As I mentioned before, the downloaded bank statements have from 1 to 3 descriptive columns, but have many criteria within one column. I want to be able to extract this information so that I can consolidate everything into one sheet, or at least be able to consolidate everything from many record sheets to analyse in terms of something - e.g. fixed expenses - on one sheet. I think it'll be easier to consolidate all accounts into one record sheet and then analyse based on that one sheet.

I hope I made sense. Would someone be able to provide some guidance to a video that addresses this kind of thing?

 

[Rijnsent]

Hi Cookienomnomnom,

I think it's mainly a personal preference... As in: I found over the years of Excel use that I'd rather have 3 small formulas than 1 big formula, especially if I have to track an error, or worse: track errors in somebody else's work. I've had my fair share of circular reference errors and what not. I found that smaller steps make it easier to maintain a spreadsheet. So why trying to squeeze everything into 1 formula? I've had people trying to impress me with long formulas, but the moment their system collapsed it was near impossible to fix... KISS: Keep It Short & Simple
In this case the system load could be a minor point: lots of array formulas do make the system slowing down a bit, but you probably want to give your sheet a spin to find out whether that's obstructive for a proper working of your spreadsheet.

Cheers,

Koen