thacca
Blogger
Share this content
0
35
36112

How to convert hard copy bank statements to excel

How to convert hard copy bank statements to excel

Does any body have any software recommendation for converting hard copy bank statements (via scanning) to excel/csv.

Replies

Please login or register to join the discussion.

avatar
By jpcc1
30th Nov 2012 15:12

You can use an OCR programme like Omnipage, this will take a scanned in file (PDF or TIFF) and export as an Excel file.

You can set up templates for different bank's statements to ensure you just capture the data you want and not the extraneous data like the bank's logo, address etc.

There will be OCR mistakes, e.g. it will read a 3 as an 8, but you can then set up a simple 'control' column in Excel to check that the closing balance on the line above plus credits less debits gives you the closing balance on the line below.

Manual corrections will have to be made to any errors.

AND... you will have a lot of trouble where information for a single is printed on two or more lines.

It's still worth trying if you've got a lot of statements.

 

However, a better and more expensive solution is to use specialist software, e.g. Altia Financial Investigator Toolkit, that automate the process and automatically sort many of the issues and highlight the ones requiring manual intervention.

This does the job excellently (and is used by police, etc).

Thanks (0)
30th Nov 2012 15:14

Able2Extract

Used it for years, it converts PDF to Word, Excel or Powerpoint and has saved me lots of times with tables of numbers.  It's far better than even Adobe Acrobat itself.

Thanks (0)
avatar
By Nana111
04th Dec 2013 07:10

reply

Paul Scholes wrote:

Used it for years, it converts PDF to Word, Excel or Powerpoint and has saved me lots of times with tables of numbers.  It's far better than even Adobe Acrobat itself.

Hi there

I am a newbie here ,and thanks for your sharing.Actually i am a new user of excel.I am looking for a powerful program which supports to convert excel to PDF files.

And i want to get a free trial package before i decided to purchase it.Thanks for any suggestion.

Thanks (0)
avatar
30th Nov 2012 15:19
Thanks (0)
avatar
30th Nov 2012 15:26

tried and tested

It depends what you are after and how detailed your subsequent analyses are required to be.

I have used Cogniview PDF2XL Enterprise with some measure of success but unfortunately the manual intervention to get things correct  - see previous posters comments re bad characters and descriptions - has outweighed the perceived advantage of a fully automated system. Unfortunately it never becomes fully automated.

I find that the services of an excellent data inputter who can split the descriptions as needed into separate columns (to aid subsequent analysis) and work with an agreed template is worth her weight in gold.

Please come back if you ever find a system that works to a 99.9% accuracy with minimal manual intervention.

regards

Bill

www.wamstaxltd.com

 

 

 

Thanks (0)
avatar
30th Nov 2012 16:24

Is it possible...

...for you to download CSVs directly from the online banking?

 

(You've probably already thought of it but just thought I'd check).

Thanks (0)
avatar
30th Nov 2012 16:38

able2extract was what we used yars ago

to scan in royalty statements i had forgotten until recently reminded by paul

you could try adobe for 15£ for a year its done on line

Thanks (0)
avatar
By AccSec
30th Nov 2012 17:23

Does Able to Extract Really Work?

Paul

The point you make about this software seems interesting. We have never used it. Last year we used different software to see if we can convert paper copies of Bank Statements to Excel or PDF copies of Bank Statements to Excel. We used Omnipage, Adobe Acrobat, Paperport but none of these work sufficiently well for us. Nowadays if we have a BS in PDF format and it is being problematic to convert to Excel, we just send it online to Nitro and they return it back to us fully converted  FOC.

However, I'd be interested to learn how successful you have been in converting (a) Paper copy Bank Statements from different banks  without any markings, to Excel & (b) Paper copy Bank Statements from different banks  WITH markings by client, to Excel. At one stage we got so fed up with the results that we just gave up conversion & nowadays analyse them manually :(

Thanks (0)
avatar
30th Nov 2012 18:06

Problem solved previously?

 

This question has cropped up on a couple of occasions previously.  There were some interesting answers on this thread:

http://www.accountingweb.co.uk/anyanswers/scanned-bank-statements

The OP in that case was very happy with one suggested solution AutoRec, now ocrex.com as mentioned by BigBadWolf above.  

I see that the company has had some changes, including to pricing by the looks of it.  

I haven't used it but it sounds interesting if you do a lot of that type of work.

Thanks (1)
avatar
By Old Greying Accountant
30th Nov 2012 18:07

It does, but ...

... best on a pdf that has been generated directly from software.

For scanned documents you need a cleanish, decently printed document, and you need to get it square otherewise the time spent proof reading and correcting can be more than re-keying, especially if lots of identical lines that will auto suggest.

 

Thanks (0)
avatar
02nd Dec 2012 15:40

AutoRec does exactly this. You can take a look at it in action in the video demonstration on the website www.ocrex.com so you can see what it does.

Unlike generic recognition software it actually makes most of the recognition corrections before the data is presented to the user. It can do this becuase it auto-detects what type of statement it is (Barclays, RBS, HSBC, etc) first so that it then knows what the structure is. It knows what date format they use, how they identiry presented cheques, which columns are numeric etc etc. It will also ignore non transaction lines such as "Balance Forward". 

 

If you'd like a live webinar demo, just give us a call on 020 3393 3058 or drop us an email to [email protected]

Alternatively, if it's a one time job and don't want buy the software, we will happily do it for you for a small fee.

 

Thanks (1)
06th Dec 2012 18:14

HELP

I have a one time job, help please, will email you

Thanks (0)
avatar
10th Dec 2012 09:48

One time job.

gaylebuchanan wrote:

I have a one time job, help please, will email you

Hi Gayle, 

Thanks for the business, I gather you were happy with the outcome?

Thanks (1)
11th Dec 2012 08:13

paper statements to .csv for Xero upload

Super High 5, thank you sooooooooooooooo much - there are a couple of kiwi bookie chics lining up to marry you for the service lol!

Thanks again - fantastic service

Thanks (1)
avatar
11th Dec 2012 17:28

AutoRec

gaylebuchanan wrote:

Super High 5, thank you sooooooooooooooo much - there are a couple of kiwi bookie chics lining up to marry you for the service lol!

Thanks again - fantastic service

 

Photos please, PHOTOS!! :-)
 

Thanks (0)
avatar
02nd Dec 2012 16:43

@ ocrex
Is there a trial version?

Thanks (0)
03rd Dec 2012 09:52

Able2Extract

@AccSec - It's a sign of our times that it's taken me 15 minutes to find a paper bank statement, it's an old one of ours.  Once scanned it took the software 10 seconds to generate an excel spreadsheet, with no errors.

Following up on OGA's point, you have to have a character recognised PDF. In the majority of cases software produces this, as does our scanner software, but if not, ie if you just have a plain scanned image you need to run OCR software and the key here is to make sure the scan is of good quality (eg say 600 dpi).

This is the main difference between Able2Extract standard & professional versions, the latter, which we use, comes with OCR conversion built in.

My scan was without hand annotations which, depending on their position and strength will throw any sofware (it's not a magician).

Again, I can't remember the last time a client sent me a paper bank statement with hand notes on it.  I'd tend to feel that if there's not a software solution to make the best of a bad job, the humans have to sort something out.

Thanks (0)
avatar
By AccSec
03rd Dec 2012 15:52

Thanks Paul for the clarification

@ Paul I will try Able to Extract and see how it goes. Thanks for your guidance. Most appreciated.

@AutoRec-Our needs are not really Bank Reconciliation, more bank summarisation for business receipts and payments. As a result, the software is an overkill for us. Also, when when we had a demo of the same, we found the cost was quite high in relation to the no. of Bank Statements we wanted analysed purely for picking up related receipts and payments.

 

 

Thanks (0)
avatar
05th Dec 2012 11:58

Monarch?

If the "paper" statement has arrived as a PDF (vs a scan of a piece of real paper), then you could try Monarch by Datawatch which is very good at converting computer reports of all types back into data. I have used it for many years for many purposes including some bank statement pdfs on one occasion.

Thanks (0)
avatar
By goblinf
05th Dec 2012 12:07

minimising repetitive error catching through automation & macros

If you've a lot of them to convert and once converted there's a lot of manual checking to do - try spending some time thinking about whether the errors are repetitive and whether you can automate some of the error checking using excel macros.  In particular if you're gifted at macro writing, it is possible to check for duplicate entries and also to amalgamate data from several rows (if you've worked out the common denominators for 100% of instances of whatever you are checking).

Takes time to set up, but saves time on large checking jobs.

We used a firm called Data2Impact a few years ago to write specific macros for us to automate error catching after taking inadequate CSV files into Excel during one huge data checking exercise at a contract I was doing.

Thanks (0)
avatar
By AccSec
05th Dec 2012 12:22

Impressive stuff

I am very impressed by the contribution that my fellow colleagues have made on this thread. It makes me happy and very proud to be associated with all my colleagues and of course, a big thank you to Accounting Web who make this website so vibrant, dynamic and flexible to use.

I will certainly trial out the additional software noted. With the advent of tax offices becoming heavy on adequate record keeping, Bank Statements & Credit Card need to be analysed and a lot of our clients still don't use internet banking & therefore rely upon sending us hard copy statements. I hate to see my colleague Accountants summarising client statements on Excel one by one. We have to date not found a 95% of the times workable solution of OCR & conversion to Excel software. Hopefully that will change with your useful suggestions.  

Thaks once again to all.

 

Thanks (1)
avatar
05th Dec 2012 13:49

AUTOREC

We have used this.  This takes the bank statement and converts into a posh excel spreadsheet.  My assistant thinks it is great.  An afternoon job of 28 bank statements took just 45 mins.

Try it for free at http://www.ocrex.com/home

 

Thanks (0)
avatar
06th Dec 2012 12:39

AutoRec

pauljohnston wrote:

We have used this.  This takes the bank statement and converts into a posh excel spreadsheet.  My assistant thinks it is great.  An afternoon job of 28 bank statements took just 45 mins.

Try it for free at http://www.ocrex.com/home

 

 

Glad to hear it Paul. I did laugh at your description of the "posh excel spreadsheet" - brilliant. 

For those interested, It's hard to explain what the "posh" means in a few lines but if you do have a lot of paper statements being processed in your practice, invest 9 minutes to take a look at the AutoRec demo. The demo shows 9 pages of scanned bank statements on a regular scanner with regular issues like marks and punchholes etc. being extracted, verified to 100% accuracy, reconciled and analysed such that it is ready to be exported to excel or csv. In fact, it'll export to specific CSV formats to suit various accounts production packages. This way, you can just import the csv into IRIS, Digita, etc. 

P.S. Paul: I'm guessing it was possibly your assistants first time using it as I would expect once a little more familiar, a 28 page job could be some notably faster still. 

 

Thanks (0)
avatar
06th Dec 2012 12:23

We do scan documents and store them for reference purposes including bank statements but for the processing of bank statement data we use Banklink. We’ve been using Banklink for a couple of years now and  bank statements I used to chase for now have come direct from the bank every day straight into Banklink’s clever software interface that also precodes most of the entries and then drops them into TAS or Sage to complete the bookkeeping.  Banklink has halved our bookkeeping effort and gives us more time to concentrate on other things (like the January filing deadline. !) Any accountant interested in finding out about Banklink can get details on www.banklink.co.uk

 

Thanks (0)
avatar
06th Dec 2012 12:40

Video demo of AutoRec

Sorry ... I forgot to leave the link ... see below

http://www.ocrex.com/autorec-video

 

Thanks (0)
avatar
10th Dec 2012 08:05

Re Auto REc Comment

The scan takes very little time however the ocassional misread and collating and categorising of the data is what took the time.

Thanks (0)
20th Dec 2012 18:00

AutoRec did it for me

I tried AutoRec after reading this thread and I have to say I have nothing but praise for the software and the support staff behind it.

I was faced with a large volume of 'bank statements' covering a period of several years on several bank accounts from more than one bank. My task boiled down to identifying which credits to the accounts comprised the deposit of cheques drawn from one or other of the remaining accounts.

I could do this manually but with several hundred pages of bank statements I wanted a faster and easier way. The obvious answer was to get the transactions into spreadsheet form and then sort and cross reference them.

But (there's always a but . . .) the 'bank statements' were not always proper bank statements - they were transaction lists generated by the banks in response to a disclosure request. And the copies I had were photocopies (or photocopies of photocopies) which were often skewed and not of great quality.

Added to that the transaction reports from one bank were laid out in columns which were offset but over-lapping.

Even the bank which did generate genuine copy statements produced them (naturally enough) in the design and layout that the bank was using when those transactions occurred - which is not the statement layout they use today.

So getting this data into spreadsheets was never going to be easy (except perhaps by some very labour intensive manual keying in).

I tried using Able2Extract but that failed due to the skewed copies and the over-lapping columns.

So I tried AutoRec but that failed as the 'bank statements' were not in a layout which the software could recognise.

But (there's always a but . . .) an email to AutoRec support and they were on the case straight away. They produced new templates to enable the software to recognise the non-standard layouts which I had. And hey presto! The data is now in excel spreadsheets and the daunting task which I had faced was cut down to size.

So well done and many thanks from me.

The software was not cheap but it has paid for itself already!

David

Thanks (2)
avatar
By MaryD
26th Mar 2013 10:08

AutoRec Prices

How much does this software cost. I am an accountant working with about 40 Shoebox clients?

Thanks (0)
03rd Apr 2013 16:53

Prices

Mary, I know you have just got the bronze package from AutoRec so you know the prices. 

For anyone else prices start at £99 for an annual subscription. Our prices are completely based on usage. With licences to suit every practice, AutoRec can provide thousands in annual cost savings for your accounting practice.

Unlike other accounting software packages we do not charge separately for support and training. These costs are built into the annual subscription in your AutoRec licence. AutoRec Prices - http://ocrex.com/pricing

 

Thanks (0)
avatar
13th Sep 2014 18:31

Tool to covert Barclays PDF bank statements into CSV
Hi, Since Barclays are very frustrating with not publishing their bank statements in CSV, and only in PDF, I've just made a tool in node.js to help my girlfriend convert 18 months' worth of Barclays business account statement PDFs into CSV. It might not help with the original question about scanning hard copies of PDFs, but it will help if the person asking can get those documents in PDF form. If you want to give it a go, it's up on GitHub here: https://github.com/penrosestudio/barclays-bank-pdf-to-csv. Cheers,  Jonathan

Thanks (0)
avatar
18th Sep 2014 16:34

Help

This looks great, I'm not that tech savvy but have managed to install Github and clone the repository - not too sure what do to next. This will help immensley if you could advise.

Thanks (0)
avatar
17th Oct 2014 00:19

Smartrex(OCR) - Convert Bank Statements to Excel

Hi Everyone,

 

Smartrex (OCR) is a bank reconciliation and analysis software. It reads bank statements and extracts information and convert bank statements to excel making it an effective bank statement reader software.

www.smartrex.co.uk

 

Smartrex(OCR) is the web based solution making it only Online Bank Statement Extraction software in the market which extract information convert bank statements to Excel, PDF and CSV.

 

I came across this software which is real value for money. It is the cheapest I found in the market which convert bank statements to excel. They do not charge per page. In just £50 per month you can convert unlimited bank statements(PDF/TIFF/JPG) to excel. i.e 1000 or 10000 pages of statements. They do not have restrict you. Simply amazing and there is no hidden cost.

http://www.smartrex.co.uk/smartrexpricing.html

 

Do not forget to thank me if you find that useful. Thank you.

Thanks (0)
avatar
By Neha
30th Oct 2014 05:33

For Large Banks

Does anyone know what do large banks like Barclays, HSBC etc use for their internal operations as an OCR solution? and why would they go with one product than the other?

Thanks (0)
avatar
19th Nov 2014 18:50

OCR of statements for large banks

Neha wrote:

Does anyone know what do large banks like Barclays, HSBC etc use for their internal operations as an OCR solution? and why would they go with one product than the other?

 

Large banks face similar problems to small accounting firms in this regard. On behalf of OCREX, I have been engaging with one of them already regarding the supply of AutoRec. They receive lots of statements originated from other banks in various departments for loan origination and other reasons. 

Regards,

Brendan Woods

 

Thanks (0)
avatar
31st Mar 2015 16:00

Bank statements to Excel

Pay-as-you-use bank statement reader bureau service launched.

Xaltia Data Services have launched a bank statement reader service that will provide you with an accurate, error-checked, and consistent Excel spreadsheet for any bank statement from just about any bank in the world.

Using Altia Solution's tried and tested software you will receive back all the data in a spreadsheet for you to analyse for your clients.

This software is trusted by police and financial investigators in the UK and across the world to accurately extract the data from bank statements to assist with prosecuting fraudsters and recovering their assets.

Visit www.xaltiatech.com to find out how it works and see how cost effective it is!

Why spend another day keying figures into your spreadsheet - let Xaltia give you the time to do the important stuff!

Cheers

Ian

 

Thanks (0)