Expense Tracker - MVP - Google Sheet Expense Tracking System

Blast from the past! Let’s go back to the topic, based on which this whole site was initially created – Get Noticed Contest, and a side project called Expense Tracker.

Post will not cover why tracking expenses is useful, I’m leaving that to other experts (if you know polish you can for instance check out the blog – jakoszczedzacpieniadze.pl created by Michał Szafrański and also his phenomenal book “Finansowy Ninja”) and to your own thoughts.

In this article however I will show you how I track expenses right now, when full blown version of the product is not ready (still work in progress), you will need only Google account (to use Google Sheets), and a bit of work each month to keep it going (if you will follow my setup).

Solution will be working on mobile devices, can be shared with many people, and you can configure it to your needs. Enjoy!

[heading align=”left”]

Tracking Expenses in Google Sheets

[/heading]

My setup is based on monthly pages (sheets) but there are other possibilities – quarterly, yearly or even all in on pages with all the expenses. Other setups will require slight configurations compared to what I’m presenting here (will not cover that here, let me know in case of any questions).

First login to Google Sheets and create new document – Expenses for instance.
In this new document create 2 sheets – current month ex. “August 2017” and “Dictionary”

In “Dictionary” we are going to create (in separate columns) dictionary of accounts, transaction type, categories and subcategories

Now let’s go back to “August 2017” sheet, in first 3 columns create a summary of what we have on each account and what we had at the last day of previous month.

Creating Drop Down list for Bank Account

Right click on cell A2 > Data validation… and make configuration like on the screen below
Then copy and paste the cell to all the cells below (number should be equal to number of your accounts)
Criteria is set to A2:A10 in Dictionary in case you would add more bank accounts

Column C – Previous Month column, this column would need to maintained manually monthly – monthly based setup (it is value on your accounts at the last day of month)
Column B – Current – will have a function that for specific bank account + sum from previous month + expenses / incomes of this month will calculate the total

=$C$2+SUMIF($E$2:$E$216,Dictionary!A2, $I$2:$I$216)

$C$2 – for each of the bank accounts, hardcoded not changing previous month total

$E$2:$E$216 – column E will store information about expenses / incomes for each account – bank account name (216 – can be changed to how many values you will have in your sheet)

Dictionary!A2 – pointer to “Dictionary” sheet to specific bank account

$I$2:$I$216 – column E will store values of expenses / incomes for each account

Column E – Account
- Create Drop Down list (described in 4.1 part of copy cell A2 J) and populate it down to as many rows as you want / will use
Column F – Transaction Type
- Create Drop Down list (described in 4.1) instead of using account column from Dictionary use TRANSACTION TYPE column
- In order to have at a glance information if something is an Expense or an Income let’s add some colours using Conditional Formatting
  1. Right click of F2 Cell and go to Conditional Formatting…
  2. In apply range set F2
  3. Then in Format cells if… set “Text is exactly” – Income
  4. Formatting style (green “Default”)
  5. Click on Add another rule and repeat operation but instead of Expense set “Text is exactly” Expense and Formatting style (red “Default”)

Column G – Transaction Date
1. Create Date Picker
2. Right click on G2 cell and go to Data Validation
3. Choose Date is valid date

This setup after saving, when cell will be double clicked will show a date picker

Column H – Amount: transaction absolute value (not -10 $ etc.) you can format is a currency
Column I – Real Amount: function that will calculate based on what was chosen In Transaction Type field real amount of transaction. If Expense than value will negative, if income positive.

=IF($F3="Expense", $H3*-1, $H3)

Column can be hidden.

Column J – Category

In order to create multiple drop down options based on category list from our Dictionary sheet we need to use “Define named range…”

Right click on J2 column > Define named range
Add a range
Select Categories headers in Dictionary sheet

Then based on that let’s created Data Validation rule to show items based on List from a range where, the range would be a define named range:

Copy the cell to all the cells that will be used below.

Column K – Sub-Category

For showing specific sub categories for selected categories we would need several things, this is the most tricky part of tutorial.

Create list of Define named ranges for all of the sub categories (based on description in point 10)IMPORTANT: Defined named ranges should be named exactly like Categories headers inside of Dictionary sheet.
Move to Dictionary sheet (or you can create separate called – Config if you want) in J column create a function that will get last chosen Category from “August 2017” sheet
```
=IFERROR(OFFSET('August 2017'!$J2:$J979,MATCH(MAX('August 2017'!$L2:$L979),'August 2017'!$L2:$L979)-1,),)
```
IFERROR – returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent.
OFFSET – Returns a range reference shifted a specified number of rows and columns from a starting cell reference.
’August 2017′!$J2:$J979 – Category column in our “August 2017” sheet
MATCH(MAX(’August 2017′!$L2:$L979),’August 2017′!$L2:$L979)-1,)
MATCH – Returns the relative position of an item in a range that matches a specified value.
MAX(’August 2017′!$L2:$L979) – returns the maximum value in a numeric dataset.
’August 2017′!$L2:$L979 – L column will contain time stamp from script – check point 12
This would need to be changed on monthly basis (if you are following my pattern).
In column K – Dictionary sheet – create a function that based on results in column J
```
=ARRAYFORMULA(IFERROR(INDIRECT($J1)))
```
ARRAYFORMULA – enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.
IFERROR – returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent.
INDIRECT – returns a cell reference specified by a string.
$J1 – column that will store information about last selected category in specific sheet
In column K – “August 2017” sheet create Data Validation rule based on K column from Dictionary sheet

Column L (Hidden date column for script calculation)

Creating script to automatically add current time stamp in L column when we are changing / selecting category. It will be crucial when we would like to correct some previous transaction (classify them under different categories) without that function all rules done in point 11 would fail on calculating maximum field value

Go to Tools > Script Editor
Name the project (script) – CategoryPicker
User following Code inside of Code pane:

function onEdit(event) {
  var categoryColumn = 10;
  var subCatColumn = 11;
  var timestampColumn = 12;
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "August 2017" )
    var r = event.source.getActiveRange();
    var searchColumn = r.getColumn();
    if (searchColumn >= categoryColumn && searchColumn <= subCatColumn) {
      s.getRange(r.getRow(),timestampColumn).setValue(new Date()); 
    }
}

categoryColumn – number (of order) of Category Column

subCatColumn – number (of order) of Sub-category Column

timestampColumn – number of Time stamp Column

Save the script. When you will try to run it will fail – don’t worry about that it will work in “August 2017” sheet”.

You may be asked by Google Sheets that script is not verified, proceed with the steps that are being shown to verify the script.

Before using function:

After using function:

Column M – Transaction Details: manually added transaction details.

[heading align=”left”] Finished Example [/heading]

Link to shared document that was created in this tutorial: https://docs.google.com/spreadsheets/d/1174QygiVR0B6ZNU3RmBxAYbsys4NZguIM6i88JetO3U/edit?usp=sharing

In case of any questions or concerns, let me know.

Thanks,

Krzysiek

Powiązane wpisy:

Leave a Comment Cancel Reply