ALL BUSINESS DATA ANALYSIS ENGLISH ARTICLES MICROSOFT EXCEL TIPS

Data Analysis of Bugdet and Actual Sales with Python

Excel file 1 : sales_january_2021_d.xlsx – Download : https://lnkd.in/dyT6zrCX
Excel file 2 : sales_feb_2021_d.xlsx – Download : https://lnkd.in/d5aaNWiq
Excel file 3 : sales_march_2021_d.xlsx – Download : https://lnkd.in/d-G2hNiM
Task
Collection of data from the above mentioned three different excel files and creation of a
new one : sales_jan_feb_mar_2021_d.xlsx – Download : https://lnkd.in/d-PTEPtv
with three different sheets :
sales_data
month_productivity
salesperson_productivity
We did a comparison between Budget and Actual Sales.
The reason we did this is to know the month productivity and the salesperson productivity for months Jan, Feb and Mar 2021.
All sheets, data, graphs and styling in file : sales_jan_feb_mar_2021_d.xlsx have been created automatically with python coding.
Tools used : Excel, Python
Code for this project
from ctypes.wintypes import WORD
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
importopenpyxl
from openpyxl.styles import PatternFill, Border, Side, colors, Alignment, Protection, Font, Color
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.worksheet.dimensions import ColumnDimension, DimensionHolder
from openpyxl.utils import get_column_letter
from openpyxl.chart import Reference, LineChart, BarChart3D
font2 = Font(name=’Calibri’, size=17, bold=True, color=’07101c’)
font3 = Font(name=’Calibri’, size=13, bold=False, color=’07101c’)
align2 = Alignment(horizontal=”center”, wrap_text= True, vertical=”center”)
alignment=Alignment(horizontal=’general’,
                    vertical=’bottom’,
                    text_rotation=0,
                    wrap_text=False,
                    shrink_to_fit=False,
                    indent=0)
font = Font(name=’Calibri’,
                 size=11,
                 bold=False,
                 italic=False,
                 vertAlign=None,
                 underline=’none’,
                 strike=False,
                 color=’FF000000′)
center_align = Alignment(horizontal=’center’, vertical=’center’)
Border3=Border(left=Side(border_style=’double’, color=’FF000000′),
                                                right=Side(border_style=’double’, color=’FF000000′),
                                                top=Side(border_style=’double’, color=’FF000000′),
                                                bottom=Side(border_style=’double’, color=’FF000000′))
import warnings
warnings.filterwarnings(‘ignore’, category=UserWarning, module=’openpyxl’)
excel_file_1 = ‘sales_january_2021_d.xlsx’
excel_file_2 = ‘sales_feb_2021_d.xlsx’
excel_file_3 = ‘sales_march_2021_d.xlsx’
df_first_month = pd.read_excel(excel_file_1, sheet_name=’Sheet1′)
df_second_month = pd.read_excel(excel_file_2, sheet_name=’Sheet1′)
df_third_month = pd.read_excel(excel_file_3, sheet_name=’Sheet1′)
print(‘—-Sales Data for Jan—-‘)
print(df_first_month)
df_all = pd.concat([df_first_month, df_second_month, df_third_month])
print(‘—-Sales Data for months Jan, Feb, March—-‘)
print(df_all)
pivot = df_all.groupby([‘Month’]).mean()
month_productivity = pivot.loc[:,’Budget_Sales’:’Actual_Sales’]
print(‘—-Month Productivity—-‘)
print(month_productivity)
month_productivity.plot(kind=’bar’)
plt.show()
pivot1 = df_all.groupby([‘Salesperson’]).mean()
salesperson_productivity = pivot1.loc[:,’Budget_Sales’:’Actual_Sales’]
print(‘—-Salesperson Productivity for months Jan, Feb, March 2021—-‘)
print(salesperson_productivity)
salesperson_productivity.plot(kind=’bar’)
plt.show()
with pd.ExcelWriter(‘sales_jan_feb_mar_2021_d.xlsx’, engine=’openpyxl’) as writer1:
    df_all.to_excel(writer1, sheet_name = ‘sales_data’, index = False)
    pivot.to_excel(writer1, sheet_name = ‘month_productivity’)
    pivot1.to_excel(writer1, sheet_name = ‘salesperson_productivity’)
month_productivity.to_excel(‘Month.xlsx’)
salesperson_productivity.to_excel(‘Salesperson.xlsx’)
#Styling the excel
wb = openpyxl.load_workbook(‘sales_jan_feb_mar_2021_d.xlsx’)
# Styling sheet sales_data
ws = wb[‘sales_data’]
dim_holder = DimensionHolder(worksheet=ws)
for col in range(ws.min_column, ws.max_column + 1):
    dim_holder[get_column_letter(col)] = ColumnDimension(ws, min=col, max=col, width=27)
ws.column_dimensions = dim_holder
for cell in ws[‘A’]:
        cell.alignment = Alignment(horizontal=”center”)
        cell.font = font3
        cell.border = Border3
for cell in ws[‘B’]:
        cell.alignment = Alignment(horizontal=”center”)
        cell.font = font3
        cell.border = Border3
        cell.number_format = ‘#,##0.00€’
for cell in ws[‘C’]:
        cell.alignment = Alignment(horizontal=”center”)
        cell.font = font3
        cell.border = Border3
        cell.number_format = ‘#,##0.00€’
for cell in ws[‘D’]:
        cell.alignment = Alignment(horizontal=”center”)
        cell.font = font3
        cell.border = Border3
for cell in ws[‘E’]:
        cell.alignment = Alignment(horizontal=”center”)
        cell.font = font3
        cell.border = Border3
for cell in ws[‘F’]:
        #cell.alignment = Alignment(horizontal=”center”)
        cell.alignment = align2
        cell.font = font3
        cell.border = Border3
ws[‘A1’].font = font2
ws[‘B1’].font = font2
ws[‘C1’].font = font2
ws[‘D1’].font = font2
ws[‘D1’].alignment = align2
ws[‘E1’].font = font2
ws[‘F1’].font = font2
ws[‘G16’] = ‘Credit :’
ws[‘G16’].font = font2
ws[‘G17’] = ‘Trifonas Papadopoulos’
ws[‘G17’].font = font2
ws[‘G18’] = ‘www.mindstorm.gr’
ws[‘G18’].font = font2
# Styling sheet month productivity
ws = wb[‘month_productivity’]
dim_holder = DimensionHolder(worksheet=ws)
for col in range(ws.min_column, ws.max_column + 1):
    dim_holder[get_column_letter(col)] = ColumnDimension(ws, min=col, max=col, width=27)
ws.column_dimensions = dim_holder
for cell in ws[‘A’]:
        cell.alignment = Alignment(horizontal=”center”)
        cell.font = font3
        cell.border = Border3
for cell in ws[‘B’]:
        cell.alignment = Alignment(horizontal=”center”)
        cell.font = font3
        cell.border = Border3
        cell.number_format = ‘#,##0.00€’
for cell in ws[‘C’]:
        cell.alignment = Alignment(horizontal=”center”)
        cell.font = font3
        cell.border = Border3
        cell.number_format = ‘#,##0.00€’
ws[‘A1’].font = font2
ws[‘B1’].font = font2
ws[‘C1’].font = font2
ws[‘G16’] = ‘Credit :’
ws[‘G16’].font = font2
ws[‘G17’] = ‘Trifonas Papadopoulos’
ws[‘G17’].font = font2
ws[‘G18’] = ‘www.mindstorm.gr’
ws[‘G18’].font = font2
values = Reference(ws, min_col= 2, min_row=1, max_col = 3, max_row = 4)
x_values = Reference(ws, range_string=’month_productivity!A2:A4′)
#initialize BarChart3D object
chart = BarChart3D()
#add data to the LineChart object
chart.add_data(values, titles_from_data = True)
#set x-axis
chart.set_categories(x_values)
##cosmetics
chart.title = ‘Month Productivity’
chart.x_axis.title = ‘Month’
chart.x_axis.scaling.min = 1
chart.x_axis.scaling.max = 3
chart.y_axis.title = ‘Sales’
chart.legend.position = ‘b’
ws.add_chart(chart, ‘E2’)
# Styling sheet salesperson_productivity
ws = wb[‘salesperson_productivity’]
dim_holder = DimensionHolder(worksheet=ws)
for col in range(ws.min_column, ws.max_column + 1):
    dim_holder[get_column_letter(col)] = ColumnDimension(ws, min=col, max=col, width=27)
ws.column_dimensions = dim_holder
for cell in ws[‘A’]:
        cell.alignment = Alignment(horizontal=”center”)
        cell.font = font3
        cell.border = Border3
for cell in ws[‘B’]:
        cell.alignment = Alignment(horizontal=”center”)
        cell.font = font3
        cell.border = Border3
        cell.number_format = ‘#,##0.00€’
for cell in ws[‘C’]:
        cell.alignment = Alignment(horizontal=”center”)
        cell.font = font3
        cell.border = Border3
        cell.number_format = ‘#,##0.00€’
ws[‘A1’].font = font2
ws[‘B1’].font = font2
ws[‘C1’].font = font2
ws[‘G16’] = ‘Credit :’
ws[‘G16’].font = font2
ws[‘G17’] = ‘Trifonas Papadopoulos’
ws[‘G17’].font = font2
ws[‘G18’] = ‘www.mindstorm.gr’
ws[‘G18’].font = font2
values = Reference(ws, min_col= 2, min_row=1, max_col = 3, max_row = 4)
x_values = Reference(ws, range_string=’salesperson_productivity!A2:A4′)
#initialize BarChart3D object
chart = BarChart3D()
#add data to the LineChart object
chart.add_data(values, titles_from_data = True)
#set x-axis
chart.set_categories(x_values)
##cosmetics
chart.title = ‘Salesperson Productivity’
chart.x_axis.title = ‘Salesperson’
chart.y_axis.title = ‘Sales’
chart.legend.position = ‘b’
ws.add_chart(chart, ‘E2’)
wb.save(‘sales_jan_feb_mar_2021_d.xlsx’)
Views: 24

Comments are closed.

Pin It