Working full time, I recently created a budget sheet. With the “Money” App, I am able to record transactions in real time. Unlike Bank Statements, it conveniently breaks down transactions by categories and sorts accordingly. It has the ability to send reports in CSV format. Unfortunately, the format is less than ideal when I want to append monthly reports to a larger budget sheet. I didn’t want to tediously go through and copy every transaction, so I wanted to automate the process somehow.
I created a program based in Python that takes the report from the money app, creates a temporary load document, and then appends it to a master budget sheet.
Here is a screenshot example of what it an input document may look like.
At a glance, I am envisioning making a 2 dimensional list. It will contain the category of the transaction and transaction items itself. The program will go line by line. Ultimately, this will be an exercise of string formatting and manipulation.
A list of categories can be easily made. As the program goes down the line, it will split each line by commas. And if the string can’t be split, then it has to be a category and will be stored in a list. The program will ignore column titles since those values will be consistent. I will most likely hard code those values in. I will ignore “Total Expense: x”,”Total Income: x” because that is better suited in excel or R.
Now how do we draw the data? I think i’ll have some sort of condition to tell the program that this is data I am looking for. Once that is done, it won’t take too hard to pull the data since when you do a string.split by commas. The values will be in a ordered list.
Thankfully, python can open, close, and write documents so we can capture and write csv simultaneously.
This what goes in my head when it comes to programming. In what criteria can I pull the data based on how it is presented?
However, I can already see limitations such as the columns and how I am capturing the data with the app. The notes column though informative is useless when it comes to data analytics.
Ask User InputRead Input File line by Line
If the line is a “Category”, store it in a list A
If the line is a transaction, store in list B
Process strings in List B
Read and Append new lines based off of List A and List B
Close Files and Program
- Right now, it is very specific to a text file. Would it be interesting that it doesn’t matter with the format that the program can read in the data and sort into columns accordingly? But that would be much further down the line.
- I want to put security measure to increase data integrity such an incorrect format.
- Data Analytics
- From Excel or other data analytic tools, I could make graphs according to my spending and break it down by category, time, and vendors.
- Work Application
- With enough practice with string manipulation and processing, I am hoping to apply this to my workplace in an effort to automate some tasks