A formula you drag down a column, backed by real completed sales rather than asking prices.
Published 17 October 2026 by CompsAPI
Every guide to getting eBay sales into a spreadsheet answers a different question than the one most people are asking. They explain how to export your own orders from Seller Hub. That is useful for bookkeeping and useless for pricing, because what you want to know is what other people's items sold for before you buy or list.
This is how to do that part, with a formula.
The template adds four custom functions to the sheet. Each one runs a real search against completed eBay sales and returns a number you can calculate with.
| Formula | What it returns |
|---|---|
=COMPS_MEDIAN("iphone 14 pro 256gb") | Median price of real completed sales, last 90 days |
=COMPS_MEDIAN("charizard psa 10", 365) | The same over any window, up to 1,095 days |
=COMPS_MEDIAN("switch oled", 90, "used") | Filtered by condition: new, used, open box, refurbished or parts |
=COMPS_COUNT("lego 75192") | How many sales actually matched, so you know how thin the number is |
=COMPS_RANGE("rolex submariner date") | The 25th to 75th percentile band |
=COMPS_LAST("ps5 disc edition", 5) | A table of the newest sales with dates and prices |
Minus words work the way they do on eBay, so iphone 15 -case -box prices the phone and not the accessories.
Three steps, about a minute.
1. Copy the template, which puts your own editable version in your Google Drive.
2. Get a free key at app.compsapi.com. It takes an email and no card.
3. Open the CompsAPI menu in the sheet, choose Set API key, paste it in.
Google asks you to authorise the script the first time, which is standard for any sheet that adds its own functions. After that, type an item in the Item column and the numbers fill themselves in.
A median built on three sales is not a price, it is an anecdote. COMPS_COUNT exists so that the thinness of a number is visible in the sheet rather than hidden inside it. In practice, treat anything under about eight sales as a direction rather than a figure, and widen the window before you trust it.
The same goes for the range. Two items can both show a $300 median while one ran $280 to $320 and the other ran $90 to $1,400. COMPS_RANGE puts the 25th and 75th percentile beside the middle so you can see which kind you are holding.
What Best Offers actually closed at. When a sale goes through as an accepted offer, eBay keeps showing the asking price with a line through it. We matched 5,859 of those sales against what the public page displayed for the same listing: 99.0% read high, by a median of 14.9%. In offer heavy categories, pricing off those asking prices means buying high every time, in the same direction, invisibly.
More than 90 days. eBay's sold filter stops at roughly three months. Pass a second argument and the same formula looks back up to three years, which is what you need before committing to seasonal stock.
A cleaned median. Lots, bundles, parts listings and mislabelled items are dropped and outliers removed before the median is worked out. A raw average over a keyword search is usually wrong by a wide margin, and by more than most people expect.
The free tier is 100 searches a month with every filter on and no card. Each unique lookup is one search and results cache for six hours, so a sheet recalculating does not quietly drain the allowance. Paid plans start at $9 a month for 5,000 searches.
If you would rather call it from code than from a cell, the API is the same data and the same account.
100 free requests a month · every filter on · no card
Get a free key