DEV Community

Richard C
Richard C

Posted on Edited on AI-assisted

How to track subscriptions from your debit card statement in Google Sheets

#googlesheets #productivity #tutorial #beginners

AI-Assisted

By Richard C, AIL Studio

Your debit card statement already lists every subscription you pay for. They're just buried between groceries and fuel, under names like MUSICBOX*APP 3007. Most guides tell you to look for the same amount every month. That misses three common cases: a price that changed, a service that bills every 4 weeks rather than monthly, and a yearly charge that only shows up once.

This post turns a CSV export into a short list of real subscriptions with the next charge date for each. Every function used here exists in Google Sheets. I checked them in LibreOffice Calc, which is where the screenshots come from.

1. Export 13 months, not 3

In your bank's app or website, download the card's transactions as CSV for the last 13 months. Three months is enough for monthly charges, but a yearly renewal needs two hits a year apart before the sheet can see it.

In Google Sheets: File → Import → Upload, then Insert new sheet. Keep only Date, Description and Amount (money out as positive numbers), and name the tab Statement.

2. Pull out a merchant name

Descriptions carry reference numbers that change every month, so the same service never matches itself. In D2, then fill down:

=LEFT(B2,FIND(" ",SUBSTITUTE(B2,"*"," ")&" ")-1)
Enter fullscreen mode Exit fullscreen mode

This keeps the first word and treats * as a space, so MUSICBOX*APP 3000 becomes MUSICBOX. If your bank puts the merchant in a different position, adjust this one cell and fill down again.

Statement tab with the Merchant formula in D4. Example data.

3. One row per merchant

On a new tab called Merchants, in A2:

=SORT(UNIQUE(FILTER(Statement!D2:D54,Statement!D2:D54<>"")))
Enter fullscreen mode Exit fullscreen mode

Then, across row 2 and filled down (replace 54 with your last row):

  • Charges: =COUNTIF(Statement!$D$2:$D$54,A2)
  • Min: =MINIFS(Statement!$C$2:$C$54,Statement!$D$2:$D$54,A2)
  • Max: =MAXIFS(Statement!$C$2:$C$54,Statement!$D$2:$D$54,A2)
  • Spread: =(D2-C2)/C2, formatted as a percentage
  • Avg gap (days): =IF(B2<2,"",(MAXIFS(Statement!$A$2:$A$54,Statement!$D$2:$D$54,A2)-MINIFS(Statement!$A$2:$A$54,Statement!$D$2:$D$54,A2))/(B2-1))
  • Monthly cost (column H, uses the Pattern in G from step 4): =IF(G2="Monthly",D2,IF(G2="Every 4 weeks",D2*13/12,IF(G2="Yearly",D2/12,"")))

Spread is the column most guides skip. Here GYMPASS went from 29.00 to 31.90 and MUSICBOX from 10.99 to 11.99. A strict same-amount match would split each of those into two half-lists.

4. Judge by the gap, not the amount

The average days between charges tells you the cadence, whatever the amount did. In G2:

=IF(B2<2,"One-off",IF(ABS(F2-28)<=1,"Every 4 weeks",IF(ABS(F2-30.4)<=3,"Monthly",IF(ABS(F2-365)<=10,"Yearly","Not regular"))))
Enter fullscreen mode Exit fullscreen mode

Merchants tab: Every 4 weeks, Monthly and Yearly, with groceries and fuel marked Not regular. Example data.

The false-positive step: groceries, fuel and pharmacy charges repeat, but not on a schedule, so they come out as "Not regular" or "One-off". Skim that list once. Delete what's clearly shopping, and move anything you recognise as a subscription by hand.

Every 4 weeks means 13 charges a year, not 12, so MUSICBOX costs about 12.99 a month, not 11.99. For quarterly and other billing cycles, I covered the formulas in how I built a subscription tracker, so I won't repeat them here.

5. Next charge, highlighted ahead of time

In I2 (Sheets supports EDATE):

=IF(H2="","",IF(G2="Yearly",EDATE(last,12),IF(G2="Monthly",EDATE(last,1),last+28)))
Enter fullscreen mode Exit fullscreen mode

Replace last with MAXIFS(Statement!$A$2:$A$54,Statement!$D$2:$D$54,A2), the latest charge date. Then add a conditional formatting rule on I2:I with this custom formula:

=AND(I2<>"",I2>=TODAY(),I2-TODAY()<=30)
Enter fullscreen mode Exit fullscreen mode

The screenshot uses a fixed "As of" date in L1 instead of TODAY(), so the example stays the same.

Next charge column: three charges due within 30 days highlighted; the yearly CLOUDVAULT renewal is a year out. Example data.

Recap

  1. Export 13 months of debit card transactions as CSV.
  2. Strip the merchant name, then list each merchant once.
  3. Use Spread to catch price changes and the average gap to find the cadence.
  4. Filter out the "Not regular" shopping.
  5. Show the next charge date and highlight anything within 30 days.

In the example, four of seven merchants are subscriptions, costing about 68.71 a month, and three of them charge again within a week.

Formulas checked in LibreOffice Calc; I haven't tested them in Google Sheets or Excel, so check them once in your own copy.

If you'd rather start from a ready-made sheet, I keep one here: Subscription Tracker.

Top comments (0)