• 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

• Python (version 3.7.4)
• Note that if you want to use any new python package, please get approved in the forum first. ���
• Make sure your code works fine on Linux and Windows platforms

Challenge Assets

• You would need to follow the existing conventions in the codebase (Logging, Check reports, Styling guide, Mapping file)

• Masked input files are provided

• 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. 

• 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. 

• 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.

Challenge Requirements

• Code Convention
  • Please follow the code convention for the Input and PB tabs in the current codebase. 
  • Alias file should be created for the tab, and the alias name should be meaningful, and you can also define context variables in it. 
  • Mapping file should be created for the tab, please refer to the existing mapping files to check the supported mapping rule format. 
• Scope
  • The following tabs are in scope:
    • AFG
    • Mkts
    • IBCM tabs
  • The input file name follows the "{Date} Country Financials_{Country}.xlsx format. 
  • Python script should be created for each tab
  • All 6 Countries and 7 SEAs excel files are in scope, and they have the same format. 
  • For static fields (with no formula), you just copy them as they are to the output file. 
  • For all the other fields, you should calculate the value and set to the output file.  Note that the output file should NOT contain any formula. 
  • Please make the code as generic as possible, so that we can extend it to support the other reports. 

 

Testing and Validation

• 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!
• Please use this tool: https://www.nunobrum.com/excelcomparator.html to compare the input file and the generated output file. There is also a compare script in test directory, you should submit the comparision results in your submission. 

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.