Two months into the season and the scorecards were now a healthy stack. I walked past Charlie’s office and he stopped me. I leaned partially on the doorframe of his office. Casually, but with a serious tone, he asked, “Do you still think that we’ll be able to use your program?”.
“Absolutely”, I replied, trying to respond in a calm and nonchalant manner. “I’ll bring my laptop".
The scorecards were for Ladies’ Eclectic. It was an on-going tournament where the ladies had their best score for each golf hole, regardless of the day the round was played, on one continuous—digital—scorecard. The ladies could turn in as many scorecards as they wanted as long as they played with another lady in the tournament. It would be like taking five different classes in school and creating your GPA based solely on your best test score from each class, but you could take the test an unlimited number of times, and discard the bad scores.
Anyway, the scorecards would pile up in a drawer next to the computer, and the low men/woman on the totem pole (who worked in the golf shop) would have to go hole-by-hole, looking at the score on the physical scorecard in their hands, looking back up to the digital scorecard, selecting the score, and changing it—if it was lower, of course. This was the repetitive and boring job of Ladies’ Eclectic: it sucked.
I don’t want to get into the golf details of my particular case, but many people, in the course of their workday, have to do painful tasks on the computer. An assistant golf pro (AGP) is no exception.
However, what may be different between an AGP, and a normal computer job, is most AGP’s (especially the older ones) have no inclination to ever learn anything more then the absolute minimum when it comes to computers. It’s usually explained by some variation of the phrase, “That’s why we are golf pros”. Furthermore, two years ago, a fellow AGP told me a story about how the older head golf professional, at the time, learned (around 2013) how to use copy-and-paste. The assistant looked at me and said, “It was like he discovered fire.”
So, when I was tasked with the job of another season entering scores into the eclectic spreadsheet I decided to try something different.
I came across a very resourceful book (while on FreeCodeCamp) titled, Automate the Boring Stuff with Python: Practical Programming for Total Beginners by Al Sweigart.
“On its own, this book won’t turn you into a professional software developer any more than a few guitar lessons will turn you into a rock star. But if you’re an office worker, administrator, academic, or anyone else who uses a computer for work or fun, you will learn the basics of programming so that you can automate simple tasks…”
Supply met demand, and I soon had my own copy. With patience and effort, I made it all the way to Chapter 12: Working with Excel Spreadsheets. This chapter taught the reader about using the openpyxl module for handling Excel spreadsheets. After hours—and days, of tinkering with my own program I had something that worked (the code will be at end of article).
I approached my boss, Charlie, and to his credit he gave me permission, although hesitant, to use my program. Remember, golf pros don’t do computers, so it wasn’t a surprise that Charlie had never heard of the Python programming language. He looked at me like I was a magician who was explaining that I was going to pull 1.5 rabbits out my ass, and solve his ailments. What I mean is that he wasn’t only cautious, he was amused, and willing to let me have my fun.
Of course, one problem followed the other. The IT department wouldn’t let me download Python: no big deal. I’ll bring my laptop to work and transfer the files with Dropbox. However, things got worse. The original design of the program used the command line—which didn’t work—so the initial launch was a bust.
This was why Charlie was getting a confirmation from me as I walked past his office that day. It was time to execute.
Thankfully, I switched up the process. The command line interface was having trouble loading the spreadsheet. It would freeze. Damn. Instead of entering the scores for each individual golfer into the command line as arguments, then running the program to compare the scores, the scores would be entered into a different Excel spreadsheet.
The new spreadsheet would have columns for the golfer name, the course they played, and holes numbered 1-18. The two spreadsheets would then “talk” to each other comparing scores and making changes as needed. The program also logged activity to a file, so I could catch improper input or spelling.
This is the part that can be applicable to another person’s situation, because working with multiple spreadsheet files in a job setting is common (Excel has many useful functions built in, and I will note that I haven’t explored if there are better formula alternatives for exchanging data between different Excel files).
Entering the scores into the new spreadsheet eliminated the need to look back and forth between the scorecards, manually change scores, and have to search for the golfers before comparing scores. Names still had to be entered (but they could be tabbed pretty quickly), and scores could be quickly entered with the ten-key on the keyboard. I could bomb through fifty scorecards at maybe 3-4 times the speed. I didn’t feel nervous when scorecards would pile up, either.
Monday came around and when I ran the program on the actual file—the second time—it worked. I double checked some of the scores, and after they matched, my three-month project was done. It was a fairly anticlimactic ending, actually. What didn’t end that day was the change in my reputation.
I didn’t write any more programs for work. Instead, I was asked many times to solve technical problems (despite having basically the same background in technology as before). I was jeered a few times (“Maybe Jarrett can do something nerdy to fix it“), but I was now a freaking snake charmer. I had a new skill that gave me a little bit of an edge in the golf shop. Not only did I feel more valuable, but I started having conversations that were process related. I wasn’t calling the shots by any means, but my input became more valued.
I had picked up a unique skill that amplified my total worth through diversification. This is the important concept: there is real value in acquiring mixed skills that apply to seemingly perpendicular industries1. Normally, the example is given of the engineer that has a knack for public speaking. Choose the ‘perpendicular’ skill wisely, because it obviously can’t be anything.
There could be a skill (normally something difficult) that could be added that may give someone a large value return, as opposed to the incremental value return of increasing the normal skills outlined for the position. I would not only recommend the book, Automate the Boring Stuff with Python: Practical Programming for Total Beginners by Al Sweigart, but any interesting skill acquisition outside of the normal—and predictable—skill sets.
I must admit, there was already a solution to the ‘eclectic program’ issue out there. Furthermore, if we used the website that offered the service, the problem would have been taken care of: no Python needed. However, change is hard, and business processes don’t normally change course on a dime. Therefore, I took the time to write the program.
Below is the digital scorecard, data comparison file, and code that I used for the eclectic program. It’s definitely a beginner script, but that’s exactly what I was.
**FINAL_Eclectic Score 2018-2019.xlsx** – Digital scorecard spreadsheet that would be changed, based on data comparison sheet. It would also be printed and bound each week so the ladies’ could see improvements.
eclecticData.xlsx – Data comparison spreadsheet that held the scores that could be quickly entered.
Code
#! /usr/bin/env python3
"""Uses openpyxl to update a specific excel spreadsheet using a different
excel spreadsheet that contains data.
"""
import openpyxl
import datetime
from openpyxl import load_workbook
import logging
# Set up logging
logging.basicConfig(filename='eclecticProgramLog.txt',
level=logging.DEBUG,
format=' %(asctime)s - %(levelname)s - %(message)s')
logging.info('Start of the program')
def saveCopyWorkbook():
"""Save copy of older workbook."""
now = datetime.datetime.now()
fileBasename = now.strftime('%m_%d_%Y')
saveFile = 'FINAL_Eclectic Scores 2018-2019_' + fileBasename + '.xlsx'
wb.save(saveFile)
logging.info('Copy of current document saved as %s' % (saveFile))
def saveWorkbook():
"""Save over current workbook"""
workbookName = 'FINAL_Eclectic Scores 2018-2019.xlsx'
wb.save(workbookName)
logging.info('Document saved as %s' % (workbookName))
def compareScores(nameRow, rowNum, course):
"""Change eclectic score if inputted score is lower"""
global scores
scoresChanged = 0
# Determine which course was played
if course.upper() == 'S':
scoringRow = nameRow + 6
elif course.upper() == 'N':
scoringRow = nameRow + 12
for i in range(len(scores)):
if int(scores[i]) > 20:
logging.error('One of the scores in ' + str(rowNum) + ' is \
greater than 20.')
elif int(scores[i]) < int(ws.cell(row=scoringRow, column=2+i).value):
ws.cell(row=scoringRow, column=2+i).value = int(scores[i])
scoresChanged += 1
logging.info('There were ' + str(scoresChanged) + ' scores that were
improved!')
def nameCheck(name):
"""Check if name is found in workbook value"""
global ws
for rowNum in range(1, ws.max_row):
golferName = ws.cell(row=rowNum, column=6).value
if str(golferName) != name.upper():
trueAndRowNum = (False, rowNum)
elif str(golferName) == name.upper():
trueAndRowNum = (True, rowNum)
return trueAndRowNum
break
def checkCourse(course):
"""Check if course variable input is N or S"""
global rowNum
try:
if course.upper() == 'N':
return True
elif course.upper() == 'S':
return True
else:
return False
except AttributeError:
logging.error(str(course) + ' is not a valid course in row ' + str(rowNum) + '.')
# Start of script
print('Scores are being updated...')
# Open the latest version of FINAL_Eclectic Scores
wb = load_workbook('FINAL_Eclectic Scores 2018-2019.xlsx')
saveCopyWorkbook()
# Create objects for worksheets
wb = load_workbook('FINAL_Eclectic Scores 2018-2019.xlsx')
ws = wb['18-19']
wbData = load_workbook('eclecticData.xlsx')
wsData = wbData['Sheet1']
# Iterate rows in eclecticData.xlsx updating Eclectic Scores worksheet
for rowNum in range(130, wsData.max_row):
name = wsData.cell(row=rowNum, column=1).value
logging.info('Updating scores for ' + str(name))
course = wsData.cell(row=rowNum, column=2).value
scores = []
# Propagate scores list to be iterated by compareScores()
for colNum in range(3, 21):
scores += [wsData.cell(row=rowNum, column=colNum).value]
try:
returnValue = nameCheck(name)
returnedName = returnValue[0]
returnedCourse = checkCourse(course)
if returnedName is True and returnedCourse is True:
nameRow = returnValue[1]
compareScores(nameRow, rowNum, course)
else:
logging.error('Something is wrong in ' + str(rowNum) + '.')
except TypeError:
logging.error('An error occurred in row ' + str(rowNum) + '. Please check spelling, course, or scores.')
wb.close()
# Save workbook and close workbooks
saveWorkbook()
wb.close()
wbData.close()
logging.info('End of program')
print('All done!')
# End of Main ScriptThanks for reading, and feel free to reach out with comments or questions!
