Price Elasticity - Data Generation and Backend API

 

Challenge Objectives

 

• Generate data set using Excel calculations - persist the results to MongoDB

• Implement API to search the db records

 

Project Background

• Our client has a tool with complex calculations implemented in excel. Calculations take a few seconds to complete so we’d like to save the calculation outputs for all possible input values and implement an API to search those records.

• The API will be used to implement a web app that showcases various analyses

 

Technology Stack

 

• Python

• Flask

• MongoDb

• Excel

 

Code access

 

Base code and excel file with calculations are available in the forums.

 

Individual requirements

 

1. Data generation
   The requirement here is simple - generate all the combinations of the input values in the excel sheet “Inputs”, and for each of them save the calculation results (“Output” and “Choices” sheet) to MongoDB.
   The implementation might not be so simple - the calculations take 2-10 seconds to complete and there are ~620k possible input combinations so your task is to speed up the process as much as possible. Whether it is parallel execution, writing a VBA script, a Python script, or something else is not important as long as your script can provide significant speed up to the data loading process (we can’t wait 42 days to complete all the calculations and populate MongoDB). Exact values for the input variables are specified in the forums.
   We have tried the following strategies

   • Python xlwings https://www.xlwings.org/ 

   • VBA Macro

   • MS Office Javascript API using Script Lab https://docs.microsoft.com/en-us/office/dev/add-ins/overview/explore-with-script-lab

All of those take approximately 20 seconds to run for one input.
The bottlenecks appear to be:

1. Accessing / getting a handle on the sheet

2. Writing the input value

3. Excel calculating the new result (5-7 seconds)

Reading from the Excel sheet is very fast (~10ms).

Key requirement here is extracting the outputs as quickly as possible - it’s up to you to pick the right strategy, programming language, libraries, etc.

MongoDB record should contain values of each input, and output values from the “Choices” and “Outputs” tab. Make sure to create indexes for each of the inputs as they will be used for filtering (see the next section)

 

2. Rest API
   Create a simple Flask API with just one endpoint to search/filter records. Each input variable should support filtering and sorting. Add pagination options (limit and offset parameters).
   Create postman and swagger files for the api

 

General requirements

 

• Unit tests are out of scope.

• All configuration parameters should be extracted to a common settings module. Sensitive configuration parameters should be set from environment variables (DB URL, credentials, etc).

• Make sure the code passes lint checks

 

What To Submit

 

• All source code

• Deployment guide

• Postman collection containing sample calls for all endpoints (success/failure)

• Verification guide - how to set up the environment, start the API, and verification screenshots

Final Submission Guidelines

See above