Business IT Support and Cloud Services
01223 852 872

Adviser Office – Client Valuation Reporting

Pie Chart showing allocation of financial assets - data held in Adviser OfficeOverview

We have recently completed a project with a Cambridge based Asset Management company to automate their Client Valuation Reporting from Adviser Office.  The reporting solution is delivered to the advisers through a compiled Microsoft Access 2016 database.   There are three components to the solution:

1. Python

Python has been used to automate the collection of CPI data from the Office of National Statistics and also to process a data feed of benchmark comparisons.  The CPI data is collected automatically monthly when the new CPI figures are released.  The benchmark comparisons are collected daily.  Python was chosen because it is easy to access API’s and websites using the requests module.  Additionally scraping websites is made very simple with BeautifulSoup.  Python also provides ODBC access using the pypyodbc module.  Python is also a multi-platform language which means that code can be run on Windows, Linux and Mac-OSX.

2. Microsoft Excel

Microsoft Excel 2016 was chosen to provide on the fly charts for the valuation reports and also financial functions such as the XIRR calculation.  Microsoft Access does not provide a chart control that produces the picture quality required for client reporting.  Excel’s charting combined with VBA to colour each chart segment to the clients needs provided a solution that matched the clients requirements exactly.

3. Microsoft Access

Microsoft Access 2016 was chosen to bring the report together for a number of reasons:

  • Provide direct read only ODBC links to the IRESS Adviser Office database.
  • Easily combine data outside of Adviser Office with data in Adviser Office.
  • Advanced reporting which allows for pixel level tuning of reports.
  • VBA programming language is easy to use for further fine tuning of the reports.

Coding Challenges

The client was using a combination of Microsoft Excel files, Microsoft Word and a Microsoft Word plugin from IRESS which allows retrieval of Adviser Office data directly in to Microsoft Word.  The process was very manual with each clients valuation report taking an average of 2 hours to complete.  Due to the manual labour, the process was error prone.  Additionally Microsoft Word is more for word processing and does not handle changes in data size or length very well.

Microsoft Access handles the reporting side very well and there were no major issues recreating the report in Microsoft Access.  Integrating the chart in Excel required setting the chart object to size correctly on the report.

Adviser Office Challenges

The biggest challenge was with understanding the Adviser Office database.  IRESS only have a one page schematic available to describe the database structure.  There are over 200 hundred tables in the database.  Once analysis was done, we ended up using only 15 of the tables from the database.  Due to the valuation reports being produced manually in the past, the data in Adviser Office needed updating to reflect what was in Excel spreadsheets.

Project Roll Out

The project was rolled out to over 20 staff using a compiled Microsoft Access database and read only ODBC connection to the Adviser Office database.  Data in addition to the data held in Adviser Office is held on a network share as a linked database.  Typical valuation report is within 8 seconds which is a significant reduction in time.  It also means that valuation reports can be taken to any meeting rather than just waiting for quarterly reviews.