AdventGuru

Over the years I have published many blogs.  Almost none of them are as frequently visited as Getting Data In and Out of Advent APX and Axys. That is a good indicator that the topic remains relevant, but a long time has passed since it was published. If you have a recent version of APX today, there is another option that was not available back then – RESTful API.   Though I knew the functionality existed within more recent versions of APX, I hadn’t had the chance to implement it with a client yet.

Last year, an APX user approached me, seeking to utilize Advent Software’s API to create data pipelines between APX and their in-house MS SQL Server Data Warehouse (DW).  The APX user identified this work as a prerequisite for them to fully migrate from Axys to APX.  They wanted to maintain the high-level integration they had created between their DW and Axys in APX.

I was somewhat concerned because I had researched previous APX versions related to the API and knew of some of the issues early adopters had encountered. Given that information, I had some trepidation about obligating myself to a project that required implementing the API. At the time I was not convinced, via first-hand experience, that the API would work reliably for their planned application of it.

I tempered the prospective client’s expectations and proposed a flat-rate job focused on determining the feasibility of doing APX API integration in their environment. Our end goal was to develop a couple of data pipelines between the DW and APX, with the caveat that those deliverables, developed in Python and/or JavaScript, would be proof-of-concept work and not necessarily ready for production use.

Together we successfully completed the project in an APX v21.x environment self-hosted by the client. The majority of the work was done over the course of a couple of months and the deliverables were ready to be moved into production almost immediately afterwards, but there were some challenges along the way.  In most of the instances detailed below, we looped Advent in for assistance, and they did a commendable job helping us resolve the issues promptly.

• Error 403 – Initially, we were getting an error when attempting to use the API.  We reached out to Advent, and they noted that the most recent Cumulative Hot Fixes (CHF) update wasn’t applied and recommended that we install it.  Applying the CHF update resolved the error, and the API worked as expected.

• Postman functionality – There were a couple of days where Postman was completely unresponsive.  During that brief period, we had difficulty doing even the most basic API testing.  This issue seemed to resolve itself, but we may also have logged out of Postman and logged back in.

• Error 500 writing data to APX – During dev the functionality to read APX data was working very well, but we found that attempting to write data to APX generated an Internal Server Error.  I assumed that this meant that the data was not being written to APX.  After looping Advent in for another call, we discovered that although the error was being generated, the data was being successfully written to APX. Advent indicated that they would put a fix request in, but it might not happen because v21.x was sunset. With some reservations, I updated my code to ignore the error 500 when we wrote selective data to APX via the API.

If you do reach out to Advent for assistance, make sure you have Postman installed.  Advent has no desire to review your code.  They will want to test the functionality of the API with you using Postman.

What is Required to Get Started with the API?

Utilizing the API requires some detailed set up and work to get up to speed.  It probably won’t be something that just works without some troubleshooting, and there is a bit of a learning curve.  The following list may not cover everything you need to do to get up and running with the API, but it is a good place to start.  I wish there had been a better resource for me when I started working with APX’s REST API.

Here are some tips that should help those interested in implementing the API:

1. Make sure you are on the latest CHF for your current version of APX.  If the latest hot fixes have not been installed, you may have problems trying to utilize the API.
2. Download the Advent Portfolio Exchange REST APIs Postman Collection from the Advent Community website.
3. Create a Postman account if you don’t already have one, and locally install the Postman software.
4. Load the collection into your Postman profile and review the documentation completely.
5. Do a search on the API in the Advent community site and read through some of the threads.  The code samples there were simple, but helpful.
6. Create the client/credential and verify its existence via SSMS.  The client is persistent, so once you have created it, you shouldn’t have to create it again unless you update APX.  Verify the existence of the client (e.g., cc.postman) in the APX dbo.clients table.  If you have trouble creating the client using your code, try using the PowerShell script to create the client.
7. The user profile you are using needs to have appropriate rights.  Though we escalated my individual user rights in all the documented required areas, I eventually started using the admin user profile, which worked more reliably in our environment.  I believe Advent recommends using the admin user profile if possible.
8. Test basic APX API functionality in Postman to make sure it works before attempting to create code via C#, Python, JavaScript, et cetera that leverages the API.

Once you have completed the set up required and can use the API to read and write data to APX, you are ready to build out your solution.  If you have trouble with your implementation, validate specific functionality of the API with Postman.

Calling the API

Almost any use case of the API to write or read data from the APX requires the following steps:

1. Get IdentityServer base address from APX authentication configuration.
2. Get token endpoint from IdentityServer configuration.
3. Get token with client_credentials grant type.
4. Perform whatever API action you want (multiple calls to the API with the access_token are fine).
5. End your API Session.  The API utilizes one of your APX seats while the session is active.

Those familiar with API use and Python are likely aware that manipulating data can necessitate working with JSON as well as Python dictionaries.  As an example, in order to read data from APX and write data from the DW into APX that is different from what is already in APX, you may need to:

1. Query APX for the relevant data via the API, which creates a JSON file.
2. Query the DW for the relevant data.
3. Load the JSON data received from APX into a Python dictionary.
4. Parse and compare the APX data from the Python dictionary with the records from the DW.
5. Add the records that meet the criteria to the JSON payload.
6. Send a patch request via the APX API.

The following diagram details this workflow.

To wrap up the project, I created a PowerPoint presentation summarizing and detailing what we did and how it all works to empower the internal development team to understand, troubleshoot, and replicate my work if they need to in the future. I am always available to support the solutions I create, but I prefer that my customers call me because they want to, not because they need to.

Why would you want to use the API instead of IMEX?

There are pros and cons to using the API. It presents an opportunity to use a single unified methodology to integrate data in your environment but may fall short of that depending on the specific needs of your firm.

The pros of using the API include the fact that is is a more modern approach to extracting and importing data at a granular level. The API can be used to pull data such as holdings, select time period performance, etc. In some use cases, APX users are likely extracting and transforming data that they drop into a DW. Some of those transformations, such as recalculating performance figures, may not be necessary when utilizing the API. The API has the potential to be more secure but given that the default password for admin user in APX frequently doesn’t get changed, it probably isn’t any more secure than IMEX in most self-hosted APX environments.

The cons of using the API are that some data elements may still be in flux. Reading and writing certain data points may not be possible via the API, which could force you to use IMEX or other methods (e.g., Replang, public views, stored procedures, SSRS) in addition to the API. It also may be difficult developers that aren’t Advent APX Subject Matter Experts (SMEs) to bridge this gap. Conversely, it may be difficult for those SMEs that are not developers familiar with the API use to implement it on their own.

Using well-established APX import and export methods like IMEX may still be the most efficient and reliable way to import and extract certain data elements from APX. However, going forward, the growing maturity of Advent’s REST API should force tech-savvy management, users, and integrators to ask “Should we be using the APX API to do this?” as they look to forge a modern data stack that integrates APX data, and meets AI-driven demands for more robust data access.

---

About the Author: Kevin Shea is the Founder and Principal Consultant of Quartare; Quartare provides a wide variety of agile technology solutions to investors and the financial services community at large.

To learn more, please visit Quartare.com, contact Kevin Shea via phone at 617-720-3400 x202 or e-mail at kshea@quartare.com.