Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Scrape a Web Page Table into Excel with pandas

Turn an HTML table on a web page into a clean Excel sheet. Fetch the page with requests, pick the right table out of several with read_html, flatten two-row headers, strip footnote markers and dashes so numbers become numbers, keep the links, and save the result with its source and time.
Databases, APIs & sourcespandas

Plenty of data that people need in a spreadsheet lives in a table on a web page: exchange rates, a supplier's price list, a public statistics release, an internal status page. Copying and pasting works once and breaks the habit of automating. pandas.read_html parses every <table> on a page into a DataFrame, and with a little cleaning the result is a sheet you can refresh on demand. This page, part of Moving Data Between Excel and Databases, goes from a URL to a formatted workbook, handling the problems real pages have: several tables, two-row headers, footnote markers, dashes and links.

Before scraping any site, check its terms and robots.txt, prefer an official download or API when one exists — Fetch API Data into Excel with Python requests covers that route — and fetch no more often than you need.

From a URL to a clean sheet Five steps. Fetch the page with requests. Parse every table with read_html. Pick the table by id or text. Clean the headers, footnotes and dashes. Save to Excel with the source and time. Fetch requests.get Parse read_html Pick attrs or match Clean headers and numbers Save with source note

Prerequisites

Bash
pip install pandas requests lxml openpyxl

read_html needs an HTML parser: lxml is fastest; beautifulsoup4 with html5lib copes better with badly broken markup and is used with flavor="bs4". The example page has a navigation table, an exchange-rate table with a two-row header, links and a footnote, and a small sales table.

Step 1 — Fetch the page

read_html can take a URL directly, but fetching with requests first gives you control over headers, timeouts and errors — and many sites refuse requests without a browser-like User-Agent:

Python
import requests

response = requests.get(url, headers={"User-Agent": "rates-report/1.0 (finance@example.com)"}, timeout=30)
response.raise_for_status()
response.encoding = response.encoding or "utf-8"
html = response.text
with open("rates.html", "w", encoding="utf-8") as fh:
    fh.write(html)

raise_for_status() stops the script on a 403 or 500 instead of parsing an error page. Saving the HTML you fetched is cheap and lets you rerun the parsing — or prove what the page said — without fetching again. An identifying User-Agent with a contact address is politer than impersonating a browser.

Step 2 — Parse every table and find the one you need

read_html returns a list with one DataFrame per <table>. In pandas 3, a plain string is treated as a file path or URL, so wrap HTML text in io.StringIO:

Python
from io import StringIO
import pandas as pd

try:
    pd.read_html(html)
except FileNotFoundError:
    print("a plain string is read as a path")

tables = pd.read_html(StringIO(html))
for i, t in enumerate(tables):
    print(i, t.shape, list(t.columns)[:3])

Three tables: the navigation bar (pages often use tables for layout), the rates and the sales. Relying on position — tables[1] — breaks the day the site adds a banner table above the one you want. Select by content instead: attrs matches the table's HTML attributes, and match keeps tables containing a piece of text:

Python
rates = pd.read_html(StringIO(html), attrs={"id": "rates"})[0]
same = pd.read_html(StringIO(html), match="Rate per EUR")[0]
print(rates.equals(same))
print(rates)

Both return the same table. An id is the steadiest hook when the page has one; look for it with your browser's "Inspect" tool. When reading a saved file rather than text, pass encoding="utf-8" — without it, the dash in the Swiss franc row comes out as garbled characters.

Ways to choose a table from read_html A table of selection methods. Position, such as tables[1], breaks when the page adds a table. match with text keeps tables containing that text and survives layout changes but may match more than one. attrs with an id matches the table's HTML id and is the most stable when the page provides one. Example Survives page changes Position tables[1] no — shifts with new tables Text match match="Rate per EUR" mostly — may match several HTML attribute attrs={"id": "rates"} yes — when an id exists

Step 3 — Flatten a two-row header

The rates table has a grouped header — "Rate per EUR" spanning "Buy" and "Sell" — so pandas builds a two-level column index, and the Currency column, spanning both rows, appears as ("Currency", "Currency"). Excel wants one row of headers; join the levels and drop the repeats:

Python
print(rates.columns.tolist())
rates.columns = [" ".join(dict.fromkeys(level for level in col if not str(level).startswith("Unnamed"))) for col in rates.columns]
print(rates.columns.tolist())

dict.fromkeys removes the duplicate "Currency" while keeping order, and the Unnamed filter handles headers where one level was empty. For a simple one-row header, the columns are already plain strings and this step is unnecessary.

Step 4 — Turn text into numbers

Look at the column types: the Sell column is numeric, but Buy is text, because one cell says 0.8561[a] — a footnote marker — and another holds a dash meaning "no rate". The thousands separator in the yen row was handled already: thousands="," is read_html's default. Strip anything that is not part of a number, then convert:

Python
print(rates.dtypes.to_dict())

for col in ["Rate per EUR Buy", "Rate per EUR Sell"]:
    cleaned = rates[col].astype(str).str.replace(r"\[\w+\]", "", regex=True).str.replace(",", "")
    rates[col] = pd.to_numeric(cleaned, errors="coerce")
print(rates)
print(rates.dtypes.to_dict())

The footnote is gone, the dash has become NaN — an empty cell in Excel — and both columns are floats. Check rates[col].isna() against the raw column afterwards so a value you did not expect, such as n/a or 1.08 (est.), is noticed rather than silently blanked. Pages that use a decimal comma need decimal="," and thousands="." in read_html instead.

The Buy column before and after cleaning Left, the Buy column as read_html returns it: text values 1.0842, 0.8561[a], 1612.40 and a dash. Right, after stripping the footnote marker and converting with to_numeric: numbers 1.0842, 0.8561 and 1612.4, and an empty cell where the dash was. A 1 Buy 2 1.0842 3 0.8561[a] 4 1612.40 5 — A 1 Buy 2 1.0842 3 0.8561 4 1612.4 5 before after clean

A table's links are often as useful as its text — the detail page for each currency, product or filing. extract_links="body" returns each body cell as a (text, href) tuple; split them into two columns and turn relative links into full URLs:

Python
from urllib.parse import urljoin

linked = pd.read_html(StringIO(html), attrs={"id": "rates"}, extract_links="body")[0]
pairs = linked.iloc[:, 0]
rates["Detail page"] = [urljoin(url, href) if href else None for _, href in pairs]
print(rates[["Currency", "Detail page"]])

The Swiss franc has no link, so its href is None and the column stays empty. Write Clickable Links from a DataFrame to Excel turns these URLs into clickable hyperlinks in the workbook.

Step 6 — Save the table with its source

Scraped figures need provenance: where they came from and when. Write the table, and put the source URL and fetch time above it so anyone reading the sheet can check it:

Python
from datetime import datetime

fetched = datetime(2026, 9, 25, 9, 0)
with pd.ExcelWriter("exchange_rates.xlsx", engine="openpyxl") as writer:
    rates.to_excel(writer, sheet_name="Rates", index=False, startrow=3)
    ws = writer.sheets["Rates"]
    ws["A1"] = "Exchange rates"
    ws["A2"] = f"Source: {url} — fetched {fetched:%Y-%m-%d %H:%M}"
    for col, width in zip("ABCD", (18, 18, 18, 36)):
        ws.column_dimensions[col].width = width

print(pd.read_excel("exchange_rates.xlsx", skiprows=3).to_string(index=False))

In a real run, use datetime.now() for the fetch time. For a table you collect every day, append each fetch to a history sheet with a date column rather than overwriting — Append a DataFrame to an Existing Excel File with pandas shows how.

When read_html is not enough

read_html sees only the HTML the server sends. Tables built in the browser by JavaScript are absent from response.text — read_html raises "No tables found". Look in your browser's network tab for the JSON request that fills the table and call that instead (it is usually cleaner than the HTML); only if there is none, render the page with a browser automation tool such as Playwright and pass its HTML to read_html. Tables behind a login need the session cookies or token the site issues; use a requests.Session that logs in first.

Common pitfalls

  • Passing HTML as a plain string. pandas 3 reads it as a path; wrap it in StringIO.
  • Selecting by position. Use attrs or match so layout changes do not switch tables.
  • Garbled characters. Set the response or file encoding to UTF-8 before parsing.
  • Numbers left as text. Footnotes, dashes and units block conversion; strip and to_numeric.
  • Losing provenance. Record the source URL and fetch time in the workbook.
  • Fetching too often. Cache the HTML and respect the site's terms.

Performance notes

Parsing is fast; the network is the slow part. Save the fetched HTML and parse from disk while developing, and use match or attrs so pandas builds DataFrames only for the tables you need.

Conclusion

Fetch the page with requests, check the status and encoding, and parse it with pd.read_html(StringIO(html)), choosing the table by attrs or match rather than position. Flatten multi-row headers, strip footnote markers and dashes before pd.to_numeric, and keep links with extract_links. Save the table with its source URL and fetch time, and switch to the site's JSON endpoint when the table is built by JavaScript.

Frequently asked questions

How do I get a table from a web page into Excel with Python? Fetch the page with requests, pass its HTML to pandas.read_html wrapped in io.StringIO, pick the table you need from the list it returns, clean the columns, and save it with DataFrame.to_excel.

Why does read_html say "No such file or directory" when I pass it HTML? pandas 3 treats a plain string as a file path or URL. Wrap HTML text in io.StringIO — pd.read_html(StringIO(html)) — and it is parsed as markup.

read_html returns several tables. How do I get the right one? Pass match="some text" to keep only tables containing that text, or attrs={"id": "rates"} to match the table's HTML attributes. An id is the most stable choice, because the table's position in the list changes when the site adds a banner or menu table.

Why is a numeric column still text after read_html? One or more cells contain something that is not a number — a footnote marker such as a, a dash for "no value", or a unit. Strip those with a regular expression and convert with pd.to_numeric(errors="coerce").

More on databases, apis & sources

More on databases, apis & sources →