How to check Amazon product prices automatically in Excel.
With Black Friday coming in the USA, it’s time to carefully check prices and look out for bargains. Ideally get those prices automatically and put them into Excel for comparison.
To do that for Amazon means ‘scraping’ or trying to grab prices from a web page. Web scraping can be hard because a web page isn’t designed for simple extraction of data. Amazon makes scraping hard because the company doesn’t like price ‘bots’ which check prices for rivals.
There are various commercial services, VBA addins etc available to get price data from Amazon to Excel on a commercial scale while trying to bypass Amazon’s limitations.
In this article we’ll show you a ‘quick and dirty’ method of getting prices from an Amazon web page. These steps work without VBA code and you can follow each step, changing to suit your needs.
Excel has had a method of web scraping for many versions you can find it at Data | Get External Data | from Web.
Place your cursor in the cell where you want the scrape results to go. For reasons that’ll become obvious, it’s best to insert the results into a temporary cell so you can ‘massage’ the results before putting them into your analysis worksheet.
Go to Get External Data | From Web. Paste in the link to the Amazon product page you want to check and press Go. You can browse in the Web Query window but it’s usually easier to find the page in a normal browser then paste the link into the Address field of the Web Query.
Excel will get a copy of that page and put it in the dialog box. You can expand the dialog to make it easier to see all the page.
Click on the yellow box next to the price and any other details you want to import. After you’ve selected the items you want, press Import. Wait while Excel executes the data import in the background.
In the worksheet you’ll see the results like this:
Or this:
Some Amazon products, like paper books, don’t work because there’s no web table to select.
Get the price
Now we have to grab the price only from the cell text that’s been scraped.
We can ignore the left column entirely and focus on the cell with the price and following text. To strip out all the text and leave only the value do this:
=Value(MID(
This formula strips all the text to the right of the first space. SEARCH(” “,
The formula assumes the price is at the start of the web scrape cell prefixed only by the currency symbol. It works for all the Amazon pages we’ve tested but you might have to tinker with the formula if the scraped results change. There are many other methods of extracting a number from a text string, we’ve chosen a simple one here.
Once you have the price you can use it in your comparisons.
You can update the prices by clicking Data | Refresh All but don’t do it too often because Amazon may block access if their system detects excessive hits on a single page.
To check or edit an existing scrape go to Data | Connections, choose a connection and then Properties | Definition | Edit Query
You can try the same scraping technique with other online stores to build up a list of the prices offered for a single product across many stores.
Tips:
- Give each connection/scrape a name to make it easier to find than the generic ‘Connection 1’ ‘Connection 2’ given by Excel. Do that at to Data | Connections, choose a connection and then Properties | Connection Name.
- If you have a few products being checked, we suggest you also scrape the name of the product and use that scraped value in your results. That will assure you that the price you see is for the product you intend and saves mix-ups.
- In this simple example we’ve used specific links for each product. A more complex system would have a list of Amazon products ID’s (ASIN)
