Welcome Tiller Money Memo readers to the new Tiller Money Community!

I love using Macros under the “Tools” menu. You can record any of those tedious things that you do over and over again in a sheet and completely automate it. And then it’s fun to go take a look at the Google Apps Script that the macro created to automate your task - and you can then update the Apps script (under “Tools” -> “Script Editor”) with any additional custom items you’d like it to do. It’s fun to play with!

I’ll echo @peter regarding ARRAYFORMULA() but I’ll up the ante…

I’m a big fan of embedding arrayformulas in column headers so that a column header is rendered in the first row but a formula is applied to all rows below. This approach allows spreadsheet users to add and delete rows throughout the column without breaking anything.

In practice, a column header arrayformula may look like this:
=ARRAYFORMULA(IF(ROW(B1:B)=ROW(B$1), "Header Name", IFERROR(VLOOKUP(A1:A,'Other Sheet'!$A$2:$B, 2, FALSE))))

Randy

2 Likes

I’m interested in how you use that formula for categories!

1 Like

One of my favorites in Google Sheets is =sumif or =sumifs - great for doing analysis and use it a lot here at Tiller for building metrics reports.

Ben Collins demos this nicely in our most recent webinar at about minute 20 when we’re working on building a cash flow dashboard from scratch.

2 Likes

There’s some great tips/tricks above, especially ARRAYFORMULA and writing a function that puts the column name in the top row.

I’ve got 2 favorites in Google Sheets. Version history can really help if I make a change and discover later that it didn’t work. I’ve also wiped out the content in a cell by mistake and Version history let’s me recover it. The ability to look back in time over the sheet is great.

I also try to remember to make a named version at key times with a useful name. For example, “Working version with better formatting”. It’s a simple Version Control System, a stripped down version of Git for users who don’t even know what Git is.

My other favorite is QUERY(). To be able to write SQL queries inside a spreadsheet is so powerful. Its such a quick way to add reports and views of your data. It’s well worth the time to learn how to use this function if you want to have greater control of how to slice and dice your data.

3 Likes

I totally overlooked Version history - that’s a feature I use almost daily. I use it so much it’s an invisible feature!

1 Like

@randy stole mine, but you can also do “B:B” instead of “B1:B”. It’s great to not have to constantly fill-down on a formula. We use that in Tiller to have a column for coarse-grained categories based on the fine-grained category.

Specific to google sheets, I’m also a big fan of custom formulas and add-ons. You can write a complex formula in Javascript, and they call it from within a spreadsheet cell. I’ve built some custom reporting on year-to-date spend using that which has been helpful for tracking. I’ve also built an add-on for splitting Tiller transactions which pops up a dialog and allows you split a single transaction into multiple transactions, each with it’s own category.

3 Likes

ARRAYFORMLA() already has a lot of coverage here as a favorite, and I’m going to give QUERY() (and the Google Visualization API Query Language!) an honorable mention, then call some attention to the obscure-but-powerful partner to those two: array notation.

Deets here: https://support.google.com/docs/answer/6208276?hl=en

It’s super-easy to understand and use…but it doesn’t really start to shine IMHO until you combine it with those others.

OK, here’s a dirt simple (but mostly useless) example -

={"Portland"; "Seattle"; "Vancouver"}

(separated with semicolons) gives you those three cities in that cell’s column:

46%20PM

and, similarly:

={"Portland", "Seattle", "Vancouver"}

(separated with commas) gives you those three cities in that cell’s row:

33%20PM

I should have stopped here, in the interest of keeping my answer to Ed’s question simple, but as I am prone to do when spreadsheets are involved, when I have a lot of caffeine at lunch, and when I’m excited about something new and cool (like the new Tiller community), I feel a little like getting carried away. So apologies in advance.

Hold.My.Beer.

All that {} array stuff above was simple, and obvious, and maybe kind of cool – but big deal, though, right? WHY would I use this?

Well, it’s a LOT to swallow in one gulp if you’re not already comfortable using QUERY and ARRAYFORMULA, but turns out you can QUERY those {} arrays, like the formula below. This beast (I’ve seen a lot worse, though!) gives me the last 365 days’ debit transactions, and adds a formatted month and the start date of each week column, such that I can then reference the resulting data in pivot tables and charts, to see just exactly how much I spent in various categories or at various merchants over the last 365 days - without having to modify my Transactions sheet. In my case, it’s important for me to leave my Transactions sheet alone because like I said, I get carried away - and after about 5 years of working on it, my particular Transactions sheet is probably referenced by like 50 other formulae just like this one, scattered throughout about 10 sheets shared with my wife and a couple of my college-aged children – so if I messed with my Transactions source sheet, it would probably break a bunch of stuff I’ve already built on top of it and my wife and college age children would call me and ask me what I broke.

But I digress. (a lot, like I said). Here’s that formula that demonstrates one reason I appreciate {} arrays.

=QUERY({'Transactions'!A:L, 
        ARRAYFORMULA(TEXT('Transactions'!A:A, "YYYY-MM")),
        ARRAYFORMULA('Transactions'!A:A - WEEKDAY('Transactions'!A:A))}, 
"
SELECT Col1, Col2, Col3, -1 * Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14
  WHERE Col1 > DATE '" & TEXT(TODAY()-365, "yyyy-MM-DD") & "'
    AND Col4 < 0
  LABEL -1 * Col4 'Amount', Col13 'Month', Col14 'Start of Week'
")

Whew! (…hopefully someone chimes in soon with how to enter those carriage returns in a cell, otherwise that sucker gets REALLY hard to read. I love carriage returns almost as much as I love arrays. But I digress further.)

Adding something like this Month and Start of Week columns is why I’d use commas, and the following (simpler!) example shows why I might use semicolons. In this case, all I’m doing is combining my manually entered balance history data with my Tiller-fed balance history into a single sheet. The query isn’t strictly necessary in this case, but I use it here to limit the results to the last 365 days. My manually entered balance history (on a separate sheet, creatively named ‘Manually Entered Balance History’) has the exact same column format as Balance History, and includes such things as the value of my home and car, and the jar of coins I keep buried in my backyard*. Similar to the more complex case above, I have a ton of other ancient sheets that reference my Tiller-fed balance history sheet, and so don’t want to mess with that one directly.

=QUERY({'Balance History'!A:I; 
        'Manually Entered Balance History'!A:I}, 
"
SELECT * 
  WHERE Col1 > DATE '" & TEXT(TODAY()-365, "yyyy-MM-DD") & "'
  ORDER BY Col1
")

*I don’t actually have a jar of coins buried in my backyard.

5 Likes

Wow @tim . Mind kind of blown by this.
Didn’t realize arrayformula, query, and arrays could be combined like this.

I see it lets you extend Google Sheet’s version of SQL so you can query from 2 or more tables (sheets) which i didn’t think was possible. Or 2 sections of a single sheet. No need to make a temporary sheet.

I tried using that Col1, Col2 formatting and see it looks like it only works if you use an array in the Query data section. Just using Col1 in a single, regular data set gave me errors.

It’s good to have this tool in the toolkit.

2 Likes

This is fantastic @tim!

Great stuff, Tim!

You can enter line breaks in a formula with Ctrl + Enter in the formula bar :slight_smile:

1 Like

Welcome to the Community, @benlcollins!

2 Likes

Excited to be part of this community! Thanks Tiller team, I think this will be a super valuable resource for us all :slight_smile:

Here are some of my favorite tips:

Ctrl + \ (or Cmd on Mac) to remove all formatting
Ctrl + Shift + V to paste as values
Shift + S to create a new Sheet in your Drive folder
Shift + F11 to create a new tab in your Sheet
Type sheets.new into your browser to create a new Sheet

Hover over the column headers line to see a grab hand and drag that down to freeze the top row(s). Also works for columns.

Grab the base of the formula bar until you see the cursor change into a little double-ended arrow. Then click and drag down to make the formula bar as wide as you want.

The ArrayFormula, curly brackets {} and the QUERY function covered by Tim above are all phenomenal tools to have in your toolbelt.

Other functions worth learning are INDEX, MATCH, VLOOKUP, FILTER.

Knowing that spreadsheets store dates as 5-digit numbers. For example, today 9/13/19 is stored as the number 43721 (change the formatting to a number to see).

Ctrl + ; inserts the current date into a cell.

Double clicking on a date in a cell brings up the date picker.

:slight_smile:

5 Likes

LOVE IT! :raised_hands:

3 Likes

Right on! I must type that in 20 times a day now :rofl:

1 Like

This is a great one, as using the menu to do the freeze (and finding where it is) is not easy. :slight_smile:

2 Likes

I love shortcuts, they make things SO fast. I didn’t know a lot of these, thanks for sharing!

2 Likes

(1) I like the Generate Reports under Add-ons/Tiller/Business. I create a P&L report every month which shows all the previous months in the year for comparison purposes.

(2) Also, I created a Portfolio tab that tracks all my investments by account, position, and lot using googlefinance formulae. It updates constantly throughout the trading day and lets me know instantly where I stand regarding every position I own, including my current paper gain/loss.

(3) I do not use it as often as I should, but the Yearly Insights tab is pretty powerful.

I use Tiller daily as it allows me to track my finances better than I could do before using Tiller. I have been a user for about 9 months.

I like Tiller as they seem to be continually inventing, investing in the future, and are on the cutting-edge.

:+1:

4 Likes

Great feedback, @Blake.

I only just discovered the Yearly Insights template and wholly agree it can be addicting to see trends and splurges so easily summarized on an annual a basis. It’s my new favorite sheet.

Randy

In Google Sheets, you can multiply columns of TRUE/FALSE values together to do AND/OR/XOR operations:

Which is good for something like this, which will filter a sheet for rows that have either one value in one column or another value in another column, for example if you want to filter transactions from two accounts and show them in the same table:

=IFERROR(
  FILTER(
    TR!$A$2:$B, TR!$B$2:$B > DATE(sheetYear(), monthIndex($E$18), 1),
    ARRAYFORMULA(
      ARRAYFORMULA(TR!$F$2:$F = "account-one")
      + ARRAYFORMULA(IFERROR(SEARCH("account-two", TR!$M$2:$M) >= 0, FALSE))
      > 0
    )
  ),
  ""
)
3 Likes