Category: Data Conversion


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.

In 2005, Advent released the first version of Advent Portfolio Exchange (APX). This paved the way for enterprise users to take Advent more seriously, while reassuring rapidly growing firms that APX would service their future needs and provide support for legacy requirements. Initially, this change was fine with many of the Axys users that have historically comprised Advent’s established userbase, but after years of baseline Axys updates and Advent’s predominant emphasis on APX, the patience of some Axys users has worn thin.

Today Axys users likely fit into one of four camps:

  1. They are planning to move to APX in the near future.
  2. They understand their options well enough, but don’t think the benefits of moving to APX outweigh the costs.
  3. They simply don’t care about APX or competing products – just as long as Axys keeps doing what they need, everything is fine.
  4. They are frustrated by Advent’s perceived abandonment of their business segment and are either actively seeking a replacement to Axys or in the process of converting to a new system.

I have repeatedly been told that owning a self-hosted version of APX is 2-3 times more expensive than Axys, but don’t take my word for it.  Advent’s pricing changes regularly.  Call Advent and get a quote.   Early on, APX conversions were very expensive, and some firms were quoted six-figure conversion costs.  Although these costs have been reduced substantially, APX is still significantly more expensive than Axys.

In the past, conversions were much more complex and time-consuming.  The primary issue seemed to be the normalization of a wide variety of Axys data.  As APX has evolved, Advent and the conversion utility within APX have created efficiencies in the conversion process.  In a recent conversation with a client, who is now considering the move from Axys to APX, I learned that Advent took copies of their Axys files and was able to demo APX 4.x with representative data from their firm in about a week.

In addition to the difference in the software cost, Advent recommends that APX users host the app in a traditional database server and application server configuration.  Some users may opt to host IIS on a separate server as well.  Currently, many small and medium businesses (SMBs) simply host Axys on their primary file server.

Why would a firm running Axys want to pay the premium for APX?

The answer is improved security, infrastructure, and functionality that meets the expectations of those with higher technological standards – historically enterprise users, not SMBs.  APX promised this from day one, but APX v1 was, well, version 1.  I sat in on a couple dog and pony shows for APX when it was first introduced.  In one, the presenter abruptly but politely disconnected a conference call with one of their early “testimonial” users when the conversation went in an unexpected direction.  At Advent’s conference in Orlando, more time than Advent would have liked was spent on the topic of APX latency, but these types of issues can be experienced with any v1 product covering as much ground as APX.

One of the most valuable benefits of Advent’s portfolio accounting systems is the maturity of their products.  This maturity is the primary reason why so many things in Axys and APX work the way they should.  Though much has changed at the core of Axys and APX, both of these systems can potentially run a report created on The Professional Portfolio (the precursor to Axys and APX) 25 years ago.  Due to the continuity of Advent’s portfolio management systems, users of The Professional Portfolio and Axys have been able to jump into APX without a lot of training.

Last year, when I attended the Advent conference in Boston, a panelist from the Advent Users Group touched upon the issue of APX owners using APX like Axys.  Her point was that you should use the newer features of APX v3, but as she mentioned it, I couldn’t help thinking how much the earlier versions of APX were like Axys.  Aside from the SQL backend and other related platform benefits, it felt like you were still using Axys, only it was more complicated and clunky.

Even now, we see that the heart of Axys continues to beat inside APX, playing a critical role with respect to backward compatibility and legacy reporting.  Over the course of its first five years, APX has matured significantly.  That initial awkward period is behind Advent APX.

In the past 18 months, Advent has made significant strides towards fulfilling the promise of APX, introducing additional SSRS reports in APX 3.x and the ability to create dashboards in APX 4.x.  I have finally heard mention of an API.  Yes, APX is more complex than Axys, but now that more of the infrastructure has been built out, you can feel better about it.  With these improvements, APX should make sense for a larger number of investment firms.

APX is a logical upgrade for Axys clients who:

  1. Want to minimize the need to retrain staff on a new portfolio accounting system.
  2. Understand that additional features, such as SSRS reporting and dashboards, come hand in hand with incremental complexity and the costs of an enterprise solution.

Those that don’t want to take on as much overhead may find solace in moving to APX on Demand (a SAAS offering), but in doing so they will have to sacrifice some of the flexibility and functionality available to self-hosted users of APX.

 

Final Score: APX 4, Axys 0

Looking at version releases of APX and Axys over the past seven years, it is easy to understand the focus of Advent’s primary resources.  Though four minor releases of Axys have been made since APX came out, there have been no major releases.  A major release implies a major change to the software, and at this point it doesn’t appear that a major Axys release is coming from Advent.

Last year’s acquisition of Black Diamond provides Axys users with another choice under the Advent umbrella, but I haven’t seen many users go from Axys to Black Diamond. While Axys improvements have stalled out, Advent’s full-throttle APX development has many of its Axys users feeling disenfranchised.  From my own perspective, Advent appears to be losing some valuable clients through a failure to more actively communicate with their SMB client base.

If Advent wants to keep Axys clients as Advent clients, they should connect with their users and reassure them that they want to work with them. Still, Advent should also understand that for some, it may make more sense to move on.

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 or contact Kevin Shea via phone at 617-720-3400 x202 or e-mail at kshea@isitc.com.

As more and more companies embrace Windows 7 as their new desktop standard, they are faced with the difficulties of getting Axys 3.x to run in a mixed environment of XP, Windows 7 and terminal server.  For those willing to make the leap, upgrading to Axys 3.8.5 is a best practice.  It’s Windows 7 compatible and should take much of the guesswork and troubleshooting out of making Axys work in a mixed OS environment.

A couple weeks ago, I was sitting with some clients, discussing the merits of moving to 3.8.5 now or early next year.  I let them know that Advent would likely announce a sunset of 3.5.1 and 3.6 later this year.  They were still using Axys 3.6 and knew they needed to eventually move to Axys 3.8.5 to stay current.  In our meeting, we reviewed the benefits of making the move, which included the various report updates, as well as compatibility updates for Adobe Acrobat 10, Office 2010, Windows 7, and Windows Server 2008.  They decided to upgrade to Axys 3.8.5 immediately.

To learn more about what upgrading may mean for your firm, read my blog titled “What Version of Axys Should Your Firm Use?

As our meeting ended, they coincidentally received an email from Advent announcing the sunset of Axys 3.5.1 and 3.6 support.  According to the announcement, Advent will work with all Axys 3.5.1 and 3.6 clients on migrating to 3.8.5 by May 31st, 2012, at which point they will no longer support Axys 3.5.1 and 3.6.  It states that DTCC 3.5.1 is only compatible with Axys 3.5.1, so DTCC users should upgrade to DTCC 3.8.5 in tandem, and also recommends that DTCC users seek Advent’s assistance to ensure a smooth transition.  Advent encourages Axys 3.7 users to upgrade, but will continue to support Axys 3.7 until further notice.

In our experience, upgrading to Axys 3.8.5 has been relatively easy.  However, since Axys 3.7 and prior versions’ data needs to be converted to 3.8.x format, the process is slightly more difficult and potentially problematic than other recent upgrades.  The data format change can cause compatibility issues with products that interface with Axys, so users should check with their vendors prior to upgrading.  There is no need to do incremental upgrades.  For example, users can upgrade from Axys 3.6 to Axys 3.8.5 directly.

The only difficulty we ran into with this particular upgrade was that the pesky User Account Control (UAC) feature on Windows 7 PCs needed to be disabled – good riddance – in order for Axys to work properly.  In this case, the upgrade was at the upper end of our 2-4 hour estimate, but upgrade times vary based on the amount of data, users, system speed, customizations and possible exceptions.  Though we haven’t run into any unanticipated exceptions yet, it is always good to plan for them.

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 or 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.