Automating Daily reporting at GROUPE Renault (RCI Financial Services) using python & Alteryx

01 Mar 2019    5 mins read.

Image Credits: Unsplash

Introduction and Background

The ‘Daily Monitor’ and the ‘Daily acquisitions’ were two daily reports sent out by the Management Accounting department and consequently, were one of my responsibilities at RCI Financial Services (A wholly owned subsidiary of GROUPE Renault). The importance of their preparation lied in the valuable information and insight one could obtain regarding where we (the company) were at a point in time, how we were performing against last year’s result, and whether we were on track to hit our targets.

The Daily Monitor

The Daily Monitor was a brief snapshot that showed our commercial and financial position on the report’s date and how this compared both against our set targets, and our achieved results for the previous year. This report was emailed to the executive committee (i.e the C Suites; CEO, CFO, CMO etc), the entire finance department and select individuals from marketing.

The Daily acquisitions

The Daily acquisitions on the other hand was a pdf summary report of the month’s total acquisitions by Brand & category, presented with some graphs, and also contained information of how the company was performing against its target. This pdf was then uploaded to declic (the social networking site for employees of the Renault GROUPE).

The data and end product

I begun preparation of the reports each morning by extracting the relevant data from the database using SQL. The extracted data contained a record of each agreements financed by the company since the start of the month. A PowerPoint report (later converted to a pdf file) is then prepared using this data.

Snapshot of the Extracted data

End product

The whole preparation process (from extraction to pdf creation) took between 30mins to an hour each day. Implementation of this automation process cut this down to about 2 – 3 mins each day.

The Process

Having become proficient in Alteryx, a data blending and analytics tool, it was naturally my first go to for this automation project. I was inclined to use it as I had succesfully automated preparation of the daily monitor a few weeks back. After about 30mins in however, it quickly became apparent that Alteryx on its own wouldn’t be sufficient. This was because the data presentation and reporting tools were limited in functionality (Granted some of the desired outputs of the report could be achieved by some tools still in beta). As a result, I sought other ways of achieving the project’s objective and python particularly stood out to me not only because of its simplicity and integration with alteryx, but also because I had been wanting to do something meaningful in it. This was also a chance for me to play with Matplotlib and Numpy; popular python libraries among data scientists. My final working solution was built using a combination of both Alteryx and python tools and the end result was a very user-friendly experience app. [See video below]

Step 1 – Prepping and setting up the environment:

As I was working on my work laptop, I first needed to setup my development environment by downloading/installing the following:

  • A portable version (no admin rights required) of the Anaconda distribution: This installed python 3 along with many popular library some of which were used in this project (NumPy, Matplotlib and win32).
  • Python-pptx library (a python library for creating and updating PowerPoint files); installed via pip by running the command line “pip install python-pptx” in cmd.
  • Excel2img library (a python library for saving ranges from an excel document as images)

Step 2 – Designing and mapping out a solution:

After thinking about multiple possible implementations, I concluded that I would first need to build a PowerPoint file, then export this to a pdf (Akin to the current process at the time). As seen from the end result above, a total of 7 slides were needed. The first slide was just a title page and could be built just using Python-pptx. The second and last slides are tables which will be built in excel and later exported to an image to be embedded in the PowerPoint. And finally for the charts, I decided I would use pyplot (from matplotlib). My mapped out solution was to perform all required computation using alteryx and export the end result to a .json file and an excel file. The python script will then take this .json file as an input and prepare the report accordingly. My implementation resulted in me building 4 python modules: chartsGenerator, slideGenerator, powerpoint2Pdf and DailyAqcsReportGenerator. If you are somewhat familiar with programming, you might perhaps have recognised this to be an object-oriented approach.

  • chartsGenerator (written with additional libraries; matplotlib & numpy) takes a number of arguments, and produces a chart image ouput.
  • slideGenerator builds the required slides using Python-pptx library and a base PowerPoint template.
  • Powerpoint2Pdf simply converts the generated PowerPoint file to a pdf using python for windows extensions – win32com.

DailyAqcsReportGenerator brings everthing together. It reads in the supplied .json iput, generates the required chart images by calling functions in the chartsGenerator module, generates the required table images by exporting this from the excel file via excel2img, then finally builds the slides by calling slideGenerator. Upon completion of building the PowerPoint file, a pdf version of the file is then built by calling a function in the Powerpoint2Pdf module.

If you're interested in this project, I'm happy to provide the source code for the modules upon request

Step 3 – Integration with Alteryx:

This was the final step. After much deliberation, I decided I would integrate my written python scripts into Alteryx through the interactive jupyter notebook of the python tool. The alternative was to run the scripts via the run command tool in Alteryx. However, I chose the former implementation as it would result in a more user-friendly experience.

Get in touch

Please feel free to reach out if you have any questions or would like to discuss this project in depth :)