Portfolio accounting and investment reporting
In this post, I’ll describe our approach to making it possible and easy for any member of the team at Titanium Birch to find insights in data about our investment portfolio. I’ll cover why I think that’s important, what use-cases I envision, what we’ve learned in researching solutions, and how we’re now going about solving the challenges.
Hopefully, this will be useful to other investment firms and interesting to potential job candidates.
Motivation and principles
Through my experience as an entrepreneur, I’ve come to believe in the following principles or approaches:
Make facts broadly available to anyone in the team. Resist the formation of silos.
Centre discussions on shared understandings of data, devise hypotheses, and let reality quickly catch up to disprove our hypotheses.
Keep toil to a minimum. Work is more fun when we’re not forced to be busy all the time. It’s more fun to build and watch over robots than to do a robot’s job.
I believe that this will help us attract and retain top talent, make better decisions, learn faster (especially when data show that we’re wrong), work better as a team, etc. Therefore, I think it’s important that facts about our investment portfolio are easily accessible to anyone on the team.
The vision: Empower our team to explore accurate, detailed, and timely data about the portfolio
I imagine anyone being able to answer the following types of questions about the portfolio:
What is its total value? That seems like a simple question, but it can be complex when investments are held across a very wide range of asset classes and holding structures. Answering this question requires data aggregated across all banks and brokers and directly-held investments across all holding companies worldwide, as well as correctly accounting for transfers, loans, currencies, etc.
How has its value changed over time, and how do those changes break down into parts? Such questions about investment returns also require us to distinguish between price-changes and fees/expenses.
How do actual fees paid compare to our expectations? It’s easy for fees from banks, brokers, and investment funds to add up to significant amounts, and it’s also possible for mistakes or misunderstandings to come up. That means it might not be enough to just accept all fees as a given, netted out in statements. Instead, we sometimes may want to write journal entries stating what we think the fees should be, then reconcile our general ledger against the statements.
What risks are we taking? How does that compare to what we said were the risks we wanted to take?
And many more. The examples above already help clarify some of our needs.
Finding the answers requires these types of data:
All positions in the portfolio and their changes over time, collected across all banks, brokers, funds, and direct investments.
Prices.
Metadata about positions, ranging from facts like coupon and dividend schedules to opinions such as about risk factors.
Our understanding of contract terms, for example transaction fees, custody fees, management fees, carry, etc. for various combinations of counterparties and investment vehicles.
Many of the questions also require the data being presented in the form of consolidated financial statements (as Jamie Dimon called it: “the language of business”).
The data should be:
Accurate.
Detailed. We’d like to break down changes in NAV into fees and price-changes, per individual position, and tag positions flexibly, such as by risk factors.
Timely. Minimise delays between something happening in the real world and our data reflecting it.
Efficiently produced. We’d like colleagues to be able to explore to their heart’s content without having to consume time from other colleagues.
Lastly, to get value out of the data: our entire toolchain across the firm should be able to access and enrich the data. Team members should be able to hunt for insights using web-based analysis tools. AIs should summarise and explain, Slack channels should receive push notifications, etc.
Challenges
As we set out to realise this vision in the summer of 2023, we started by speaking with other investment firms (especially those that invest across a wide range of asset classes like us) to learn about their challenges and solutions. We also reached out to many vendors in this space and evaluated their solutions. Surprisingly, we couldn’t find a firm that had realised all parts of the vision, and we couldn’t find any single vendor to meet all of our needs. Clearly this is difficult. Is my “letter to Santa Claus” worth pursuing? Yes, I think so, because of the “motivation and principles” above.
From our discussions with other investment firms, we noticed the following areas where they weren’t quite satisfied with their solutions:
Duplicated effort across investment reporting and accounting. That leads to higher costs (sometimes even departments with multiple people) and challenges in reconciling the two. When there are two different systems stating the total value of the portfolio and they’re stating two different numbers, how do we find out why they’re different and decide what to trust?
Vendor lock-in and limited ability to satisfy use cases that weren’t addressed by a given vendor. A common issue was how data was hard to export from a vendor’s product. Sometimes there were no APIs, and even the “click to export a CSV” features were limited.
Long delays between an event happening in reality and it being made visible in the data accessible to team members, sometimes more than a month. That reduces the value of the data.
Why? What’s so difficult? We noticed these challenges:
Getting the data.
Banks and brokers often don’t have APIs. Instead, many of them deliver data via small CSV files incrementally each day via SFTP. The recipient then needs to concatenate the daily data, learn to understand each bank’s schema, deal with the administrative overhead of SFTP, and somehow find data for time periods prior to the SFTP feed going live.
For investments outside of banks and brokers, the data-delivery mechanisms aren’t standardised at all.
For private-market funds, they deliver unstructured PDFs a few times per year, often with complex issues such as recallable distributions that require careful accounting treatment. There are vendors to help collect and process them.
For direct investments into startups, data is ad-hoc; a friend of our firm (ExpressVPN alumnus Ricardo Mota) is solving problems in that space through his startup VerifiedMetrics, though many challenges remain.
Reconciling. Let’s say we get all the data and sum it all up. How do we know the total is correct? That requires reconciliation, and double-entry bookkeeping is one well-established way of doing that. Using which accounting software? Xero and Quickbooks struggle to consolidate multiple entities, their APIs have gaps, they’re vulnerable to phishing (no SSO at time of writing), and other challenges abound.
Automating the processing of all data. For many processes, most vendors still relied on CSV uploads (and thus human involvement).
Protecting the data. In asking vendors to walk us through how we could become comfortable to trust them with our data, we were generally left with many concerns.
We evaluated a wide range of vendors and roughly classify them along these lines:
Investment-returns only, no accounting. These services get data from banks and brokers and aggregate them into reports showing investment returns. To create journal entries and financial statements, we’d need to export the data and then pass them to an accounting team that would use a separate software, effectively creating two silos of data and duplicating some effort. We heard from other firms using this approach that it can be challenging to keep the two silos reconciled.
General ledger plus investment returns. These services also ingest data directly from banks and brokers. They do the accounting, then base investment reports on reconciled data. That approach makes sense to us. However, we couldn’t find a vendor in this category that could give us sufficient control over our own data, or sufficient flexibility to automate workflows and feed data in and out. The APIs generally weren’t full-featured enough.
Self-contained end-to-end solutions, often specifically for family offices. They ingest data directly from banks and brokers, offer managed services to also ingest data from private-market investments, do the accounting first, then run investment reports. However, we found they lacked flexibility in control of our data. The APIs didn’t meet our needs.
Our strategy for a solution
We’ve adopted these approaches:
Do the accounting as early as possible. Reconcile our expectations against statements from counterparties, then have a single source of truth. Then base investment reporting and risk management on reconciled data.
Take possession of the data ourselves. Then we grant access to various vendors/systems where needed, as opposed to the vendors getting the data and selectively letting us export them.
Buy where practical, then build the glue ourselves as necessary. Rather than buying a single solution to meet all of our needs, we’re working towards a mosaic of several specialised products, integrated with “glue” that we’ll build ourselves.
Blog and open-source. We plan to blog and open-source much of our work and hope to be useful to other investment firms.
Continually exchange notes with others: Not only with other family offices and investment firms, but also with Singapore-based tech companies working to solve problems in this space.
Architecturally, we’re working towards these building blocks:
Getting the data:
From banks and brokers: The mechanics of getting data via SFTP turned out to be rather complex. Some push, some pull, some zip, some PGP-encrypt, some reuse filenames, and the file formats are very varied. We built a cloud-based system to handle those complexities and make the data available for downstream systems to process. We’ll blog about this and open-source it later this year.
Directly held funds and other investments: These usually deliver PDFs on ad-hoc schedules in unstructured formats. We’re currently handling them ourselves through a human workflow based on a ticketing system. We’ll be evaluating vendors to outsource this.
Generating journal entries and then reconciling balances and positions against statements: We need a general-purpose general ledger.
This GL must meet these criteria:
Can be entirely driven by APIs
Handles multi-entity consolidation
Protects us from phishing
We’ve found a product that meets most of our needs, except it is not yet phishing-resistant. Once it is, we’ll publicise our usage of their product and recommend it to others. We’re far enough along to be using it for all of our Singapore 2023 audits already.
Later this year we’ll go shopping for solutions specifically for investment reporting, risk management, and order management based on top of our reconciled data.
Next steps
This post is meant to give a big-picture overview of what we’d like to achieve in the domain of reporting (a single source of truth) and how we’re going about addressing our needs. It’ll provide context for future follow-up posts on more detailed topics.
Further reading:
TJ discusses standardising disparate data feeds for investment reporting and accounting.
Disclaimer: The content in this blog post should not be taken as investment advice and does not constitute any offer or solicitation offering or recommending any investment product.
