Portfolio Performance Reporting Automation for Concert Wealth Managment

Executive Summary

                This project was completed for my dad’s retirement planning and investment advisory company, Concert Wealth Management in Reno, Nevada.  He has made a habit of creating monthly performance summaries for his clients to show them in a very simple format how their investments have performing over the last 12 and 24 months. However, as his client base has grown, creating these reports has become a more and more time consuming process.  I decided I would automate the process of gathering the data, updating the Master Account Values File, and updating all the client reports including tables, charts and graphs.

                This project broke down into 4 distinct modules.  First, retrieving the account values from the online portal provided by NetX360.  Second, updating the Master File with the most recent data from the balances sheet downloaded from NetX360.  Third, updating all the client report sheets from the Master File, allowing for customized timelines including year-to-date, inception-to-date, and various month ranges such as trailing 12 and 24 months.  Finally, saving these client sheets as PDFs to their client folders, allowing for the user to save all reports at once, or select individual or multiple clients to save ad hoc.  Each of these modules required a unique and more complicated sub procedure.  And to allow the user to customize the content and output of these sub procedures, I created a user form that is accessed from the ribbon.  The user form has several smaller sub procedures to facilitate the user’s interaction with the macros.

                The final product is a workbook that contains all the tools to complete a task in a few minutes that used to take several days.  The user is able to open a form with three tabs, retrieve the data from NetX360 and update the Master File in one, update the client reports choosing the reporting ranges in a second, and save specific client reports as PDFs to their client folders in the third.

http://files.gove.net/shares/files/15w/cshaffer/MasterFileVBAProject.xlsm
http://files.gove.net/shares/files/15w/cshaffer/Final_Project_Write_Up.pdf