Hello r/personalfinance !
Around this time last year, a user posted a link here to a spreadsheet that they used for keeping track of their finances, so that other people could use it too. I downloaded it, and since then have made many, many changes. I’d like to share my updated version that I've been working on for the last year, and hopefully some of you can take benefit of all the improvements I’ve made. There’s three main sheets; Main, Overview and Regular Expenses
- Main is where all the transactions are stored. Everything from buying pizza to being paid. These transactions are separated into three categories; Cash (physical money), Bank (the main place to keep money) and Paypal (internet money). Each day has lots of space for entering each transaction individually, so you don't have to calculate together anything. You also have a cell to describe what the transaction was. At the end of each month, each day is computed together to show you some extra information (how much cash you spent, how much money exited your bank account, what the overall in/out flow is for that month, etc)
- Overview gives you a viewpoint over all the movement of money in your accounts. Here, those purple sections from Main are collected together and further processed to show you information on what the flow is like per day or weekday. The real feature here is a collection of graphs to the right, which show you the ‘flow’ and ‘total’ values for each of the three accounts, plus the total. ‘Flow’ shows you your ‘wealth velocity’ (rule of acquisition 54); the speed at which money is entering and leaving your account (in units per month). ‘Total’ is the amassed wealth you have overall, and how its changed over time.
- Regular Expenses is a somewhat more experimental sheet. Using some custom functions I’ve written (available in Tools > Script Editor) the sheet attempts to figure out what your most common transactions are, and graph them over time. It uses a keyword search of the Main sheet (with those description boxes) to find the most common words, then brings together all the transactions associated with those words. It’s a little hard to explain (or perhaps I’m just not very good at it) and hopefully my example sheet makes it clear. The custom scripting is a little heavy though, and complicated, so you’ll have to forgive some processing time and instructions for use.
Let me know if I need to explain anything else about it. Hopefully my example sheet should make things obvious on how to use it. And let me know what you think overall, or if you have any suggestions/code for it.
Links
Clean
https://docs.google.com/spreadsheets/d/1K2Goo3hibQdt_bbhN5KSzWGhjlZQDHs9mgW5IIjRMjM
Example
https://docs.google.com/spreadsheets/d/1Nuu6B5PfaPqYvkjuGuAdw2HbIKF2ZxNJ-PUjSycI4cI/
Additional Instructions:
These sheets are not set up for leap years.
- Main. You need to add in your starting numbers at the very top in the blue section. The functions should handle everything after that, but be careful about dragging cells around as you could inadvertently change the values in the orange column functions. (I’m aware that ‘indirect’ is a thing, I just haven’t gotten round to rewriting this sheet with it yet)
- Overview. To change the year, simply retype the number in the top left orange cell.
- Regular Expenses. Google sheets won’t recalculate functions whose inputs don’t change. So, to get the custom script to re-run, click on cell H3 (=commonWordFinder ...) and change the last number in its input to whatever you like. It's a useless argument within the script, so changing it just makes Google Sheets think the input has changed, and thus will recalculate it.Within the Script Editor, in the function commonWordFinder, you can find an array called “wordsToIgnore” and a mapping called “wordsToBundle”. As the name suggests, “wordsToIgnore” is a list of words that the function won’t bother doing calculations for. “wordsToBundle” is used, so that the script will know that words like “drinks” can be considered the same as words like “drink”. In this way, you can make a transaction description of “Drinks with Friends” and have “friends” ignored and “drinks” included with calculations for “drink with james”, and represented under “drink”. These are sortof edge cases though, and you’ll only need to edit things here when it becomes a problem for you.
Submitted December 23, 2018 at 08:31AM by metasophiea http://bit.ly/2V4LIQu
