Construct a Subscription Tracker in Excel with Renewal Alerts

This complete information will provide you with sensible steps to construct a subscription tracker to trace all of your subscriptions in a single place utilizing MS Excel On-line and ship you e mail alerts earlier than renewal. This can be a essential device, particularly in at the moment’s world, the place we frequently pay for pointless instruments and companies and sometimes neglect about them till a renewal price is charged to us. This information will aid you robotically calculate renewal dates for any billing frequency(Weekly/Month-to-month/Yearly), and ship e mail renewal alerts, whereas on the identical time monitoring spending patterns throughout classes and over a time period.

Step 1: Design The Column Construction

Open Excel On-line and determine the column construction based mostly on the small print we need to monitor, and based mostly on that, fill within the header rows. I’ve used the construction under:

A1: Service - Subscription title (Netflix, Spotify) 
B1: Class - Sort (Leisure, Productiveness) 
C1: Value - Month-to-month/annual worth 
D1: Foreign money - USD, EUR, and many others. 
E1: Frequency - Month-to-month, Quarterly, Yearly 
F1: Begin Date - First billing date 
G1: Standing - Energetic, Cancelled, Paused 
H1: Auto-Renew - Sure/No 
I1: Reminder Days - Alert timing (7, 14, 30) 
J1: Subsequent Renewal - Calculated renewal date 
K1: Alert Date - When to ship reminder 
L1: Days Till - Countdown to renewal 
M1: Month-to-month Equal - Normalized month-to-month value

Step 2: Fill in Pattern Knowledge

Insert a pattern of varied subscription companies. We will add completely different merchandise based mostly on numerous classes. Additionally embrace each lively and cancelled subscriptions to check the formulation. Depart Columns J, Okay, L, and M empty for now, as they are going to be crammed robotically with the assistance of formulation within the upcoming steps.

Step 3: Constructing Good Calculation Formulation

Now, we’ll add formulation to robotically calculate renewal dates and alerts. These formulation deal with completely different billing cycles and exclude cancelled subscriptions.

1. Subsequent Renewal Date Components (Column J):

Click on cell J2 and enter this formulation:

IF(G2="Cancelled","",IF(E2="Month-to-month",IF(DAY(F2)>DAY(TODAY()),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(F2)),DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(F2))),IF(E2="Quarterly",DATE(YEAR(TODAY()),MONTH(F2)+3,DAY(F2)),IF(E2="Semi-Annual",DATE(YEAR(TODAY()),MONTH(F2)+6,DAY(F2)),DATE(YEAR(TODAY())+1,MONTH(F2),DAY(F2))))))

This returns clean for cancelled subscriptions and calculates the subsequent month-to-month renewal robotically based mostly on the beginning date.

2. Alert Date Components (Column Okay)

Click on cell K2 and enter this formulation: =IF(G2="Cancelled","",IF(J2="","",J2-I2))

This subtracts reminder days from the renewal date to find out when alerts ought to set off.

3. Days Till Renewal (Column L)

Click on cell L2 and enter this formulation: =IF(G2="Cancelled","",IF(J2="","",J2-TODAY()))

This creates a countdown displaying days remaining till renewal.

4. Month-to-month Equal(Column M)

Click on cell M2 and enter this formulation:

=IF(G2="Cancelled",0,IF(E2="Month-to-month",C2,IF(E2="Quarterly",C2/3,IF(E2="Semi-Annual",C2/6,C2/12))))

This normalizes all prices to month-to-month quantities for finances planning.

Step 4: Construct A Abstract Dashboard

Add a abstract dashboard beginning at row 24 to trace key metrics:

1. Whole Month-to-month Equal (In USD): Add This Components to calculate month-to-month prices:

=SUMIFS(M:M,G:G,"Energetic")

2. Energetic Subscriptions: Add This Components to calculate lively subscriptions:

=COUNTIF(G:G,"Energetic")

3. Renewals In Subsequent 30 Days: Add This Components to calculate Upcoming Renewals:

=SUMPRODUCT((G2:G50="Energetic")*(L2:L50<=30)*(L2:L50>0)*(L2:L50<>""))

4. Estimated Upcoming Value (In USD): Add This Components to calculate Upcoming prices:

=SUMPRODUCT((G2:G50="Energetic")*(L2:L50<=30)*(L2:L50>0)*(L2:L50<>"")*(C2:C50))

Step 5: Creating Visible Alerts

Now arrange conditional formatting to spotlight pressing renewals:

1. Choose column L (Days Till Renewal)
2. Go to House  ->  Conditional Formatting -> Spotlight Cell Guidelines
3. Select lower than and equal to 4, and select pink formatting for pressing alerts
4. Repeat step 2 and add one other rule with yellow formatting for reasonable warnings for cell values between 5 and seven
5. Repeat step 2 and add one other rule with inexperienced formatting for gentle warnings for cell values 8 and above

Now, subscriptions will robotically spotlight in numerous colours based mostly on their pending renewal days.

Step 6: Arrange Automated Electronic mail Alerts

Since Excel On-line doesn’t have built-in e mail automation, we’ll use Google Apps Script to create highly effective automated e mail alerts.

Steps for Setting Up Google Apps Script:

1. Add File: Add your Excel file to Google Sheets.
2. Entry Apps Script: In your Google Sheet, go to Extensions -> Apps Script
3. Delete Default Code: Take away the prevailing code
4. Create New Challenge: The venture will auto-save as “Untitled venture”
5. Rename Challenge: Click on “Untitled venture” and rename to “Subscription Electronic mail Alerts”

Paste this entire script into the Code.gs file:

perform checkSubscriptionRenewals() {

 const sheet = SpreadsheetApp.getActiveSheet();

 const information = sheet.getDataRange().getValues();

 const headers = information[0].map(h => h.toString().trim().toLowerCase());

 // Discover column indices

 const serviceCol = headers.indexOf('service');

 const costCol = headers.indexOf('value');

 const statusCol = headers.indexOf('standing');

 const daysCol = headers.indexOf('days till renewal');

 const renewalCol = headers.indexOf('subsequent renewal');

 if ([serviceCol, costCol, statusCol, daysCol, renewalCol].consists of(-1)) {

   throw new Error("A number of required headers not discovered within the sheet.");

 }

 let alertMessages = [];

 // Test every row (skip header)

 for (let i = 1; i < information.size; i++) {

   const row = information[i];

   const days = Quantity(row[daysCol]);

   if (row[statusCol] === 'Energetic' && days <= 7 && days > 0) {

     alertMessages.push(

       `• ${row[serviceCol]}: $${row[costCol]} renews in ${days} days (${row[renewalCol]})`

     );

   }

 }

 // Ship e mail if there are alerts

 if (alertMessages.size > 0) {

   const topic="Subscription Renewal Alerts";

   const physique = `The next subscriptions are renewing quickly:nn${alertMessages.be part of('n')}nnCheck your subscription tracker for particulars.`;

   GmailApp.sendEmail('[email protected]', topic, physique);

   Logger.log('Alert e mail despatched!');

 } else {

   Logger.log('No upcoming renewals.');

 }

}

Output:

Learn extra: Excel for Knowledge Evaluation

Finest Practices for Upkeep

Listed below are among the pointers you possibly can observe to maintain the method working:

• Weekly Evaluate: Test upcoming renewals and take motion on undesirable companies
• Month-to-month Updates: Add new subscriptions and replace cancelled ones
• Quarterly Evaluation: Evaluate spending patterns and negotiate higher charges
• Annual Audit: Complete overview of all subscriptions for optimization

Conclusion

With numerous subscription companies, it’s troublesome to maintain monitor of all of your recurring bills. Nevertheless, now with the assistance of this Excel tracker, you may be on prime of your issues and assist lower your expenses. With the assistance of this data, now you can:

1. Construct a complete subscription tracker in Excel On-line with superior formulation
2. Automate e mail alerts utilizing Google Apps Script
3. Deal with sophisticated subscriptions with completely different billing cycles, in addition to cancelled subscriptions
4. Preserve management over recurring bills by means of systematic monitoring and alerts

With this method, you’ll by no means lose monitor of subscription prices once more and might make knowledgeable selections for under these subscriptions that present actual worth.

You may entry the subscription tracker Excel file from right here: Excel On-line

Continuously Requested Questions