Lowest Cost Bus Pass Calculator

I don't know if anybody else in the world has this problem, but if you do, the attached Microsoft Excel visual basic code may help you.

In the winter I ride the bus to work. In the summer I ride my bicycle. Our transit system recently switched to a smart card fare system, and they now offer a dizzying array of pass options: e-cash (replaces tickets), 24 hour pass, 3 day pass, 5 day pass, 7 day pass, 14 day pass, 21 day pass, 28 day pass, monthly pass, annual pass. So the obvious question is, given the number of times that I plan to ride the bus on each day of the winter, what is the lowest cost combination of fares that I can buy?

Step 1: A Solution

Although for simple months you can determine that month's purchases fairly easily, I always wondered if I was missing some savings in future months because of what I was purchasing this month.

One possible way to solve this problem is with what computer people call dynamic programming. For each day in the calendar, you can calculate the lowest cost option up to that date by considering each pass option along with the lowest cost options on the preceding days that you have already calculated.

Step 2: The Microsoft Excel Program

The attached spreadsheet is what I used to answer the question of which passes I should buy.

The first sheet, Trips, has the list of all days that I want to consider, and the number of times I plan to ride the bus that day.

The second sheet, Pass Options, has a list of the pass options that the city transit offers, with their cost and how long they are valid for.

The last sheet, Solutions, shows the lowest cost solution for each day. Each column with the date at the top is the solution for bus passes starting on the first day and ending on the day at the top of the column. The solution for the last day is at the far right of the spreadsheet.

To use the spreadsheet, just fill in the number of trips on each day in the Trips sheet. Do not skip days. You must put every day with a 0 if you are not riding the bus that day. Do not change any column headers or table names in the spreadsheet without making the matching changes in the VBA code. Fill in the pass options on the Pass Options sheet. Press the "Calculate" button at the top of the Trips sheet, and then view the solutions on the Solutions sheet.

Unfortunately you must use Microsoft Excel since the program is written in VBA. It will not work in alternative spreadsheet programs such as google sheets or LibreOffice, or OpenOffice, or whatever else is out there. I use Excel 2013. It may not work in older versions because of built-in function changes.

Step 3: Additional Improvements

My original plan was to fill in the background of each pass purchase with the same background colour (or color if you prefer) shown on the Pass Options sheet, and to draw an outside border around the date range for each pass purchased. But once I saw the solution I needed, I abandoned any additional work.

The code is a mess I'm sure, my computer programming skills are limited.

Feel free to take it if you want and expand it and improve. Or even write a better version from scratch. Just be sure to post your version so I can start using it instead of this one.