The Copy-Paste Trap Is Real

Last week I needed a list of book prices from an online store. My first instinct? Highlight, copy, paste into Excel. Forty books in, my eyes were crossed and I'd pasted a price into the wrong column three times.

There's a better way. A Python script can grab that data in seconds, clean it up, and drop it straight into an Excel file. You don't need to be a developer to make this work—if you can run a Python script, you can scrape data to Excel with Python.

By the end of this guide, you'll write a script that pulls book titles, prices, and ratings from a real website, saves everything into a proper .xlsx file with multiple sheets, and even adds new data without erasing what you already have. Let's build something useful.

What You Need

We're using three Python libraries. Each does one job well:

  • requests – fetches the web page
  • beautifulsoup4 – picks out the data we want (titles, prices, stars)
  • openpyxl – writes data into Excel files

I'm running Python 3.8.10 on Ubuntu 20.04 with requests 2.32.4, beautifulsoup4 4.8.2, and openpyxl 3.1.5. If you're on Windows or macOS, the same code works.

Open your terminal and install them:

pip install requests==2.32.4 beautifulsoup4==4.8.2 openpyxl==3.1.5

That's it. No Excel installation required—openpyxl creates .xlsx files all by itself. We'll talk more about that in the FAQ.

Step 1: Grab the Data

We're using books.toscrape.com, a fake bookshop built specifically for practicing web scraping. The site is stable, the HTML is clean, and it won't get mad at us for poking around.

We'll scrape the first page — 20 books. There are 1000 books total on the site, but twenty examples are plenty for learning.

Here's the first part of the script:

import requests
from bs4 import BeautifulSoup

# Target URL for practice scraping
url = "http://books.toscrape.com/catalogue/page-1.html"

# Fetch the page using response.content (not response.text!)
response = requests.get(url)

# Pass the raw content to BeautifulSoup
soup = BeautifulSoup(response.content, "html.parser")

# Check it worked
print(soup.title.string)

Run that. You should see the page title printed in your terminal — something like All products | Books to Scrape - Sandbox. If you get garbled characters, you've hit the encoding issue I'll explain in Step 2.

Step 2: Clean It into a Simple List

Now we extract the data we want. Each book lives inside an <article> with class product_pod. We'll grab:

  • Title – from the h3 a tag inside that article
  • Price – from the <p> with class price_color
  • Star rating – from the <p> with class star-rating

The Encoding Trap (I Learned This the Hard Way)

When I first wrote this, I used response.text. Big mistake. The site doesn't send a charset header, so requests guessed ISO-8859-1. The £ symbol turned into gibberish and my script crashed.

Always use response.content and let BeautifulSoup figure out the encoding from the HTML meta tags. That's why we passed response.content in Step 1.

Here's the extraction logic:

books = []

for article in soup.find_all("article", class_="product_pod"):
    # Title
    title_tag = article.find("h3").find("a")
    title = title_tag.get("title") if title_tag else "No title"

    # Price
    price_tag = article.find("p", class_="price_color")
    price = price_tag.text.strip() if price_tag else "No price"

    # Star rating (convert class name to readable text)
    rating_tag = article.find("p", class_="star-rating")
    if rating_tag:
        rating_classes = rating_tag.get("class")
        rating = rating_classes[1] if len(rating_classes) > 1 else "No rating"
    else:
        rating = "No rating"

    books.append({
        "title": title,
        "price": price,
        "rating": rating
    })

# Show what we got
for book in books[:5]:
    print(f"{book['title']} | {book['price']} | {book['rating']} stars")

Run this and you'll see five books printed with their prices and ratings. Everything is stored in a list of dictionaries—one dictionary per book.

Step 3: Write It to Excel with openpyxl

Time to send that data into Excel. We'll create a new workbook, add a header row, then fill in each book row by row.

from openpyxl import Workbook

# Create a new workbook and grab the active sheet
wb = Workbook()
ws = wb.active
ws.title = "Today's Prices"

# Write headers
headers = ["Title", "Price", "Rating"]
ws.append(headers)

# Write each book
for book in books:
    ws.append([book["title"], book["price"], book["rating"]])

# Save the file
wb.save("book_prices.xlsx")
print("Excel file saved: book_prices.xlsx")

Open book_prices.xlsx with Excel, LibreOffice, or Google Sheets. You'll see a clean table with three columns and twenty rows of data (here's a preview):

Title Price Rating
A Light in the Attic £51.77 Three
Tipping the Velvet £53.74 One
Soumission £50.10 One
Sharp Objects £47.82 Four
... ... ...

Step 4: Add a Second Sheet

A single sheet is useful, but real projects often need multiple sheets. Maybe you want "Today's Prices" and "Historical Records" in the same file.

Let's add a second sheet with some extra info—like a timestamp and a note about which page we scraped.

from openpyxl import Workbook
from datetime import datetime

wb = Workbook()
ws_prices = wb.active
ws_prices.title = "Today's Prices"

# Headers on sheet 1
ws_prices.append(["Title", "Price", "Rating"])

for book in books:
    ws_prices.append([book["title"], book["price"], book["rating"]])

# Create second sheet
ws_history = wb.create_sheet("Historical Records")
ws_history.append(["Scrape Date", "Page URL", "Total Books"])
ws_history.append([
    datetime.now().strftime("%Y-%m-%d %H:%M"),
    "http://books.toscrape.com/catalogue/page-1.html",
    len(books)
])

wb.save("book_prices.xlsx")
print("Excel file saved with two sheets!")

Open the file. You'll see two tabs at the bottom—one with your book data, another with a timestamped record.

Step 5: Append New Data Next Time

The script we've written overwrites the file every time. That's fine for a one-off task, but what if you want to scrape data to Excel with Python every day and keep a growing history?

Use load_workbook to open the existing file and add new rows instead of replacing everything.

from openpyxl import load_workbook
from datetime import datetime

# Load existing file
wb = load_workbook("book_prices.xlsx")
ws = wb["Today's Prices"]

# Find how many rows already have data
next_row = ws.max_row + 1

# Add new books at the bottom
for book in books:
    ws.cell(row=next_row, column=1, value=book["title"])
    ws.cell(row=next_row, column=2, value=book["price"])
    ws.cell(row=next_row, column=3, value=book["rating"])
    next_row += 1

# Also update the history sheet
ws_history = wb["Historical Records"]
ws_history.append([
    datetime.now().strftime("%Y-%m-%d %H:%M"),
    "http://books.toscrape.com/catalogue/page-1.html",
    len(books)
])

wb.save("book_prices.xlsx")
print(f"Added {len(books)} new books to existing file.")

Run this once, then run it again. Each time, the new data appears below the old rows, and the history sheet logs every run.

Real-World Tips

The books.toscrape.com site is friendly. The real web is messier. Here's how to handle common problems.

Dynamic Content (JavaScript-Heavy Sites)

Some sites load data with JavaScript after the initial page loads. requests can't run JavaScript, so you'll see empty divs or loading spinners.

For those sites, use Playwright instead. It controls a real browser that runs JavaScript just like Chrome or Firefox. I cover this in my Playwright web automation guide.

Broken Selectors

Websites change. One day your selector works, the next day it doesn't. Before you run your script, open the site in your browser, right-click the data you want, and choose "Inspect" or "Inspect Element". Use the DevTools to check if the class names have changed.

My script uses article.product_pod h3 a for titles and p.price_color for prices. Verify those on the live site before you hit "run". If they've changed, update your code accordingly.

Be Polite with Delays

Hitting a website with 100 rapid requests is a good way to get your IP blocked. Add a small pause between requests when you scrape multiple pages:

import time
time.sleep(2)  # Wait 2 seconds between each request

Treat other people's servers the way you'd want yours treated.

FAQ

Does this need Excel installed on my computer?

No. The openpyxl library creates .xlsx files directly. You don't need Microsoft Excel, LibreOffice, or any spreadsheet program installed to generate the file. You only need Excel (or a compatible viewer) to open the file afterward. If you're on a Linux server with no GUI, this script works perfectly—you can generate Excel files and email them to yourself or transfer them to another machine.

How do I export scraped data to Excel with headers?

You already did it in Step 3. The ws.append(headers) line writes the header row before any data rows. If you're appending to an existing file and the headers are already there, skip the headers and start from the next empty row. Here's the pattern to remember:

# New file: write headers once
ws.append(["Title", "Price", "Rating"])

# Then append data rows
for book in books:
    ws.append([book["title"], book["price"], book["rating"]])

Headers are just the first row of your sheet. Write them first, then write your data rows underneath.

Can I run this every day without overwriting my data?

Yes—use load_workbook as shown in Step 5. The append script opens your existing file, finds the last row with data (ws.max_row), and adds new rows below it. Your old data stays intact. To automate the daily run, set up a cron job on Linux (see my cron guide) or use Task Scheduler on Windows.

What if the website uses JavaScript to load data?

The requests library only fetches the raw HTML from the server. If the site loads content via JavaScript after the page loads, requests will miss it. That's when you switch to a browser automation tool like Playwright or Selenium. I explain how to handle these cases in my Playwright beginner guide. For simple static sites like books.toscrape.com, requests is all you need.

Next Steps

You've got a working script that grabs web data and drops it into Excel. Here are three ways to take it further:

Go run the script. Open the Excel file. See that table of books you pulled from the web without a single copy-paste? That's your first automation win. Now go find something else to scrape—just be nice to the servers out there.

All code in this article was tested and runs successfully on Python 3.8.10 (Ubuntu 20.04) with requests 2.32.4, beautifulsoup4 4.8.2, openpyxl 3.1.5 — live scrape of books.toscrape.com (20 books/page), workbook with two sheets, and row appending all verified — verified August 2026.