Normalising dates, currencies and units

A client once asked me why one supplier in a price file looked about 40% cheaper than everyone else in the same category. The supplier was not cheaper. That listing was priced per 100 grams while the rest of the file was priced per pack, and my parser had written both into a column called price.

Nothing in that run was broken. The fetch worked, the selector matched, the string cast to a float, the row inserted. The dataset was wrong anyway, and stayed wrong until somebody found it odd enough to ask.

That is the category worth writing about. Not scrapers that fail, which announce themselves. Values that parse successfully and mean something other than what you assumed.

The failure mode with no exception

Most scraping writing is about getting the page. After that the assumption is that extraction is mechanical: find the node, take the text, cast it to a type.

The cast is where the damage happens. float("1.234") works. Parsing 03/04/2026 as a date works. Neither call knows anything about the site it came from, so neither can tell you it has just produced a confident, well typed, wrong answer.

A validation layer will not catch this. I have written separately about validating records before they hit your database and those rules earn their place, but they catch impossible values: a negative price, a date in the year 2900, a required field that came back empty. Everything in this article is entirely possible. It passes every bound you would think to write.

Deduplication does not catch it either. That work, which I have covered on its own, decides whether two rows describe the same thing. Two rows can be identical and both wrong in the same direction, off the same misread convention.

Three ways a date goes quietly wrong

The first is ambiguity. 03/04/2026 is the third of April and it is also the fourth of March. Whichever your date library picked, it picked from a default, usually inherited from the locale of the machine running the job. That machine has never seen the site.

You cannot resolve this from one string. You can resolve it from a column. Pull a few thousand dates from that source and look for a single row where the leading number exceeds 12. That row tells you the convention, and now you have evidence instead of a default. If you are lucky the markup carries a machine readable date beside the human one, with a four digit year, and you can skip the whole exercise.

The second is relative time. “Posted 2 days ago” is not a date, it is an offset from a moment the page never states. Store the string and you have stored nothing. Resolve it against the moment you fetched, store the resolved instant, and store the fetch time next to it. Skip that last part and someone reparsing your stored HTML eight months later gets a different answer with no errors and no way to tell which run was right.

The third is time zones. A date with no zone is a guess you have already made without recording it. A listing posted at 23:40 local sits on either side of a day boundary depending on where you stand, and any report that groups by day will move it between buckets based on nothing more interesting than which server ran the query. I keep the instant in UTC with the offset recorded, and I keep the site’s own calendar date in its own column whenever the day boundary is what someone is going to count.

Money is at least two fields

A currency symbol is not a currency. The dollar sign is shared by Singapore, Hong Kong, Australia, Canada, New Zealand and the United States, several of them on English language sites that look much alike. A scraper that reads the glyph and writes USD is inventing information.

The currency has to come from somewhere with real signal: an explicit three letter code in the markup, structured data, the domain, or the locale you requested. That last one is a separate subject, requesting a locale and keeping the request consistent so the site serves the version you asked for. I have covered it on its own. This article is what happens after the page answers.

The two ends have to agree. Requesting the Singapore storefront and then hard coding USD in the parser produces a dataset that is confidently wrong at scale, and no single row in it looks suspicious.

Then the separators, where I have lost the most money. In one convention 1,234.56 is a thousand and a bit; in another the same value is written 1.234,56. The two characters swap jobs. When both appear you can work it out, because the one closer to the end is the decimal point. The trap is a single separator with exactly three digits after it. 1.234 is either one and a bit or one thousand two hundred and thirty four, and nothing in the value picks between them.

Column level checks catch this where row level checks cannot. Sample a thousand prices from one source. If a third of them terminate in exactly three digits after a dot, that dot is a thousands separator, because real prices do not cluster that way.

Two more money details that bite:

• Minor units are not always hundredths. Japanese yen has no decimal subdivision at all, so a schema that stores everything as cents quietly multiplies by 100.
• Tax handling differs between sites in the same market. One quotes with consumption tax included, the next quotes without, and both call the field “price”.

Converting at scrape time destroys information

The most expensive habit I see in other people’s pipelines is converting currency during extraction and storing only the result.

Once that number lands you can no longer answer what the page said, which rate was applied, or whether that rate came from the scrape date or the run date. If the rate feed was wrong, or two jobs applied the conversion twice, there is no route back. A conversion is not reversible when you did not keep the input.

Store the amount as the page gave it, the currency code beside it, and the rate with its own date as separate fields. The converted figure is then a view you can rebuild whenever your understanding improves.

Quantities need their denominator

A price without its unit is meaningless, and the unit is frequently nowhere near the price field. It lives in the title, on a separate line, in a variant selector, or it is implied by the category and stated nowhere on the page.

Sizes behave the same way. A size 10 garment is different clothing depending on whose sizing the site uses, and a bandwidth figure where one vendor means megabits and the next megabytes is off by a factor of eight.

The rule sitting under every example so far: every value on a page is a rendering of something, produced under a convention the page rarely states. What you scraped is the rendering. What you wanted was the thing.

Keep the string, in the row

So the discipline, which is short.

Store what the page said next to what you decided it meant. The raw text of that field, untouched, in the same row as the parsed value. There is a broader argument about retaining whole raw responses for provenance, and that is a separate topic. This is the narrow version, at field level, and it is the one that pays off during an incident. Nobody joins to cold storage while a customer is waiting.

Never overwrite that string afterwards. Not to trim whitespace, not to repair an encoding, not when a parsing rule improves. It is the only record of what you were handed.

Put the assumption in the column name

A column called price is a lie. A column called price_sgd_cents is a claim somebody can check against a page. Parsing code is where assumptions go to die, because in six months a colleague will query your table and never open your parser. The schema is the document people actually read.

Here is the position I will argue about: any normalisation that discards the original is a decision you cannot revisit, and you will want to revisit it. Every rule you write is your best understanding of a source on the day you wrote it. Sites change and your understanding gets better, and the whole value of a stored dataset is that you can apply what you learned later to what you already collected.

The four months I did not notice

The bug that taught me all of this was a European decimal separator read as a decimal point. That column was wrong by a factor of 1000 for four months.

I had validation. The rule said greater than zero and under a ceiling, and 1.23 sailed through it. I had monitoring on parse failures, and there were none, because nothing failed. What I did not have was the original string in the row, so when the question arrived I could not answer it from the table. I went back to stored pages and some had rotated out.

The repair took an afternoon: keep the raw price text beside the parsed value, and run a per source distribution check that flags when digits after a separator cluster in a way real prices never do. The four months was the noticing, and nothing in the pipeline did the noticing. A person did.

Some pages genuinely never state their convention, and no column trick recovers it. Store those as unresolved and let them be visibly missing. A gap gets a question; a wrong number gets used. Carrying the original text next to every parsed field also costs real storage, and at a few hundred million rows that is a bill I still choose to pay, because a smaller table of numbers I cannot verify is worth less than a larger one I can.

None of this widens what you are allowed to collect: public pages, the robots file honoured, a crawl rate that does not hurt the site, and an official feed preferred every time one exists. The rest of what I have written on pipeline design, storage and the proxy infrastructure underneath it is over here.

Get new guides and videos first — join the Telegram channel.