Money tips

Your Top Merchants This Month: Where the Money Goes (Canada Edition)

A Google Sheets method for ranking the merchants you spend most with each month, using one QUERY formula, with a worked example in Canadian dollars.

Editorial illustration for Your Top Merchants This Month: Where the Money Goes (Canada Edition)

In Google Sheets you can rank your merchants with one QUERY formula: it groups a month's transactions by merchant, adds up the amounts and sorts them from largest to smallest. Paste your transactions into a sheet, tidy the merchant column, and the top ten appears in a single cell range.

Set up the sheet

Use three columns. Column A holds the clean merchant name, column B the date and column C the amount as a positive number for spending. Paste a month of transactions starting at row 2 and keep row 1 for headers. Clean names matter: if a shop appears as three spellings, the formula will count it as three merchants.

The formula that does the ranking

Type this in E2:

  • =QUERY(A2:C500, "select A, sum(C), count(C) group by A order by sum(C) desc limit 10", 0)

It returns up to ten rows with the merchant, the total and the number of visits. The final 0 tells the sheet your range has no header row. If you pass A1:C500 and want the headers read, change that last value to 1. If the sheet complains, check that every amount in column C is a number and not text.

A worked example in Canadian dollars

Say the month's spending came to C$2,150, with invented numbers.

MerchantTotalVisitsAverage visit
Grocery storeC$52010C$52.00
Home internet and phoneC$1352C$67.50
Gas and car washC$2105C$42.00
Restaurants and cafesC$18812C$15.67
Online marketplaceC$2646C$44.00

The total for these five is C$520 + C$135 + C$210 + C$188 + C$264 = C$1,317, or about 61.3% of C$2,150. The formula would order them grocery C$520, online marketplace C$264, gas C$210, restaurants C$188, internet C$135.

What if a fixed bill is crowding the list?

Rent, a car loan or a phone plan can sit at the top and make the ranking boring. Add a fourth column D with the word Fixed or Flexible, and extend the query to: select A, sum(C) where D = 'Flexible' group by A order by sum(C) desc limit 10. Now the ranking shows only the merchants you could change next month.

Is the ranking a budget?

No. It describes what already happened. Use it to pick the one or two merchants worth a conversation with yourself, then decide a number for them separately. A budget built on last month's actuals is only as good as that month was typical.

How do you compare two months side by side?

Make a second sheet for last month with the same layout and a second QUERY in column J. In a third area, list the merchants from the current ranking and use a lookup to find last month's total: =IFERROR(VLOOKUP(E2,$J$2:$K$11,2,FALSE),0). Subtract it from this month's figure in column F to get the change. The IFERROR returns zero for a merchant that was not in last month's top ten, which is useful, because a new name in the top ten is itself worth a look.

A note on cash back and rewards

If a card gives rewards or a statement credit, record it as a credit against the merchant or leave it out of the ranking and track it separately. Mixing it in makes a merchant look cheaper than the money you handed over. The ranking should reflect what left your account, so you see where your money goes before any reward comes back.

Keep the sheet simple. A ranking with five columns that you update each month is worth more than an elaborate dashboard you abandon after two.

A repeatable routine

  1. Paste the month's transactions in on the first weekend of the next month.
  2. Clean the merchant column and fill the Fixed or Flexible label.
  3. Read the two query results side by side.
  4. Write one sentence under them describing what you learned.

For anyone also tracking Indian rupee spending, SpendixAI is built around India and INR and its dashboard lists top categories and merchants, but the sheet above works fully without it.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports