How to streamline the foundation of your ETF workflow

A 'just-in-time' solution that met the immediate requirement of a top-tier ETF service provider to respond to client demand and maintain excellence in their offerings.

AND a 'just-in-case' solution that envisaged a handful of potential problems and solved them before they had the chance to emerge.

The problem

Warning! Fin sector jargon ahead. My client needed to calculate Portfolio Composition Files (PCFs) for several clients who wanted to launch Exchange Traded Funds (ETFs) for their multi-class funds.

Until now, PCFs were calculated at a fund level where all funds only had a single class.

Many, if not most, of the ETFs are ANTs (Active Non-Transparent ETFs), where solution flexibility and PCF accuracy are front and centre.

The solution

Although they’d provided ETF services for almost a decade, this Fund Administrator was unsure how to best to do this differently.

If you’re after the geeky nuts and bolts of an elegant solution for calculating PCFs for dozens of funds and classes in funds in just a few minutes, here you go.

If you’re more interested in Consider-it’s process for solving the problem du jour and finding a scaleable, transparent, cost-effective solution to a gnarly workflow issue, read on.

Championing artisan teams


I founded Consider-it to champion self-sufficient Operations Teams by designing and creating highly intuitive, outstandingly efficient workflows.

I design and build systems that teams can maintain, troubleshoot, and enhance by themselves. Consider-it solutions dissolve the distance between solutions and their end users. Remove reliance on external expertise, and you lower the risk of glitches and meltdowns.

Beyond that, show me an Ops team (or any team) with creative and technical control of their tools, and I’ll show you a skilful, confident, contented bunch of artisans that’ll maximise the potential of said tools to do amazing things.

When this client contacted me, they had limited internal programming expertise coupled with the specific business knowledge needed to interpret and address any new requirements for their robust but arcane VBA-based solution.

The analysts using the solution hoped to get the expected result. If they didn’t, and there were issues with the output, rapid, timely troubleshooting was next to impossible without solid in-house expertise.

I built the new solution with end users in mind. It’s as simple as it is solid. The vulnerability caused by lack of transparency and key person risk is hugely reduced.

Working with top tools


Consider-it is software agnostic. I’m not affiliated with any major software or systems provider.

For starters, I’ll give you honest actionable advice on enhancing your operations by working with what you’ve got. If we can optimise how you collect and transform your data without upgrades, that’s what we’ll do.

If I believe your best solution involves changing or upgrading software, you’ll get 100% transparent recommendations tailored to your operational needs, culture, and budget.

In the case of this client and the one before them, Microsoft Power Platform’s Power BI and Power Automate were peerless, perfect fit options for collecting and transforming ephemeral financial data in a market-driven instant. They’re:

  • Low-code products designed with non-developer end users in mind, making them intuitive and easy to use
  • Wildly flexible and adaptive, they play nice with a myriad of less sophisticated, dare I say it, archaic tools and software
  • Cost-effective and able to slot seamlessly in alongside your ever-present Microsoft applications – Excel, Word, Outlook, SQL Server.

Want the technical version of ‘circumventing clunks and creaks’ in this project? Go here.

In short, a Power BI and Power Automate-driven solution allowed this client to meet their client’s demands for radical innovation in providing up-to-the-minute investment data in a super-constrained timeframe.

Going beyond the problem du jour


Consider-it’s solutions are sustainable. You’ll get more than you bargained for and be well-placed to deal with whatever comes next. Moving to a cloud solution wasn’t viable for this client at the time. However, I built the new solution to support seamless migration from their on-premises solution if or when they’re ready.

Besides solving the problem du jour – enabling my client to expand its services to include multi-class PCF calculation, the new solution also:

  • Ensures that there is no manual intervention after the PCF is created and any adjustments are recorded before your busy morning
  • Facilitates the conversion of stocks – e.g., unlisted securities to cash, rights lines to parent lines, etc.
  • Allows for price adjustments – e.g., if you need to adjust to the TERP for a rights issue
  • Enhances the incorporation of the day’s dividend data, specifically when dividends are paid in a different currency to the stock held in the portfolio
  • Incorporates a new step in the workflow, where the final calculated PCFs are verified against NAV data sourced directly from the investment accounting system
  • Displays options in the PCF when required, whereas previously, all option values were rolled into your cash lines…

… because it can.
And why not pre-empt your client’s next problem du jour before it’s even a problem?

Want smarter, faster ways to collect and transform your data?
 Need help to streamline your workflows to keep pace with client demands?