Challenge Overview

Create python scripts to generate automated excel reports from given input files

Project background

Our client is looking forward to automate the report generation tasks which is currently performed by a team of experts in their company.
The reports are very crucial to our client and few of the reports are viewed everyday by C-Level executives
Many automated report development challenges are planned in this series, jump in right now to get familiar with automated report generation techniques

Technology Stack

Challenge Assets

Starter pack for this challenge is provided in the challenge forum which will be accessible after registration. Starter pack has code for one of the reports (NNA report) under development
You would need to follow the existing conventions in the starter pack during further development (Logging, Check reports, Styling guide, Mapping file)
Masked input files and sample output are provided as part of the starter pack
Business requirement document for excel report in scope is shared in the forum.

Individual Requirements

General requirements

Python script should perform necessary validations before generating the report. If any validation fails, report generation should stop and the error should be reported to the user. Validation check involves
- File existence check - Check whether all the files required for generating the report is available. If any of the file is missing, raise error
- Missing values check - Check if any of the fields required for generating the report is missing. If any such field is missing, raise error
- Valid values check - Check if any of the fields required for generating the report is having values like `#REF`, `NA` or having formulae without `=`. If there is any such field, raise error

Result of each such check should be logged properly to the log file. Please follow the technique used in starter pack code.
User should be kept updated about the progress by printing appropriate informational messages in the console
Formatting shown in the sample output reports need to be maintained in the reports generated by Python script as well
Python script should be agnostic enough to handle report generation for any date. There should not be any hardcoding of the dates or cell references in the code. Mapping of input to output fields should be kept in the mapping file under separate tab for each report (config/input_to_output_mapping.xlsx).
Please keep in mind that the end users of this application are not tech savvy, hence the application should be designed and documented in such a way that even non technical users could run the application easily and generate their reports.

Input for all reports

The input file `Inputs_Adjustments.xlsx` is the driving factor for each report generation
The date for which reports need to be generated should be read from the above file
There is no change expected in the structure of this input file. We can change the values in this file to generate different report. E.g. for different date or for report with different initial values

Capital Report

Business requirements for Capital report is provided in the `Capital` tab of the BRD excel sheet
Possible FAQs for Capital report is also shared in the forum
Create a separate python file and separate tab in mapping file config/input_to_output_mapping.xlsx to drive the report generation process
Create a template file in the template directory
Ensure that input files for the Capital report generation doesn’t have any formula in it. You could copy the values in the sheet and use paste by value.

Testing and Validation

You could use the sample output files provided for validating the reports generated from Python Scripts.
During the review, we will be using a different set of files to validate the accuracy of your report generation. Accuracy of report matters a lot!

Important Notes:

Please pay attention to the shortened timelines of this challenge. Submission, Review and Appeals phase are shorter than general challenge.
Code should follow google python style guide. You could use pylint to validate the conformance of your python files with style guide.
Ensure that code has sufficient amount of comments to understand its purpose as well as there is no unnecessary comments which just clutter the file.
Clean up the unnecessary code before submitting
If you have any doubts, Please feel free to post it in the forum. We will be happy to resolve your queries as soon as possible.

Zip your updated codebase and submit the zip file using the submit link in the challenge
After submitting, please ensure that you are able to download your submission from Online Review