I Built a Scraper That Turns 1,000 Book Listings Into a Clean Excel Report — Here's How

Most "learn web scraping" tutorials stop at printing a few titles to the terminal. That never felt like a finished tool to me, so I built one that goes all the way: scrape a catalogue, clean the data, and hand over a formatted Excel report someone could actually use.

The stack is plain Python: requests, BeautifulSoup, pandas, and openpyxl. No frameworks, no paid APIs. The target is books.toscrape.com, a site built specifically for practising scraping — so everything here is on a public practice page, not a real store.

What you end up with

Running two scripts produces:

• data/books_raw.csv — the raw scrape, one row per book
• data/books_clean.csv — the same data, cleaned
• data/books_clean.xlsx — a formatted Excel report: bold headers, filters, a frozen top row, and sensible column widths

The full run collects 1,000 books, and the cleaned report's average price comes out at £35.07.

Step 0: Setup

pip install requests beautifulsoup4 pandas openpyxl 

Three files: scraper.py (collect), clean_export.py (clean + export), and a data/ folder for the outputs.

Step 1: Scrape every page, not just the first one

A catalogue scraper that only reads page 1 is a demo, not a tool. The real job is walking the pagination until there is no "next" link left. The core loop does exactly that:

def scrape(max_pages=None): all_books = [] page = 1 while True: url = CATALOGUE_URL.format(page) response = requests.get(url, headers=HEADERS, timeout=20) response.raise_for_status() response.encoding = "utf-8" books, has_next = parse_page(response.text, url) all_books.extend(books) if not has_next: break page += 1 if max_pages and page > max_pages: break time.sleep(0.2) # be polite to the server  return all_books 

Each page is parsed with one function that pulls five fields out of every book card:

def parse_page(html, page_url): soup = BeautifulSoup(html, "html.parser") books = [] for article in soup.select("article.product_pod"): title = article.h3.a["title"].strip() price_text = article.select_one("p.price_color").get_text(strip=True) rating_word = article.select_one("p.star-rating")["class"][1] availability = article.select_one("p.instock").get_text(strip=True) rel_url = article.h3.a["href"] books.append({ "title": title, "price": price_text, "rating": rating_word, "availability": availability, "url": urljoin(page_url, rel_url), }) has_next = soup.select_one("li.next a") is not None return books, has_next 

Three details worth calling out, because each one bit me or would have:

1. The £ encoding gotcha. This site doesn't declare its charset, so requests guesses Latin-1 and corrupts the £ sign. The pages are actually UTF-8, so one line — response.encoding = "utf-8" — fixes it before parsing. If your scraped prices ever look like £51.77, this is almost certainly why.
2. Be polite. A 0.2 second sleep between pages and an honest User-Agent (python-automation-toolkit/1.0 (learning project)) cost nothing and keep you from hammering someone else's server. Scraping fast is not a skill; scraping without getting blocked is.
3. Relative URLs. Book links on the page are relative, so urljoin(page_url, rel_url) turns them into full URLs you can actually click later.

Run it with python scraper.py (or --pages 5 for a quick test run), and you get data/books_raw.csv with 1,000 rows.

Step 2: Clean the data before anyone sees it

Raw scraped data is never report-ready. Prices are strings with currency symbols (£51.77), ratings are words (Three), and availability is a sentence fragment. The cleaning step turns all of that into typed, analysable data:

RATING_MAP = {"One": 1, "Two": 2, "Three": 3, "Four": 4, "Five": 5} def clean(df): df = df.copy() # "£51.77" -> 51.77 (drop anything that is not a digit or a dot)  df["price"] = df["price"].astype(str).str.replace(r"[^0-9.]", "", regex=True) df["price"] = pd.to_numeric(df["price"], errors="coerce") df = df.dropna(subset=["price"]) # "Three" -> 3  df["rating"] = df["rating"].map(RATING_MAP) # "In stock" -> True, anything else -> False  df["in_stock"] = df["availability"].str.contains("In stock", na=False) df = df.drop(columns=["availability"]) df = df.drop_duplicates(subset=["title", "url"]) return df 

The pattern here is worth stealing for any scrape: strip, convert with errors="coerce" (bad values become NaN instead of crashing the run), drop the rows that didn't convert, and drop duplicates on a sensible key — here, title + URL.

Step 3: Export an Excel file a human would enjoy opening

A CSV is fine for machines. If a person is going to read the report, formatting matters — and it's only a few lines with openpyxl:

def write_excel(df, path): with pd.ExcelWriter(path, engine="openpyxl") as writer: df.to_excel(writer, sheet_name="Books", index=False) sheet = writer.sheets["Books"] for cell in sheet[1]: cell.font = Font(bold=True) sheet.auto_filter.ref = sheet.dimensions sheet.freeze_panes = "A2" widths = {"title": 55, "price": 10, "rating": 10, "in_stock": 10, "url": 60} for idx, col in enumerate(df.columns, start=1): sheet.column_dimensions[get_column_letter(idx)].width = ( widths.get(col, 20) ) 

Bold headers, filters, a frozen top row, columns wide enough for the content. That's the difference between "here's a file" and "here's a report."

python clean_export.py also prints a short summary: book count, average price, how many are in stock, and the rating mix.

The result

Average price across the catalogue: £35.07.

Three lessons I'd pass on

1. Scrape practice sites to learn. Real sites have terms of service, rate limits, and anti-bot measures. Learn the mechanics on a site built for it, and only scrape public pages with permission when real data is involved.
2. The pipeline is the project. Scraping is the easy third. Cleaning and presenting the data is where a script becomes a tool someone would pay for.
3. Small, polite, honest scripts beat clever ones. A 0.2 second delay and a real User-Agent never got anyone blocked; pretending to be a browser did.

The full code is in my Python Automation Toolkit project, and the same scrape → clean → report pattern works for any tabular listing: products, jobs, or prices. Next, I'll write about the other half of the toolkit — a file organiser that tidies a messy downloads folder without ever deleting anything by default.

Malik Ali Hassan — .NET & full-stack developer, final-year Software Engineering student. I build Python automation, AI assistants, and web apps.