Keeping track of expenses and sticking to a budget can be a tedious ordeal. We usually collect receipts and sit down with hundreds before going through them. Maybe you’ve gotten tired of it and eventually quit keeping them. This isn’t a good practice but is normally what results from the physical hassle and confusion of keeping track of all those purchases.
But how do you keep track of these expenses without a physical copy of the receipts? This is extremely easy with Google Forms. You can make a form that outlines what you’d normally see in a receipt and record in your books. Include questions on the form like what the purchase was for and its price. Use this form from your smartphone right after you make a purchase. This will then send the form results to a spreadsheet you can review back at your computer at home.
In addition to the nice layout of transactions, you can set up the spreadsheet to display how much money you have remaining in your bank account (or in cash). Especially useful for students and the like, who are always worried about draining their budget through reckless spending before they know it.
In this article, we’ll first show you how to create a Google form to quickly submit expenses. We’ll then demonstrate how to calculate your available balance and then publish it in the form of a simple web page that can be easily accessed through any mobile phone’s browser. Finally, we’ll talk about publishing a chart of the available balance instead.
1. Create a Google Form
Open your Google Drive account and select Create > Form from the menu.
Enter any form title and select a theme before pressing OK.
We’re going to make this form simply with just a few questions. These can be customized however you see fit. We’ll use a Price question with a Text field as the type of question. This will let us enter any amount we want.
A price is useless without a category of what the purchase was for. Make a new field that’s a Multiple choice question that includes common categories like Gas, Bills, Groceries, etc.
Click View live form from the top menu to see the form as is.
Ours looks very simple but could even include images or other questions. Finally, copy the URL of the live form and send it your smartphone. Make sure you bookmark it there for quick and easy access when required.
Next to the View live form button is a responses option. Select this to be prompted for a new spreadsheet name. Enter a new name and press Create.
Use your new form to enter a sample price as a Gas expense.
We can now view and publish the results. Let’s see how to do it.
To view results, enter the form and press View responses to open the newly created spreadsheet.
The form submission from above is now categorized in the appropriate place.
Because only one category has been populated and we want them all to be present before moving forward, enter a zero amount (0) for each category like so. Make sure you do this from the live form page.
Off to the side of the page, after you’ve completed all the blank category inputs, enter the following info like in the screenshot below.
The Amount to begin with should have how much money you currently have available to spend. The second field for Total purchases will include a formula for calculating these transactions against our initial amount. The third and final field called Amount to spend will be how much money we have available after the submitted prices (deductions).
For the formula on calculating the total purchases, simply enter =SUM(cell:cell). Replace the words cell with the category you have for the Price. Ours is column B.
Now for the final cell on the Amount to spend, subtract the total purchased amount cell from the initial dollar amount.
The final result will always be an updated cell on how much money we have to spend. Take note of the cell. Ours is F5.
Go to File > Publish to the web.
Choose the correct sheet in the first section and then click Start publishing. Then select the same sheet in the bottom section and enter the cell that includes the Amount to spend value. Because ours was F5, we’ll enter that in the text field. This will ensure only that cell is published, which is all that’s required to view the available money to spend.
Save the URL at the bottom with a regular Copy command and send it to your phone via an email for bookmarking. This will make it very easy to view on the go.
The result is simply one cell like this, which is viewable in any browser:
Any change made from the form will make a subsequent change in the Google spreadsheet. This will then automatically publish to the web page, thus updating the page you saved on your phone. You can now make expense records and view your available cash at hand at any given moment. All you need to do is visit that page on your smartphone’s browser.
3. Publish a Chart of Expenses
Instead of viewing your available balance, you may also create a chart of the results to display a graph of where your money is going. For this example the category needs to be placed before the price column in the form. For example, move the category question above the price question to begin.
Now on the responses spreadsheet, go to Insert > Chart to and use the columns as the data ranges. Also, select Use row 1 as headers.
Now choose an appropriate chart like the Pie chart.
Update the chart details with the Update button and then on the chart to select Move to own sheet.
Return to File > Publish to the web. This time, choose the chart’s tab from the bottom portion so you can get a link to the chart in the form of a web page.
Copy the resulting link at the bottom and use this from any browser to see an updated category view of where your expenses are going.
You see it’s very easy to submit your transaction on the go to a nice spreadsheet all while not opening your Google account or even being near a computer. Not only this, you can get an updated balance of your account with ease. Just don’t share the document link with anyone. 😉