Credit
Card Expense Analysis
Executive
Summary:
The purpose of this
project was to develop an automated system that would log in to an online
credit card statement, download the transactions, and sort them into categories
based on the vendor. In this case the business was an individual, rather than a
corporation or other business entity, however the basic principles of the code
can be used by other entities trying to run a similar process. Once implemented
the program would result in a 95% improvement in processing efficiency.
Prior to the
development of this VBA process, the user had been manually going into the credit
card transaction history 5-6 times per year and pulling the transactions into
an excel sheet, where the user could then manually enter the data, and
categorize based on vendor. Overall this process displayed some inefficiencies
since work was tedious and redundant, and the time invested in updating the
data was not worth the effort.
As a result, the
user saw an opportunity to develop an automated process that could log into the
credit card system, and perform these operations on its own. Once created, the
system would save the user 4-6 hours every three months. In addition, the
completed system gave the user data in a form that was easier to manipulate,
since the final output created a pivot table which could then be manually
changed to view different spending trends or transaction types.
The outcome of the
project provided the developer of the program the opportunity to learn numerous
skills that can be applied to business settings in the future. For example:
1.
How
to work with web pages using excel and VBA, including how to read html code
from a webpage
2.
Automation
of Pivot Tables using Excel
3.
Manipulation
of the Ribbon to include a new button
4.
Programming
ribbon buttons to run sub procedures
5.
Provided
practice with Do, Do Until, & Do while Loops, If statements, offset,
current region, row highlighting, and nested loops
This program has
practical application for small business users who manage their own books, but
don’t want to purchase software to track expenses. With the use of this
program, a user can easily pull credit card transactions, build a pivot table,
and then manipulate the data however they see fit. They can track expenditures
by day, compare month-to-month spend to different vendors, and compare total
spend to different classes of vendors (i.e. food service, materials, office
supplies, rent & utilities etc.).
In conclusion the developer
believes that when used properly, this program can save time, and serve as a
strong management tool that will provide managers with data in a useable format,
promoting improved business decisions.
No comments:
Post a Comment