David Vo David Vo - 11 days ago 4
Python Question

Extracting workbook name from Workbook Name List

I'm a noob in python. Currently, I have a list of workbook names that I am passing through the load_workbook function. However, I have a bunch of if statements that depends on the workbook. So I need to parse their names or find another way to do checks with the workbooks. Heres my code:

for x in range(0, len(allbooks)):

wb = openpyxl.load_workbook(allbooks[x], keep_vba = True)
print (wb)

if wb == "Subportfolio 1.xlsm":
ws = wb.worksheet("Positions")
if datetime.datetime.today().weekday() == 6: #check if its sunday
if ws.cells('D1') != "Price on %s" % last_friday: #check to see if date is last friday
print ("Need to Update Subportfolio")
elif ws.cells('D1') != "Price on %s" % d: #check to see if date is today
print ("Need to Update Subportfolio")

elif wb == "Mock Portfolio - He Yibo 2 (TMT).xlsm":
ws = wb.worksheet("Positions")
if datetime.datetime.today().weekday() == 6:
if ws.cells('E1') != "Price on %s" % last_friday:
print ("Need to Update Mock Portfolio - He Yibo 2 (TMT)")
elif ws.cells('E1') != "Price on %s" % d:
print ("Need to Update Mock Portfolio - He Yibo 2 (TMT)")

elif wb == "Mock Portfolio - He Yibo 2 (Utilities).xlsm":
ws = wb.worksheet("Positions")
if datetime.datetime.today().weekday() == 6:
if ws.cells('E1') != "Price on %s" % last_friday:
print ("Need to Update Mock Portfolio - He Yibo 2 (Utilities)")
elif ws.cells('E1') != "Price on %s" % d:
print ("Need to Update Mock Portfolio - He Yibo 2 (Utilities)")

Answer

This first part is not very pythonic indeed. In Python, you don't need indices to loop over a list. A for in Python acts as a foreach in most other languages, so this

for x in range(0, len(allbooks)):

    wb = openpyxl.load_workbook(allbooks[x], keep_vba = True)

can be made shorter into

for book in allbooks:
    wb = openpyxl.load_workbook(book, keep_vba = True)

Another way to improve this would be to replace all the elif statements with a dict or namedtuples. If it's only the cell that changes you can easily do this with a dict

books = {'Subportfolio 1.xlsm': 'D1', 'Mock Portfolio - He Yibo 2 (TMT).xlsm', 'E1'} #etcetera
for book, important_cell in books.items():
    wb = openpyxl.load_workbook(book, keep_vba = True)
    ws = wb.worksheet("Positions")
    message = 'Need to Update %s' % book
    if datetime.datetime.today().weekday() == 6: #check if its sunday
        if ws.cells(important_cell) != "Price on %s" % last_friday: #check to see if date is last friday
            print (message)
    elif ws.cells(important_cell) != "Price on %s" % d: #check to see if date is today
        print (message)

More parameters per workbook

When you have more parameters per workbook, as for example the worksheetname, you can do that in a few ways

namedtuple

If it is a fixed number of parameters which will not change, a namedtuple is a very convenient structure:

myworkbook = namedtuple('myworkbook', ['filename', 'sheetname', 'cell'])
allbooks = [myworkbook('filename0', 'sheetname0', 'cell0'),
            myworkbook('filename1', 'sheetname1', 'cell1'),...]
for book in allbooks:
    wb = openpyxl.load_workbook(book.filename, keep_vba = True)
    ws = wb.worksheet(book.sheetname)
    message = 'Need to Update %s' % book.filename
    if datetime.datetime.today().weekday() == 6: #check if its sunday
        if ws.cells(book.cell) != "Price on %s" % last_friday: #check to see if date is last friday
            print (message)
    elif ws.cells(book.cell) != "Price on %s" % d: #check to see if date is today
        print (message)

dict of dict

this works approximately the same, only this is more generic. It uses the dict.get method which takes a default argument when the key is missing in the dict

default_cell = 'D1'
default_sheet = 'Positions'

books = {'Subportfolio 1.xlsm': {'sheet' = 'other_sheet'}, 'Mock Portfolio - He Yibo 2 (TMT).xlsm': {'cell': 'E1'}} #etcetera
for book, book_info in books.items():
    wb = openpyxl.load_workbook(book, keep_vba = True)
    ws = wb.worksheet(book_info.get('sheet', default_sheet))
    message = 'Need to Update %s' % book
    important_cell = book_info.get('cell', default_cell)
    if datetime.datetime.today().weekday() == 6: #check if its sunday
        if ws.cells(important_cell) != "Price on %s" % last_friday: #check to see if date is last friday
            print (message)
    elif ws.cells(important_cell) != "Price on %s" % d: #check to see if date is today
        print (message)

Class

you could make a MyWorkbookClass to keep the information of each workbook, but this might be overkill. A namedtupleacts as a kind of mini-class with fixed members