JD
Blogger
Share this content
0
10
6108

Coding/Analysing Bank Statements

Coding/Analysing Bank Statements

While it does not take too long to analyse bank statements downloaded into an Excel/CSV file, into its different expense classifications, has anybody been able to speed the process up sucessfully using a lookup table or similiar. I have tried several different methods (including using the previous year's analysed statements) but so far I am not quite getting there.   

Replies

Please login or register to join the discussion.

05th Aug 2011 15:27

This works for me

I just set one column for an expense description, then type the description in. If you start to type the same description again, Excel remembers it and gives you the option of accepting it or completing a new description. If you sort on bank transaction description before starting it will group a lot together, so you can just drag the expense description down the rows (but dont forget to set up row numbers first, in case you want to restore the original sequence).

Once everything is analysed, you can either do a pivot table, or sort on description, and then use the subtotal feature.

I tend to use: sort on desc/date, and then subtotals, as it is then easier to view all the transactions, or just the totals.

Thanks (0)
avatar
DotasScandalDotOrg
05th Aug 2011 16:57

Or

ShirleyM wrote:

 

Once everything is analysed, you can either do a pivot table, or sort on description, and then use the subtotal feature.

I tend to use: sort on desc/date, and then subtotals, as it is then easier to view all the transactions, or just the totals.

 

Or use SUMIF()

Thanks (0)
avatar
By JD
05th Aug 2011 19:03

 

 

Many thanks for the comments ShirleyM and would agree the method you have described (and I also use) is relatively quick even with large amounts of data. I tend to prefer subtotals to pivot tables as well. However having completed the analysis in this way for year one, is it possible; for instance; to use that analysed statement as a look up table (or simliar function) in year two making it a simple job of copy/pasting a formula into the relevant column to generate the expense classification   

 

Thanks (0)
ShirleyM
05th Aug 2011 19:24

Lookup

You could use lookup, if you think it worth your while. Lookup, if set up correctly, could look for the same bank transaction desc, and then use the expense analysis for that desc. Whether it's worth the effort ..... maybe, but I've never felt the need.

Thanks (0)
avatar
By paul.k2
07th Aug 2011 19:43

My Experience

JD I have done something like this for a client.

They have a large number of bank transactions that they have to analyse to their Nominal.

I use a lookup that builds the transaction file that is interfaced to their accounts system.

I think the big difference with your situation is that virtually all of the transactions are electronic and so I have a set description to work with. There are a large number of transactions and the process is definitely worth the effort.

To the best of my knowledge, in about 5 years it has only failed twice and on both occasions it was when the decriptions on the statements changed.

I have considered using something similar for a firm of accountants I am working with, but I do not see it is a viable solution for a practice with lots of staff and clients. Excel is not a tool for building systems. At present I am concentrating on finding a specialised tool.

Paul

www.kellysolutions.co.uk

 

Thanks (0)
avatar
By JD
07th Aug 2011 20:19

 

 

Many thanks Paul

Worth finding a solution, given the amount of time spent on what is a simple data processing exercise. The lack of a set desrciption is what has caused us a problem. Any suggestions of a suitable specialised tool welcome

    

Thanks (0)
avatar
By paul.k2
07th Aug 2011 23:07

There is hope

JD nothing definite yet, but I hope to have settled on something by the Autumn.

Thanks (0)
avatar
12th Aug 2011 17:18

coding using filters

I use autofilters for showing just positive figures, then enter sales and copy down to all transactions, then filter for small transactions - usually bank charges, then filter for similar descriptions in "contains" field and code that. itis pretty quick

Thanks (0)
avatar
12th Aug 2011 17:30

I find that the quickest way to do it is to first add an index column to number each entry consecutively.

a) Sort the lot on receipt amounts.

b) Remove the payments block from the bottom and paste them into a new worksheet.

c) Sort on payee or customer name.

d) Head up and extend each block out into the analysis column.

You can then add comments etc for any entry in a notes column.

Finally, re-sort the whole analysed block into the index order your started with, and all the information is before like a beautifully analysed cash book! (Wages, PSA, Motor...)

For those who want pivot tables to retain the best of both worlds, keep the un-analysed data on separate worksheets.  The index column enables item-by-item analysis, which prevents date order from grouping each day's transactions.

Worth a mention is the "Data" -> "Text to Columns" facility, which can be used to weed out any unwanted text.

 

 

 

 

Thanks (0)
avatar
12th Aug 2011 17:42

Account number

I have a simple list of standard code numbers and names which are like a cut down nominal code chart.

I quickly whizz down each months entries applying the relevant code to each item.

I then use pivots to summarise or extract whatever I need.

Codes at least overcome inconsistent descriptions and of course can be mapped to the accounts prep codes at the year end.

 

Thanks (0)