Category: SQL Server


iStock_000011255731XSmallWhen Advent first introduced The Professional Portfolio (aka Proport) 25 years ago, one feature that contributed  largely to its long-term success was the ability to get data in and out of the program easily.  Advent software has continued to make this feature a priority throughout every iteration of their original portfolio management product line.

In contrast competing products like Centerpiece, which would eventually be purchased by Schwab and rebranded Portfolio Center, appeared to be a black box.  You could see what was being calculated, but could not see the components of the calculation.

Proport files were stored in an open text format and could be easily read and written as necessary.  Axys v1.x, Advent’s premier Windows product at the time, maintained a similar open file structure.  Axys v2.x was the first version to implement a binary file format.  At the time, some users were concerned that the format change would complicate maintenance of existing customized solutions and inhibit their ability to continue to create solutions that exchanged data with Advent’s portfolio management system.

Firms were slow to embrace Axys v2.x and some never upgraded to it.  Perhaps it was concern over the new file formats.  Instead, most firms eventually upgraded to Axys v3.x. Concerns about the format change were moot since Advent also introduced IMEX, which allowed users to import and export files in CSV, tab, and fixed formats.

Exceptions apply, but the IMEX tool facilitates the ability to move data in and out of Axys with relative ease.  The features of IMEX combined with the ability to import transaction and label data through the trade blotter provide a comprehensive means to get fundemental data in and out of Advent Axys and APX.  Additional methods of importing and exporting data follow:

Axys users may write or read data directly to data files if they have knowledge of the underlying data format.  However, this is not a best practice due to changing file formats between versions.  For example, upgrading from Axys v3.7 to v3.8 requires a file conversion process.  Some of the resulting Axys v3.8 files have a different file format, so any process directly writing or reading these files would need to be updated to take the new file formats into account.

APX users may

  1. query the APX database via Excel (and other software programs).
  2. write SSRS or Crystal reports to extract data.
  3. use many other SQL based tools to export and import selected data.

Axys and APX users can

  1. export reports directly to Excel with the push of a button or create a macro that stores report output in XLS and other file formats.
  2. create custom reports via Report Writer Pro, which can easily be changed to CSV format.
  3. modify existing replang reports to build CSV, other text formats, and various Advent file formats.
  4. use third-party Extract, Transform and Load (ETL) products like xPort.

APX (v1.x to v4.x) maintains the functionality of IMEX, but the ability to generate files in a fixed format has been eliminated.  In addition, you can export data to an Axys v3 format.

Due to these capabilities and a host of other Advent features that facilitate automation and integration, Axys and APX users as well as third-party vendors like ISITC.com have developed many custom solutions utilizing Advent Software’s infrastructure to address day-to-day investment operations workflow and reporting requirements.  The building blocks of these solutions faciliate subsequent projects and allow investment firms to further enhance Advent’s portfolio management systems to meet their evolving needs with less effort and cost.

Here is a sample of some VB code we use to integrate and automate data handling of exported Axys and APX data.  The code can be used in Excel with VBA and User-Defined Functions (UDFs) to pull data from Axys and APX like Bloomberg BLP functions are used in Excel. This function is just one of the routines in our library of code that enable us to seemlessly integrate our solutions with Advent’s infrastructure.  I wrote the original source code for this routine twenty years ago and have updated it as necessary to support later releases of Axys and APX.

Using similar resources, integrators can move quickly from prototype to production when developing solutions for Advent users.  In fact, Advent’s most recent annual report continues to cite customers building their own solutions as one of their largest sources of competition.  For instance, a number of firms have created their own Order Management Systems – not that I’d recommend it.

Advisors abhor inefficiency and are typically willing to make a reasonable investment to reduce it.  Automation not only increases efficiency, but lowers risk by eliminating manual processes that may rely on individuals and their exclusive knowledge of manual or semi-automatic procedures.  Some financial services firms have customized their systems to a degree that makes staying on the Advent platform for twenty plus years possible and the thought of switching to another platform regrettable.

Thanks to the way Advent handles getting data in and out of their systems, users can continue using Axys to meet their ever-changing system requirements and leverage most solutions created for Axys on the APX platform.   Similar and potentially better tech options may exist on other competing platforms, but most of those systems lack the maturity, depth of resources, third-party relationships, and corresponding reliability of Advent’s platform choices.  Knowledge and acceptance of these competing products among advisors, employees, and third-party solution providers won’t match Advent for a long time.

As a result, even though technologically superior portfolio management platforms may emerge, many firms will continue using Advent’s best known portfolio management systems for the foreseeable future.

About the Author: Kevin Shea is President of InfoSystems Integrated, Inc. (ISI); ISI provides a wide variety of outsourced IT solutions to investment advisors nationwide.

For details, please visit isitc.com, contact Kevin Shea via phone at 617-720-3400 x202 or e-mail at kshea@isitc.com.

I take issue with the implied generalization that some portfolio management systems are “open” just because they utilize SQL server architecture.  With respect to data formats, a product using a SQL format can be considered “open” and a product using a proprietary format is thought of as “closed”, but this gross simplification of the nature of systems can be ill-used by marketers that need you to need something new before it is ready to fulfill its promise.

Don’t get me wrong.  I’m all for open systems, and a system that leverages SQL server architecture is almost always a plus in comparison to the alternatives. However, to determine whether purchasing a new system will effectively satisfy their requirements RIAs need to understand what the differences between the systems they are considering mean to them in the short-term and long-term . Most RIAs don’t understand the issues at a level sufficient to grasp the near-term pros and cons of SQL based systems without experiencing them firsthand.

Last year, I read an article which said advisors are being held hostage by Portfolio Management Software (PMS) companies. The article called Advent ‘s portfolio management systems closed, but after I posted a lengthy comment, the article was revised changing their closed comment to refer to Axys rather than APX.

My original comment on the article follows:

It was a thought provoking article. However, Advent has been around for over 20 years. It is as much of a standard as you are likely to get in this industry. dBCAMS+ and Portfolio Center have been around about the same time, but Advent’s Axys and APX PMSs are the standard by which other systems are measured.

Though I normally act as an advocate for my investment management clients and not Advent, it isn’t fair to call Advent’s PMSs closed especially when you take into account the scripting, macros, import/export tool and the public views offered in APX. The maturity of the product also makes it easier to find people and resources to help you.

The easiest way to get data from one system to another is hire resources that are qualified to do just that by proof of specific experience. It can be difficult to transfer decades of data, but the issue isn’t necessarily one of standards now. It is difficult because there were not any standards in the past.

Converting a client with 20 years of data can potentially mean bringing a mix of data entered manually, downloaded automatically, and random fields into another system that the data doesn’t easily translate to. The process of translating involves field mapping much the same as you would need to move your contacts from one CRM to another, but it is more difficult because of the nature of transactions that can alternatively appear on one line or multiple lines. Intelligent translation tools interpret multiple lines of transactions simultaneously to determine how individual lines should be translated.

Though Advent does not allow you to import transactions directly into portfolio files, you can import the data easily enough. First you need to convert the source files from your old portfolio management system to Advent’s trade blotter format. Once you have done that you can import them to the trade blotter and then post them to your portfolios.

Getting data out of Advent is not difficult for experienced users. You can export transactions, portfolios, groups, securities, composites, performance files and almost any other data you want. Additional data can be generated as needed via reports written in Advent’s report writing language (REPLANG), or Report Writer Pro which allows you to send report data directly to a CSV format. You can also use SQL Server Reporting Services (SSRS), Crystal Reports, or even Excel to access APX data via SQL.

Users thinking of switching to other portfolio management platforms should take a hard look at the existing infrastructure of their current platform and compare it to the new system before making a switch. Infrastructure includes support for investment instruments/multi-currency, people, partnerships, third-party solution providers, interfaces, support-levels, standards and delivery.

Investment advisors need to understand the value of being able to get data in and out of their PMSs. Advisors should always own their data, and never be at the mercy of their PMS, or service provider for access to data that would be required to switch to another vendor.

With regard to the ease with which you can switch from one PMS to another, no matter how accessible, and open the data seems to be – conversion from one system to another still requires standardization and mapping that increases in difficulty based on four primary factors: the number of accounts, the number of investment types, age of portfolios and quality of data.

Perhaps the conversion of data from one PMS to another can be best understood with the analogy of translating a book from one language to another.  Some things translate very easily while other aspects can be extremely difficult to get across.  I have also heard a bit of a buzz about how much easier it will be to switch from one system to another once firms go to the more commonly used SQL platform, but this is a generalization and not a rule.  The complexity of conversions has more to do with common denominators (tables and data structures) between systems than the type of data format they share.   PMS software that shares the same data format does not necessarily share the same data structures and logic.

For now it is debateable whether the benefits of a system that really is open outweigh those of a platform that is more mature, reliable and robust without being “open.”  If you have any doubt of this ask firms that are on the cutting (aka bleeding) edge and find out how well it all works right now.  Firms that have available staffing resources with the expertise to create added efficiencies through the use of a SQL based system may be able to leverage a SQL based platform at a level that justifies the cost.  Lean firms should think hard about the costs before making a commitment.  There are probably significantly less expensive options to add efficiencies on their existing platforms.

In a perfect world, all of your software programs would transparently exchange data as needed in an open architecture.  Today, however, you still have to do some work to make your software programs exchange the data. In order for PMS products to be truly open, an Application Program Interface (API) or similar mechanism would need to exist for every system, facilitating the transport of data.  Hypothetically, PMS products could share a single underlying data format, but that is unlikely to happen.  In the interim, products like xPort pave the way for firms to extract data from their PMS and produce high-end reports without the overhead of a platform change.

Some PMS companies, like Schwab’s Performance Technologies, have made use of Extensible Markup Language (XML) as a vehicle to automate data extraction in a format that is more easily interpreted by developers unfamiliar with their SQL database.  XML provides a measure of portability that other formats like CSV, fixed format and tab delimited do not.  Unfortunately for those without XML experience, dealing with data in an XML format represents yet another technological challenge.

We are now entering an era where open standards and integration between systems in the financial sector should accelerate dramatically.  In the past year, the big three (Fidelity, Schwab and TD Ameritrade) appear to have acknowledged open systems as the latest required initiative (and marketing buzzwords) to ensure the continued success of their institutional arms.  And, in fact, left unchecked open standards may be the biggest threat to the relative monopoly these firms enjoy as the technical visionaries of the RIA marketplace.  On some level, the openness of systems is an eventuality.

The big three are technology leaders.  By proactively augmenting the technology available to their instutional clients these firms make it easier for RIAs to do business and subsequently attract more business themselves.  Each of these firms deserves kudos for their achievements thus far, but there is a clear conflict of interest here.  Another goal of these competing firms is most certainly to provide competitive and proprietary technology with the potential to sway and keep business income in their coffers.  On the topic of open systems RIAs should listen to what the big firms say, but keep a watchful eye on what they actually do.

The truth of the matter is that you should want your systems to be more open, but not so open that there aren’t sufficient controls.    Firms with relatively simple requirements may never want to change their platform, but be assured that, just as it has in the past, the platform will change in the future.  The transaction cost to change it will go down as PMS provider’s incentives to sunset older platforms increase.  The question is whether it makes more sense to make a switch now or later. 

Whether you are on a SQL platform or not,  you still need to have work done to automate/integrate your systems and create high-end client statements.  For example,  going to the latest version of APX 3.0 with support for SSRS will not get your firm the custom reports they need overnight.  There is considerable work involved and the latest platform is still relatively new.  APX 3.0 provides a platform, but solutions still need to be built for that platform.  There will be more choices in coming years, but right now I suspect there are more reporting choices on the Axys platform.

Spending money on improving systems infrastucture is necessary, but the what, where and when is critical.  Your firm’s technology expenditures should be timed with your firm’s best interests for tangible results in mind.  In 2011, your company may be better served by adding a new trade order management system, or beefing up your research resources rather than moving to a PMS platform that utilizes SQL server.  The issues vary from firm to firm, but for most this decision demands a disciplined cost benefit analysis with detailed specifics not generalizations. 

Those that decide not to move to a SQL server platform this year should revisit the decision annually.

About the Author:
Kevin Shea is President of InfoSystems Integrated, Inc. (ISI); ISI provides a wide variety of outsourced IT solutions to investment advisors nationwide. For more information, please visit isitc.com , contact Kevin Shea via phone at 617-720-3400 x202 or e-mail at kshea@isitc.com.