• 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:
    • SEA&FM country file
      • SEA Country Revenu (2)
      • SEA Country Expenses
      • SEA Country PTI
      • SEA Country revenue trends
      • SEA Country Expense trend
      • SEA Country PTI trend
    • Japan country file
      • AM
      • AM essbase
  • The input file name follows the "{Date} Country Financials_{Country}.xlsx format. 
  • Python script should be created for each tab
  • 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. 

You still need to add these tabs in the template file, but you should remove these sheets for the other countries. 

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.