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

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

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

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

reports/sales/sales.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

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)

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

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; seescheduling SQL reports. To rerun September later, passDRE_RUN_AT and the dates render as they did then.

Download a workbook DRE built (the finance example)

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 →The monthly finance pack