Excel looked at your security identifiers and decided some were dates.
Yes, really. That column of ISINs, Cusips, or whatever alphanumeric mess your market uses went into a spreadsheet, and suddenly half of them are now formatted as 1970s birthdays. The others lost their leading zeros because, apparently, a zero at the front doesn’t matter. A few more vanished into scientific notation.
Here’s the thing: identifiers are labels, not numbers. Their characters and their order matter. Arithmetic does not. When Excel sees a string that looks like a date or a number, it helpfully interprets it as such. That’s not a bug. It’s a feature. A terrible, terrible feature for reference data.
And then there’s the 1900 date system. For legacy compatibility, Excel treats 1900 as a leap year even though it wasn’t. Its date serials include a nonexistent February 29, 1900. It’s an old compatibility decision, not the cause of every mangled identifier, but it’s a useful reminder that spreadsheet date handling comes with history attached.
So how do you stop this nonsense?
- Import identifier columns as text. Before Excel gets a chance to guess.
- Preserve the untouched source. Because formatting a damaged cell is like repainting a missing door.
- Validate formats and check digits where applicable. If the value looks right on screen, it’s not necessarily fine.
- Reconcile the output against the input. If the count or the characters changed, you’ve got a problem.
The spreadsheet was trying to help. That’s the problem. It’s not a harmless way to move a small reference-data file. It’s a black box that rewrites your data based on its own assumptions. And no, you can’t just add the zeros back later. If the identifier changes in transit, you haven’t moved reference data. You’ve made some up.
Stop asking a spreadsheet to guess what your securities are.