Moving a spreadsheet into a real system without losing the numbers

The spreadsheet is the specification, and it is usually more accurate than any document written about the process. Its columns are the fields, its formulas are the business rules, and the column somebody fills in by hand every week is a feature nobody has built yet. The migration works when you read those rules out, clean the data before importing it, and reconcile the result against a printed figure before anyone switches over. It fails when the file is treated as a format to convert: you move the numbers and leave the rules behind.

Why is the spreadsheet the specification?

Because it survived contact with reality. Whatever document describes how the process should work, the file describes how it does work, including the exceptions that happen every month and the column added before the season. A requirements document that contradicts the file is wrong, and finding that out costs less now than after go-live.

Read the file at two levels:

• The formulas are the rules. A column multiplying quantity by unit price with a conditional discount is a pricing policy in a language nobody documented.
• The columns added late are the gaps. If "notes" sits at the far right and everyone writes the same phrase in it, the system is missing a status field.

How do you read the rules out of the file?

Open it with the person who uses it, and do not let them tidy it first.

1. Which sheet is the real one? Almost always a second one the team trusts, while the first is shown to visitors.
2. Which formula does everyone rely on and nobody understand? If it has an IF nested three deep, it is three rules.
3. Which cells do people overwrite? A formula replaced by a typed value is an exception. Write down three real examples.
4. Which colour means something? Conditional formatting is often the only status system in the file.
5. What breaks when the rows are sorted differently? Order-dependent logic is a hidden running total.
6. What does the note in the corner say? "Check with Karim" is a requirement.

Record the answers beside the file, in the repository, before writing an import script. That record is the acceptance test.

What has to be cleaned before anything is imported?

Cleaning is not cosmetic. Each of these produces a wrong figure, and each is easier to find in the old file than in the new one.

What you find	Why it hurts	What to do
Duplicate customers	Two balances, neither complete	Merge, keep one identifier
Dates stored as text	Sorting and ageing break	Convert, verify a sample
Total row inside the data	It imports as a record	Remove, rebuild as a formula
Mixed units in one column	Quantities cannot be summed	Convert to the base unit

Two rules make the rest manageable. Freeze the source: take a copy, mark it read-only, and work only on the copy, so the numbers you validate are the numbers you import. Then clean reversibly — flag a bad row instead of deleting it.

How do you know the import is right?

Control totals, agreed before the import and checked after it. Pick four or five figures a person can verify by hand:

• the number of rows in each table;
• the sum of quantity across the file, and per category;
• the sum of value, and the count of rows with a zero or negative quantity;
• the customer balance total, and the count of customers with a balance.

Compare against a printed page, not the file on screen. Print or export a PDF when you freeze the copy, and reconcile against that. The screen keeps changing; the page does not, and it is still there when somebody asks why the opening balance was what it was.

If a total is off, stop. Importing the rest and promising to fix the difference later destroys your only signal.

Should the old and new systems run in parallel?

Yes, for exactly one close. Both systems receive the same inputs and you compare the outputs at the end of a real period — a month, a term, a stock cycle. One close catches the rules you misread; a second is where people start keeping two sets of books and quietly trusting the wrong one.

Define the close first: which reports must agree, to the last digit or to a tolerance, and who signs off. A parallel run with no comparison agreed in advance is double work with an argument at the end.

How do you stop the old file changing?

You cannot ask politely. Access control is the only thing that works.

1. Take a copy, name it with the date, make it read-only for everyone but one named person.
2. Move the working file to a folder only that person can write to. Not a shared drive where read-only is a suggestion.
3. Tell the team where the new system is, in the same message. A file that vanishes without a replacement becomes a new file, unnamed, in somebody's downloads.
4. Retire it on a date, not on a feeling. "Read-only from the first close" is a rule; "once everyone is comfortable" is not.

What order do you do this in?

1. Interview the person who uses the file, with the file open. Record the rules, the exceptions and the manual steps.
2. Copy and freeze the source, print the control page. Everything after this works on the copy.
3. Clean, flagging rather than deleting. Keep the trail reversible.
4. Import into a test environment and reconcile the totals. Fix, re-import, reconcile again.
5. Run one full close in parallel. Compare the reports agreed in advance.
6. Freeze the old file and point everyone at the new one. Same day, same message.

Where this goes wrong

• The import is treated as a formatting exercise. Columns are mapped, the row count matches, and nobody checks whether the discount rule survived.
• The file is cleaned after the import. The new system inherits every duplicate, and they are harder to merge because they now have transactions attached.
• Control totals are chosen to match. Someone picks the one figure that agrees and stops looking. Choose the figures that would embarrass you.

What this does not cover

This is about moving records and the rules attached to them, not about choosing the new system or modelling the database. It says nothing about how long you must keep the old file, which depends on your own accounting rules. And it assumes one spreadsheet, or a small family. A migration from twenty linked workbooks with macros is a different project, whose first step is deciding which of them is the source of truth.

We build the imports and the reconciliation that prove them: row counts, control totals, and a frozen copy that can be re-read when the question comes back.