Level 3Pulling Data InProject, 6 min
A competitor price check (simulated)
The task
Bean There, the café across the street, posts its menu as an HTML table, and Grounds for Celebration shows its latte price on a product page. Pull both into the sheet and compare. Both web pages are simulated in this practice sheet.
- Import the menu tableIn A3, pull the first table from the menu page in B1:
=IMPORTHTML(B1, "table", 1). - Average 12 oz priceIn F4, average the price of their 12 oz drinks from the imported table.
- Pull one price with IMPORTXMLIn F7, read the latte price from the rival page in F1. The price sits in a
<span class="price">, so the XPath is//span[@class='price']. - CompareIn F10, subtract the rival's latte price from Bean There's 12 oz latte price (it's in the imported table). A positive number means Bean There charges more.
The data
The starting sheet. Cells outlined in green are where your formulas go.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Menu page | https://beanthere.example.com/menu | Rival page | https://groundsforcelebration.example.com/latte | ||
| 2 | ||||||
| 3 | ||||||
| 4 | Avg 12 oz price | |||||
| 5 | ||||||
| 6 | ||||||
| 7 | Rival latte | |||||
| 8 | ||||||
| 9 | ||||||
| 10 | Our 12 oz latte vs rival | |||||
| 11 |