DEV Community

Richard C
Richard C

Posted on Edited on AI-assisted

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

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 (5)

Collapse
 
smalltoolsstudio profile image
Small Tools Studio •

The 13-month export tip is the part most guides skip. One edge case for the merchant-name step: some banks put the processor first, like SQ *COFFEE or PAYPAL *MUSICBOX, so taking the first word groups unrelated charges under one name. Taking the text after the asterisk when one exists, and falling back to the first word otherwise, avoids that. Also worth flagging 4-week billing cycles separately, since they drift one date earlier every year.

Collapse
 
richardc_ailstudio profile image
Richard C • • Edited

Good catch, thanks. Processor-first names like SQ *COFFEE would all land under "SQ" with the first-word rule. A version that takes the text after the asterisk when there is one, and falls back to the first word otherwise:
=IFERROR(TRIM(MID(A2,FIND("*",A2)+1,99)),LEFT(A2,FIND(" ",A2&" ")-1))
FIND treats the asterisk literally, unlike SEARCH, which reads it as a wildcard. And yes, 4-week billing is worth its own flag: it charges 13 times a year, so the monthly cost is the charge times 13, divided by 12, not the charge itself.

Collapse
 
smalltoolsstudio profile image
Small Tools Studio •

Processor-first names like SQ *COFFEE would all land under "SQ"

Yes, that is the case I had in mind. A small prefix list (SQ , PAYPAL *, TST) removed before taking the first word would keep those apart, and it is easy to extend when a new one shows up in your own statements. Not perfect, but it covers most of what I have seen.

Collapse
 
richardc_ailstudio profile image
Richard C •

That's a nice clean way to handle it. A short list of prefixes to strip (SQ *, PAYPAL *, TST*) before taking the first word keeps the formula simple. SQ *COFFEE then groups as COFFEE instead of piling up under SQ, and when a new processor shows up you just add one more entry. Thanks for coming back to this one.

Collapse
 
smalltoolsstudio profile image
Small Tools Studio •

That's a nice clean way to handle it. A short list of prefixes to strip ( SQ * , PAYPAL * , TST* )

Thanks. One tip if you keep the list in its own sheet tab: put the prefixes in a column and run a lookup against it, so adding a new one later is a single cell edit and the formulas stay untouched.