These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, December 12, 2013

Family Verizon Bill Distribution and Payment Tracker

Executive Summary
My family participates in a Verizon family plan that allows for up to ten plan participants.  Our plan features unlimited minutes and 1 gigabyte of data.  The basic plan charges are shared by all seven plan members, while two of the members share the data charges equally.  My father logs on to the website every month to collect the charges for the month and divvy them up based on whether each plan member participates as a basic-plan or data-plan member.  This is a tedious but necessary process to ensure that every plan member knows how much to pay every month.  My wife and I are the primary plan members, and given that the total payment is posted to our own credit card every month, we anticipate timely reimbursement from all of the plan members.

To better facilitate timely payment and to provide reminders to those who have not paid, I developed a program to scrape the monthly billing information from the Verizon website and to divvy up the amount owed by each plan participant.  The code presents the amounts owed by each participant in a new spreadsheet that is created for the most recent bill.  I also created a Google form that enables the participants to identify when they have paid their share of each month’s bill.  The code sends e-mails to all plan participants with the amounts they owe and a link to the form to indicate when they have made payment.  Finally, the code downloads the Google form's results spreadsheet and checks to see who has paid on the most recent bill.  A “Paid” indication is shown next to each plan member who has paid, and e-mail reminders are sent to the plan participants that have not paid every time the code is executed.  The same code is run to accomplish all of the above, so it is programmed to distinguish whether the most recent bill has already been downloaded and, if so, it skips the web scraping to check the most recent version of the Google spreadsheet and to send the reminder e-mails.

Created by Glen Sanders

No comments:

Post a Comment

Blog Archive