CG Pilot Training Tracker

Currency lived in three places and agreed in none.

Aircrew flight qualifications expire. Each one runs on its own clock, some every 30 days, some every 90, most annually, and a lapsed qualification grounds the aircrew until it is renewed. Knowing who is close to the edge meant pulling three separate exports, the flight logbook, the simulator logbook and the designations list, and cross-referencing them by hand.

Done properly for a whole air station, compiling that picture took over a week, and the flight schedule built on top of it took six weeks wall to wall. Long enough that the answer had aged by the time it arrived, which is the failure mode that matters: a currency report nobody can produce often enough is a currency report nobody trusts.

Three exports in, one graded sheet out.

I am its sole author and sole maintainer. I wrote it in Python and later VBA so it would run inside the Excel the unit already had. Two officers at USCG Sector San Diego shaped it as domain stakeholders: they explained the manual process it replaced, defined what each qualification and interval actually meant, and reviewed every iteration of the output. The requirements came from the people who had been doing it by hand.

A run takes three CSV exports, loads each into a temporary sheet, matches pilots across all three, computes every interval against the report date, writes one row per pilot grouped by designation class, and deletes the temporary sheets behind it. Nine qualification dates and eight recency counters per pilot, on one sheet, in one pass.

The finished sheet contains no formulas and no conditional formatting. The macro writes values and paints fills directly, which is a deliberate choice rather than an omission: the output is a fixed artifact of the moment it was run, and it cannot silently recalculate into a different answer on a different machine on a different day. The thresholds live in the code, where they can be read and reasoned about, and they are not uniform: a pilot’s designation class decides how many days count as overdue.

Two days, at every air station.

The report now takes three minutes to generate instead of over a week, and the flight schedule it feeds went from six weeks to two days end to end. It was adopted at every U.S. Coast Guard air station and is still in daily use. The work was recognised with the Coast Guard Auxiliary Achievement Medal, awarded by the Commandant of the U.S. Coast Guard in March 2023; the citation credits a training records program that spread fleetwide.

All of it is unpaid volunteer work. I have been a Coast Guard Auxiliary volunteer since 2015 and have written software for Sector San Diego since 2022, a role that did not exist until I created it, and I have been its only maintainer since.

A second tool for the same Sector, a parts inventory system and database, cut helicopter parts search time by roughly 85 percent. It stays a card on this site rather than a case study, for the honest reason that there is nothing about it that can be shown.

• Python
• VBA
• Excel
• pandas
• numpy