Sales Rep

Assignment Content

Top of Form

For this last assignment we will combine almost everything we learned in this course: for loops, if statements, lists, dictionaries, and searching.

Create a Python application that will analyze our Sales Data spreadsheet and find three pieces of information:

1. • Which salesperson had the biggest dollar sale, and how much money was that sale?

   • Which salesperson had the most total dollar sales, and what was that total dollar amount?

   • Which product was the best seller (based on units sold, not dollar amount), and how many of those items did we sell?

Notice that you already wrote the code for question number 1 for the previous homework assignment.

For question 2, you will need to build a dictionary. The keys will be the names of the sales reps: Jones, Smith, etc. The values in the dictionary will be the dollar amounts of their total sales. Remember the code we wrote a few times to build a dictionary with a for loop and if statement:

if name not in salesRepDict :

salesRepDict[name] = saleAmount

else :

salesRepDict[name] += saleAmount

Once you have a dictionary of everyone's total sales, you can search the dictionary for the largest value and the key that goes with that value. We did that in class.

Question 3 is very similar to Question 2, but uses a different dictionary that was built from different columns from the spreadsheet.

So that you can test your code: The answer to number 2 is Kivell with total sales of $3,109.44. The best-selling product was Binders with 722 units sold.

Bottom of Form

Processing Excel Data

Problem One

Problem:

I need to know on which dates a particular sales person made sales. For example, on what dates did Jones make a sale?

Solution:

· Grab columns A,B,C. We will just ignore column B.

· use a for loop to go through each row

· each row will contain a date in [0], a region in [1], and a person in [2]

· if nextRow[0] is the person you want, then nextRow[2] is a date you want

Note 1: Dates are their own type of variable. Dates are not strings, integers, or floats. The strftime() function will turn a date into any format you want. I asked for the day of the week, month (like December, not 12 or Dec), Day and Year.

Note 2: This code also checks to be sure the Excel file can be opened by our Python code. The TRY statement allows our code to exit nicely instead of just crashing.

Code:

# Find each Date a particular Saleperson made a sale

from openpyxl import load_workbook

# open the SalesData spreadsheet

try :

workbook = load_workbook ("SalesData.xlsx")

except :

print ("*** Could not find and open the Excel file ***")

print ("*** Exiting program... ***")

exit()

sheet = workbook.active

# which salesperson do they want?

salesperson = input("Which salesperson? ")

# grab columns A, B, and C; starting with row 2

gridData = sheet.iter_rows(min_row=2,min_col=1,max_col=3)

# loop through each row and check the person's name

# if column C is the person you want

# then column A is a date you want

# convert the time data into a nice string

for nextRow in gridData :

if nextRow[2].value == salesperson :

salesDate = nextRow[0].value

salesDate = salesDate.strftime("%A %B %d %Y")

print (salesDate)

Problem Two:

I want to know how much of a particular item we are selling. For example, how many pencils did we sell?

Solution:

· Grab columns D and E.

· nextRow[0] will be the product name from column D

· nextRow[1] will be the amount sold from column E

· if nextRow[0] is the product we want, then add nextRow[1] to a total

Note:

In the code below we request only values from iter_rows(). So, in the code for Problem One we wrote this:

if nextRow[2].value == salesperson :

but now can leave off the .value and write this:

if nextRow[0] == product :

Code:

# Sum the quantities sold for a product

from openpyxl import load_workbook

# open the SalesData spreadsheet

workbook = load_workbook ("SalesData.xlsx",

read_only=True,

data_only=True)

sheet = workbook.active

# which product do they want?

product = input("Which product? ")

# grab columns D and E

gridData = sheet.iter_rows(min_row=2,

min_col=4,max_col=5,

values_only=True)

# loop through each row and check the product name

unitsSold = 0

saleCount = 0

for nextRow in gridData :

if nextRow[0] == product :

unitsSold += int(nextRow[1])

saleCount += 1

# print the results

print ("We made",saleCount,"sales totaling",unitsSold,"units of",product)

Problem Three

Problem:

I need a list of which salespeople belong to which region. Is Jones in the East, Central, or West? What about everyone else? I want something like this:

Jones --- East

Smith --- Central

Hey! That looks like a dictionary!

Solution:

Build a dictionary where the key is the person's name and the value is the region where they work. Column B is Region and column C is Rep. So, for each row, if the Rep is not in the dictionary then add them. At the end, just print the dictionary.

Code:

# build a dictionary of reps with their region

from openpyxl import load_workbook

# open the SalesData spreadsheet

workbook = load_workbook ("SalesData.xlsx",

read_only=True,

data_only=True)

sheet = workbook.active

# grab columns B and C

gridData = sheet.iter_rows(min_row=2,

min_col=2,max_col=3,

values_only=True)

# loop through each row and check the Rep

repDict = {} # create a blank dictionary

for nextRow in gridData :

repRegion = nextRow[0] # pull out the Region

repName = nextRow[1] # pull out their name

if repName not in repDict :

repDict[repName] = repRegion

# print the dictionary

print(" Rep --- Region ")

print("------------------------")

for name,region in repDict.items():

print (name.rjust(10),"---",region)