What do you do if you have a year (or more!) worth of paper credit card statements and you want the individual transactions itemized in Excel? The thousands of transactions would be a nightmare to input manually – is there a way to scan to Excel and have the data populated automatically?
This is the situation that Linda English, a Professional Organizer with Organizing With EASE in the New Jersey/New York area found herself in for a client project.
She recently shared her project with other National Association of Professional Organizers members, and I thought it would be an interesting case to feature here on DocumentSnap.
Can You Download The Statements?
Of course, the easiest thing to do would be to go to the bank website and download the transactions in Excel or CSV format. Then you don’t need to bother scanning at all.
Most banks allow you to do this, but in this particular case the statements were too old. They tried it get them from the bank electronically, but no luck. Time for Plan B.
Scan To Excel Function of the Fujitsu ScanSnap
After some investigation and talking to Fujitsu, it looked like the ScanSnap may be able to do what they need to do.
She picked up a ScanSnap iX500 and went to work.
Here are the results and lessons learned in her words:
Success!! For those interested, here’s how it went. Overall, yes, using the ScanSnap saved so many hours of tedious data entry, and potential for data entry error. I was able to create a spreadsheet of 3,000 transactions in 12 hours, including the manual adding of categories and personal notes my client had written down for each entry (that’s approx 250 entries per hour – pretty darn good I must say!).
Here’s some feedback on using the ScanSnap for this project:
- Setup and connection to my computer was fairly straightforward. I’m no expert yet, but was able to fairly quickly figure out how to use it for the purposes of this project. So much more functionality to explore, and I’m looking forward to it!
- It’s not a simple scan and it populates Excel. You scan it either into Excel or Word, and then you copy/paste the data into your master Excel document and tweak the formatting a bit more in Excel. I found that some pages kept better formatting when scanning into Excel, and some were better into Word. There was no rhyme or reason, just did trial and error. There is a fair amount of manual cut/pasting and reformatting needed, so you need some good Excel skills.
- Scanner got very confused with the handwritten notes my client wrote on the statements (when I say confused, I mean the data got jumbled more when moved into Excel or Word). When I covered them up with strips of post-its, the formatting and capturing of data got better, but there were still some pages where I had to manually enter some data because the format of the data in the capture would have been harder to fix than just manually typing it.
- Most data captured was accurate, but occasionally I had to fix a transaction’s date or charge amount because it couldn’t be read due to the quality of the document being scanned – those were easy to find. I’d say 95% of the data scanned accurately without my intervention.
Overall, a great product to tackle this kind of project, but you need pretty good skills in Excel.
Thanks Linda! I’d say her findings line up pretty well with mine. Scan to Excel (with the ScanSnap anyways) works quite well, but the document needs to be structured in a way that makes sense for a spreadsheet.
It’s also not a simple case of “scan it and forget it”. You do need to go through the spreadsheet and tweak things in most cases. Still a heck of a lot easier than typing it all in though.
You can find more about scanning to Excel in my Unofficial ScanSnap Setup Guide, and you can learn more about Linda’s services over at Organizing with EASE.
