Manage Your Personal Budget With Google Sheets

The beginning of the year is a common time to make promises to yourself, and hopefully, this can help you keep those promises related to personal finance and budgeting.

This document started as a way for my wife and I to manage our budget while focusing resources on getting our student loans paid off. Trust me, I’m aware there are a number of (free-ish) tools out there, so if you already have a system that works, great. If not, or if you’re looking for a free alternative to the budgeting tool you’re using, then this post is for you.

The Personal Budget template described can be found here:

https://docs.google.com/spreadsheets/d/1fUjNr-n5u-GLzvmWLJk2_m9CYOIHSpmS-A8m3Jjz1-c/edit?usp=sharing

The following steps will help you through the process, to get your budget set up in around 20 minutes.

Create a Copy

Upon entry to this URL, you’ll quickly see you won’t be able to edit the current version. You’ll need  to sign in to your Google Account (create an account if you don’t have one already) and create a copy for your personal use.

personal budget template

Choose Spending Categories

Click on the “Spending Categories” tab, and review the pre-populated list of expense and income categories. Add, edit, and/or delete categories to align with your own spending patterns and budget goals. If you haven’t done a budget before, start with generalized categories, and revisit once you have a better feel for your spending patterns.

Export Bank Transactions

From your bank, export transactions to CSV from the beginning of the month you plan to start your budget.

bank transaction export

Click on the “Bank Data” tab, and clear the placeholder data. Your bank may differ in the ordering of the columns, so you’ll need to paste your transaction data into the “Bank Data” tab to correspond with the Date, Description, and Debit, and Credit columns.  Some banks separate the Debit and Credit values into two columns and some list all values in the same column.  If the values are combined in the same column, just paste into column D, under the “Debit” heading, and the formulas will take care of the rest.

Categorize Bank Transactions

By clicking the right corner of the cells in Column “A” on the “Bank Data” tab, you’ll see the populated list, based on the categories specified in the “Spending Categories” tab. Go through each row to categorize each transaction. This is important, since the Monthly Budget tab uses these labels to determine how much is spent in each category.

categorize transactions

Monthly Budget

Each month is broken out into two sections, Expenses and Income.

Carryover – For the income, the first tab you’ll notice the first tab is labeled “Carryover.” In this case, you will paste in the dollar value in your checking account at the beginning of the month. This will help you monitor your current balance as transactions are added, and to ensure it lines up with your account balance reported straight from your bank.

Next, if you changed the categories “Income” or the “Gift/Award” categories, you will need to use the arrow drop down to select the income categories desire. Next, use column “B” to add in the estimated income based on the different category groups. In this example, the budget shows a monthly income of $4,200, resulting from two $2,000 payments, and two $100 payments. Disregard the Cash flow for now, we’ll cover that shortly.

planned income

 

Moving onto the expenses. Here, you’ll assign a monthly value to each expense category. Recurring costs are simple to budget, as you can look to the previous month’s bill for the exact figure, where groceries and dinner/out budget will take additional thought to come to a figure that is accurate. As the estimated expenses are populated, the running balance will adjust to account for the total planned income, and the total expense for the month. It all starts will the plan. This is your chance to assign money to the categories that are important to your life, and not the expenses that are short lived and spontaneous.

Be honest with yourself, and accept the spending and cuts you plan to make.

monthly expenses

 

Deleting Duplicates

After you return from the initial budget setup, you’ll need to frequently (daily/weekly/bi-weekly) paste in the latest transaction data to keep your budget current. Use the same process as before, but paste the data below the last transaction, rather than overriding previous transactions. Sometimes transactions take multiple days to clear, so you might have missed transactions that didn’t process a day or two before you’re last import. To solve this issue, I recommend pasting in data overlapping 7 days from the previous import date. Using this method, you’ll have duplicates that need to be removed (luckily I built a script for that).

On the far right of the menu, click on “Duplicates” to remove duplicates from the Bank Data tab. The first time you run the script, Google will ask you for Authorization for the script to run, and for the ability to connect to the Google sheet.

google script authorization

Once it completes, any duplicates will be removed. If you are activating for the first time, you’ll need to re-run the Remove Duplicates script for it to fully execute.

The rest is on you to manage your budget… It takes time, but it’s worth it.