I have a master .xlsx file on which other spreadsheets depend. Each week, I export a new .xlsx file (from MRP) that may or may not be the same as the master. The columns are always identical. Rows may increase or decrease. Cell values may change, usually just one (price value, column AB).

I wrote a Python program that reviews the differences, and updates the master with the new values. I wanted this program to update the master workbook to match the new values. Any new rows in the new workbook would be appended to the master as well.


  1. This is slow. Is there a more efficient way to do this? Luckily there are only about 2 - 4k rows. As in VBA code instead? Or another language altogether?

  2. Any Pythonification updates I could make? I am a beginner so I'd like to learn how to optimize code as I go.

  3. Any other advice or help would also be appreciated.

    import sys
    import openpyxl
    import openpyxl.utils
    from datetime import datetime as dt
    import os
    #get teh mater path
    hm_dir = "C:\\path\\MASTER SHEETS"
    suffix = ".xlsx"
    #loop to get the choice of master workbook to update
    while True:
        wb_choice = input("Enter the master sheet you want to update:\n" \
                            "a for Main Warehouse\n"\
                            "c for Composite Item List\n"\
                            "i for Item Master\n")
        if wb_choice.lower() == "a":
            old_workbook_path = os.path.join(hm_dir, "Main_WHSE.xlsx")
        if wb_choice.lower() == "c":
            old_workbook_path = os.path.join(hm_dir, "COMPOSITE.xlsx")
        if wb_choice.lower() == "i":
            old_workbook_path = os.path.join(hm_dir, "ITEMS.xlsx")
            print("Enter A C or I")

    # Get the paths to the new workbook 
    new_workbook_fn= input("Enter the filename of the new workbook" \
                            " It must be xlsx format and " \
                            "it must be in the same folder as the master" \
                            " sheets, which is: \n" \
                            + str(hm_dir) \
                            + "\n\nFilename: ")
    new_workbook_path = os.path.join(hm_dir, new_workbook_fn + suffix)

    # Load the older and newer workbooks
    old_workbook = openpyxl.load_workbook(old_workbook_path)
    new_workbook = openpyxl.load_workbook(new_workbook_path)
    # Select the active sheet in each workbook
    old_sheet = old_workbook["Item"]
    new_sheet = new_workbook["Item"]
    sheet2 = old_workbook["Sheet 2"]
    #copies the current max rows of the mater worksheet before updating
    current_row = sheet2.max_row + 1
    sheet2.cell(row=current_row, column=4).value = old_sheet.max_row

    # Create a dictionary to map unique IDs to row numbers in the new workbook
    new_id_map = {}
    for i in range(2, new_sheet.max_row + 1):
        new_id_map[new_sheet.cell(row=i, column=1).value] = i
    #Create a dictionary to map unique IDs to row numbers in the old workbook   
    old_id_map = {}
    for i in range(2, old_sheet.max_row + 1):
        old_id_map[old_sheet.cell(row=i, column=1).value] = i

    # Loop through the rows in the older workbook
    rows_updated = 0
    for j in range(2, old_sheet.max_row + 1):
        old_id = old_sheet.cell(row=j, column=1).value
        if old_id in new_id_map:
            # If the ID is found in the new workbook, update the data in the old workbook
            new_row_num = new_id_map[old_id]
            updated = False  # flag to keep track of whether row was updated or not
            for k in range(1, new_sheet.max_column + 1):
                old_value = old_sheet.cell(row=j, column=k).value
                new_value = new_sheet.cell(row=new_row_num, column=k).value
                if old_value != new_value:
                    # update the value only if it is different from the new value
                    old_sheet.cell(row=j, column=k).value = new_value
                    updated = True  # mark the row as updated
            if updated:
                rows_updated += 1

    # Loop through the rows in the new workbook and add any missing rows to the old workbook
    for j in range(2, new_sheet.max_row + 1):
        new_id = new_sheet.cell(row=j, column=1).value
        if new_id not in old_id_map:
            # If the ID is not found in the old workbook, add a new row to the old workbook
            old_sheet.append([new_sheet.cell(row=j, column=k).value for k in range(1, 
            new_sheet.max_column + 1)])
            old_id_map[new_id] = old_sheet.max_row
            rows_updated += 1

    # Save the changes to the older workbook

    # Add a row to Sheet 2 of the old workbook with the date
    sheet2.cell(row=current_row, column=1).value = 
    dt.now().strftime("%m/%d/%Y %H:%M:%S")
    sheet2.cell(row=current_row, column=3).value = new_sheet.max_row

    # Save the changes to the old workbook

    # Display the number of rows updated
    print(f"Number of rows updated: {rows_updated}")
  • 1
    \$\begingroup\$ In terms of approach, python and VBA will have similar performance although VBA may be a bit easier to integrate into Excel. However I would use PowerQuery - built into Excel also, which is made for pretty much this exact application of importing data. It is built one layer above SQL and has a graphical editor. You would use some kind of Join to update the master sheet as you describe and the updates could happen automatically as new data is output from MRP. \$\endgroup\$
    – Greedo
    Feb 23 at 20:50

1 Answer 1


Too big for a comment, this is an alternative approach:

Suppose you have some different files like this:

enter image description here

The image shows 3 tables of data, each representing a different file, each with a File Created column, each with the same column names, each with an ID column and some fields of data.

I've also highlighted where a new piece of data differs from the same ID in a previous table.

To merge these, we can use the following algorithm:

  • Load all tables and append them
  • Group duplicate data; rows where the ID and fields are identical, but set a new "Last Modification Date" to be the oldest date in that group - i.e. the data has remained unchanged since that date
    • e.g. M002 is no different between 1-Jan (blue table) and 15-Jan (Orange Table). So group into a single row and set the last modified date to 1-Jan
  • Now for each ID, keep only the most recent "last modification" as this is the most up-to-date data
    • This can be achieved by sorting the "Last Modification Date" newest -> Oldest and dropping any rows with duplicate IDs
  • Finally sort alphabetically by ID or by modification date, whatever you find most logical.

Following that algorithm you get a table like this:

enter image description here

See how M004 has remained unchanged the whole time and so its last update was 1-Jan, M005 was updated 2-Feb etc.

Hopefully this is what you are after. The whole thing can be achieved using Excel's builtin PowerQuery

enter image description here Add a blank query to your Master Workbook and then go to View -> Advanced Editor and paste the following code:

    FieldNames = {"MRP ID", "Field 1", "Field 2", "Field 3"},
    IDField = List.First(FieldNames),
    SourceFolder = "C:\path\MASTER SHEETS\MRP Data",
    Source = Folder.Contents(SourceFolder),
    #"Filter MRP Files" = Table.SelectRows(Source, each ([Extension] = ".xlsx") and ([Name] = "MRP1.xlsx" or [Name] = "MRP2.xlsx" or [Name] = "MRP3.xlsx") and ([Attributes]?[Hidden]? <> true)),
    #"Read Tables from Files" = Table.AddColumn(#"Filter MRP Files", "First Table", each Excel.Workbook([Content]){[Kind="Table"]}[Data]),
    #"Discard Other Columns" = Table.SelectColumns(#"Read Tables from Files", {"Date created", "First Table"}),
    #"Merge Tables" = Table.ExpandTableColumn(#"Discard Other Columns", "First Table", FieldNames),
    #"Squish Unchanged Data" = Table.Group(#"Merge Tables", FieldNames, {{"Last Modified", each List.Min([Date created]), type nullable datetime}}),
    #"Force Most Recent files to top" = Table.Buffer(Table.Sort(#"Squish Unchanged Data",{{"Last Modified", Order.Descending}})),
    #"Drop out-dated Data" = Table.Distinct(#"Force Most Recent files to top", {IDField}),
    #"Sort Alphabetically" = Table.Sort(#"Drop out-dated Data",{{IDField, Order.Ascending}})
    #"Sort Alphabetically"

You should end up with something like this:

enter image description here

Now in the Home tab of the powerquery editor click Close and Load to a table in your workbook. Refresh using the refresh all command in Excel.

The reason to do it this way is:

  • Very fast compared to what's easily achievable in python without a lot more thought, as PowerQuery is optimised for working with Tabular Data
  • Simple Expressive code is easier to maintain, again PQ is the tool for the job and makes it easier to write simple code in this instance.
  • Built into Excel so no added dependencies easier to maintain and distribute

It is possible to pass params like filepaths, column names etc from VBA to PowerQuery if you want interactivity. By default the data will refresh whenever you open or close the workbook.

  • Note, modify the FieldNames parameter in the query to match the columns in your workbook, make sure there is an ID column (right now it just uses the first column as ID)
  • This uses file creation date to find the most recent data.
  • If a row of data is changed then changed back, the "Last Change" column will find the first occasion where the product had those values. You can get around this by deleting old data, or adjusting the algorithm
  • Table.Buffer is needed to prevent PQ lazily evaluating the sort, since this would result in random records being dropped, not necessarily the oldest ones
  • \$\begingroup\$ Alternative implementations can be part of a review, if they tell why the alternative is better than the original. All answers should contain an insightful observation, or they wouldn't be reviews. \$\endgroup\$
    – Mast
    Feb 24 at 16:02
  • \$\begingroup\$ @Mast See comments at end of post \$\endgroup\$
    – Greedo
    Feb 24 at 16:04
  • \$\begingroup\$ Oh I see you were objecting to the first sentence. It made more sense when I had a comment on the post before it was closed the first time \$\endgroup\$
    – Greedo
    Feb 24 at 16:05
  • 1
    \$\begingroup\$ Is your approach faster? Does it consume less memory? Do you consider it more maintainable? There must be a reason why you suggest this alternative over the original approach. You list benefits of your approach, but make no mention of whether any of those benefits were missing in the original. \$\endgroup\$
    – Mast
    Feb 24 at 17:32
  • 1
    \$\begingroup\$ @Mast Agreed, updated \$\endgroup\$
    – Greedo
    Feb 24 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.