Python and Openpyxl Sales Data and Report Code
Description
Using the SalesData Excel file attached, write a program that will summarize the sales data and report the following information:
Total Sales for all Regions
Total Sales for each Sales Rep
- Total Sales for each Item
- Sales Rep with the highest sales
- Sales Rep with the lowest sales
- Item responsible for the highest sales
- Item responsible for the lowest sales
- You already have the code for items 1 and 2 (below under LAST WEEK). Number 3 is very similar to items 1 and 2. Items 4-7 require you to use one of the search methods in pdf attached.
- Your output can look any way you think works best, but make your output look nice: align the numbers, label the information, etc. The following is what my version output.
—————————
— REGIONAL TOTALS ——-
East — $ 6,002.09
Central — $11,139.07
West — $ 2,486.72
—————————
— SALES REP TOTALS ——
Top Rep = Kivell
Low Rep = Andrews
Jones — $ 2,363.04
Kivell — $ 3,109.44
Jardine — $ 2,812.19
Gill — $ 1,749.87
Sorvino — $ 1,283.61
Andrews — $ 438.37
Thompson — $ 1,203.11
Morgan — $ 1,387.77
Howard — $ 536.75
Parent — $ 3,102.30
Smith — $ 1,641.43
—————————
— ITEM TOTALS ———–
Top Item = Binder
Low Item = Desk
Pencil — $ 2,135.14
Binder — $ 9,577.65
Pen — $ 2,045.22
Desk — $ 1,700.00
Pen Set — $ 4,169.87
LAST WEEK code for items 1 and 2:
# homework
# ———————————————
# open the SalesData spreadsheet
import os
from openpyxl import load_workbook
if not os.path.exists (“SalesData.xlsx”) :
print (“Sales data file not found!”)
exit()
workbook = load_workbook (“SalesData.xlsx”,
read_only=True)
sheet = workbook.active
# ———————————————
# Build dictionaries for regions’ and reps’ sales
# key = region or rep
# value = sum of sales
regionDict = {} # blank regional sales dictionary
repDict = {} # blank dictionary of reps’ total sales
# select columns B to G
cellData = sheet.iter_rows(min_row=2,
min_col=2, max_col=7,
values_only = True)
# process each sales record
for nextRow in cellData :
region = nextRow [0] # grab column B
rep = nextRow [1] # column C
sales = nextRow [5] # column G
if region not in regionDict :
regionDict [region] = sales # new region
else :
regionDict [region] += sales # existing region
if rep not in repDict :
repDict [rep] = sales # new rep
else :
repDict [rep] += sales # existing rep
# ———————————————
# print results
# message format => right justify — 2 decimals
msg = “{:>10} — ${:9,.2f}”
print (“nTotal Sales by Region:”)
for key,value in regionDict.items () :
print ( msg.format(key,value) )
#print (“——————————–“)
print (“nTotal Sales by Sales Rep:”)
for key,value in repDict.items () :
print ( msg.format(key,value) )
Unformatted Attachment Preview
Date
Region
1/6/21 East
1/23/21 Central
2/9/21 Central
2/26/21 Central
3/15/21 West
4/1/21 East
4/18/21 Central
5/5/21 Central
5/22/21 West
6/8/21 East
6/25/21 Central
7/12/21 East
7/29/21 East
8/15/21 East
9/1/21 Central
9/18/21 East
10/5/21 Central
10/22/21 East
11/8/21 East
11/25/21 Central
12/12/21 Central
12/29/21 East
1/15/22 Central
2/1/22 Central
2/18/22 East
3/8/22 West
3/25/22 Central
4/11/22 Central
4/28/22 East
5/15/22 Central
6/1/22 Central
6/18/22 Central
7/5/22 East
7/22/22 Central
8/8/22 Central
8/25/22 West
9/11/22 Central
9/28/22 West
10/15/22 West
11/1/22 Central
11/18/22 Central
12/5/22 Central
12/22/22 Central
Rep
Item
Jones
Pencil
Kivell
Binder
Jardine
Pencil
Gill
Pen
Sorvino
Pencil
Jones
Binder
Andrews Pencil
Jardine
Pencil
Thompson Pencil
Jones
Binder
Morgan
Pencil
Howard
Binder
Parent
Binder
Jones
Pencil
Smith
Desk
Jones
Pen Set
Morgan
Binder
Jones
Pen
Parent
Pen
Kivell
Pen Set
Smith
Pencil
Parent
Pen Set
Gill
Binder
Smith
Binder
Jones
Binder
Sorvino
Binder
Jardine
Pen Set
Andrews Pencil
Howard
Pen
Gill
Pencil
Gill
Binder
Kivell
Desk
Jones
Pen Set
Morgan
Pen Set
Kivell
Pen Set
Sorvino
Desk
Gill
Pencil
Sorvino
Pen
Thompson Binder
Andrews Pencil
Jardine
Binder
Jardine
Binder
Andrews Binder
Units
Cost
95
50
36
27
56
60
75
90
32
60
90
29
81
35
2
16
28
64
15
96
67
74
46
87
4
7
50
66
96
53
80
5
62
55
42
3
7
76
57
14
11
94
28
Total
1.99
189.05
19.99
999.50
4.99
179.64
19.99
539.73
2.99
167.44
4.99
299.40
1.99
149.25
4.99
449.10
1.99
63.68
8.99
539.40
4.99
449.10
1.99
57.71
19.99 1,619.19
4.99
174.65
125.00
250.00
15.99
255.84
8.99
251.72
8.99
575.36
19.99
299.85
4.99
479.04
1.29
86.43
15.99 1,183.26
8.99
413.54
15.00 1,305.00
4.99
19.96
19.99
139.93
4.99
249.50
1.99
131.34
4.99
479.04
1.29
68.37
8.99
719.20
125.00
625.00
4.99
309.38
12.49
686.95
23.95 1,005.90
275.00
825.00
1.29
9.03
1.99
151.24
19.99 1,139.43
1.29
18.06
4.99
54.89
19.99 1,879.06
4.99
139.72
Purchase answer to see full
attachment
Have a similar assignment? "Place an order for your assignment and have exceptional work written by our team of experts, guaranteeing you A results."