Friday, November 8, 2019

This Fully Automated Expense Spreadsheet Is Surprisingly Simple To Be Made

11/08/2019 12:54:00 PM

This is the ultimate expense tracking spreadsheet that I have built over the year based on Google Sheets. I call it the ultimate spreadsheet because it can automatically perform a lot of things. With this latest version, it is so easy to use that I actually enjoy entering the expense records (the real version I use I just copy entries from my credit card’s web report) and immediately knowing summarised reports. This is what the spreadsheet can do:
One simple range to enter detailed record of expenses. No need to change formula in other sheets to see the result once setup.

  1. Automatically extract all Unique Categories from detailed records
  2. Automatically extract all Unique Payment Accounts from detailed records.
  3. Automatically extract all Unique Vendors from detailed records.
  4. Automatically summarise amount by Categories and by Month
  5. Automatically summarise amount by Payment Account and by Month
  6. Automatically summarise amount by Vendor and by Month

If you have a bit of a hard time imagining what I am taking about, try to look at the following pictures.

This is all that you need to enter on a day to day basis:



Then you will automatically get this summary by category without doing anything:



You also get this summary by payment account:




And for the Vendors of course:



It’s more simple than you think

The interesting thing about this spreadsheet is that it is that the formulas used to automatically extract and summarise the categories and etc are so simple. I have not used any macro or script programming and no third party plug-in. Only plain simple built-in formulas in Google Sheets. Basically I used two functions to build my sheets:
=UNIQUE()
and
=SUMIF()
UNIQUE() is only available in Google Sheets. I don’t think Excel has a similar one but I wouldn’t be surprise that it will have a similar one in future or via 3rd party plug in. What this function does is it automatically extract a unique list of items out of a list of items with duplicated values such as the category or payment account column in entry records sheet. You can look up detail description of UNIQUE() here.

SUMIF() is available in Excel for decades. It is also available in Google Sheets as well. As the name implies, it summarises a range of number base on certain IF condition. You can look up detail description of SUMIF() is here.

Both functions are rather simple and easy to understand. To make the spreadsheet automatically summarising data requires us to create ONE formula that create a matching conditions for the SUMIF function to work. I name it as “SUM-CODE” meaning that it is THE CODE that allows summarisation of everything. Anyway, it’s just a notation for the sake of easy referencing.

BUILDING THE EXPENSE ENTRY SHEET

First let’s create a (or use the first sheet) for all the records entry. I call it “Entry Records”. You may prefer just “Records” or “Expenses” for simpler reference.




Here come the SUM-CODE formula:



Row 3 is your first row of expense record. In Cell H3 enter the following formula:

=if(A3=””,”-”,text(A3,”yyyy-mm-dd”)&”|”&B3&”|”&C3&”|”&D3)



Basically the formula concatenates the date(col A), vendor (col B), category (col C) and Payment Account (col D) together but separate them with a vertical bar character “|”. The result of this formula creates a text string in specific format so that it can be used to be in the “SUMIF” in other sheets. In the above figure, the formula in cell H3 format the date cell A3 (same row as in H3) into “YYYY-MM-DD” text and concatenate “&” it with the other columns as is.

Just make sure the SUM-CODE formula is copied to every new expense entry. That’s it. Nothing fancy for the data entry sheet.

THE SUMMARY SHEET

First create another sheet can name it “Category”



When we finished building this category sheet, all we need to do is to duplicate it to create the Payment Account and Vendor Summary sheets. So first let’s focus on getting the Category Summary report done!

Date Range Row


First let’s create a header row that controls the time period to be summarised. In cell B2 enter a starting month by typing “Oct 2019”. Google Sheet will automatically recognise this as 1-Oct-2019 but hide the date and show it as “Oct 2019” as you can see in the figure below:



Then on cell C2, which is beside the first month, we enter the following formula that calculates the following month in cell B2.



The formula =date(year(B2),month(B2)+1,1) extract the year and the month from cell B2 and “Add 1 month” to form the next month. Please note that we always keep the 1st day for the month in the formula. These 3 parameters of Year, Month+1 and day 1 are used in the “DATE()” formula to create a real Google Sheet valid date data. Again while the resulting date is “2019–11–1”, the presentation is reformat as “Nov 2019” with the numeric formatting code “MMM YYYY”.

This date formula can be copied to however number of columns you need to create the range as in the following example. You just need to type in the starting month and the rest of the columns will automatically adjust month by month.



The real spreadsheet that I use start with Jan 2019 instead of Oct 2019 and extend the date range for 12 months for obvious reasons.

This monthly headers will be used for summarising so it’s important.

Grand total row

I have a practice of putting the grand total on top of the table because I like to see the big picture first before I drill into details. In this spreadsheet design, it is however necessary that the grand total is put on top of the rest of the details categories because the number of categories can be dynamically expanding and contrasting as we add new entry records. So I just want to put the grand total on top of the details table so that I can give as many rows downward as possible. In cell B3, which is just below the date header, put in the formula “=SUM(B$4:B)” basically it tells google to sum everything in column B starting row 4 all the way to the end. This way the range to be summarised does not have to be pre-defined.



Extracting Categories Automatically

To automatically extract a list of unique categories from the entry sheets is easy. All we need to use is one simple function “UNIQUE()”. Since I have all the categories in Column C in “Entry Records” sheet, all I need to do is to enter the entire column C in Entry Records sheet starting in row 3.

Just enter “=Unique(‘Entry Records’!C3:C)” in cell A4. It will automatically create a list with all the unique category as follows:



Next we enter the SUMIF formula in Cell B4 to summary expenses for category in A4 and for the month in B2.





The formula is here:

=if($A4=””,0,sumif(‘Entry Records’!$H$3:$H,text(B$2,”YYYY-MM-”)&”*|”&$A4&”*”,’Entry Records’!$E$3:$E))

The formula first checks if there is nothing in the cell A4 to shows a number zero. This is because I want to copy this formula to as many row and columns as I want to cater for the dynamic increase in categories or vendors. So I have to cater for the condition of an empty row (without any category).

If there is a category in cell A4, it will then use the SUMIF() formula to extract the numbers. The SUMIF formula first take the “SUM-CODE” column ‘Entry Records’!$H$3:$H as the matching range. Then it uses the month in B2 and category in A4 to form a conditions to sum only the rows that match the year month (YYYY-MM) and the Category. It’s important to note that there is a wildcard character “*” after the “YYYY-MM-*” to match any date in the month and after category such as “Food*” to another text following the category.

The SUMIF formula can be copied to any cells to the left and below because it fixes input from column A for category and row 2 for year-month. It can be copied because we put in the absolute reference sign $ before Column A and Row 2.

That’s it. That’s all you need to create an automatic summary sheet for categories.

Just Duplicate To Create Payment Account and Vendor Sheet

Once you got the Category summary sheet done, all you need to do is to duplicate the sheet for Payment Account and Vendor.



I will not hand hold you to create the payment account and vendor sheets. I think it’s easy enough that you can easily figure out which formula to change. Let’s use those as your practice exercise to sink in what I have presented today.

Much More than expense tracking

In real life, I use the same techniques to track very complex budget that spent across multiple years and months. The businesses I managed were “advance” and “complex” that demands creative ways to formulate KPI. I have to create new business models from time to time. Traditional software just does not cut it for me. Having a dedicated accountant to help me was too slow to test out formula. So I have to do it myself. Anyway, over the year I was surprised that how useful the “SUMIF” function is when applied creatively. The Unique is a nice touch from Google for further productivity gain. However, I have been using Excel with Sumif for years without much trouble.

Just learn to master how to use SUMIF creatively. It’s really the most important formula I have come across in my career as P/L owner of my business. There are other formula such as Vlookup but SUMIF is seriously powerful and under-rated by most spreadsheet users.

If you don’t like to use Google and therefore will not have access to “Unique()” function, all you need to do is to pre-define a list of category and payment account instead of using Unique() to generate the list for you. There are benefits in having a fixed list:


  • order can be defined as you like
  • you don’t get to freely type any category on the fly which helps controls explosion of unnecessary categories due to the freedom

Anyway, thanks for reading this article. Hope it’s helpful.