Designing a Financial Calendar: Part 2

September 26, 2020

In Part 1 I took some time and talked about what lead to me programming a financial calendar. In today's post, I plan to talk about some of the functions and why I still use it today although some alternatives exist. 

The concept was really simple, create a calendar that allowed for transactions to be added and account balances to be tracked. I had two bank accounts, a credit card account, and a savings account. The software uses the transactions in the database to calculate the running total of each account that it is tracking and then goes day-by-day displaying the transactions for that day as well as the updated account balances. The bank and savings account would show as positive and the credit account would show as a negative number. Ideally, the bank accounts would be more positive than the credit card was negative. Although I thought about implementing alarms from time to time, it never really came to that. 

Implementing these core functions up and running for a time was enough for me to work my way from the early struggle of balancing money out vs money in back to a much more stable financial situation. Over the years I've slowly added features and updated mark-up. I created graphs, balance sheets, donut charts, and more. One year I helped teach an IOS course at the school and I programmed a native app for the calendar. At one point I had to rebuild the transaction database to add fields and make the structure fit the required data better. All of these features were useful in their own way, but there were a few that really made a difference in how I used the software.

At some point, I noticed that both my bank and my credit card company offered ways to download my transactions in a CSV (comma-separated values) formatted file. These files are very basic ways to save tabular data, like that found in an Excel spreadsheet. While the file does not contain formulas like some spreadsheets, it is a good way to transfer data between applications, being readable in most programming languages. Using this data it became possible for me to verify the data in my calendar. All I had to do was download the CSV and import it into my software. It would then check that transactions were on the correct date for the correct amount. By using the bank's transaction identifier I was able to mark which transactions had been verified and which hadn't. This one feature made it much easier to keep my balances up to date. 

Another feature that has significantly enhanced just how useful the calendar can be is a prediction system. I will explain my approach but if anyone reads this that is interested in coming up with a more sound solution I would be glad to work with them. Just like the ability to plan ahead really helped me when I was first getting started with this program, I wanted the calendar to be able to predict transactions that might be coming up without me entering them manually. To do this I build a list of transactions I have paid in the last 2 years organized by who was paid. For each group, if I have enough transactions, the software checks how many days are between each transaction. It then eliminates any outliers by calculating the standard deviation. By testing how many of the transactions fall into this pattern a confidence level is established. Any transactions over a certain confidence level are determined to be reoccurring. Finally, the reoccurrence is tested for relevance. An irrelevant reoccurring transaction is usually one that has since ended. If it is determined to still be valid it is added to a list along with the expected cycle length and how much the last transaction was. This list is then used to pencil in the predictions for the rest of the calendar year. There are approximately 5 or 6 payments that my calendar is currently monitoring as repeating transactions. These include things like gym memberships, paycheck deposits, savings deposits, and interest payments. 

The prediction system isn't perfect. For a while, I was eating Subway almost every day for lunch at work, but since I wasn't eating it on weekends the level of confidence was not reached. This got me to thinking about other ways to try to predict a repeating transaction. Although I've put it aside for now, I was working on trying to generate a sort of heat map. Instead of only looking at how many days between transactions, I attempted to break down the transactions day of the week, day of the month, and distance from the end of the month. By breaking these values down I was able to generate a numeric index for each day. Eliminating those days with lower probability usually resulted in very accurate predictions. Unfortunately, it was around this time that I decided that if I were to continue I would need more statistical knowledge. I'm still waiting for that to magically be imparted to me, but I will make sure to write a follow-up if that ever occurs.

The most recent significant addition was the ability to establish a connection and download transactions using OFX. OFX stands for Open Financial Exchange and is a format used by financial institutions to authenticate and communicate account balances and transactions. Although I haven't been able to establish a connection to my primary bank, I have been able to set up a connection to my credit card company. The software now connects to my credit card company, gathers all of the transactions for the last 30 days, and checks to make sure they are accounted for in my database. If they can't be found, they are added. Every couple of hours the download will stretch further back in history just to make sure no transactions have been added with an earlier date. Usually, this happens in the case of refunds as far as I've been able to tell. It would be nice if my bank offered the same ability, but since I pay most expenses with my credit card it takes care of a large portion of data entry. 

Well, that's it. I know it's been a lot to discuss. This is definitely one of those cases of a pet project really turning into something useful. I have to point out that when I started this project none of the banks or credit card companies I knew about were offering anything like this. Over the years I have noted PNC offering a calendar view on their virtual wallet account and I believe some banks and credit card companies offer various graphs. Although it's nice to see that kind of thing being added I still prefer my calendar which can track multiple accounts and account types in one place. Mint, which was purchased by Intuit (the makers of TurboTax and other financial software), lets you connect all your accounts in one place but seems much more focused in an overview of your current standing as well as some analysis of your spending. It's a great tool, and I use Mint from time to time, but I also believe in using the right tool for the job. For me, my financial calendar is the perfect tool to verify the charges on my accounts, to see how things are looking at the moment, and to plan for future expenses. 


Categories: Programming  Finances  PHP