Automating Daily reporting at GROUPE Renault(RCI Financial Services)using python & Alteryx
Introduction and Background:
The Daily Monitor and the Daily acquisitions are the 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 lies in the valuable information one can gather as they offer an insight into where we are at a point in time, how we are performing against last year’s result, and whether we are on track to hit targets.
The Daily Monitor is a brief yet detailed snapshot that shows our commercial and financial position on the report’s date and how this compares both against our set targets and our achieved results for the previous year. This is then emailed to the executive committee, the entire finance department and select individuals.
The Daily acquisitions on the other hand is 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 is performing against its target. This pdf was then uploaded to declic (the social networking site for employees of the Renault GROUPE).
Both reports are were prepared using an excel worksheet. In this portfolio entry however, I will be explaining the process of how I eventually automated the preparation of only the Daily acquisitions report. I have altered all the data related to RCI FS (as that is the property of the company) so instead, the process will be explained via a fictional company called ‘Generic company’.
The data and end product:
Each morning, I extract data from the database via an SQL extraction. This data contains 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.
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.
After becoming proficient in Alteryx, a data blending and analytics tool and acing the certification exam a few weeks earlier, it was naturally my first go to for this automation project. I had also automated the daily monitor a few weeks back then using it. However, after about 30mins in, 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 have been wanting to do something really 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.
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 still reading through my implementation, I am going to assume that you are somewhat familiar with programming and perhaps have recognised this to be the object-oriented approach.
chartsGenerator (written with additional libraries; matplotlib & numpy) takes a number of arguments, and produce 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 using by calling a function in the Powerpoint2Pdf module.
Step 3 – Integration with Alteryx:
This was the final step, of the project. After much deliberation, I decided I would integrate my written python scripts into Alteryx through the interactive jupyter notebook of the python tool. My 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.
Leave a comment below if you have any question or are interested in the project and would like a more detailed explanation of the codes or process.