Google Docs To Create A Mobile Form That Tracks Expenses

I am very nerdy when it comes to tracking personal and business expenses. The problem is that I am usually away from my computer when spending money. I do have an IPhone, but it’s a pain in the rear to enter transactions into my Google Apps spread sheet via the mobile interface. There are apps that can be used for this purpose, but I have found nothing that works with multiple users (eg. spouse or co worker).

After a little tinkering, I discovered a quick and easy way to track expenses from my IPhone. Create a form that connects to a spreadsheet in your Google Apps account. You can then create a shortcut that links to the URL of the form. Here is how you do it…

If you don’t already have one, create a Gmail or Google Apps account.

Click the Google Docs link in the upper left corner of your Google account and create a new form.

The idea behind this concept is to keep things simple, so we are only going to create two fields.

1. Description – the description of the transaction
2. Debit(-) – the amount you spent

We will make the Debit (-) field required then click save at the top of the page.

Go back to your Docs home page. You will see a new Spread Sheet. Open it.

You now have a basic spreadsheet that will record each form entry. There is a column for each form field as well as a column for date and time.

This is a good start, but we need to add a little more functionality as well as make it a little prettier.

1. Add a column to the left of the Timestamp field for Check Number.
2. Add a C column to the right of Description for reconciliation purposes.
3. Label the two columns to the right of Debit(-)…Credit(+) and Balance.
4. Select the entire form and align your cells to center.
5. While the cells are still selected click and drag the rows to make them bigger.

Select everything below the first row (eg. Row 3-100) and delete it.

Double click the cell below balance and insert this formula…

=ArrayFormula(IF(ISNUMBER(F2:F)+ISNUMBER(E2:E);MMULT(TRANSPOSE(F2:F-E2:E)*(ROW(F2:F) >=TRANSPOSE(ROW(F2:F))); SIGN(ROW(F2:F)));IFERROR(1/0)))

Then click ENTER.

Add a beginning balance in the Credit(+) cell.

Now when you enter information in the form we created earlier, your Debits will be subtracted from your Credits and the Balance will carry forward.

To use this on your Phone, simply email or text yourself the URL of the form and create a bookmark. When you make a purchase, select the bookmark and record the transaction. When you want to record a Credit, add a new row via your computer and record the information.

About the Author

Michael is owner/operator of The Graphic Design Shop, which he started after graduating from the Savannah College of Art and Design in 2000. Michael is a fan of all things creative and lives by the motto "We Don't Just Talk About It!".

Visit Michael Krapf's Website♦Visit Michael Krapf on Twitter♦Subscribe