Scrape Amazon Prices Using Excel and VBA

Amazon changes prices at an astonishing rate—around 2.5 million times a day. This means the price of a product can fluctuate every 10 minutes. Whether you're an Amazon seller, a marketer, or a savvy shopper, staying on top of these changes can provide a competitive edge. In this guide, we'll walk you through how to track Amazon prices using Excel. From simple built-in tools to powerful APIs, we'll cover everything you need to monitor prices with minimal hassle.

Why Monitor Amazon Prices

Price tracking isn't just for the curious; it's a must. Here's why:
For Amazon Sellers: Stay ahead of competitors by adjusting your prices dynamically.
For eCommerce Businesses: Position your products better by comparing your prices with Amazon's offerings.
For Marketing Agencies: Analyze trends and pricing patterns to craft better client strategies.
For Shoppers: Get notified when your favorite products go on sale.
Take a real-life example: A developer set up an Excel tracker for a $1,500 gaming laptop. After just three weeks of tracking, they caught a 6-hour flash sale and saved $220—15% off the original price. Without automated tracking? They would've missed it.

Method 1: Track Prices with Excel Power Query

If you're new to web scraping and prefer a straightforward solution, Excel's Power Query is your best friend. No code required—just follow these steps:
Step-by-Step Setup:
Open Excel and go to the Data tab.
Click Get Data > From Other Sources > From Web.
Enter the URL of the Amazon product page you want to track.
In the Navigator pane, browse through the available HTML tables to find the price data. Look for tables with key details like discount, MRP, and current price.
Click Transform Data to clean up the data (remove unnecessary rows/columns).
Finally, click Close & Load to import the cleaned data into your Excel sheet.

Updating Prices: To keep your data fresh, right-click your query in the Queries & Connections pane and select Refresh.
Set Up Automatic Refresh:
Open the Data tab, and go to Queries & Connections.
Right-click the query and select Properties.
Check Refresh every and set the interval (minimum 1 minute).
Optionally, select Refresh data when opening the file.
Hit OK to save the settings.
Your Excel sheet is now automatically updated at the interval you choose. There's no need for further action after the setup.

Method 2: Advanced Tracking with VBA

Want more control over your scraping? VBA (Visual Basic for Applications) lets you dive deeper into Amazon's price data with custom scripts. Here's how to set it up:
Set Up VBA in Excel:
Enable the Developer Tab: Go to File > Options > Customize Ribbon and check Developer.
Open the VBA Editor by pressing Alt + F11.
Insert a new module via Insert > Module.
Paste this code:

Sub ScrapeAmazon()  
    Dim HTML As Object, URL As String  
    Dim ItemName As String, Price As String  
  
    URL = "YOUR_AMAZON_PRODUCT_URL"  
  
    ' Create HTTP request  
    With CreateObject("MSXML2.XMLHTTP")  
        .Open "GET", URL, False  
        .send  
        Set HTML = CreateObject("HTMLFile")  
        HTML.body.innerHTML = .responseText  
    End With  
  
    On Error Resume Next ' Handle missing elements gracefully  
  
    ' Extract item title  
    ItemName = HTML.getElementById("productTitle").innerText  
    ItemName = Trim(ItemName)  
  
    ' Extract price  
    Price = HTML.getElementsByClassName("a-price-whole")(0).innerText  
    Price = Trim(Price)  
  
    ' Output to Excel  
    Range("A1").Value = "Item Name"  
    Range("B1").Value = "Price"  
    Range("A2").Value = ItemName  
    Range("B2").Value = Price  
  
    On Error GoTo 0  
End Sub  

Run the Script by pressing F5.
Since Amazon updates its HTML often, you may need to adjust the code's selectors. Use Chrome's Developer Tools (right-click and select Inspect) to find the right selectors.

Automating VBA: Want to make it fully automatic? Create a script to refresh the data every few minutes:
Insert the following code to automate the scraping process:

Dim NextScrapeTime As Double  
  
Sub ScrapeAmazon()  
    ' Scraping code here  
    NextScrapeTime = Now + TimeValue("00:02:00")  
    Application.OnTime NextScrapeTime, "ScrapeAmazon"  
End Sub  

Double-click ThisWorkbook and add:

Private Sub Workbook_Open()  
    ScrapeAmazon  
End Sub  
  
Private Sub Workbook_BeforeClose(Cancel As Boolean)  
    StopAutoScrape  
End Sub  

This will make Excel automatically start scraping when you open the workbook—and stop when you close it.

Limitations of Excel Scraping

While Excel is handy, it has limits:
JavaScript Rendering: Many Amazon price elements are loaded via JavaScript, and Excel can’t execute it.
Anti-bot Protection: Amazon actively blocks automated scraping, so this method may fail at scale.
Dynamic HTML: Amazon changes its page structure often, requiring regular updates to your scrapers.
IP Blocking: Repeated requests from one IP can trigger CAPTCHAs or blocks.
For serious, large-scale scraping, you’ll need more robust solutions.

Alternative Approaches

If Excel-based tracking doesn’t cut it, here are some alternatives:
Specialized Price Tracking Tools:
Keepa: Great for tracking price history, though exporting data can be tricky.
CamelCamelCamel: Free and functional, but with a somewhat clunky interface.
Honey: Easy to use for price tracking, but lacks advanced features for serious monitoring.
Python: For those with coding skills, Python is a powerful option. Libraries like Beautiful Soup, Requests, Selenium, and Playwright offer flexible ways to scrape Amazon prices. Combine them with rotating proxies and you're good to go.
Google Sheets: If you prefer Google Sheets over Excel, you can still track prices. Check out our detailed guide on how to scrape Amazon prices with Google Sheets.

Scraping API

For hassle-free, high-volume scraping, Scraping API is the way to go. It handles all the heavy lifting:
JavaScript Rendering: Scrapes dynamically loaded price data.
Proxy Rotation: Avoid IP blocks by distributing requests across multiple IPs.
CAPTCHA Solving: Automatically solves verification challenges.
Global Coverage: Tracks prices from any Amazon locale worldwide.
Get Started:
Select Scraping API in the dashboard.
Choose your plan (Core or Advanced) and start free trial.
Enter the product ASIN and hit Send Request.
The API will return structured price data in JSON or CSV format, ready to use.

Wrapping Up

We've covered a range of methods for tracking Amazon prices, from simple Excel tools to powerful APIs. While Excel is a great starting point, it's limited when scaling up. If you need consistent, large-scale price monitoring, Scraping API is your best bet. No need to worry about CAPTCHA, IP blocks, or JavaScript rendering as the proxy handles it all, allowing you to focus on making smarter decisions and staying ahead of the competition.