Back to Blog

How to Build a Spending Tracker Template in Google Sheets

Tracking expenses is the only way to make sure that your money does not go down the drain. The idea is pretty simple: you need to categorize expenses by category and analyze how much you spend on each.  Examples of categories include food, insurance, utilities, etc. Based on the analysis, you’ll be able to decrease expenses and increase savings. Does it sound enticing? Then check out the personal expenses tracker template, which is available for your use for free.  

Spending tracker template explained

Link to the spendings tracker

The tracker was designed in Google Sheets and consists of two sheets:

  • Expense Tracker – the sheet with the tracker itself: it allows you to filter out expenses by categories according to the selected period. This sheet also contains the breakdown of income and expenses by categories/months. 
  • Import your income/expenses – the sheet where you can manually or automatically import data about your revenues and expenses.

Expense Tracker

On the Expense Tracker sheet, you’ll see the grouped rows for income and expenses. Click on “+” to expand each of them. 

Here you need to specify your categories for income and expenses. If the default ones are okay, then skip this step.

The tracker looks as follows:

It lets you filter out expenses by a certain period. Click on B43 cell and choose the period from the drop-down list. The pie chart will update once you change the period to filter by. 

Import your income/expenses

The personal expense tracker template will work after you feed data into it. For this, you need to go to the Import your income/expenses sheet. It has 5 columns:

  • Date – specify the date of your income/expense. Click on the cell and select the date. 
  • Period – a technical field which extracts the name of the month from the data you specify in the Date column. 
  • Income – select the type of income from the drop-down list. (The list  consists of the income categories from the Expense Tracker sheet)
  • Expense – select the type of expense from the drop-down list. (The list  consists of the expense categories from the Expense Tracker sheet)
  • Amount – specify the amount of income/expense in the currency you need. 

You can input the expense/income data manually. It’s convenient for the expenses in cash. For non-cash spending and revenues, it’s better to import transactions from your bank. This can be done automatically with Coupler.io. The only prerequisite for this is checking the API available at your bank.

How to import bank transactions to Google Sheets automatically

Coupler.io is a Google Sheets add-on to automatically import data from third-party data sources, such as Pipedrive, Xero, and many more. Install Coupler.io from the G Suite Marketplace.

JSON Client importer

An importer is an integration that connects Google Sheets to a specific app or data source. The JSON Client importer is an omni-purpose tool to integrate with JSON APIs. For example, we used it to schedule messages to Slack, export data from Shopify, and connect to other apps.
Go to the Add-ons tab of your spreadsheet, select Coupler.io => Open dashboard. Click on the +Add Importer button and choose JSON Client.

You’ll need to take four steps to set up the integration:

  • Fill out the Title field: Name the integration.
  • Set up your data source: Fill out the fields in the Source section based on the API documentation of your bank.
  • Set up your data destination: Name the sheet, which will be receiving data.
  • Configure importer’s settings: Set up the Automatic data refresh on a schedule, as well as other additional parameters. 

Check out Knowledge Base for details.

As a case study, check out the parameters I used to set up the integration with Monobank to export bank transactions to spreadsheets:

Field in JSON Client importerParameter
Titlemonobank transactions
JSON URLhttps://api.monobank.ua/personal/statement/8RcI3ExQnq7AdtezWsTHyg/1596240000/1597708800
HTTP MethodGET
HTTP HeadersX-token:u7fsEluZkFE-cjIBLRgIiDUKwMK27tmBl35r11GH_kTg
Sheet namemonobank transactions
Automatic data refresh frequencydaily

Once the parameters are set up, click Save & Run to make the initial data import. After that, Coupler.io will refresh the data automatically every day.

Then, you need to query some of the imported data to the Import your income/expenses sheet. In our case, these are three columns: time, description, and amount

We applied the following QUERY formula in the E2 cell to reference the amount and description columns:

=query('monobank transactions'!C2:E24,"select E, C")

Learn more about the power of the QUERY Function Google Sheets

The values in the time column are in the unix epoch time format (in seconds). To convert them into real data, apply the following formula based on the ARRAYFORMULA + DATE functions in the A2 cell:

=arrayformula(
   if(
      len('monobank transactions'!B2:B)=0,,
      ('monobank transactions'!B2:B/86400 + date(1970,1,1))
   )
)
  • 86400 – the number of seconds in a day (24 hours x 60 minutes x 60 seconds)
  • date(1970,1,1) – the date value for the Unix Epoch, January 1, 1970 (25569)

The only thing left is to allocate categories (Income or Expense) to the imported data. In our case, we must do this manually. Once you map the expenses/income data, it will appear in the Expense Tracker sheet.

This is possible due to the following formula that links cells between sheets:

=iferror(
   sum(
      filter(
         'Import your income/expenses'!$E$2:$E,
         'Import your income/expenses'!B2:B = {cell with the name of the month},
         'Import your income/expenses'!C2:C = {cell with the type of income/expense}
      )
   )
)

Now, let’s get back to the tracker.

Monitor your spending with the personal expense tracker

Go to the tracker and select the period to track. For example, we selected August, and all the imported expenses in August have been returned:

The pie chart will update accordingly. The tracker has two fundamental elements:

  • Period selector with the drop-down list. To make this, you need to apply data validation (list from a range).
  • Filter formula which extracts the data based on the criteria specified in the period selector. Here is how it looks:
=filter(A19:N39,A1:N1=B43)

Read our tutorial to learn more about the FILTER Function in Google Sheets

Note: the pie chart works with absolute values only. In our case, the imported bank transactions were both negative (expenses) and positive (income). After the mapping, it’s recommended to use the ABS function to get absolute values for minus numbers (or ARRAYFORMULA + ABS for an entire column).

To track, or not to track, that is the question

Frankly speaking, I was not used to tracking my spending in detail. As many of us do, I relied on my memory. My expenses monitoring resembled “$200 for sneakers are too much..maybe in a couple (not Coupler:) of months.

Now that my spending data is structured, I know exactly what kind of expenses I can afford and how this can affect my cash balance. I hope that my experience and this expense tracker template will help you as well. Good luck with your data!

Back to Blog

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Access your data
in a simple format for free!

Start Free