Problem: Every year, as an independent contractor I have to rummage through, total, and categorize all of my receipts for the year. The process is not enjoyable, tedious, and time consuming. I thought that if the process were automated, it would go much quicker and much more painlessly. I decided that I would make use of a new iPhone application called ImageToText to automate the process of sorting and totaling my receipts.
Solution:
I created an Excel workbook that can effectively (1) manage all incoming files from ImageToText, (2) upload the data from those files to a database, and (3) import the data from the database into a worksheet on the controlling workbook.
1. ImageToText.
ImageToText is an application for the iPhone that allows the user to take a picture of any text, and the ImageToText service will email a digital version of the information to an email address the user specifies. The data can be formatted as a simple text file, or more usefully, an excel spreadsheet.
The files the email service initially sends is a text file that must be saved as a .xls file before it is usable by our program, but one line of code will be sufficient to automate that process. For the sake of this executive demonstration, the executive teacher’s assistant Nathan Dudley instructed me, in response to my project proposal, to have some sample data ready as if all of the data were downloaded and formatted to be processed by my program. I have included some sample receipts from my most frequently shopped stores to demonstrate my program.
The ImageToText files are loaded into a folder called “Data,” located in the same folder as the Receipt Processor workbook, by a third-party program called GearMage. GearMage downloads all un-downloaded attachments from a specified email address and saves those files in a specified location. For our project, we specify GearMage’s target as the “Data” folder in our main project folder.
This project also requires each user to create a free google email account, designated as the recipient for all ImageToText files. The only emails in that inbox should be emails from ImageToText.
2. Uploading Data Files to a Database.
Nathan Dudley told me that the project would be bolstered by uploading my receipt data to a database, and I agree. All receipt data is extracted from the files in the “Data” folder, from ImageToText, and distributed into a database file, located in the same folder as the “Receipt Processor” workbook. After the data is exported to the database, any files used in the export will be moved to a folder called “Archive” which is located in the main project folder, also. Keeping the files will allow us to review the spreadsheets in case we run into a problem with the ImageToText service inaccurately converting the information. The database tracks which file each batch of data is coming from, in case we need to physically look at a receipt that is garbled, or causing us any other type of trouble--the ImageToText app can sometimes convert the printed information inaccurately. Moving the files will also ensure that we do not put the same receipt data into the database twice.
The programming behind extracting the data from each of these ImageToText files was the tricky part, since I am not able to take a picture of the entire receipt with the ImageToText app; instead, identifying which store each receipt comes from relies on unique markers or wording on the receipt. When I identify certain hints that a receipt is from a certain store (e.g., using the term “amount” instead of “total”) I use variables to identify which store and what type of purchase the receipt represents, right along with the code I used to look for the identifier.
The ongoing part of this project will be when I bring a receipt from a new store, I will need to go into the code and distinguish which markers will set that store’s receipts apart from other store’s receipts. A unique marker will always exist on different stores’ receipts. E.g., if two stores use the same jargon such as “Total” instead of “Amount,” maybe the order of how things are listed on the receipt will tell us where the receipt comes from, and I will need to program the markers into the code anytime I bring a receipt from a new store. The nice thing is that this identification process will not take long, and I’ll only have to code once for each new store I shop at.
The only steps, then, required to get a full workup of your receipts is to run gearmage, then open the workbook and process the information with the buttons on the “Receipts” ribbon. Each receipt type only needs accounted for in the code once, and then the code does not need touched again. You can then just snap a picture of each receipt exactly as you get them, then that big mess of digging through a garbage bag full of receipts at the start of April is alleviated!
3. Importing Into Excel.
After the files have been exported and incorporated into the database, the data can be downloaded into excel to be examined, totaled, and reported. Because the dates vary so much in format from one store’s receipt to the next, keeping a running report through the years is not practical. Users should start a new workbook for every tax cycle.
The data is easily analyzed for totals in each type of receipt by generating a pivot table in the “Receipts” ribbon. Each “Type” is displayed by how much each store contributed to expenditures in that type of receipt.
I programmed this project by myself, although I had practice in each of these programming areas because of our class exercises. I used our practice files as a good starting place, and built on the principles in those files.
I spent several times more time on this project than I have on any of my other projects. Trying to master the logic of identifying what markers are on each receipt, and then coding to match those markers, took a lot of time. However, I believe the returns will be worth the time. The satisfaction of pulling out my phone to take a picture of my receipts to alleviate a headache for myself in a few months will also be worth the time I invested into completing this project. I have appreciated the challenge to apply several of the principles we have learned in class to form a productive solution to a recurring problem I have every year.
No comments:
Post a Comment