Excel Automation with Python

Problem Statement: Automation of Data Collation and Transfer with Email Notification Project Overview The objective is to automate the collation of data from two separate Excel sheets, "ETD" and "FYTD," into a single consolidated sheet. This automation includes transferring specific columns from the "ETD" sheet to corresponding columns in the "FYTD" sheet, managing exceptions for missing values, and notifying managers upon completion via email. Scope of the Project 1. **Data Collation:** - Automate the merging of data from "ETD" and "FYTD" sheets into a single Excel workbook. 2. **Data Transfer:** - Transfer specified columns ("TER," "Billing," and "NUI") from "ETD" to new columns in "FYTD." - Append "ETD" to the names of transferred columns for differentiation. 3. **Exception Handling:** - Implement mechanisms to identify and handle missing values during data transfer. - Display message boxes indicating the presence of missing values and their locations. 4. **Email Notification:** - Develop a feature to send email notifications to managers upon successful collation completion. - Notify users of unsuccessful completions before sending emails. 5. **Naming Convention:** - Name the final file as "Book of Business_[Date of extraction from the portal]." Functional Requirements 1. **Data Collation:** - Merge data from "ETD" and "FYTD" sheets into a single sheet within the same workbook. 2. **Data Transfer:** - Transfer data from "TER," "Billing," and "NUI" columns in "ETD" to new columns in "FYTD." 3. **Exception Handling:** - Identify and handle missing values during data transfer. - Display message boxes with missing value details. 4. **Email Notification:** - Automatically send an email to managers upon successful collation completion. - Ensure email contains relevant details about the collated data. 5. **Naming Convention:** - Name the final file according to the specified format. Acceptance Criteria 1. **Data Collation:** - Data from both "ETD" and "FYTD" sheets should be merged into two different tabs in one worksheet without data loss. 2. **Data Transfer:** - Accurate transfer of specified columns from "ETD" to "FYTD" with "ETD" appended to column names. 3. **Exception Handling:** - Effective identification and handling of missing values. - Notification of users about unsuccessful completions before sending emails. 4. **Email Notification:** - Managers should receive email notifications upon successful completion. 5. **Naming Convention:** - The final file should be named "Book of Business_[Extraction_Date]."