Using A Query In Google Sheets to Organize Your Bank Account Transactions

The Transactions sheet in any Tiller-powered Google Sheets finance template lists your transactions from all accounts you have linked to the sheet. But what if you want a separate sheet to view transactions from a single account? In this post, we'll give you several ways to do it, including a pre-built sheet you can copy into an existing Tiller spreadsheet and an explanation of how to write a query for the DIYers.

Copy In Our Account Transactions Organizer Sheet

If you want a sheet that lists the transactions of any single account linked to your Tiller spreadsheet, and don't want to build it yourself, copy this sheet into your Tiller spreadsheet.

  1. Open one of your Tiller spreadsheets.
  2. Copy the URL of your Tiller spreadsheet.
  3. Open the Reports Master Sheet here.
  4. Right click the tab called “Account Transactions” at the bottom.
  5. Click “copy to” from the menu.
  6. Paste the URL of your Tiller spreadsheet into the bottom of the window that opens.
  7. Click “Select” to copy the "Account Transactions" sheet into your Tiller spreadsheet as a new sheet.
  8. Navigate back to your Tiller spreadsheet and find the new tab along the bottom called “Copy of Account Transactions” and rename the sheet by double clicking the sheet name.
Screen Shot 2018-06-09 at 1.36.20 PM.png

To use the sheet, select the account for which you want to view transactions in the drop down in cell B1.

The sheet is pre-built to show the first 10 columns from your Transactions sheet. If you want to view different columns, write down a list of the column letters for the columns you want to see from your Transactions sheet for an account. Then, in the formula in Cell A3 on the Account Transactions sheet replace SELECT A,B,C,D,E,F,G,H,I,J with the letters of the columns you wrote down. Don't make any other change to the formula.

Note: This sheet filters transactions using the Account column in your Transactions sheet. If you have a column named Account Name or Account # or Institution that you want to filter by instead, change the name in the hidden column Z in the Account Transactions sheet, in Cell Z1, from "Account" to the column name you want to match.

Pull in Your Account Transactions For A Single Account Using a Query

Google Sheets includes a very powerful QUERY function that makes filtering data (as well as many other data manipulations) possible. By entering a query formula in just one cell, you can build an entire sheet of data.

Here's what a formula to filter by an account looks like.

=QUERY(Transactions!A:Z, "SELECT A,B,C,D,E,F WHERE G = 'Citibank'")

Breaking down that formula,

=QUERY( Formulas always start with an equals symbol. The name of the function is QUERY. Function formulas start with a (.

Transactions!A:Z, This is the data range the query will pull in. A comma is need after the range. In this example, we are using Columns A thru Z from the Transactions sheet. If you have less columns, you can replace Z with the last column you want to view.

"SELECT A,B,C,D,E,F This is where you select which Column letters from the Transactions sheet you want to display. Column letters are separated with commas.

WHERE G = 'Citibank'") Column G is the Account column. If your Accounts column is different than Column G, replace the G with your Account column. This will filter the data to show only transactions where the Accounts column equals the text Citibank. The word Citibank is put in single quotes. After the ending single quote, there is a double quote to end the Query and a ) to end the formula.

If you enter that formula in Cell A1 of a new sheet, all your Citibank transactions will be listed. If you want separate sheets for each account, just replace Citibank with the Account you want to see on the sheet.

How Our Account Transactions Sheet Works

hidden-columns-query.png

The sheet provided in Option 1 above uses the same basic idea but it takes the concept a bit further by allowing the customer to select which Account to view on a single sheet. Here's how it works:

Before we can get to the QUERY formula, there's some values that need to be figured out.

Take a look at the hidden columns Y and Z.

The column name to filter is listed in Z1. This is set by default to Account, but it can be changed to any column name.

Z2 uses the formula =match(Z1,Transactions!A$1:Z$1,0) to lookup the number of the column name from Z1.

Z3 converts the column number to a column letter using the formula =char(64+Z2).

Z4 creates a text version of the column range with the formula ="Transactions!"&Z3&"2:"&Z3. This is needed for the formula in Z5. By fixing the column range, such as G2:G, new transactions won't cause the range to change.

Z5 creates a list of Accounts using the formula =UNIQUE(INDIRECT(Z4)). The list is used to create the data validation list used in the Account selector dropdown in Cell B1. Since you might have the Accounts column anywhere, this method will work no matter where the Account column is.

Now we can review the query in A3 that generates the filtered transaction list:

=QUERY(Transactions!A:Z,"SELECT A,B,C,D,E,F,G,H,I,J WHERE "&Z3&" = '"&B1&"'")

The query is similar to the one explained above:

=QUERY(Transactions!A:Z, "SELECT A,B,C,D,E,F WHERE G = 'Citibank'")

But the fixed column letter G, for the column name to search in, has been replaced by "&Z3&", a dynamic reference to the Account column. Also, the fixed name 'Citibank' has been replaced by B1, which shows the Account to view from the dropdown list.

The use of double quotes, single quotes and ampersands surrounding the cell references are required to make the formula work.

Using Google Sheets Query For Your Own Experiements

With this knowledge you should be able to start exploring how you can create more customized reports based on Tiller's Transaction sheet to meet your unique financial reporting needs.