Quarterly report automation

Problem:

A bank in Canada needs to regularly update a report, showing trends, and assessing credit risk, this is submitted to a regulatory agency to control the levels of risk the bank is taking with their loans, mortgages,etc...

The report is in a word format detailing different tables that have to be filled with specific information of all areas of a bank. To do this the bank has several sources of data and model's results ranging from SAS code, to Excel and PowerBI calculations. What makes this report hard to fill is that they have to manually copy and paste the data from each one of this sources on its corresponding table, this makes the report slow to produce and prone to human error.

Task:

Transfer data calculations made in SAS and Excel to Python.

Format the data according to specifications (decimal numbers, letter type) to fit the table in the Word report.

Update automatically each table that has information available automatically into the Word report.

Solution:

A program was made with python using Scypy, Numpy and Pandas libraries to recreate the data analysis by feeding the necesary data in an specific sharepoint folder.

Use the code to create the data frames and format the data according the specifications.

Save this dataframes in dictionaries. Then open the Word template (with all the tables empty) inside python using docxtpl, iterate over them and filling them with the data form the dataframe.

Output the updated report into a folder in sharepoint to be reviewed. The code can be accessed using an interface made in powerBI, where variables can be updated, this allows the user to select the report to update, or add the dates to be used.