Skip to main content
Better BusinessFeatured ArticlesFinancial Simplicity

Still using spreadsheets for portfolio management? Read this

By 3 March 2022February 27th, 2024No Comments

Hands up all those who use spreadsheets to review asset allocations and portfolio composition. I’m sure I don’t need to tell you how cumbersome and time-consuming of a process that can be, particularly if you’re doing it across multiple client accounts or investment platforms come portfolio review time.

I worked with one firm in the UK that shut its entire practice for two weeks, a few times a year, to rebalance its several hundred client portfolios. I guess they were hoping the markets remained stable in that period!

Begins well with the best of intentions

Does this scenario sound familiar? An enthusiastic and passionate group of professionals launch a financial planning or wealth management business, wanting to deliver a better client experience than they had been able to in prior roles. A quant-minded member of the team develops a calculation model using spreadsheet software. Systems are put into place.

Clients come on board. The business grows and the spreadsheet system is enhanced, modified, and iterated. Spreadsheets are duplicated for new clients. All good so far – a nimble and cost-effective way to get a business off the ground.

The risks, threats, and opportunities of success.

As the business (and the number or complexity of spreadsheets) grows, it starts to become difficult to maintain proper oversight. Essentially this means the business is challenged and constrained in five key areas:

  1. Servicing each client to what is important to them
  2. Business scale
  3. Investment categorisation to suit the firms policies
  4. Operational risk
  5. Uplifting client experience (and vulnerable to disruption)

Servicing each client to what is important to them

As the spreadsheet(s) grow, there is inherently a desire to standardise and have ‘similar’ spreadsheet models for clients. Ultimately this then starts to either:

  • Make the spreadsheet massively complex to suit all clients
  • Make compromises in the interests of the majority, at the sacrifice of what is important to each client. This is the classic and complex resolution of the conflict of service and scale, and spreadsheets are somewhat ill-equipped to be the way to resolve this on an enterprise basis.

Business scale

Let’s get really clear here. Spreadsheets are a great business tool but scaling the use of them to support a mission critical business process with macros and other capabilities is cottage engineering. Regardless how sophisticated the spreadsheets are, there is a point where that sophistication (and in many cases the more sophisticated the quicker this happens) lacks the control measures and reaches a practical limit (in many cases this is PC processing power of memory or just anxiety about the number of unaudited points in a business process) which limits the ability or creates sufficient frictions that limit how the business can grow.

Investment categorisation to suit the firm’s policies

There are also emerging industry challenges to consider as new and emerging asset classes become more important to clients. The universe of investment options is becoming increasingly broad. More investible assets are in a form that platforms (that feed data to the spreadsheets) may not support, such as bitcoin and unlisted property trusts. Hence, more client assets are being held off-platform. In other cases, assets are held across several platforms, sometimes being categorised in different ways, rubbing up against the firm’s or even each client’s investment philosophy.

This implies that firms need to categorise client investments in a way that works for their investment philosophy and specific client base. They need a consolidated, multi-platform (including off-platform) portfolio control system solution, that can tailor to the firm and each individual client.

Operational risk

As the business grows and more spreadsheets are generated, there is a complex web of external data/price feeds and manual data entry tasks to be managed, often without the control checkpoints or measures that clients and regulators would expect. Inconvenience aside, these are material processes, with data entry and operator risks requiring diligent oversight and checks.

Then something happens that the business hasn’t planned for. The team member who created the system leaves the firm. A formula is inputted incorrectly. A data entry mistake impacts a client. Or, simply, the process becomes too big to manage.

Uplifting client experience

And while customer relationship management systems have some capability, the client-facing team gets to a point where the manual spreadsheet approach limits the ability to adequately communicate the firm’s investment identity and deliver the desired customer experience, not to mention the compliance and management oversight getting nervous.  The unique investment philosophy the group of likeminded professionals launched the firm with has become somewhat diluted, if not polluted. Clients are being offered and exposed to better more intuitive client reporting and portfolio insights by technological savvy competitors and disrupters.

New generation – portfolio control system solutions

The spreadsheet system that made sense to a firm when it was starting out may no longer meet these client service and control system challenges as it grows. Fortunately, as with so many other industries, technology advancement is providing viable solutions. Of course, the right control system approach will (and should be) interpreted and implemented slightly differently for every firm, and any system that wants broad market take up must recognise this.

To help you evaluate various solutions to replace your manual-based processes for reviewing asset allocations and portfolio composition, here are some key functionalities the control solution should support.

Flexible categorisation:  of each investment security to fit into the firm’s investment thesis or philosophy. A classic example is the categorisation of iShares S&P 500 ETF on the ASX (code IVV). Some may mark it as “Australian equities” as it is on the ASX, but in reality, it is an international equities security.

On-platform or off-platform assets:  with the growth of off-platform assets the solution should cater seamlessly for both.

High degree of configurability: to support the specific investment propositions to suit the identity, philosophy, or value proposition of each adviser or firm. For example, Firm A’s core thesis and approach to investment may be about a set ratio of growth and defensive assets for different investing risk profiles, while Firm B’s core thesis may be specifically around asset allocation across multiple asset classes in a proportion to suit various risk profiles, for which they form their own views as to which investment fits into each bucket (like the IVV example above).

Purpose-built and designed: to enhance an adviser’s or wealth manager’s service proposition by recognising the unique nature of each investing client without having to do iterations of “pre-trade compliance” workflows, saving time, effort, and costs to service more clients. It is more about addressing the ‘why’ for clients and their advisers than the ‘what’ of investments.

Let’s face it, growing an advice or wealth management business built around spreadsheets is highly problematic and certainly has risks. Human errors, staff changes, and the sheer number of hours it takes to monitor, review and rebalance even a single portfolio mean unnecessary costs and risks, not to mention an inability to quickly evaluate and respond to changes in the market.

These may have been acceptable trade-offs in the past for businesses just starting out. But there no longer needs to be a compromise. Best-practice businesses get to a point where they must use technology to reduce or eliminate manual processes. By having a reliable system that is designed around the ‘why’ of each client in place that uses defined and repeatable processes for reviewing client portfolios, firms can better deliver on whatever their unique philosophy is and demonstrate real value for clients.


Stuart Holdsworth

Author Stuart Holdsworth

Stuart has over 30 years of experience in the use of technology for the strategic competitive advantage of businesses in the financial markets and investment industry.

More posts by Stuart Holdsworth