“Mon premier reporting Excel automatisé avec Python.” Ce titre vous a peut être interpelé ou vous êtes peut être arrivé sur cet article par hasard. Ils sont rares ceux qui sont sensibles à l’automatisation des travaux récurrents sur Excel. Ils le sont encore plus ceux qui ont compris la puissance du langage de programmation Python pour l’automatisation des reportings Excel. Cet article va vous initier tout en douceur à ce sujet au travers d’un exemple simple. Au terme de cet article j’espère que vous aurez envie vous aussi de tester Python et de l’associer à Excel.
Avant de rentrer dans le coeur du sujet, je tiens à vous indiquer qu’il s’agit ici de vous présenter un exemple simple et compréhensible même si vous n’avez aucune notion de programmation. Je souhaite vous faire prendre conscience du potentiel de Python dans Excel. Les pouvoirs de Python vont bien au delà de ce que vous pourrez lire dans cet article. Je vous donnerai des pistes de réflexion à la fin de cet article.
On est parti !
Pourquoi automatiser votre reporting Excel avec Python ?
Je pars de ce principe sain que vous êtes néophyte sur Python. Je me permets donc de vous rappeler les bases succintement. J’ai fait un article plus complet il y a quelques temps sur l’automatisation des reportings Excel via Python.
Python c’est quoi ?
Python est un langage de programmation. Il permet de développer des sites web, des applications, des logiciels,… Il jouit d’une communauté très active. C’est ce qui fait la force d’un langage de programmation open source. Plus la communauté est active, plus le langage s’adaptera notamment par le biais des librairies. Il s’agit d’ajouts au langage de base permettant d’inclure de nouvelles fonctionnalités. La communauté des data scientists, qui ont l’habitude de traiter des jeux de données importants et sur lesquels ils pratiquent des calculs complexes (régressions, modèles prédictifs, analyses statistiques poussées,…), ont créé plusieurs bibliothèques qui peuvent être utiles à tout un chacun.
Parmi ces librairies nous trouvons :
- Pandas : cette bibliothèque permet la manipulation des données et la réalisation de calculs ;
- Matplotlib : permet de générer des graphiques ;
- Seaborn : permet de générer des graphiques et autres modèles de visualisation des données ;
- Openpyxl & xlsxwriter : permet de lire, créer ou modifier un document Excel.
Les avantages de Python
Se servir de Python pour automatiser vos reportings Excel a de nombreux avantages. Tout d’abord, même s’il s’agit d’un langage de programmation, son apprentissage est aisé. En quelques heures vous pourrez créer vos premiers reportings. La lecture du code est également aisée. En choisissant les bons termes, on comprend facilement ce que le code fait.
Python est plus puissant qu’Excel dans le sens où les calculs sont linéaires. Lorsque vous modifiez une cellule dans Excel, le logiciel recalcule l’ensemble du document. Python économise des ressources de votre ordinateur puisque le programme est lancé une seule fois.
L’élément que je trouve le plus important est l’auditabilité de votre processus de réalisation du reporting. Lorsque vous récupérez un document très fourni d’un collègue, il est complexe de déterminer le cheminement entre les données initiales et le rendu final. Dans Python, vous retrouvez ce cheminement et vous pouvez clairement identifier s’il y a des biais ou autres erreurs.
La maitrise de Python a clairement changé mon quotidien de contrôleur de gestion.
Après cette remise en contexte, passons à la pratique. 😁
Comment automatiser mon reporting Excel avec Python ?
Pour ce premier reporting Excel automatisé avec Python, j’ai récupéré un jeu de données directement sur le site Kaggle. Ce site est une référence dans le monde des data scientists et me permet de trouver des jeux de données pour s’exercer et pour imager mes tutoriels.
Il s’agit ici d’un export contenant des caractéristiques des travailleurs. Imaginons qu’il nous soit demandé un reporting Excel périodiquement. Ce reporting Excel, que nous voudrions automatiser avec Python, doit mettre en exergue le nombre d’heures travaillées entre les hommes et les femmes suivant le statut personnel. Cette analyse doit se porter uniquement sur les individus travaillant à leur compte (en société ou en nom propre) et ceux travaillant dans le secteur privé.
Je vais travailler mes données dans un carnet de notes Jupyter. Un carnet de notes Jupyter est une application web permettant de créer et partager des documents mêlant code, texte et résultat du code. Il est, à mon sens, le meilleur moyen d’entrer dans l’univers de Python du fait de la visualisation direct du code. Je vous partage via Google Colab le carnet de travail.
Import des données
Pour commencer j’initialise mon document en précisant que j’ai besoin de la bibliothèque Pandas ainsi que la biliothèque numpy. Je les avais précédemment installées sur mon ordinateur.
import pandas as pd
import numpy as npPar la suite, je récupère les données précédemment téléchargées et stockées dans le même dossier que mon carnet de notes Jupyter. Je demande à visualiser les 5 premières lignes de mon jeu de données.
donnees = pd.read_csv('income_evaluation.csv')
donnees.head(5)Voici le rendu dans le carnet Jupyter :
J’ai pu récupérer facilement les données malgré le format csv. Si je l’avais ouvert avec Excel, j’aurais obtenu ce rendu :
En deux lignes de code, j’ai pu récupérer mes données et voir les 5 premières lignes de ces données. La biliothèque Pandas n’a eu aucune difficulté à identifier le format et à séparer les valeurs en colonnes.
Nettoyage des données
Nous allons regarder nos données pour identifier s’il faut les nettoyer. Pour cela, je vais demander les valeurs uniques des secteurs d’activité et des statuts personnels.
Je constate que dans les statuts personnels, il est indiqué “Mari” et “Femme” et je préfère qu’il soit indiqué “Marié”. Il faut que je corrige donc :
donnees.loc[donnees[' relationship'] == ' Wife', ' relationship'] = ' Maried'
donnees.loc[donnees[' relationship'] == ' Husband', ' relationship'] = ' Maried'Mon analyse doit se limiter aux 3 secteurs suivants : ‘ Self-emp-not-inc’, ‘ Private’, ‘ Self-emp-inc’. Je réduis mon jeu de données comme ceci :
donnees_filtrees = donnees.loc[donnees[' workclass'].isin([' Self-emp-not-inc', ' Private', ' Self-emp-inc'])]Mes données sont nettoyées. Passons au rendu.
Reporting avec Python
Je vais me limiter ici à produire un tableau croisé dynamique version Pandas-Python. Pour ça, je vais utiliser la fonction pivot_table de Pandas.
Voici le rendu :
Mon reporting automatisé avec Python est fait. Il ne manque plus qu’à l’exporter vers Excel.
Exporter les données vers Excel
Pour injecter le rendu directement dans Excel, une seule ligne de code suffira :
pivot.to_excel("1er reporting.xlsx")J’ouvre mon document et voici le résultat :
Ce cas est un exemple simple du potentiel de Python. A ce jour, j’ai pu mettre en place des centaines d’automatisations de reporting Excel avec Python. Parmi ces cas, nous trouvons :
- Transformations de fichiers FEC en compte de résultat, bilan, tableaux de bord ;
- Transformation d’export d’un logiciel en import d’un autre logiciel (fichier passerelle) ;
- Budget automatique de la masse salariale ;
- …
On peut notamment inclure des graphiques, faire des mises en forme, mener des calculs complexes, créer des macros,… Les possibilités sont infinies.
Conclusion
Au travers de cette article, j’espère vous avoir intéressé à Python et à son potentiel pour l’automatisation des reportings Excel. Je constate encore trop souvent la réalisation de tâches sans valeur ajoutée dans les entreprises. Des solutions existent pour les automatiser. Je produirai de nouveaux tutoriels sur la mise en oeuvre de Python ces prochaines semaines afin de vous aider à monter en compétence. Si vous avez des travaux à réaliser périodiquement sur Excel et que vous souhaitez les automatiser, venez me le dire en commentaire.
