Can we build a scalable hedge fund allocation engine in one hour using an Excel spreadsheet?

Spreadsheet using Pebble Stream to perform hedge fund tax accounting

At Georgetown Software House, we build modern accounting systems. Many of the systems we create replace legacy systems. These legacy systems generally suffered from one or more of what we’ve coined as the Seven Failures of Enterprise Accounting Systems:

1. Poor performance — the system is slow at processing or fails to process large jobs.
2. Not auditable — the system cannot clearly explain why it produced a given result.
3. Nondeterministic — At different times, the same system release can produce different outputs given the same inputs.
4. Lack of transparency — the system’s accounting logic is in computing languages that accountants need developers’ help to understand.
5. Inflexible — The system cannot efficiently execute new business logic or cannot tailor business logic given various criteria.
6. Cannot perform partial calculations — all input data is required to produce a meaningful result.
7. Limited deployment options — cannot be gracefully deployed as a desktop, web service, or high-performance cloud computing application using the same code base.

In a later blog post, we will dive deeper into the Seven Failures and how to address them properly; however, most of you are almost certainly experiencing several of these failures in the enterprise system you use today.

One prevalent way to address these concerns is to use Excel. Accountants often turn to Excel to patch the broken functionality of legacy accounting systems. Analysts often use Excel to manage inflexibility in Enterprise Resource Planning (ERP) systems, and accountants primarily use Excel to cover missing accounting system capabilities.

The reason for using Excel is apparent. Spreadsheets are perfect for performing ad-hoc calculations. Accountants without programming savvy can quickly solve computational problems using a simple spreadsheet. Spreadsheet models are a white box to accountants. Contrast this against the black box nature of terse computer code. Spreadsheets can perform partial calculations and automatically update whenever data changes are detected. A spreadsheet’s data-friendly presentation can make logic issues more apparent. When you disregard confinement to the desktop and scalability limitations, spreadsheets represent a perfect computation platform.

What if you could solve the deployment and performance problems of desktop-bound spreadsheet applications like Excel? Would that make the spreadsheet the perfect platform for creating backend accounting processes? At Georgetown Software House, we answered that question by inventing a lightweight spreadsheet library embeddable in enterprise applications. It is fast and deployable in web servers and browsers, cloud functions, containers, clusters, and desktops, and it is available for the .Net, JavaScript, and Java programming platforms.

And it’s compatible with Excel. We call it Pebble Stream.

With a spreadsheet engine like this at your disposal, what could you do with it?

This week, I decided to push the envelope of spreadsheet computing with Pebble Stream. In 2018, my team built a prototypical hedge fund allocation engine to replace a legacy tax accounting system. The proof of concept took two developers three months to build. We designed the prototype from the inception to process the largest hedge fund clients’ organizational structures. These structures contained thousands of partnerships. At that time, the legacy system took 24 hours to process structures of that size. Our prototype took minutes to process the same. Could I now create a hedge fund accounting spreadsheet in Excel, run it on the Pebble Stream spreadsheet engine, and quickly process similarly sized structures on my laptop?

Before I tell you what we discovered, I want to describe the process of creating that spreadsheet. I spent about an hour on the task, designing five dependent worksheets in my spreadsheet.

Two of the worksheets were input sheets. One of the input sheets contained information describing the organizational structure and the capital activity of all the partners in the structure’s partnerships. The second input worksheet detailed the direct income for each of the structure’s partnerships.

Two other worksheets performed the income allocation calculations using sharing percentages derived from the relative capital positions of partners at the beginning of the accounting period. These worksheets expanded the organizational structure to uncover all possible income paths, a computationally heavy task.

The final worksheet performed verification checks designed to detect logic failures. Was all the income accounted for? Did any partnerships produce more income than possible? Did every partnership’s calculated partner ratios add up to 1?

I then generated inputs simulating a large hedge fund. This organizational structure was comparable to the ones we created for our prototype. I processed this hedge fund using the Pebble Stream spreadsheet engine.

The Pebble Stream spreadsheet engine produced full tax accounting detail, generating all possible income pathways resulting in over 15 million allocations, way more than Excel’s 1 million row limit. It took 7.25 minutes to complete. All verification checks passed.

A startlingly good result!

Let’s recap:

• With years of development using traditional databases and large enterprise servers, the legacy system took 24 hours to process a large hedge fund structure.
• The prototype, handwritten in 3 months by two Georgetown developers and explicitly designed to process large hedge fund structures using cloud computing clusters, ran in 3.5 minutes, a many-fold performance improvement.
• An Excel spreadsheet, written in two hours and run on a single Pebble Stream spreadsheet engine on my laptop, produced 15 million rows of fully traceable allocation detail in 7.25 minutes.

How is this impressive result even possible? One reason is that we optimized the Pebble Stream spreadsheet engine to perform spreadsheet calculations at scale. It can also perform graph data transformations, a neat trick to have up your sleeve when processing organizational structures.

The other reason is that expressing accounting models in Excel is much easier than defining them in computing code, hence the short development time. Yet another reason is the complexity of deploying an enterprise app in the cloud versus running a lightweight Pebble Stream process on the desktop.

But, a significant reason is the massive knowledge transfer impedance that all accounting and finance projects must overcome. It takes time for a subject matter expert (SME) to explain how hedge fund accounting works to a programmer. This knowledge transfer time often dominates the overall time to complete a computing project. This impedance collapses to zero when the SME is Excel savvy and can rely on Pebble Stream to run her model.

This breakthrough result is just the beginning of a new computing revolution. If we can do this on a laptop, imagine what is achievable by simultaneously executing hundreds of lightweight Pebble Stream spreadsheets in the cloud!

Curious about what this spreadsheet looks like? Would you like to learn more about Pebble Stream? Reach out to us at www.pebblestream.com/contact. We will happily show you how to turn your Excel-savvy SMEs into 10x developers.