A recorded transaction, an asking price and a valuation are three data points with three sources and three dates. How to model them apart, and why streets break
Key takeaways
- Transaction, listing and estimate differ in who produces the number and what fixes its date. A single column called "price" erases exactly that distinction.
- A transaction has two dates, not one: the event date and the publication date. A listing has a posting date and a price that changes with no history unless you snapshotted it. An estimate has an effective date and a method version.
- A professional valuation carries a verifiable author. The register of real-estate appraisers on data.gov.il holds 3,105 rows with the fields appraiser name, licence number, file number and city. A model output carries no licence number.
- The precision levels do not line up. In the Ministry of Justice ownership file, of the first 5,000 rows I sampled, 4,998 carry a sub-parcel value of 0 - the identifier that would separate flats in one building is not populated in that slice.
- At street level the observation count is small. A median over four transactions is not a median, and one unusual sale moves the whole row.
Three numbers are all called "price" and are not the same thing: a recorded transaction is an event that happened, a listing price is a request that has not yet been answered, and a valuation is the output of a method. Each has a different producer, a different date and a different level of geographic precision, so each needs its own table.
This is a technical description of a data model, and is not appraisal, investment or legal advice.
Three numbers, three producers
This is not a theoretical distinction. It determines which question each number can answer, and under what condition two of them may be compared at all.
| Recorded transaction | Listing price | Valuation | |
|---|---|---|---|
| What it is | An amount actually agreed | An amount the seller asks | The output of an estimation method |
| Who produces it | The parties, then an official record | The seller or the agent | A licensed appraiser or a model |
| What fixes the date | The transaction date, then publication | The posting date, and it moves after | A chosen effective date |
| What moves it | Only a new event | A seller's decision, at any moment | A new method version or new input |
| What it cannot answer | What is happening this week | What was actually paid | What actually occurred |
The last row is the one that catches people. Anyone wanting to know what was paid and looking at listings gets an answer about sellers' expectations. Anyone wanting to know what is on offer today and looking at transactions gets an answer about deals closed months ago.
Why do the three numbers disagree?
Because they do not measure the same population, and not at the same moment. The gap between them is information, not noise to be averaged away.
- Different population: transactions exist only for properties that sold. A property listed and never sold never enters the transactions table, even though its price says something.
- Different direction: a listing price tends to drift down over time until closing, so the distance between listing and transaction depends on how much time has passed.
- Different censoring: a listing disappears when it comes down, and if you did not snapshot it at the time you have no evidence it was ever there.
- Different time anchor: an estimate refers to one effective date, even if the run producing it happened a month later.
- Different phrasing: an estimate answers "what is it worth", a transaction answers "what was paid", and a listing answers "what is being asked".
So an average of a listing price and a transaction price is a number with no referent. It does not represent a transaction, does not represent a listing, and cannot be explained. Comparison is legitimate, but it has to be presented as a gap between two measures rather than as one number.
Why does street level break the numbers?
A street is a tempting unit of aggregation, because people think in streets. The problem is that the data was not built around it, and two separate limits collide there.
The first is quantity. A typical street produces a handful of transactions a year. A median over four observations is completely sensitive to each of them, and a confidence interval on a sample that size is too wide to be useful. The practical consequence: show a count next to every median, and suppress the number entirely below a pre-set observation floor.
The second is geographic resolution. Land registration rests on block and parcel, and the Ministry of Justice ownership file on data.gov.il does publish the fields block, parcel, sub-parcel, method description and ownership type, across nearly 2.87 million rows. But in the slice of the first 5,000 rows I sampled, the sub-parcel is 0 in 4,998 of them. A parcel identifies land, and in a residential building dozens of flats share one parcel. Without a populated sub-parcel you cannot descend from plot level to flat level, and that is separate work rather than a conversion.
- Store block and parcel as the key, not a textual address.
- Store the raw address as published as well, as evidence.
- Measure what share of rows can be attributed to a street, and report that share in the output.
- Show an observation count next to every summary number.
- Set a minimum observation floor below which no median is displayed at all.
How to model this without a single price column
The rule is that every row records an observation, and an observation carries its own type. There is no property table with a price. There is an observations table pointing at a property.
- A transactions table: property key, transaction date, publication date, amount, transaction type, source.
- A listings table: property key, posting date, sample date, asking price, status, source.
- An estimates table: property key, effective date, value, method, method version, author.
- A property entity table holding only the geographic keys, with no price at all.
- A unified view that adds an observation-type column, and does not permit aggregation without filtering on it.
- An author field in the estimates table separating a licensed appraiser from a model output.
Step six is the forgotten one. An appraiser's valuation is the opinion of a person holding a licence that can be verified against the register of appraisers, which carries 3,105 rows with name, licence number, file number and city. A model output is a calculation, and has no licence number. The two rows look identical in a table if you did not separate them, and then somebody quotes a model as though it were an expert opinion.
What to do when the numbers contradict each other
You do not average them. You read the gap, because it usually says something about the market or about the data, and in both cases it is worth knowing which.
- A listing far above recent transactions in the same area: an expectations gap, or a property that is not comparable.
- Transactions above listings: usually a sign that the transactions are older than the listings, not that the market fell.
- An estimate far from both: check the effective date before concluding anything about the method.
- A sharp change in the most recent month of transactions: almost always data that has not arrived yet, not a market that stopped.
- Two sales on the same parcel days apart at very different amounts: check the transaction type before drawing any conclusion.
Background on official sources versus listing sites is set out in collecting Madlan real-estate data, the practical side of listing collection in scraping real-estate listings, and the specific case of the Yad2 board in a guide to scraping Yad2 listings.
Sources
Frequently asked questions
Can listing prices be used to estimate value?
This article gives no valuation recommendation. On the data alone, a listing records a request rather than an outcome, which makes it suitable for measuring supply and time on market. A question about the value of a specific property goes to a licensed real-estate appraiser.
Why store block and parcel when there is an address?
Because Hebrew addresses are inconsistent across sources: with and without the street prefix, with an apartment number appended, with the locality spelled differently. Joining on an address produces wrong matches. Block and parcel is a stable identifier, and that is what makes reliable cross-referencing between files possible.
What do you do when a street has two transactions a year?
Show them as observations rather than as a median, or widen the aggregation unit to a larger area and say so. A pre-set observation floor beats an ad hoc decision, because it stops the report from displaying a number that looks stable and is not.
How long does transaction data take to appear?
It varies, so quoting a figure would be wrong. What is right is to build the report so it marks the most recent period as incomplete, and to measure the lag yourself: compare the count for a given month across two pulls a few weeks apart and see how many rows were added.
Keep reading
Related service
Business Automation
I build custom automations that remove repetitive work end to end.
About the author
Yehonatan Saadia
Freelance automation, web & MVP developer
I'm Yehonatan Saadia, a senior developer who builds business automation, custom websites, and MVPs for small and mid-sized companies across the US, Europe, and Israel. These guides come from real client work, not theory.
Work with meHave a project like this?
Tell me what you're trying to automate or build. I reply within 24 business hours with a few targeted questions, then we walk through it on a free 30-minute call, with no commitment. You come away with a scope, a timeline and a fixed price - or a straight answer that it isn't worth building.