Tracking things in Excel sucks. Maybe Excel just sucks.

Working as a contractor, it is your responsibility to track your hours worked (duh, right?). You may use existing software, but it might cost money. You may use existing software, but you are strictly limited to the features the software provides. You may use Excel, but you are also constrained to the features that Excel supports (and you have to use Excel).

As usual, I have to take matters into my own hands 😈.

For my current internship, Initially, all shifts were tracked in Excel. The only data recorded were the date, clock in and out time, and the elapsed time for that SOW (Statement of Work). ShiftTrackPro implements a myriad of new features, with each additional feature being added as I discovered the need for it. ShiftTrackPro now automatically tracks allocated time for each SOW, time remaining, amount invoiced, amount received (and not yet received), which invoices have been paid out, current and historical wages, and even lunch breaks.

Also, on completion of a SOW (or when triggered with the complete flag), ShiftTrackPro dynamically generates an invoice with hours billed, the amount due, duties/responsibilities for that SOW, my contact information, and even requests permission to automatically email the invoice to my employer. This has saved me countless hours that I would have spent fiddling with an invoice template, manually populating each field.

ShiftTrackPro is a CLI (Command Line Interface) written in Python with a SQLite database to store records. The underlying database has two tables, with the following schemas:

SOWHours (stores SOW properties and statuses)

• sow INT PK
• hours INT
• completed TEXT
• collected TEXT
• wage INT

sows (stores the shifts)

• date TEXT
• clockIn TEXT
• clockOut TEXT
• break TEXT
• difference TEXT
• sow INT FK

In hindsight, using the INT type for all boolean fields might have been more appropriate.

Each command is in the format [SOW] (flag), with the exception of the wildcard cur flag. While [SOW] is an integer representing the ID of a particular SOW, cur can replace the [SOW] argument and automatically find the latest in-progress SOW. If there are multiple SOWs in progress, a warning is displayed, urging the user to specify a SOW. Each flag is formatted a little differently and may have additional optional or required arguments.

Here are some implemented flags:

• create: initializes a new SOW with a specified amount of allocated hours and a pay rate
• read: displays all records/shifts for a particular SOW
• hours: checks if there is a record tracking how many hours are allocated to a particular SOW
• add: extends the time allocated for a particular SOW (or reduces it, if provided with a negative value)
• collect/uncollect: flag that payment was received for a particular SOW (or reverse that flag)
• complete/uncomplete: flag that a SOW is complete, even if it might have time remaining (or reverse that flag)
• import: migrates records from a CSV file into the SQLite database
• refresh (now obsolete): recalculate the difference between all clock in and clock out times (shift duration)

Omitting the flag argument logs a shift with the specified clock in and clock out times. For example, cur 10:00am 6:00pm creates a record for the current SOW with today’s date, a clock in time of 10:00am, a clock out time of 6:00pm, an 8 hour duration, and no lunch break.

The invoice generation is one of the latest features I added and also one of the most problematic to implement. Initially, I used python-docx to generate the invoice, email.mime to generate the email objects, and smtplib to send the emails. I was emailing the invoices as .docx files (yuck), until someone pointed out to me that a PDF file would likely be more professional.

This is when I discovered that programattic .docx to .pdf conversion is a nightmare, at least with Python. Many of the solutions are either paid or are intrusive during usage, such as docx2pdf which requires Office to be installed and opens Word to perform the file conversion.

At this point, I decided to generate the invoices as HTML documents using Yattag, which allowed for significantly more flexibility for modification and post-processing. Employing PDFKit allowed for instant and unintrusive PDF conversion. In the end, I did not run into issues with the email generation and sending so I stuck with email.mime and smtplib.

The motivation for ShiftTrackPro was essentially what was said in the introduction, which also closely aligns with the motivation for most of my projects: There was a need to be met and no existing product perfectly fulfilled it. Initially, the bare minimum was implemented: just basic logging of hours elapsed and the corresponding dates. As the existing features were refined, it became apparent that more of the process could be automated, such as invoice generation and emailing.

A pleasant byproduct of creating ShiftTrackPro (as opposed to using Excel) was the protection against accidental data deletion. Sure, control + z exists, but sometimes my cursor might skip to a random cell and overwrite some data without me noticing it. ShiftTrackPro introduces a middleman that processes the commands and makes it impossible to erroneously modify any records. The only gripe is that I have to be extra careful with row deletion and modification when manually changing rows in the SQLite database during debugging, but there’s a nice quirk that remedies that issue: in your DELETE queries, replace DELETE with SELECT * to see every row that you are about to delete 😉.

So, does ShiftTrackPro live up to its standards, ideologies, goals, and motivations?

I love ShiftTrackPro. It is convenient and so simple to use, saves me an incredible amount of time, and allows for easy feature implementation and upgrades. I love using a CLI (I feel like a hacker) but I was considering creating a web app interface to allow for shift logging even if I am away from my laptop. We’ll see where that goes.