What manual reconciliation actually involves
Reconciliation is the process of comparing position and transaction data across multiple sources (prime brokers, fund administrators, custodians, and internal order management systems) and resolving any discrepancies before that data can be used for reporting, risk management, or investor communication.
In practice, this means pulling files in different formats, performing comparisons, identifying differences, investigating root causes, and documenting resolutions. For many firms, this happens every morning to ensure investment professionals are working with accurate data.
At smaller firms, the process is manageable, for a while. A few accounts, a few counterparties, someone who knows where everything lives. But as a firm grows, with more strategies, more prime broker relationships, and more investor-specific reporting requirements, the data relationships multiply faster than headcount does. In addition, the cost of the additional headcount often exceeds the cost of leveraging automated tools to perform the function and allow professionals to analyze data.
Most operations teams are still performing these processes through Excel by reformatting columns, writing lookup formulas, building macros that break when a prime broker changes their file format. It works until it doesn’t.
The time cost: what the numbers actually show
The most visible cost is time. But firms tend to underestimate it because the hours are distributed across multiple people and roles rather than sitting in a single budget line.
Consider a mid-sized hedge fund reconciling positions across three prime brokers and a fund administrator. A conservative estimate: two to three hours of direct reconciliation work per day. Across a year, that is 500 to 750 hours, the equivalent of three to four months of a full-time employee’s time, spent on a process that produces no alpha and adds no strategic value.
That estimate doesn’t include month-end close, when every position needs to be verified against the administrator’s shadow NAV before books can be finalized. It doesn’t include the hours spent re-running reconciliations when something looks off, or the time operations staff spend fielding questions from portfolio managers who need a position confirmed before placing a trade.
With T+1 settlement now standard in U.S. equity markets, the window for identifying and resolving breaks has compressed significantly. A break that might have been caught and corrected over two days now needs to be resolved same day. Manual workflows that were already under strain are now operating with less margin for error than ever.
The risk cost: errors, key-person dependency, and operational due diligence
Time is recoverable. Errors aren’t always.
Manual reconciliation is inherently error prone. Formulas break silently and copy-paste mistakes are invisible until they cause a problem downstream. A missed break or a miscategorized position can flow through to an investor report, a regulatory filing, or a risk calculation before anyone catches it.
Another challenge with the manual reconciliation is key-person dependency. When reconciliation knowledge lives in one or two people, held in their macros, their familiarity with which counterparty’s file format changes quarterly, their mental map of which breaks are chronic versus which signal a real problem, the firm is exposed every time someone takes leave, falls ill, or resigns.
This exposure has become a more visible issue as operational due diligence (ODD) has become more rigorous. Institutional allocators increasingly ask detailed questions about data controls, break resolution workflows, and the degree to which operations are automated versus manual. Firms running reconciliation through spreadsheets are finding that the process signals operational immaturity, not because the work is wrong, but because the controls are not there.
Manual vs. automated reconciliation: what actually changes
The table below summarizes the practical differences between spreadsheet-driven reconciliation and a straight-through processing (STP) approach built on a centralized data foundation.
| Area | Manual reconciliation | Automated reconciliation |
|---|---|---|
| Data ingestion | Pulled and formatted by hand each morning | Aggregated and normalized automatically from all sources |
| Break identification | Manual comparison across spreadsheet files | Breaks surfaced immediately with full audit trail |
| Resolution tracking | Tracked in spreadsheets or email threads | Managed in a single system with workflow history |
| Shadow NAV sign-off | Requires full re-verification at month-end | NAV oversight built into the data pipeline |
| T+1 readiness | High pressure: limited time to resolve breaks | Same-day break resolution by design |
| Key-person risk | High: process knowledge held by individuals | Low: repeatable and documented regardless of staffing |
| Operational due diligence (ODD) narrative | Difficult to document and evidence cleanly | Straightforward, evidence-based, auditable |
The distinction matters most not in the day-to-day, but at the margins: during high-volume periods, during staff transitions, and during allocator due diligence.
What this costs your team beyond the spreadsheet
Every hour spent reformatting a prime broker file is an hour not spent improving reporting quality, building data infrastructure, supporting investor due diligence, or analyzing data. The opportunity cost is real even when it rarely shows up in a budget conversation. It compounds quietly: in the projects that never get started, the hires that get made to absorb manual work rather than add new capability, and the allocators who ask hard questions and don’t get clean answers.
Firms that have moved away from spreadsheet-driven reconciliation consistently report the same outcome: their operations teams didn’t shrink, they got redeployed. The same people who spent their mornings chasing breaks are now doing work that moves the firm forward.
The bottom line
Manual reconciliation is one of those costs that feels fixed until it isn’t. The firms addressing it aren’t doing so because they have a reconciliation problem. They’re doing it because they’ve decided that operational drag has a real price, and they’d rather spend that capital elsewhere.
Lightkeeper helps investment firms streamline reconciliation and build the data foundation that makes everything else possible. Contact us to learn how.
