Automate Excel Tasks with Python
Description
Excel Automation with Python
Program to automate Excel tasks with Python and Pandas
PROJECT DESCRIPTION
The aim of this programming challenge is to practice Excel automation with Python. Your task is to automate the work many consulting firms do in a spreadsheet every month/year to calculate the salaries of their consultants and the fees charged to their clients. It is a Python real world project.
You will receive two Excel files: The time tracking each consultant fills in and sends every month with the hours worked during the week/year and an annex with the rate for every professional category. You will have to write a program that takes both spreadsheets as inputs and yields the salary of each employee and some statistics about the professional positions (see picture).
INSTRUCTOR
DAVID ABRAM
David Abram is a Data Scientist at MICROSOFT. He has been the CTO of an energy efficiency startup and he has also worked as research engineer at a big multinational of energy storage.
Based in the US, David holds a Ph.D. in Chemical Engineering (Stanford University).
DOWNLOAD / CONTENT
You will receive an email with a ZIP file. The download is always available on your Practity account.
The download includes the next files:
- One PDF with the instructions and guidelines, including the project divided in several steps and online resources that may help you finish the challenge step by step.
- Two spreadsheets: the Time Tracking (12 sheets, 1 per month)Â and an annex with a country-ISO mapping and consultancy rates for each professional category.
- A “py” file and a Notebook file with the Python project solved (430 lines of code). It contains not only the source code but also detailed explanations and comments about how the code works. You may run the code from the Notebook.
IMPORTANT: to run the program, you need to have jupyter or the ANACONDA package installed on your machine. If you do not have it, you may download it here. It is free.
WHAT YOU WILL PRACTICE
– Python Libraries: you will have to import (and install) the following python libraries: pandas, datetime, os, and locale.
-Once you finish it, you will acquire the basic knowledge to replace common Excel tasks like Vlookup, sums, if+ AND/OR functions, date management, pivot tables, separator settings, currency format, etc, with Python and Pandas .
You will practice and learn the following concepts:
– Write functions and call them.
– Raise exceptions (classes)
– Error handling,
– for Loops,
– Conditionals statements inside a function,
– Lists.
– List comprehension.
– Formatted String Literals (f-strings).
REQUIREMENTS
Excel automation with Python entails working with the popular Pandas library. You must be familiar with the Pandas library. You will need it to read the Excel files and manage data.
VERSION
Python 3.8.5
Pandas: 1.0.5
Numpy: 1.19.5
CONTACT
If you need additional information, do not hesitate to contact us.
Additional information
Specification: Automate Excel Tasks with Python
|
Reviews (1)
1 review for Automate Excel Tasks with Python
Only logged in customers who have purchased this product may leave a review.
phil jones (verified owner) –
Coming from a business background I was impressed with Pandas and the things you can do with it. Actually I´d say you need a basic understanding of Pandas if you want to solve all the exercises.
What I liked best is that the code is clear and easy to follow. It ´s the first time I read code written by a senior engineer working at a big tech.