How I Automated Sales Tax Reports by State With Python and pandas

Learn how to turn a Shopify, Stripe or Square export into a monthly sales tax report by state, with an economic nexus check, using Python and pandas. Full code included.

If you sell online to customers across the US, you already know the pain: every month you need to know how much you sold and how much tax you collected in each state. The numbers are buried in an export file with thousands of rows, and building the report by hand in Excel takes hours.

I got tired of doing that for a small online store, so I wrote a Python script that turns a raw orders export into a clean Excel report in a few seconds. It also flags the states where sales are getting close to the economic nexus thresholds.

Here is the whole process, step by step.

Quick disclaimer: I’m a developer, not a tax professional. This script helps you organize your data. Always confirm filing rules, rates and thresholds with each state’s Department of Revenue or a CPA.

Why this matters: economic nexus

Since the 2018 Supreme Court decision in South Dakota v. Wayfair, states can require out-of-state sellers to collect sales tax once they pass a certain amount of sales or number of transactions in that state. Many states use $100,000 in sales or 200 transactions in a year, but some use different numbers, and the rules change over time. That is why I wanted my report to show sales and order counts per state, not just tax collected.

What you need

Python 3.9+
pip install pandas openpyxl
An orders export (CSV) from Shopify, Stripe, Square, WooCommerce or similar

Step 1: Standardize your export

The script expects these columns. Rename yours if they are different:

Column	Meaning
`order_id`	Unique order number
`order_date`	Date of the order
`ship_to_state`	Two-letter state code (TX, CA, NY)
`subtotal`	Product sales before tax and shipping
`tax_collected`	Sales tax charged on the order

Example orders_export.csv:

order_id,order_date,ship_to_state,subtotal,tax_collected
1001,2026-09-02,TX,120.00,9.90
1002,2026-09-03,CA,85.50,6.19
1003,2026-09-03,OR,60.00,0.00
1004,2026-09-10,TX,45.00,3.71
1005,2026-09-18,NY,200.00,17.75

Step 2: The script

Save this as sales_tax_report.py:

import pandas as pd

ORDERS_FILE = "orders_export.csv"
OUTPUT_FILE = "sales_tax_report.xlsx"
REPORT_MONTH = "2026-09"  # format: YYYY-MM

# Example thresholds. VERIFY these with each state's Department of Revenue.
DEFAULT_THRESHOLD = {"sales": 100_000, "orders": 200}
STATE_THRESHOLDS = {
    "CA": {"sales": 500_000, "orders": None},
    "TX": {"sales": 500_000, "orders": None},
}
WARNING_RATIO = 0.80  # flag states at 80% of the threshold


def load_orders(path: str) -> pd.DataFrame:
    df = pd.read_csv(path, parse_dates=["order_date"])
    df["ship_to_state"] = df["ship_to_state"].astype(str).str.strip().str.upper()
    df["subtotal"] = pd.to_numeric(df["subtotal"], errors="coerce").fillna(0.0)
    df["tax_collected"] = pd.to_numeric(df["tax_collected"], errors="coerce").fillna(0.0)
    df["taxable_subtotal"] = df["subtotal"].where(df["tax_collected"] > 0, 0.0)
    df["month"] = df["order_date"].dt.to_period("M").astype(str)
    return df


def monthly_summary(df: pd.DataFrame, month: str) -> pd.DataFrame:
    month_df = df[df["month"] == month]
    summary = (
        month_df.groupby("ship_to_state")
        .agg(
            orders=("order_id", "nunique"),
            gross_sales=("subtotal", "sum"),
            taxable_sales=("taxable_subtotal", "sum"),
            tax_collected=("tax_collected", "sum"),
        )
        .round(2)
        .reset_index()
        .rename(columns={"ship_to_state": "state"})
        .sort_values("tax_collected", ascending=False)
    )
    total = summary[["orders", "gross_sales", "taxable_sales", "tax_collected"]].sum()
    total_row = pd.DataFrame([{"state": "TOTAL", **total.round(2).to_dict()}])
    return pd.concat([summary, total_row], ignore_index=True)


def nexus_check(df: pd.DataFrame, month: str) -> pd.DataFrame:
    end = pd.Period(month, freq="M").end_time
    start = end - pd.DateOffset(years=1)
    window = df[(df["order_date"] > start) & (df["order_date"] <= end)]

    totals = (
        window.groupby("ship_to_state")
        .agg(sales_12m=("subtotal", "sum"), orders_12m=("order_id", "nunique"))
        .round(2)
        .reset_index()
        .rename(columns={"ship_to_state": "state"})
    )

    def status(row):
        rule = STATE_THRESHOLDS.get(row["state"], DEFAULT_THRESHOLD)
        sales_limit, orders_limit = rule["sales"], rule["orders"]
        sales_hit = sales_limit and row["sales_12m"] >= sales_limit
        orders_hit = orders_limit and row["orders_12m"] >= orders_limit
        if sales_hit or orders_hit:
            return "THRESHOLD REACHED"
        sales_close = sales_limit and row["sales_12m"] >= sales_limit * WARNING_RATIO
        orders_close = orders_limit and row["orders_12m"] >= orders_limit * WARNING_RATIO
        return "CLOSE" if (sales_close or orders_close) else "OK"

    totals["nexus_status"] = totals.apply(status, axis=1)
    return totals.sort_values("sales_12m", ascending=False)


def main() -> None:
    df = load_orders(ORDERS_FILE)
    summary = monthly_summary(df, REPORT_MONTH)
    nexus = nexus_check(df, REPORT_MONTH)
    month_df = df[df["month"] == REPORT_MONTH]

    with pd.ExcelWriter(OUTPUT_FILE, engine="openpyxl") as writer:
        summary.to_excel(writer, sheet_name="Summary", index=False)
        nexus.to_excel(writer, sheet_name="Nexus Check", index=False)
        for state, state_df in month_df.groupby("ship_to_state"):
            state_df.drop(columns=["month", "taxable_subtotal"]).to_excel(
                writer, sheet_name=str(state)[:31], index=False
            )

    print(f"Report saved to {OUTPUT_FILE}")
    print(summary.to_string(index=False))


if __name__ == "__main__":
    main()

Step 3: Run it

python sales_tax_report.py

You’ll get an Excel file with:

Summary: orders, gross sales, taxable sales and tax collected per state, plus a total row.
Nexus Check: trailing 12-month sales and order count per state, with a status of OK, CLOSE or THRESHOLD REACHED.
One sheet per state: the order-level detail, so you can back up every number if you are audited or need to file.

Problems I ran into

1. States written in different ways. Some exports had “Texas”, others “TX”, and some had extra spaces. I normalize everything with .str.strip().str.upper(). If your export uses full state names, add a dictionary that maps them to two-letter codes before grouping.

2. Refunds inflated my numbers. My first report ignored refunds. Most platforms export refunds as negative rows or in a separate file. Make sure refunds are included in the same CSV (with negative subtotal and tax_collected) so the totals come out net.

3. Shipping and tax rules differ by state. Some states tax shipping and some don’t. I kept the script focused on product sales. If your state taxes shipping, add a shipping column and include it in the taxable amount.

4. Wrong thresholds. The values in STATE_THRESHOLDS are examples. Rules change, so check each state’s official site before relying on them.

The result

What used to take me about two hours every month now takes a few seconds, and the numbers are the same every time. The nexus sheet was the most useful surprise: it showed which states were getting close to the threshold long before it became a problem.

Ideas to take it further

Pull orders directly from the Shopify or Stripe API instead of a CSV.
Add city and county columns for states with local taxes.
Email the Excel report to your accountant automatically on the 1st of each month.

Want a version that connects straight to the Shopify API? Let me know in the comments.