Python script - pop-up reminder based on data from Excel

Completion date: December 17, 2018

Changes to the script (Dec 14 2018):

1. Used Date format to compare dates. 

2. Show message for update AR if the date is overdue and due today.

Changes to the script (Dec 17 2018):

1. Corrected the ws.max_row+1 to ws.max_row.

2. Changed to loop from bottom up (so the the newest records gets checked first)

=========== The script ===========

#! python3 -- Modified on Dec 17 2018

import openpyxl

import tkinter.messagebox

from datetime import date

import os

#Get the workbook, worksheet

file = "AR.xlsx"

wb = openpyxl.load_workbook(file)

ws = wb['Sheet1']

#Iterate through the rows of column "Next update on" = Column D = 4, from bottom up (the newest record to oldest)

for i in range(ws.max_row, 2, -1):

    next_update_datetime = ws.cell(row=i, column=4).value

    if (next_update_datetime is not None):

        #convert to date

        next_update_date = next_update_datetime.date()

    #Check if any AR is due or overdue for update

    if next_update_date <= date.today():

        result = tkinter.messagebox.askquestion("AR Progress Update Reminder", "You need to update AR today.  Would you like to open the workbook?", icon='question')

        if result == 'yes':

            os.startfile(file)

            break

        else:

            break

=========== End of script ===========

Completion date: December 7, 2018

It has been a while since my last invention stories.  Last week, I have created something using Python programming language.

My application is a simple script that checks if anything are due for update on the day.  I have configured it to run automatically on each Windows startup (I turn off my notebook every day after work).  It is to help my work on chasing the accounts receivables, as I realize I might forget to update since it is not a daily chore.  So, the program would check if I have anything due on a the day under “Next update on” column in the excel file, without me opening the excel file.  If there is something due for update on the day, I would get a dialog box (picture above) to open the excel file, if not, the dialog box will not pop up.

Here is the script below, it’s very simple but it works.

Remark: I created an .exe file from .py using pyinstaller, and then I put a shortcut of the .exe file in the startup folder (Win+R —> shell:startup).

Alternatively, you can create a scheduled task in  Windows (in system tools) for auto-running the script at a specific hour.

===================================
#! python3 -- Created on Dec 7 2018
import openpyxl
import tkinter.messagebox
from datetime import date
import os

#Get the workbook, worksheet
file = "AR.xlsx"
wb = openpyxl.load_workbook(file)
ws = wb['Sheet1']

#Iterate through the rows of column "Next update on" = Column D = 4
for i in range(2, ws.max_row+1):
   next_update_datetime = str(ws.cell(row=i, column=4).value)
   next_update_date = next_update_datetime[0:10]
   if next_update_date == str(date.today()):
       result = tkinter.messagebox.askquestion("AR Progress Update Reminder", "You need to update AR today.  Would you like to open the workbook?", icon='question')
       if result == 'yes':
           os.startfile(file)
           break
       else:
           break
===================================