Program developed for accounting firm to parse investment reports and generate .xlsm files for clients

parseproject.py is a program I developed for an accounting firm's Personal Financial Planning (PFP) department. Every day the office receives automated investment reports for all of its clients in a not-so-useful format. All account records for all clients are arbitrarily sorted in one huge csv file, making it time consuming for the employees to copy all of the records for a certain client into the macro-enabled excel workbook template that they use to analyze important financial information regarding that client. parseproject.py begins by deleting old reports to reduce clutter, it then creates dictionaries associating clients with all of their accounts, as well as some other bits of information. Once this has been done, parseproject.py can create a new csv file for each client, containing only the records pertinent to that client. For each client, the Excel workbook template is then loaded, into which the client's records are transcribed into the 'raw data' worksheet. It also recreates all of the charts and graphs from the template manually since openpyxl cannot preserve these. Then the file is saved as a new workbook specific to that client, with the raw data formatted such that the existing VBA macros can operate on the data, populating charts, graphs, and other cells used by the financial advisors. Due to the relatively long time that the openpyxl module takes to read and write from/to .xlsm files, I've parallelized this part of the program by mapping the workbook transcription function to a process pool, created with pythons multiprocessing module. This way a user can easily specify the number of concurrent processes in order to massively speed up the creation of client workbooks. Since creation of a client's workbook is independent of any other, there is no issue in performing these operations simultaneously.

Once the program has finished processing all of the client data and generating these .xlsm files, it terminates the process pool and sends an email to select members of the IT and PFP departments using yagmail, notifying them that the program has completed and that all files are up to date. I've also included an extremely basic python script used for registering an email and associated password with python's keyring module, so that these emails can be automatically sent through one of our IT member's email without exposing the password in the script. Of course I am unable to provide the various .csv investment reports used in aggregate to create the workbooks, which are parseproject.py's final product, since these reports contain confidential financial information for all clients. Though in the future I may upload dummy-sample files that are of the same format and compatible with this program.

The provided parseproject.py is a local test version of my program, which reads and writes locally on my machine for easy testing. The actual program is scheduled on a company server to run every morning after the investment reports have been downloaded, and writes the new workbooks to a network drive only accessible by PFP and IT.