Sunday , October 2 2022
google sheets expense tracker template

Google Sheets Expense Tracker Template + Tutorial

Google sheets expense tracker template. An expense tracker is a software program that allows you to keep track of your spending. A good expense tracker keeps track of not just what you spend but also what you earn over the course of the month or year. It allows you to see your expenses by category, allowing you to determine which expenses are needed and which are not.

Why Keep Track of Your Expenses?

It’s critical to determine where you want your money to go if you want to have true financial control. However, simply planning is insufficient. You should also hold yourself accountable and attempt to stick to the budget you set for yourself.

A simple application or even a spreadsheet that categorizes how much money comes in and where it goes can help you achieve your financial objectives faster. Keeping track of your income and expenses over a period of months might actually assist you in developing more realistic budgets and goals.

The best way to ensure that your money does not go to waste is to keep track of your costs. The concept is straightforward: you must categorize your spending by category and calculate how much you spend on each. Food, insurance, and utilities are examples of categories. You will be able to reduce expenses and improve savings as a result of the analysis. Does it appeal to you? Then, look at the google sheets expense tracker template, which is completely free to use.

The tracker is made up of two sheets that were built in Google Sheets:

Expense Tracker – the sheet that contains the expense tracker: allows you to filter expenses by category and period. This chart also shows the income and cost split by categories and months.
Import your income/expenses – this is the sheet where you can manually or automatically import revenue and expense data.

Google sheets expense tracker template

Download Link: google sheets expense tracker template

Google Sheets Expense Tracker Template 2022/Financial tracker google sheets

The grouped rows for revenue and expenses can be seen on the Expense Tracker sheet. To enlarge any of them, click “+.”

Click + to expand grouped rows

You must define your income and expense categories here. If the defaults are fine, you can skip this step.

The tracker looks like this:

Select a period to track spending

It allows you to filter expenses by time period. Select the era from the drop-down menu in the B43 cell. When you modify the period to filter by, the pie chart will update.

Import your earnings and expenses

After you enter data into the personal spending tracker template, it will work. To do so, navigate to the Import your income/expenses sheet tab. There are five columns in total:

  • Enter the date of your income or spending. Select the date by clicking on the cell.
  • A period is a technical field that retrieves the month name from the data entered in the Date column.
  • Select your source of income from the drop-down menu. (The income categories from the Expense Tracker sheet are included in the list.)
  • Expense – choose an expense type from the drop-down menu. (The spending categories from the Expense Tracker sheet are included in the list.)
  • Amount – enter the amount of income or spending in the required currency.

The Import your income/expenses sheet

You can manually enter the spending and income information. It is convenient for cash expenses. It’s better to import transactions from your bank for non-cash expenditure and revenue. Coupler.io can do this for you automatically. Checking the API availability at your bank is the sole requirement.

How to automatically import bank transactions into Google Sheets

Using Coupler.io, we’ll get data from a banking JSON API. It’s a service that lets users automatically import data into Google Sheets, Excel, and BigQuery from third-party data sources like Pipedrive, Xero, and others. On Coupler.io, you may sign up for a free account or get the Google Sheets add-on from the Google Workspace Marketplace.

coupler.io-google-workspace-marketplace

Importer for JSON Clients

The JSON Client importer is a multi-purpose JSON API integration. We utilized it to send messages to Slack, export Salesforce data, and link to other apps, for example. Here’s how it works:

Sign up for Coupler.io, then click “Add new importer,” give your importer a title, and complete the three steps: source, destination, and schedule.

5 JSON source destination schedule

  1. Set up your data source by filling out the sections in the Source section according to your bank’s API specifications.
  2. Set up your data destination: Select the file to which you want to transfer data and adjust the data import mode and range.
  3. Set up the Automatic data refresh on a schedule in the importer’s scheduling settings.

Check out the parameters I used to set up the Monobank integration to export bank transactions to spreadsheets as an example:

6 monobank transactions

After you’ve set up the parameters, click Save & Run to start the data import. Coupler.io will then automatically refresh the data every day.

Then, on the Import your income/expenses sheet, you must query some of the imported data. These are three columns in our case: time, description, and amount.

To refer to the amount and description columns, we used the QUERY formula in the E2 cell.

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

query monobank transactions

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

=arrayformula(
   if(
      len('monobank transactions'!B2:B)=0,,
      ('monobank transactions'!B2:B/86400 + date(1970,1,1))
   )
)

The amount of seconds in a day is 86400. (24 hours x 60 minutes x 60 seconds)
date(1970,1,1) — the Unix Epoch’s date value, January 1, 1970 (25569)

ARRAYFORMULA DATE functions

The only thing remaining is to assign categories to the imported data (Income or Expense). We have to do it manually in our instance. The expenses/income data will appear in the Expense Tracker sheet once you’ve mapped it.

Map imported income/expenses

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

Select the period to track in the tracker. For example, we chose August, and all of August’s imported charges were returned:

How to use the personal expenses tracker

The pie chart will reflect this change. The tracker is made up of two main components:

The period selector has a drop-down menu. You’ll need to use data validation to accomplish this (list from a range).
Filter formula that retrieves data from the period selection based on the conditions supplied. This is how it appears:

=filter(A19:N39,A1:N1=B43)

The pie chart only works with absolute values. The imported bank transactions in our situation were both negative (expenses) and positive (revenue) (income). After mapping, the ABS function (or ARRAYFORMULA + ABS for an entire column) should be used to retrieve absolute values for minus numbers.

Note: With a few tweaks, this tool can be a “business expense tracker google sheets.”

Credit to Source: https://blog.coupler.io/personal-expenses-tracker-google-sheets/

About Info Wealth Geeks

“Persistence gives confidence and continued right mental attitude followed by consistent action will bring success. When you have that knowing inside of you, fear has vanished and the obstruction to a life of all good removed.”

Check Also

13th Month's Pay

How to Spend Your 13th Month’s Pay Wisely

13th Month’s Pay. We have a lot to be happy about as December approaches. We …

Leave a Reply

Your email address will not be published.