Track competitor prices in Excel: a weekly snapshot workflow

By the ScrapeSheet team · Published October 27, 2026 · Updated October 27, 2026 · 6 min read

Black Friday 2026 falls on November 27. If you sell online, your competitors will move their prices before then, and you want to see it while there is still time to react. You do not need a paid price-tracking tool for a short watchlist. One folder of weekly snapshots, one Power Query, and a handful of formulas will show every change and flag the ones that matter.

This guide uses Excel for Windows (Microsoft 365, or Excel 2019 and later, which have MAXIFS). If you work in Google Sheets instead, follow our price scraper in Google Sheets guide.

Step 1: Which competitor pages can you track?

1. List the products you want to watch: 10 to 30 is plenty for a weekly check.
2. For each product, note the public page where each competitor shows its price, such as a category page or a product page.
3. Open each site's terms of use and its robots.txt file. Keep only sites that do not forbid automated collection.
4. Give each competitor a short label without underscores, such as shop-a. You will use it in file names.

Step 2: Save one dated CSV per competitor each week

Pick a fixed day, for example every Tuesday morning. Then:

1. Create a folder named Price snapshots. Every file goes there, and nothing else.
2. Open the competitor's page and export the product name and price to a CSV file. The ScrapeSheet section at the end shows how.
3. Name the file YYYY-MM-DD_label.csv, for example 2026-10-27_shop-a.csv. The date and the label in the name become columns later.
4. Do not edit old files. Each one is a frozen record of that week.

Weekly is a good rhythm for most shops. Daily snapshots catch short flash sales, but they take more time to collect. Whatever you choose, stick to it so the % change always compares like with like.

Step 3: Combine the snapshots with Power Query

Power Query reads every CSV in the folder and stacks them into one table. You set it up once.

1. In Excel, go to Data > Get Data > From File > From Folder and select Price snapshots.
2. Click Combine > Combine & Transform Data, choose the first file as the sample, and click OK.
3. In the Power Query Editor, select the Source.Name column. Choose Transform > Split Column > By Delimiter, enter _, and pick Left-most delimiter.
4. Rename the first new column Date and set its type to Date. Rename the second Competitor, then use Replace Values to replace .csv with nothing.
5. Rename the scraped name and price columns Product and Price. Replace the currency symbol in Price with nothing and set the type to Decimal Number. If the prices use a comma for decimals, right-click the header and choose Change Type > Using Locale.
6. Select Date, Competitor, Product, and Price, then choose Remove Other Columns.
7. Rename the query History and click Close & Load. Check that the new table is named History under Table Design.

Each week, drop the new CSV files into the folder and click Data > Refresh All. The History table grows by itself.

Step 4: Add % change, gap, and alert formulas

Add a sheet named Compare with these headers from A to I: Product, Competitor, Your price, Last date, Last price, Previous date, Previous price, Change, Gap vs. you. Fill in columns A to C by hand. The product name and the competitor label must match the History table exactly. Then enter these formulas in row 2 and fill them down.

Latest and previous snapshot

In D2, find the latest date for that product and competitor:

=MAXIFS(History[Date], History[Product], A2, History[Competitor], B2)

In E2, read the price on that date:

=IF(D2=0, "", AVERAGEIFS(History[Price], History[Product], A2, History[Competitor], B2, History[Date], D2))

In F2, find the snapshot before that:

=MAXIFS(History[Date], History[Product], A2, History[Competitor], B2, History[Date], "<"&D2)

In G2, read its price:

=IF(F2=0, "", AVERAGEIFS(History[Price], History[Product], A2, History[Competitor], B2, History[Date], F2))

MAXIFS returns 0 when there is no match. Give columns D and F the custom number format yyyy-mm-dd;; so a zero shows as a blank cell instead of a 1900 date.

% change and gap vs. your price

In H2, the change since the previous snapshot:

=IF(OR(E2="", G2=""), "", (E2-G2)/G2)

In I2, how far the competitor sits from your price:

=IF(OR(E2="", C2=""), "", (E2-C2)/C2)

Format both as percentages. A kettle that shop-a listed at $49.99 last week and $44.99 this week shows -10.0% in column H. If your price is $47.50, column I shows -5.3%: that competitor is now 5.3% cheaper than you.

Alerts with conditional formatting

1. Select H2:H100 and go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
2. Enter =AND(ISNUMBER($H2), $H2<=-0.05) and pick a red fill. This flags any cut of 5% or more since last week.
3. Select I2:I100, add a second rule with =AND(ISNUMBER($I2), $I2<0), and pick an orange fill. This flags every competitor that is cheaper than you.

For a week-by-week grid, insert a PivotTable from History with Product and Competitor in Rows, Date in Columns, and Price in Values set to Average. If Excel groups the dates by month, right-click a date and choose Ungroup.

One caveat applies if you then announce a reduction yourself in the EU. As a general rule, the "prior" price you show must be the lowest price you applied in at least the 30 days before the reduction (Article 6a of Directive 98/6/EC), with some national exceptions. Take that figure from your own store records, not from weekly snapshots, which can miss a short price change.

How to do it with ScrapeSheet

You do not always need an extension. If a page shows prices in a plain HTML table, Excel can read it directly; see Excel Get Data from Web. For three or four products, copying by hand is fine. ScrapeSheet helps with category pages full of product cards, where Excel finds no table. Our competitor price monitoring page shows the full use case.

1. Install ScrapeSheet from the Chrome Web Store and pin it.
2. Open the competitor's category or search results page.
3. Click the ScrapeSheet icon to open the panel, then click Extract. It detects the tables and lists on the page on its own.
4. Export to CSV and save the file in your snapshot folder with the dated name from Step 2.
5. Back in Excel, click Data > Refresh All.

Know the limits. The free plan exports up to 20 rows per export and one page per run, which covers a short watchlist. ScrapeSheet Pro (€12.99 per month) removes the row limit and follows pagination and infinite scroll, so one run captures a whole category. Its price-monitoring mode reads the list on screen and tells you the cheapest and the most expensive item; the free plan lets you try each mode. The extension runs when you click it. It does not check prices on a schedule, so the weekly routine is yours to keep.

More step-by-step guides are on the ScrapeSheet blog.

FAQ

Is there a free price-tracking tool for Excel?
Excel itself is enough for a weekly check: Power Query combines your snapshots, and MAXIFS and conditional formatting do the tracking. ScrapeSheet has a free plan that exports up to 20 rows per export from one page per run, which covers a short watchlist.

Can Excel track competitor prices automatically?
Partly. Once the folder query is set up, Refresh All updates every formula and alert in one click. Collecting the snapshots is still a step you run. If a page is a plain HTML table, Get Data from Web can pull the current prices on refresh, but you still need to keep a dated copy each week to build a history.

How often should I check competitor prices?
Weekly works for most shops and keeps the comparison fair. Before Black Friday and Cyber Monday, you may switch to daily snapshots for your most important products, then go back to weekly.

Is it legal to monitor competitor prices?
Looking at prices that anyone can see is common practice, but each site's terms of use still apply, and some forbid automated collection. Stay on public pages, never bypass logins or blocks, and set your prices on your own. This is general information, not legal advice.

Sources

• Directive 98/6/EC on price indication, consolidated text, Article 6a (EUR-Lex)

General information, not legal advice. Always check the website's terms and the data-protection rules that apply to you.

Keep reading

• Price scraper in Google Sheets: build a Black Friday price tracker
• Excel Get Data from Web: what it does well and where it fails
• Is web scraping legal?

← Back to the blog · ScrapeSheet home