Git-hub repository at: https://github.com/kakashisensei101/Timesheets-Pipeline-Bot
- Jupyter notebook: Timesheets_Consolidation _ Email Bot.ipynb
- data set: Timesheets-Pipeline-Bot/Weekly Files to be Consolidated/
- This README describes work done on the timesheets tool created by me for my team at Impendi Analytics. Resources used include Python, Tableau, Excel and Egnyte for Cloud storage (and associated python packages Jupyter, matplotlib, Seaborn, scikit-learn, statsmodels, and SciPy). These packages all come as part of the Anaconda distribution of Python.
- The analysis takes the form of a single Jupyter notebook of filename given above. To view this file, download it from this repository and start Jupyter notebook in the folder containing the file. Use the command Jupyter notebook on the command line.
- Alternatively, view a static version of the notebook (by providing its GitHub url) using Jupyter Nbviewer.
- The weekly timesheet files are included in the repository. The data set is a dummy data set. I replaced actual data with dummy data as it conflicted the privacy policies at my firm.
- All images intended for inclusion in this README are located in the repository.
- I have tried to structure the Jupyter notebook and this README so that they have corresponding sections. However, I do not wish to merely repeat here what has been stated in the notebook. I will endeavour to have this README summarize the work of the notebook and, hopefully, complement the analyses done there.
-Step one: Team members fill in the weekly timesheets through an excel workbook on cloud storage for all hours, including commute, team meals, meetings, weekend hours (if any), paid time offs, vacations, holidays etc. As soon as they would fill the timesheets, the timesheets would get sent to my folders through macros on the workbook.
-Step two: When all workbooks reach my timesheets folder, a python code is executed which consolidates the 75 workbooks, removes duplicate rows, removes blank rows, fixes erroneous entries and validates the total hours.
-Step three: The code sends the cleaned and consolidated file to the project managers through email using smtbl library. -step four: Tableau Dashboards are automatically refreshed for all since the dashboards are linked to a csv file on the drive with a live connection.
Team members are weekly notified to fill in the timesheets by EOD (Friday). The notfication is sent via the email ID managed through the python script.
Team members fill in the weekly timesheets through an excel workbook on cloud storage for all hours, including commute, team meals, meetings, weekend hours (if any), paid time offs, vacations, holidays etc. As soon as they would fill the timesheets, the timesheets would get sent to my folders through macros on the workbook.
Step two: When all workbooks reach my timesheets folder, a python code is executed which consolidates the 75 workbooks, removes duplicate rows, removes blank rows, fixes erroneous entries and validates the total hours.
Step three: The code sends the cleaned and consolidated file to the project managers through email using smtbl library.
step four: Tableau Dashboards are automatically refreshed for all since the dashboards are linked to a csv file on the drive with a live connection.
The key impact of this analysis is below:
- Increased visibility into team work life balance
- Increased visibility into project split of the work hours
- Increased visibility into firm development activities
- Ability to identify overworked individuals
- Ability to avoid concentration of work