260|PYTHON – Sales Automation

Challenges
, , ,
1

BYU Student Author: @Jason_Nguyen
Reviewers: @Kaden_Sandberg, @Saul_Esplin
Estimated Time to Solve: 30 Minutes

We provide the solution to this challenge using:

  • Python

Need a program? Click here.

Overview
You are a purchasing agent at Cougar Office, responsible for procuring items for both students and the Art Department. This week, the website development team is updating your procurement system, which will be unavailable for a few days. In the meantime, you must record all purchases in an Excel file and generate corresponding Excel documents for backup. These Excel files will be printed and used by your coworkers to track purchases during the system downtime. Manually entering data and converting files is time-consuming and tedious—so you decide to automate the process using Python.

Instructions
Download the Sales Order Data and Sales Order Template Excel files.

Write a Python Script to:

  • Remove Duplicates – Keep only unique sales orders.
  • Eliminate Incomplete Entries – Remove sales orders missing Vendor ID, Vendor Name, or Sales Order Number.
  • Populate the Template – Fill in sales order details, ensuring all line items are included.
  • Make sure to include the current date in each sales order
  • Save Files – Name each file as SO_{SalesOrder#}.xlsx.

Ensure each sales order would include a number of products similar to the Sales Order Data file. For example, SO7364 has three different products which should all be shown in the sales order template in distinct line items.

Data Files

Suggestions and Hints

Try to use pandas to read read the files into DataFrames

Here are some useful functions to clean the data set

  • Drop_duplicates
  • Dropna

This is the code to fill out the dynamic number of line items in each template:


while f'Item{item_num}' in row and pd.notna(row[f'Item{item_num}']): 
sheet[f'A{start_row}'].value = row[f'Item{item_num}'] 
sheet[f'F{start_row}'].value = row[f'Quantity{item_num}'] 
sheet[f'G{start_row}'].value = row[f'Price{item_num}'] 
start_row += 1 
item_num += 1

Solution

Challenge260_PYTHON_solution.docx
Solution Video: Challenge 260|PYTHON_solution_video

Here is an example of how your sales order look like SO_7364.xlsx