Cash Positioning: From Spreadsheets to Systems
How to migrate from manual cash positioning to automated daily visibility, and what to do when your TMS isn't delivering the full picture.
Which one are you?
Before we talk about the path forward, it helps to be honest about where you're starting. In our experience, treasury teams pursuing better cash visibility fall into two distinct camps, and the right approach depends entirely on which one you're in.
You don't have a TMS yet
Your team downloads CSVs, PDFs, or Excel files from bank portals every morning. Cash positioning lives in a spreadsheet, maybe a sophisticated one with tabs per entity and formulas that only one person understands. Someone is logging into multiple portals, copying numbers, and trying to reconcile before the 10am meeting.
This is more common than people admit. And it's the more straightforward path to fix, because the transformation is dramatic. A well-implemented TMS replaces hours of daily manual work with automated ingestion, rules-based categorization, and reporting that updates without anyone touching a spreadsheet.
You have a TMS, but you're still in Excel
You have a treasury management system. It's live. Bank statements flow into it. But when it comes time to actually build your daily cash position, you export the data out of the TMS into Excel and do the real work there.
The system has become a bank reporting consolidation tool, which still saves time, but it's not delivering the categorized, actionable visibility it was purchased to provide. In our experience it usually happens when the team inherited a configuration it didn't design, or when reporting was planned for a later phase and other priorities took over.
- 62% of treasury professionals say cash or liquidity forecasting is their most challenging task AFP, 2025 Treasury Benchmarking Survey
- 54% of organizations rank cash forecasting among the three areas where they spend the most time Strategic Treasurer & TD Bank, 2024 Treasury Perspectives Survey
- ~80% of treasury professionals polled still rely on manual or fragmented systems TD Bank poll of AFP 2025 conference attendees
A TMS that's only consolidating bank statements has more to give than it's being asked for. The gap between "we have a TMS" and "we're using a TMS" is where most of the unrealized value sits.
Path A: From manual to automated
If your team is still downloading bank statements and building cash positions in Excel, the transformation a TMS provides is significant. But it only delivers if the implementation addresses the right things from the start.
The core of what changes is simple: instead of pulling data from portals and assembling it manually, the TMS ingests bank files automatically and applies rules to categorize every transaction. What used to take hours of copy-paste becomes a dashboard that refreshes on its own.
Understanding your bank files
Treasury teams typically receive bank statement data in one of three standard formats: BAI2, ISO 20022 XML (camt.053 for end-of-day statements, camt.052 for intraday), or the legacy SWIFT MT94X family (MT940 for prior-day, MT942 for intraday). The ISO standard carries richer, more structured data fields that make downstream categorization easier, and is replacing the MT formats. SWIFT ended MT and ISO 20022 coexistence for cross-border interbank payments in November 2025. Statement messages such as the MT940 and MT942 are still supported, and SWIFT plans to end their coexistence with its November 2028 standards release.
Each of these file types arrives with structured information that teams often under-use. BAI2 files include standardized bank transaction codes that describe the nature of each cash flow: wire transfers, ACH credits, lockbox deposits, returned items. The transaction detail and comment fields carry additional context like counterparty names, reference numbers, and remittance information. This is the raw material that makes automated categorization possible.
Rules-based categorization
Once the files are ingested, the TMS applies transaction-based rules to sort each line item into cash flow categories. These rules typically key off a combination of the bank transaction code and information in the detail fields. A wire credit from a known counterparty might auto-categorize as "customer collections." An ACH debit matching a payroll pattern categorizes as "payroll disbursement."
The value here is consistency. A manual process depends on whoever is building the spreadsheet that morning to make the same categorization decisions every time. A rules engine does it the same way, every day, across every account.
Virtual bank accounts for smarter categorization
When the information in your bank statement comment fields isn't granular enough for accurate categorization, virtual bank accounts offer a powerful alternative. A virtual account is a sub-ledger that sits within your primary physical bank account. It carries its own unique account number, but all funds settle into the parent account.
The practical benefit for cash positioning is straightforward: the virtual account number typically comes through on the bank statement as part of the transaction record. On the TMS side, you can use that number as a categorization key. If you've structured your virtual accounts around business lines, payment types, or counterparty groups, then every transaction that routes through a specific virtual account is automatically categorized based on that account's purpose, regardless of how limited the original payment detail might be.
This is particularly useful for receivables, where incoming payments often arrive with minimal remittance detail. Instead of trying to parse inconsistent comment fields, you route collections through designated virtual accounts and let the account number do the categorization work.
Positive pay files as forecasting tools
Here's a technique that gets overlooked: if your organization generates positive pay files for your bank (the check issue files used for fraud validation), those same files can be imported into the TMS as a cash positioning forecast. The positive pay file lists the check number, issue date and amount of every check you've issued, plus the payee name if you use payee positive pay. Load it as a forecast, and your TMS now knows which checks are outstanding.
When the bank statement arrives and a check clears, the TMS matches it against the forecast reference. The check you were expecting to clear is confirmed. For every day it doesn't clear, you roll the forecast forward. Over time, this gives you a tighter picture of your outstanding disbursements and a more accurate current-day position, using data your organization is already generating for a completely different purpose.
Path B: Unlocking what you already have
If your team has a TMS but still relies on Excel for the real cash positioning work, the answer isn't always "use the TMS more." Sometimes the system genuinely can't do what you need. Sometimes it can, but that part was never configured. Knowing which situation you're in is the first step.
Your Excel needs are legitimate
Some cash positioning work genuinely belongs outside the TMS. If you're running scenario analysis, toggling between base case, worst case, and stress liquidity scenarios, or applying factors to model different outcomes, a spreadsheet's flexibility is hard to replace. You may need the ability to save snapshots of different cases and compare them side by side. For this kind of work, a well-built workbook is often the faster and more flexible tool.
In this case, the priority is to eliminate the manual export, not the spreadsheet itself. The goal is a seamless data feed from the TMS into your working spreadsheet so that the foundational numbers (account balances, categorized flows, transaction details) refresh automatically. The most convenient method is an API connection that lets you pull data without logging into the system. If your TMS doesn't support this, or makes it hard to maintain, that's a legitimate reason to evaluate systems with more flexible data access capabilities.
The system can do it, you just don't know how
In our experience, this is the most common scenario. The TMS has a reporting module. It came with the license. It may not have been fully configured yet, often because reporting was planned for a later phase, or the people who understood the configuration have since moved on. The current team inherited a system they weren't trained on and defaulted to what they know: export and pivot in Excel.
Most TMS platforms include reporting modules that range from template-based report builders to full database access with custom query capabilities. Some allow you to write reports in SQL or similar languages. Others provide drag-and-drop interfaces with pre-built templates covering standard use cases. Some allow you to join different data sources within the system to create views unique to your organization. The differences between platforms are pronounced in this area. It's one of the features that most clearly separates systems in the market.
The system genuinely can't do what you need
Sometimes you've done the diligence and the reporting module simply doesn't offer the pivots, views, or calculated fields your cash positioning process requires. The templates don't fit your process. The query access doesn't reach the data you need. The output format doesn't match what your team needs to make decisions.
This is a harder conversation because it potentially means evaluating a new system. But it's important to have it honestly. A TMS you can't report from effectively is a TMS that will always be supplemented by manual processes. That supplementation creates risk, adds time, and means you're paying for capabilities you aren't using.
- 49% of organizations rank cash positioning and reporting among the three areas where they spend the most time Strategic Treasurer & TD Bank, 2024 Treasury Perspectives Survey
The question isn't whether your TMS can do it. It's whether anyone configured it to.
The journey: prior day, intraday, forecast
Regardless of which path you're on, the sequencing of automation follows the same logic. Start where the data is richest and the wins are fastest.
Start with prior-day postings
Prior-day bank statement files are the foundation. They're the most reliable, the most data-rich, and the quickest win. These files, typically delivered overnight, contain the complete record of the previous day's activity with full transaction detail. Getting the ingestion, categorization, and reporting right for prior-day data is the first milestone, and it's where most of the value sits for teams coming from manual processes.
Add intraday visibility
Once prior-day is solid, intraday reporting tightens the real-time picture. Intraday files (camt.052, or the legacy MT942) don't always contain the full detail of a prior-day statement. Transaction descriptions may be truncated, codes may be less specific. But they give you a view of what's happening today. Many organizations receive a single intraday file, but depending on your cash positioning needs, receiving multiple intraday updates can significantly improve the accuracy of your current-day view. The frequency depends on your operational reality: some treasury teams need hourly updates, others are fine with one mid-morning pull.
Layer in forecasting
With a solid base of categorized historical data, teams can begin forecasting. This is its own discipline, and a guide unto itself, but the foundation matters. If your TMS has been properly categorizing transactions for months or years, you have a dataset that's valuable for identifying seasonal patterns, building trend models, and validating forecast accuracy.
Some teams export historical TMS data, process it externally, and re-import the resulting forecasts back into the system. Others feed the data into a data lake alongside historical forecasts and build trend analysis using BI tools. The right approach depends on the analytical capabilities of your TMS and the infrastructure your organization already has in place.
Twelve-month forecasts remain hard to get right, and teams with clean, categorized history are better placed to improve them.
Where to start
Whether you're preparing for your first TMS implementation or re-evaluating an existing system, these are the questions that determine what "better" looks like for your organization.
Evaluate your current state
How much time does your team spend building the daily cash position?
Quantify the manual effort. Include portal logins, downloads, copy-paste, reconciliation, and formatting. This is your baseline, and the most tangible measure of what automation replaces.
What file formats are your banks sending you today?
Understanding whether you're receiving BAI2, camt.053, or legacy MT940 files, and how complete the data is in each, determines how much categorization the TMS can automate. If you don't know, that's worth finding out before any project kicks off.
If you have a TMS: what are you using it for, and what are you exporting to Excel?
Map the boundary. Where does the TMS stop being useful, and where does the spreadsheet take over? That boundary tells you whether the gap is configuration, training, or a genuine system limitation.
Who configured your current system, and are they still here?
Institutional knowledge walks out the door with every departure. If the people who built the original configuration are gone, there's a good chance that workarounds have calcified into "the way we do things," and that untapped features are sitting dormant.
Are you generating positive pay files or using virtual accounts?
If so, you already have data assets that can improve categorization and forecasting. These are often low-hanging fruit that get overlooked because the teams generating them don't think of them as TMS inputs.
Evaluate your system
What reporting capabilities does your TMS actually have?
Not the full product catalogue. What's licensed and switched on in your environment. Template-based builders, SQL access, custom query tools, cross-module reporting, scheduled report delivery. Know what's in the box before you decide it's not enough.
Can you get data out via API?
If your scenario analysis and modeling legitimately belong in Excel, the critical question is whether the TMS can feed those spreadsheets automatically. API connectivity is the gold standard. Scheduled file exports are the fallback. Manual downloads are what you're trying to eliminate.
What enrichment would improve your categorization?
Work backward from the cash position view you want. If a category is too broad, trace it back to the bank file. What additional fields or codes would allow tighter categorization? Sometimes the answer is a conversation with your bank, not a system change.
Start with the questions. The right system follows from honest answers.
A note on this guide
This guide is intentionally vendor-agnostic. The principles apply whether you're evaluating your first TMS, optimizing an existing implementation, or considering a migration. The specifics, which system handles reporting how, which vendor supports what connectivity method, require context about your organization. If you'd like to have that conversation, we're here for it.