What breaks when you track loss of exclusivity in a spreadsheet, and what to do instead

The list everyone starts with

Molecule, brand, originator, patent expiry, SPC expiry, “LOE date”. Twenty columns, a few hundred rows, built in a week from a mix of annual reports, a subscription, and someone’s memory. It is useful on the day it is finished. The question is what it looks like eighteen months later, and the honest answer is usually: nobody knows, because nobody can tell which cells were checked and which were copied.

These are the failure modes we see most often, in the order they usually bite.

1. One row per product

A product does not have an expiry date; a product has one in each country. The compound SPC runs to June 2031 in Germany, was never applied for in Ireland, lapsed for non-payment in Portugal and was invalidated in the UK. A single “Europe” column forces a choice between the earliest date (useless for most markets), the latest (dangerous for the rest) or the German one (common, and wrong in a third of countries).

Fix: one row per molecule per country, or at least per molecule per market you actually trade in. The row count goes up by an order of magnitude; the usefulness goes up by more.

2. Dates typed, not calculated

An SPC expiry is an output: first EU authorisation date, minus patent filing date, minus five years, capped at five, added to the patent expiry, plus six months if the paediatric extension is granted. Type the result into a cell and every later correction to any input is invisible. The notification-date rule alone moved thousands of centrally authorised products’ SPC dates by two to four days; a typed date never notices.

Fix: store the inputs (filing date, first EU authorisation date and its basis, patent expiry, paediatric status) and compute the SPC expiry with a formula. Then a register correction is a one-cell edit.

3. Status lives nowhere

“Granted” was true when the row was made. Since then the patent has been opposed, the SPC lapsed in two countries, a revocation action is pending in a third and a paediatric extension was granted in a fourth. A spreadsheet with no status column cannot say any of that, and a status column with no date cannot say when it was last true.

Fix: a status field with its own “as at” date, per right per country, using the register’s own words (pending, granted, in force, lapsed, revoked, expired), not a home-made traffic light.

4. The source is lost

Where did the June 2031 date come from? The German register, a law-firm newsletter, a competitor’s list, a guess? If the cell cannot say, the only way to trust it is to re-derive it, which is the work the spreadsheet was supposed to save.

Fix: a source reference next to every date: the register, the record number, the date it was read. It is tedious once and priceless every time a figure is challenged.

5. Secondary patents are invisible

The sheet carries the compound patent and its SPC. The product also has a formulation patent to 2033, a dosing-regimen patent to 2034 and a crystalline-form patent to 2035, none of which appears, because they were filed under different numbers years later and the person who built the list searched by the compound. The sheet says the molecule is free in 2031; the generic launches into an injunction.

Fix: search by product and applicant, not only by compound, and record secondary patents with a note on what each one actually blocks; or accept that the sheet covers the compound position only and say so at the top.

6. The paediatric extension arrives late

An originator can apply for the six-month extension up to two years before the SPC expires, and offices grant it months after that. A sheet built in year one correctly shows no extension; the extension is granted in year three; the sheet is now six months wrong on the single date that matters most, and in the direction that costs money.

Fix: a “paediatric: not applied / applied / granted / refused” field with a review trigger when the SPC is within three years of expiry.

7. Nobody owns the review

The sheet was made for a project. The project ended. The sheet was emailed, copied, renamed “final v3”, and three teams are now using three versions, one of which has a formula that was overwritten by a typed value in 2024.

Fix: one copy, one owner, a review cadence (quarterly for products within three years of any expiry, annually otherwise), and a change log. If nobody will own it, the sheet should say on its face when it was last reviewed, so that readers can discount it correctly.

What good looks like, in any tool

• Keys: molecule, country, right (patent, SPC, paediatric, data exclusivity, market protection), one row each.
• Inputs stored, outputs computed: filing date, first authorisation date, grant, paediatric status in; expiry out.
• Status with a date, in the register’s words.
• Source on every fact, with the date it was read.
• Secondary patents flagged with what they block, or an explicit note that only the compound is covered.
• Review rules by proximity to expiry, and a log of what changed and why.

A spreadsheet built this way is a database that happens to live in Excel, and it will serve a small portfolio well. It stops serving when the rows reach the tens of thousands, when several people need to read it at once, or when the time spent checking registers exceeds the time spent using the answers. That is the point at which a maintained service pays for itself: MPA Search is this structure, for every molecule and country, with the register checks done on a schedule and the source on each record, and with people to call when a date needs interpreting.