Implementing family bookkeeping with Google Sheets, chewing gum and scotch

Soon the new year, and, like last year, one of the most pressing issues for any family is the question “where does the money go?”. We all periodically ask ourselves this question, and it is obvious that to solve it, we need intrafamily financial reporting (as for any organization). I tried to use desktop accounting software as well as mobile applications, but I still could not find a suitable solution. There are good free programs, but they, as a rule, do not have a sharing function, and paid ones require a license purchase for each user. The full set of criteria that has developed for me, and which I would like to see in the program for managing family bookkeeping:

• should allow the entry of expenditure data to multiple users
• should have a flexible set of categories of expenses (and, in general, be flexible)
• should allow you to keep track of the total costs by period and category
• should be free
• should have a simple interface

To form criteria, I chose expenses, because, as a rule, family income sources are fixed and known in advance (mainly salary), so it makes no sense to follow this category of budget, but if you wish, all that is true for expenses applies to income (if there is a desire consider card bonuses, discount programs, cashback, etc.).

Not finding a solution that satisfies the listed criteria among existing applications, I began to think about my own, but thinking about this topic, I realized that there is no point in another accounting application, when all that is needed to solve a problem is a fiduciary table. Run through the Google Sheets API, found the SUMIFS function, which completely satisfied my accounting requests. But first things first.

Step 1 Preparatory

1. Create a new Google Sheets and set up for it the necessary access rights for all participants.
')
2. We make markup for maintaining items of expenditure (income). It should get something like this:



Alternatively, you can add a Person column to indicate who made the entry.

Step 2 Creative

After the first step, we have in our hands a general table where we can enter all the necessary information. Of course, there is a nuance that, in theory, the names of expenditure categories should be coordinated between all participants, so that the reporting turned out to be more stringent, but as practice shows, in case of inaccuracies, everything is quickly solved by the Find & Replace function (an additional bonus was that if you can’t think adequate title for the category of expenses, then most likely this is something that you do not need). Now you can use the SUM and SUMIFS functions to create “watchers” for the desired expense categories. I stopped at the total amount, the amount for the period, the amount for the month and the same amount for the “jambs” category (expenses caused by forgetfulness, loss of documents and other things of the same kind). If desired, you can add tracking for any category, the calculation of interest relative to the total amount and other functions. Despite the fact that Google Sheets is a relatively simple spreadsheet editor, it has a powerful set of tools (at least for most common tasks), which you can read about here .

For the presented markup, I got the following set of functions

#
=SUM($E$2:$E$100000)

# , J2, K2
=SUMIFS($E$2:$E$100000,$A$2:$A$100000,">="&J2,$A$2:$A$100000,"<="&K2)

# 30
=SUMIFS($E$2:$E$100000,$A$2:$A$100000,">="&(today()-30))

# J5
=SUMIFS($E$2:$E$100000,$B$2:$B$100000,"="&J5)

# J6, K6 L6
=SUMIFS($E$2:$E$100000,$B$2:$B$100000,"="&J6,$A$2:$A$100000,">="&K6,$A$2:$A$100000,"<="&L6)

# J7, 30
=SUMIFS($E$2:$E$100000,$B$2:$B$100000,"="&J7,$A$2:$A$100000,">="&(today()-30))


The summation is carried out on the first 100,000 rows of the table (which should be enough for at least a year).

So the result is in the final table:



A nice feature was that under the conditions of the SUMIFS function, you can specify not only cells, but also expressions like "> =" & (today () - 30) (I just wanted to focus on this).

Step 3 and the hardest. Advertising

Now that everything is ready, it is necessary to convince relatives of all the advantages and possible benefits of doing home bookkeeping and strict financial reporting. Here you may need tape, for the wall newspaper, describing all the benefits of such a decision, chewing gum as a first reward and other legal means that you can afford.

Conclusion

Google Sheets is a convenient platform for family bookkeeping (and other similar tasks), the main advantages of which are accessibility, ease of use, sharing, free use, as well as incredible flexibility and extensibility.

PS

Just want to say that by itself, the overall financial statements discipline and lead to lower costs (up to 10% or more) due to lower spending on “poorly named” categories, “shoals” and other entropy manifestations in our life and character. An additional bonus can be called the fact that seeing as a tracker the amount of monthly expenses approaching the mark X, you can go back and figure out the situation. And finally, there is always an answer to the question “where is Zin's money?”, Which is good news, especially in our difficult financial time.

→ Link to table template