Migrating from Spreadsheets to a PMS: A Step-by-Step Plan

Migrations rarely fail because the software cannot do the job. They fail because nobody agreed what the data meant, opening balances did not reconcile, and four weeks after go-live half the team is still updating the old spreadsheet "just in case".

This is a sequence that avoids that. It assumes a shared-living operator with beds rather than units, real arrears, and compliance documents that matter. Scale the effort to your portfolio, but do not skip steps.

Step 0: Confirm you actually have a spreadsheet problem

Spreadsheets are excellent, and plenty of operators run 60 beds on them perfectly well. The honest triggers for moving are:

• Reconciliation time. If closing a rent cycle takes more than a day of someone's life every month, you are paying for a PMS already, in salary.
• Key-person risk. One person understands the workbook. If they leave, you lose your operation, not just a file.
• Arrears latency. You find out about non-payment two weeks late because the bank statement has to be manually matched.
• Concurrency. More than two people need to change the same data on the same day.
• Compliance expiry. Certificates, licences and Right to Rent follow-ups live in a calendar that nobody owns.
• Bed-level anything. Per-bed occupancy, per-bed pricing or per-bed reporting is where spreadsheets genuinely stop scaling.

If none of these are true, save your money. If three or more are, keep reading. If you are still at the shortlist stage, the shared living software comparison covers selection; this post assumes the decision is made.

Step 1: Inventory your sources before you inventory your data

Before you touch a field mapping, list every artefact that currently holds operational truth. In practice this is never one workbook. Expect to find:

• The master tenancy tracker
• A separate arrears or payments sheet, often maintained by a different person
• A deposit register, sometimes only in the deposit scheme's own portal
• A maintenance log, often a WhatsApp group
• A compliance folder in Drive or Dropbox, organised by whoever created it
• A viewings and enquiries sheet
• One or two shadow copies that someone made "temporarily" in 2024

For each, record: who owns it, how often it changes, whether it is a source of truth or a derived view, and whether anything downstream depends on it. Derived views do not migrate. They get rebuilt as reports.

Step 2: Agree the data model, and make the bed the atomic unit

This is the decision that determines whether the migration is worth doing. In shared living, the unit of inventory is the bed, not the property and usually not the room. If you migrate a property-level model into a new system you have bought a more expensive spreadsheet.

A workable minimum model:

Property > Unit / Flat > Room > Bed
Bed --< Agreement >-- Resident
Agreement --< Charge schedule --< Invoice --< Payment
Resident --< Documents, Right to Rent checks, deposit
Property --< Compliance certificates, licences

Two rules that save pain later:

1. Bed identifiers must be stable and unique across the portfolio. "Room 2" exists in forty properties. Use a scheme like ASH-01-B2 and never reuse an identifier after a reconfiguration.
2. A resident is a person, not a tenancy. The same person taking a second room next year should be one resident record with two agreements. Getting this wrong destroys your renewal and lifetime-value reporting on day one.

If you are moving from a property-level to a bed-level model, our post on migrating from per-property to per-bed inventory goes deeper on the restructuring itself.

Step 3: Clean before you migrate, not after

Every hour spent cleaning before import saves several after. Run these checks against your source data and fix them in the spreadsheet, where fixing is cheap.

The rent field is the one that quietly wrecks migrations. If half your portfolio is bills-inclusive and half is not, and both are stored as a single number, no report you build afterwards will be comparable. Split it now.

Step 4: Decide how much history to bring

The instinct is to migrate everything. Resist it. History multiplies the migration effort and almost never gets used.

Migrate:

• All beds and property structure
• All open and future-dated agreements
• All current residents
• Opening balances as at the cutover date (arrears and prepayments)
• Deposits with scheme references
• In-date compliance certificates and licences
• Right to Rent checks with their follow-up dates

Do not migrate, archive instead:

• Closed agreements from prior years
• Line-by-line transaction history predating the cutover
• Historic maintenance tickets that are already resolved

Freeze the old workbook as a read-only archive with a clear name and date, and tell everyone that is where history lives. You will look at it perhaps four times in the first year.

The one common exception: if you need prior-year data for investor reporting or a tax position, export it once into a flat archive file rather than importing it as live records.

Step 5: Opening balances, the part that actually decides success

Pick a single balance date. Every resident gets one number as at that date: the amount they owe you, or the amount they have paid in advance. Not a transaction history, one net figure per resident per agreement, split by charge type if your system supports it.

Then:

1. Produce the balance list from your ledger as at the balance date.
2. Reconcile the total against your bank position and your arrears report. These will not agree the first time.
3. Investigate the difference item by item. Unallocated payments, forgotten credits and duplicate charges all surface here.
4. Get a named person to sign off the final list. Ideally whoever owns the P&L.
5. Import that signed list, and nothing else, as opening balances.

Do not begin cutover until step 4 is done. A migration that goes live with unreconciled balances generates arrears chases against residents who do not owe money, which costs you far more in trust than the delay would have. Whatever system you are moving to, confirm before cutover exactly how it wants opening balances loaded, since rent and payments handling differs meaningfully between platforms.

Step 6: Choose the cutover date deliberately

Three rules:

• Align to the start of a rent cycle. Cutting over mid-cycle means splitting invoices across two systems, which is the worst of both worlds.
• Avoid your peak turnover window. For PBSA and student housing, that rules out the whole late-summer intake. For coliving and flex, avoid whatever your seasonal peak is.
• Leave a buffer before a reporting deadline. Never cut over the week before a quarter end or an investor report.

Write a dated runbook for the cutover itself: who freezes the spreadsheet and at what time, who runs the import, who validates, who has authority to call a rollback, and what the rollback actually is. Half a page is enough. The value is in having agreed it in advance.

Step 7: Validate against numbers you agreed beforehand

Write your validation checks before the import, and record the expected value for each. Checking afterwards against whatever the new system says is not validation, it is confirmation bias.

Every one of these should reconcile exactly. "Close enough" on bed counts on day one becomes a wrong occupancy rate in every report for the next two years.

Step 8: Run a shadow cycle, not a parallel run

Conventional advice says run both systems in parallel for two or three months. In a small operations team this is usually a mistake: dual entry is expensive, error-prone, and it guarantees the two systems diverge, at which point you have to reconcile the reconciliation.

A better version: run one rent cycle end to end in the new system, and shadow-reconcile it against a manual calculation. Compare the invoice run, the collection, and the closing arrears figure. If those three agree, you are done. If they do not, you have found a real configuration problem in week one rather than month three.

Step 9: Turn the spreadsheets off, properly

Set a date, then actually revoke edit access on the old workbooks and make them read-only. Announce it. If the old sheet stays editable, someone will keep it updated, other people will start trusting it, and within a quarter you will be running two systems and paying for one.

Expect one round of complaints in the first fortnight. Most will be genuine gaps in training or configuration rather than reasons to go back. Log them, fix them, and hold the line on the shutdown date.

Illustrative effort by portfolio size

The following is a planning heuristic, not observed data. Treat it as a starting point for your own estimate.

The variable that drives this is not bed count. It is the number of distinct source files and the number of people who have been editing them, because every additional source is another reconciliation.

What to watch in the first 30 days

• Rent run accuracy. Invoice count and total, checked against expectation before sending.
• Payment matching rate. How much still needs manual allocation. This should fall week on week.
• Arrears movement. A sudden jump usually means a configuration issue, not a collections issue.
• Staff workaround count. Every time someone exports to Excel to do a task, write it down. That list is your configuration backlog.
• Compliance alerts firing. Confirm that expiry reminders actually reach a human.

If you want a structured version of this with owners and dates against each phase, our 30-day PMS deployment guide lays it out as a schedule, and the coliving software buyer's guide covers the selection decision that comes before it.

The migration itself is mostly your work, not your vendor's. Pick a vendor who will tell you that honestly, insist on a sandbox import before cutover, and get your opening balances signed off by a person whose name is on the P&L.