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) 

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

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.