# How to automate SQL reports to Excel, with or without code

> Turn a SQL query into a formatted Excel workbook that is emailed on schedule: the Python script approach, the DRE approach, and when each is the better fit.

[← All guides](https://getdre.com/guides/)

# How to automate SQL reports to Excel

The job: every month, run a query, put the result in a workbook with formatted numbers and a totals row, and email it to finance. There are two common ways to automate it. This guide shows both, with the trade-offs, so you can pick the one that fits.

## The query

Both approaches start from the same SQL: last month’s net revenue by region.

sales.sql

```sql
select region, count(*) as orders, sum(amount) as net
from orders
where order_date >= date_trunc('month', current_date) - interval '1 month'
  and order_date <  date_trunc('month', current_date)
group by region
order by net desc
```

## Option 1: a Python script

The usual first version: pandas runs the query, openpyxl formats the sheet, smtplib sends it, and cron runs the script. It’s quick to write and can do anything you can code.

send\_sales.py

```python
import os, smtplib, pandas as pd, sqlalchemy
from email.message import EmailMessage
from openpyxl.utils import get_column_letter

engine = sqlalchemy.create_engine(os.environ["DATABASE_URL"])
df = pd.read_sql(open("sales.sql").read(), engine)

with pd.ExcelWriter("sales.xlsx", engine="openpyxl") as xl:
    df.to_excel(xl, sheet_name="By region", index=False)
    ws = xl.sheets["By region"]
    for cell in ws["C"][1:]:
        cell.number_format = "#,##0.00"
    ws.append(["Total", int(df.orders.sum()), float(df.net.sum())])
    for i, col in enumerate(df.columns, 1):
        ws.column_dimensions[get_column_letter(i)].width = max(12, len(col) + 2)

msg = EmailMessage()
msg["Subject"], msg["From"], msg["To"] = "Monthly sales", "reports@example.com", "finance@example.com"
msg.add_attachment(open("sales.xlsx", "rb").read(), maintype="application",
                   subtype="vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename="sales.xlsx")
with smtplib.SMTP(os.environ["SMTP_HOST"], 587) as s:
    s.starttls(); s.login(os.environ["SMTP_USER"], os.environ["SMTP_PASSWORD"]); s.send_message(msg)
```

This works. The cost shows up later: the second report copies this file and changes it, a retry on a flaky mail server needs more code, “send September again” means editing the date logic, and a reviewer has to read Python to check a report change. Each new destination (SFTP, a bucket, Slack) is another block of code in every script that needs it.

## Option 2: declare it with DRE

With DRE, the query stays SQL and a short YAML file says what the workbook looks like and where it goes. Formatting, delivery, retries, secrets and logs are done by DRE, the same way for every report.

terminal

```bash
pip install dre-cli          # or brew install get-dre/tap/dre
dre new reports --profile warehouse
cd reports
```

reports/sales/sales.sql

```sql
select region, count(*) as orders, sum(amount) as net
from orders
where order_date >= '{{ run.date.prev_month.start.date }}'
  and order_date <= '{{ run.date.prev_month.end.date }}'
group by region
order by net desc
```

reports/sales/sales.yml

```yaml
queries:
  - {query: sales, tab_name: By region}
output:
  format: xlsx
  columns:
    orders: {total: sum}
    net: {format: "#,##0.00", total: sum}
  destination:
    profile: mail
    to: finance@example.com
    subject: "Sales {{ run.date.prev_month.start.date }}"
```

profiles.yml (the mail server, once per project)

```yaml
destinations:
  mail:
    targets:
      dev: {deliver: false}   # develop without sending
      prod:
        type: email
        host: smtp.example.com
        username: reports@example.com
        password: "{{ env_var('SMTP_PASSWORD') }}"
        from: "Reports <reports@example.com>"
```

terminal

```bash
dre validate -s sales            # checks the YAML, compiles the SQL, shows where the file goes
dre run sales --preview          # builds the workbook from the first 100 rows, sends nothing
dre run sales --target prod      # builds it and emails it
```

Column widths fit the data, headers are styled and the totals row is added for you. To run it every month, name a schedule in `schedules.yml` and have cron or your orchestrator call `dre run --schedule`; see[scheduling SQL reports](https://getdre.com/guides/schedule-sql-reports/). To rerun September later, pass`DRE_RUN_AT` and the dates render as they did then.

[Download a workbook DRE built (the finance example)](https://getdre.com/media/downloads/finance_pack.xlsx)

## Side by side

Task

Python script

DRE

Formatting (number formats, widths, totals)

Code per report (openpyxl)

Declared in YAML

Delivery (email, SFTP, buckets, Slack)

Code per destination

A destination entry

Secrets

Up to each script

env\_var(), never in the files

Rerunning last month

Edit the date logic

DRE\_RUN\_AT or --var

Reviewing a change

Read the script

A diff of SQL and YAML

Custom logic per row (API calls, odd layouts)

Anything you can code

Not its job

## When to keep the script

Keep a script when the output needs logic no report tool expresses, such as calling an API for every row, or when the report is a one-off. Move to DRE when you have several recurring reports, when they go to more than one kind of destination, or when someone other than the author needs to review and change them.

[Your first report in 10 minutes →](https://getdre.com/docs/first-report/)[The monthly finance pack](https://getdre.com/use-cases/monthly-finance-pack/)