Trying to better budget using excel

I'm not looking for an answer here but a bit of guidance which rabbit hole to explore.

What I want to be able to do is to import my monthly bank statement into excel, and automatically allocate each entry a certain category, and then create a chart against it to compare against my budget.

So, for example a transaction with a description of tescos would be allocated to 'groceries' and I can total up all my monthly groceries rather than painfully going through all the transactions one by one.

I've had a look at conditional formatting and lookups but have not managed to get far. Would macros be the way forward here, or can you do lookups as part of creating a chart?

Thanks in advance.

you could have a lookup table in a separate sheet -

so a list of items and the catorgories

in column A > B

tesco > groceries
SEb > Electric
SWW > Water
sainsbury > groceries

Then use vlookup() to assigned those categorises
and then you could use another area to sum by those categories - using sumif() and use that to produce the chart

can you load a sample spreadsheet with dummy data - and what items need to be categorised - then we can work out the rest for you

i have added a sample

If you want to have a history of months - then that can be setup as well - but we need to see the format of the data and know the outcome you need

This is a great start for me. Instead of vslookup is there a function that gives you the category is the word is found within the text?

Eg, my descriptions vary a lot. I could have one called "merchant 1919 tesco express london" and another one called "tescos extra camberly 1121". So i'd like a function that would set it to grocery if it contained the word tesco.

i'll add a sheet to show what i mean when i'm back on a computer. Its a bit tricky doing it on an ipad!

Thanks v much.

yes, it may be possible to do a search, but do you have a years worth of history and you can then make the reference table to include all the different elements
as we would be searching for other things as well , so you could end up with a huge list of searches

My suggestion is that Macros would not be required for this project. To do anything like this and make it truly automated, you'd need a list of the IDs that show up in your statement and associated categories... then you could import or past in your financial data into a predefined range. Your formulas would then convert that data give you whatever 'business intelligence' you desire. You could set up a pivot chart or pivot tables based on the range of data in those formulas to give you a set of data that is easier to work with.

1. Initial data -> 2. formulas to convert (based on lookups and categories) 3. -> Pivots and or Pivot Charts for Presentation.

Good Luck!

-Brian
www.excel-help.net

Ok I'm making progress - many thanks for the advice so far - I've created a VLOOKUP against all my bank statement info, but its a bit crude.

I have merchants such as:

Marks & Spencer CAMBERLEY
Marks & Spencer Sspltd PADD BR STN
Marks & Spencer Sspltd READING STN

and ones like these:

Tesco Store 3149 SANDHURST
Tesco Store 3149 SANDHURST Withdrawal 03 February 2013
Tesco Store 3149 SANDHURST Withdrawal 03 March 2013
Tesco Store 3149 SANDHURST Withdrawal 10 March 2013
Tesco Store 3149 SANDHURST Withdrawal 17 February 2013
Tesco Stores 3149 SANDHURST
Tesco Stores 5315 CROWTHORNE

Is there a function that works similar to VLOOKUP but rather than look for an exact match it updates successfully if it matches within the field? I've tried looking at the find function but that just returns the position with the match starts. The match function is similar too.

Maybe I need to create a function here? The reason I ask is because each month I'm likely to get new descriptions and will have to grow the lookup table accordingly, making this a bit pointless as I'm trying to reduce the effort not increase it!

eg:

Sample Data
A B C
1 Description Amount Category
2 Sainsburys 2152 03 March 2013 £13.40 Groceries -> searches for reference to A2 in ref data and sets category to groceries
3 Sainsbury 2152 13 March 2013 £24.80 Groceries


Reference Data

Field Category
Sainsbury Groceries

Thanks again.