Import Online Banking Transactions – VBA Final Project

Shawn Tabrizi's Executive Summary

The system that I built for this project is meant to automate and simplify the process of consolidating bank transactions into a P&L statement, and a statement of tax deductible expenses for the year. Rather than solving a business problem, this solves a personal financial management problem by partly automating the process of producing monthly income and expense statements and a tax deductible expense report.

The motivation for building this system comes from my own personal financial management habits. For several years, I have tracked and summarized my family’s finances. For us, a P&L statement is helpful for two reasons. First, it gives us the format to budget our spending in a way that makes sense for our family. Based on projected cash flows, we can forecast debt repayment plans, make purchase plans, and make other crucial decisions. Second, it’s very helpful to see how our actual spending habits compare to our budget. Without this information, we would likely be spending much more money than we do. Lastly, aggregating our tax deductible expenses throughout the year into a simple and streamlined report aids in simplifying tax reporting and record keeping.

The Intent of the Code

When I sought after this project, I wanted to organize the code into three components, each associated with a button on the “Transactions” tab of the workbook. The Import Transactions button is meant to do exactly that. The goal was to make this sub procedure completely customizable. If I only wanted to pull transactions from one account, I should have that option. If I only wanted transactions for ten days, I should be able to do that, and so on. The Edit Transactions button is meant to edit or add to the imported data. One poignant example is the need to designate tax deductible expenses, as online banking services do not have that information. The Generate Statements button is meant to use the imported transaction data to aggregate the transaction data among the other two sheets, “Cash Flow” and “Tax Deductions.” The optimal system would do the same thing for multiple years.

What the Code Does

As written, the only component that works out of the three above is the first. The reason for this is that I grossly underestimated the scale of this project. Because I wanted to make the process of importing transactions so flexible, there was over 50 hours of coding (from a rather inexperienced coder, mind you) that went into that component alone. So, though I intend to complete the project with time, the system as it stands only imports transaction data.

Learning Points

As I spent so much time automating a spreadsheet that is really for my use only (though any chase.com user can use it with a little customization), I wondered if I would have any ROI. However, much of that 50 hours of work was spent going through a problem-solving process that is unique to programmers, I think. Mostly, this project ended up being a refining immersion into the world of programming that I believe will benefit me personally, as well as in my career.

Links to Project Write-up and Workbook

http://files.gove.net/shares/files/14w/stabrizi/Shawn_Tabrizi_Final_Project_Write-up.pdf
http://files.gove.net/shares/files/14w/stabrizi/Shawn_Tabrizi_VBA_Final_Project.xlsm