Excel parser

from pathlib import Path
from sxl import Workbook
from datetime import datetime
from datetime import timedelta

# Get today's date, end date, and initilize dictionaries
today = datetime.today().replace(hour=0, minute=0, second=0, microsecond=0)
end_date = today + timedelta(days=3)
sap_index = {}
data_dict = {}

# Get data from Excel file
file_path = Path(__file__).resolve().parent / 'harmony_export.xlsm'
workbook = Workbook(file_path)
sheet_names = ['Oil', 'Water', 'Gas']

# Set up dictionaries to store values
# Get SAP IDs from header data on first sheet
sheet = workbook.sheets[sheet_names[0]]
head = sheet.head(10)
first_data_row = 5
for i, row in enumerate(head):
    if row[0] == 'SAP ID':
        for j, sap_id in enumerate(row):
            sap_index[j] = sap_id
    elif row[0] == 'Date':
        first_data_row = i + 1

# Set up SAP ID keys
for sap_id in list(sap_index.values())[1:]:
    data_dict[sap_id] = {}

# Set up Date keys
for i, row in enumerate(sheet.rows):
    if i < first_data_row:
        continue
    elif row[0] < today:
        continue
    elif row[0] < end_date:
        row_date = str(row[0])
        for j, date in enumerate(sheet.rows):
            if j == 0:
                continue
            if j > len(data_dict):
                break
            data_dict[sap_index[j]][row_date] = {}

# Loop through all sheets and populate dictionary
for product in sheet_names:
    sheet = workbook.sheets[product]
    for i, row in enumerate(sheet.rows):
        if i < first_data_row:
            continue
        elif row[0] < today:
            continue
        elif row[0] < end_date:
            row_date = str(row[0])
            for j, data in enumerate(row):
                if j == 0:
                    continue
                print(data)
                data_dict[sap_index[j]][row_date][product] = data

print(data_dict)

Tip: if you change the name of the file to be a .py extension, it'll render the code with Python syntax highlighting!