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)
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.
3. One row per merchant
On a new tab called Merchants, in A2:
=SORT(UNIQUE(FILTER(Statement!D2:D54,Statement!D2:D54<>"")))
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"))))
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)))
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)
The screenshot uses a fixed "As of" date in L1 instead of TODAY(), so the example stays the same.
Recap
- Export 13 months of debit card transactions as CSV.
- Strip the merchant name, then list each merchant once.
- Use Spread to catch price changes and the average gap to find the cadence.
- Filter out the "Not regular" shopping.
- 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)
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.
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.
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.
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 *COFFEEthen 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.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.