Building Excel Reports From SQL Queries People Trust

The monthly sales report exists in four versions. Someone in finance has one that adds up, an analyst has another built from a different query, a manager has last month's with a typo corrected by hand, and the new version is open on a laptop right now. The reader has no way to tell which is right, so they ask, and the analyst spends the first week of every month answering "why does this differ?". Reports people trust come from a process with one source of numbers and a file that can be regenerated on demand. This guide covers that process end to end: the query, the generator, the formatting, the checks, the housekeeping and the schedule. The spokes linked throughout go into each step, and sending database results to Excel is the short version if you only need to get a table into a sheet today.

What trust means for a report

A reader trusts a report when three questions have quick answers. Where did the numbers come from? Do they add up? Will next month's version be built the same way? A report that answers all three has a visible source, visible checks and a repeatable build. Most of the work in this guide is making those three things true without the reader having to ask.

Source means the query, stated or at least identifiable. Checks mean control totals that tie to something independent, such as the ledger or the billing system. Repeatable means a script and not a person following a recipe. A beautifully formatted workbook assembled by hand each month fails the third test every time someone is on holiday.

There is also a quieter requirement: the report should be boring. The same sheets in the same order, the same column names, the same number formats, month after month. People build their own formulas and pivot tables on top of a report, and every surprise in layout breaks one of them (building multi-sheet Excel reports covers the layout side).

Step one: design the query first

Do the thinking in SQL, before a workbook exists. The query should return the report's rows, in the report's order, with the report's column names, and the business rules should live in the query where they can be read and tested. How does the report define revenue? Are refunds netted or listed? Which date decides the month? Which customers are excluded as test accounts? If those rules are applied by formulas in the sheet, they are invisible, and two analysts will implement them differently.

SELECT
  date_trunc('month', o.ordered_at)::date          AS month,
  c.region                                          AS region,
  count(DISTINCT o.id)                              AS orders,
  sum(oi.qty * oi.unit_price)                       AS gross_revenue,
  sum(oi.qty * oi.unit_price) FILTER (WHERE o.status = 'refunded') AS refunds,
  sum(oi.qty * oi.unit_price) - coalesce(sum(oi.qty * oi.unit_price) FILTER (WHERE o.status = 'refunded'), 0) AS net_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN customers c    ON c.id = o.customer_id
WHERE o.ordered_at >= %(start)s AND o.ordered_at < %(end)s
  AND c.is_test = false
GROUP BY 1, 2
ORDER BY 1, 2;

Several choices are worth noticing. The date range uses a half-open interval (>= start, < end), which handles end-of-month timestamps without surprises. The test-account exclusion is a visible line. The aggregation happens in the database, so the workbook gets a few hundred rows rather than a few million. Parameters are bound, never pasted into the string (designing a report query before touching Excel goes through these decisions in order).

Step two: get the result into a data frame

Read the result with a driver that returns native types, keep money as decimals and keep dates as dates.

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg://[email protected]/shop")
with engine.connect() as conn:
    df = pd.read_sql_query(
        text(open("sql/monthly_sales.sql").read()),
        conn,
        params={"start": "2026-09-01", "end": "2026-10-01"},
    )

Use a read-only database role for report jobs. It limits what a bug or a leaked credential can do, and it makes the audit trail honest: the report job cannot change what it reports on. Keep the SQL in its own file rather than in a Python string, so a diff shows a change to the query as a change to SQL (version controlling the query behind an Excel report).

numeric columns arrive as Decimal objects, which pandas stores as object columns. Convert them deliberately: df["net_revenue"].astype(float) is fine for display when you round on the way out, and keeping integer cents is the safer path if the sheet will be summed by other people. Say which in your notes.

Step three: write the workbook

For a formatted report, XlsxWriter gives the most control over a new file, and pandas can hand it a data frame.

with pd.ExcelWriter("sales_2026-09.xlsx", engine="xlsxwriter",
                    engine_kwargs={"options": {"strings_to_formulas": False}}) as xw:
    df.to_excel(xw, sheet_name="Sales", index=False, startrow=1, header=False)
    wb, ws = xw.book, xw.sheets["Sales"]

    head = wb.add_format({"bold": True, "bg_color": "#EEEEEE", "border": 1, "text_wrap": True})
    money = wb.add_format({"num_format": "#,##0.00;[Red]-#,##0.00"})
    whole = wb.add_format({"num_format": "#,##0"})
    datef = wb.add_format({"num_format": "mmm yyyy"})

    for col, name in enumerate(df.columns):
        ws.write(0, col, name, head)
    ws.set_column("A:A", 12, datef)
    ws.set_column("B:B", 14)
    ws.set_column("C:C", 10, whole)
    ws.set_column("D:F", 16, money)
    ws.freeze_panes(1, 0)
    ws.autofilter(0, 0, len(df), len(df.columns) - 1)

The header row is written separately so it gets its own format; the data starts on the second row. Number formats are applied to columns, not baked into strings, so the cells stay numeric and the reader can sum them. The frozen header and the filter are small things that every reader notices when they are missing (adding formulas and number formats from Python and freezing panes, filters and column widths cover the details).

Write user-controlled text as text, as shown by the strings_to_formulas option, so that a name beginning with an equals sign is not evaluated (formula injection in Excel exports).

Step four: add what makes it a report

A table is not yet a report. A few additions change how people use it.

A totals row, written as a formula (=SUBTOTAL(109, D2:D38)) so that it follows filters, or as a plain value when you want the total fixed. State which you chose.

Conditional formatting, for the cells that matter: negative net revenue in red, a refund rate above a threshold highlighted, the top region in bold (writing conditional formatting from code).

Charts, if they help a reader see something the table hides, such as a trend (sending charts from a query to an Excel sheet). One clear chart beats four decorative ones.

A README sheet as the first or last tab: what the report is, the period, the query's name and version, the time it was generated, the definitions of key columns and who to ask (putting a README sheet into every generated workbook). This is the single cheapest trust feature.

Print setup, for the reports that are still printed: a wide page orientation, repeat the header row on each page, fit to one page wide.

Step five: format numbers for the reader

The people reading a finance report notice rounding and currency before they notice anything else. Decide, once, how numbers appear: whole currency units for a summary and two decimals for a ledger view, thousands separators, negatives in parentheses or red, percentages with one decimal, dates in one format. Apply the decisions through number formats in the cells, not by rounding the stored values, unless the business rule says that the rounded figure is the true one (rounding and formatting numbers for finance readers).

Currency and locale deserve their own attention. A workbook written by a script contains stored numbers and format codes, and the reader's Excel displays them with the reader's settings for separators. A format code such as #,##0.00 displays as 1.234,50 on a German system, which is correct and surprising to a developer who tested only on their own machine. Put the currency in the column header or in a format code, never in the stored value (handling currency and locale in generated workbooks).

Step six: check the numbers before anyone sees them

Before the file leaves the job, compare it to the database through paths that do not share the generator's logic.

1. Row count. The number of data rows in the workbook equals the number of rows the query returned.

2. Control totals. Re-run a simple independent query, for example total net revenue for the period straight from the ledger table, and compare it to the sum in the workbook to the cent.

3. Reconciliation to another source. If finance has a figure for the month, compare against it and report the difference.

4. Sanity bounds. Fail if revenue is negative, if a region is missing that was present last month or if the total moved by more than a set percentage.

ledger_total = conn.execute(text(
    "SELECT sum(amount) FROM ledger WHERE posted_at >= :s AND posted_at < :e AND account = 'sales'"
), {"s": "2026-09-01", "e": "2026-10-01"}).scalar_one()

assert abs(float(df["net_revenue"].sum()) - float(ledger_total)) < 0.01, "does not tie to ledger"

A failed check should stop the job and notify a person, leaving the previous report in place. A wrong number sent on time is worse than a correct number sent late (testing a generated Excel report lists more checks, including reopening the saved file and reading it back).

Step seven: keep the query and the generator in version control

The report is code, and it should be treated like code. The repository holds the SQL file, the generator script, the tests and a short README. Every change goes through review, and the commit hash goes into the workbook's README sheet, so a question about a number can be traced to the exact query that produced it. When finance changes the definition of revenue, the change is a pull request with a description, and the old report can be regenerated from the old commit. That history answers the audit question, "why did this number change in October?", with a link.

Name the files after what they produce, and keep parameters out of the SQL text so the same query serves every month (version controlling the query behind an Excel report shows a layout that works).

Step eight: schedule it and say when it fails

A report that depends on a person remembering to run it will eventually be late. Run it on a schedule from cron, a workflow tool or your CI system, on the first business day after the data closes. Write the output to a fixed location, with a dated name, and send it to the readers or post a link.

# 06:00 on the 2nd of each month
0 6 2 * * /opt/reports/run.sh monthly_sales >> /var/log/reports/monthly_sales.log 2>&1

Make the failure path as good as the success path. If a check fails or the database is unavailable, send a message to the owner with the reason, and do not send a partial report. Log row counts, totals and the duration of each run, so you can see a slow drift before it becomes an outage (emailing a scheduled Excel report from a database and automating weekly Excel reports with Python cover delivery).

Data arrival matters too. If the source tables load overnight, check that the load finished before the report runs, for example by querying a load log, and fail with a clear message if it did not. A report built from half-loaded data is the typical cause of a number that is wrong on the 2nd and right on the 3rd.

Protecting the file and the readers

Once the workbook is generated, decide how it is protected. Locking the formula cells and the structure helps against accidental edits; it is not security, and the files should be treated accordingly (protecting a generated workbook from accidental edits). If the report contains personal or commercially sensitive data, restrict where it is delivered, and prefer an authenticated download link to an attachment. Build the file from data so that nothing hidden travels with it (hidden sheets and metadata leaking out of shared workbooks).

Making Excel open it cleanly

A few details decide whether the first impression is good. Dates should be real dates with a format, not text. Long identifiers should be text so that Excel does not display them in scientific notation. Column widths should fit the content without anyone widening them. Wrapped headers should not clip. The first sheet should be the one readers want. The file should open without a repair prompt, which means closing the writer properly and avoiding invalid characters in sheet names (31 characters at most, and none of []:*?/\). See making Excel exports open cleanly for the full list and an exported Excel date that shows as a number for the usual date trap.

Reports that grow

Reports start as one sheet and grow. Each new request adds a column, then a sheet, then a chart. Resist adding to a report without asking what decision the new part supports. A management pack that has forty tabs is read in the first three. When growth is justified, keep the structure: one query per sheet, each in its own file, each checked by its own totals. When a report has become a dashboard, it may deserve a different tool (building dashboards in Excel from SQL output).

Alternatives to a generated workbook

Not every reader needs a file. A shared dashboard, a view in the database exposed through a BI tool or a CSV for another system may serve better. Excel suits readers who want to filter, add their own columns and keep a copy, and finance teams often do. The rule of thumb is to ask what the reader will do with the numbers. If the answer is "look at them", a dashboard or PDF is fine. If the answer is "work with them", give them a workbook with clean data, and treat the formatting as a courtesy rather than as the product.

A worked example from request to schedule

A regional sales director asks for "the monthly numbers by region". The first conversation matters more than the code. Ask what decision the report supports. The answer is usually that she reviews each region's revenue against budget before the Monday meeting and wants to see which region is behind. That tells you the report needs net revenue, the budget figure for comparison, the variance in currency and percent, and an order that puts the worst variance at the top. It does not need order-level detail, a chart of daily sales or fifteen columns.

Next, agree the definitions in writing. Revenue means invoiced amounts net of credit notes, booked by invoice date, in the reporting currency at the monthly average rate, excluding internal test accounts. The budget comes from a table the finance team maintains. Put those sentences into the README sheet and into comments at the top of the SQL file, and ask the director to confirm them. Ten minutes here prevents the argument in month three about whether refunds count.

Then write the query against a copy of last month's data and check it against a number the director already trusts, such as the figure finance published. If they disagree, find out why before building anything. Typical culprits are a different date column, a missing currency conversion and test accounts that finance excludes by a rule nobody told you about. A report that reproduces last month's trusted figure on day one earns its credibility on day one.

After that, build the workbook with one Summary sheet, one sheet per region for detail if the director wants to drill in, and the README. Run the checks, schedule the job for 06:00 on the second business day, and send the file to the director and the regional leads with a short message that says what changed since last month, if anything. Watch the first two runs yourself. By the third run nobody should need to think about it, which is the real measure of success.

Serving more than one audience

The same query often serves several readers with different needs. Finance wants every line and the reconciliation. The director wants a one-page summary. Regional managers want only their region. Resist producing one workbook that tries to be all three. Instead, run the query once, keep the full result in memory and write several outputs from it: a full workbook for finance, a summary for the director and a per-region file for each manager, each with only the rows that person should see. That also gives you a natural place to apply access rules, since a regional manager's file never contains another region's data.

When you split by recipient, compute the control totals per file and across files: the regional totals must add to the full total. A check that the per-region files sum to the finance workbook catches filter mistakes that a single-file check cannot. Name the files so they sort sensibly and carry the period, such as sales_2026-09_north.xlsx, and keep the generation time inside the file rather than in the name, so reruns overwrite cleanly.

Keeping the query fast and kind to the database

A report job runs against a system that other people depend on. Treat it politely. Run it against a replica or at a quiet hour when you can. Keep the date filters sargable, which means comparing the raw column to bounds as in the example, not wrapping it in a function, so an index on the date can be used. Aggregate in the database and fetch only the result. Set a statement timeout for the report's role, so a mistake becomes an error instead of a lock that blocks the application. If the query is slow, read its plan before adding hardware (the plan shows whether an index is used before you spend money on hardware).

Materialize heavy aggregates when several reports share them. A monthly summary table, refreshed once after the data load, lets ten reports read a few thousand rows instead of each scanning the order history. Document the refresh as part of the pipeline and include it in the preflight check, so a report never reads a stale summary.

Failure modes worth planning for

Most failures in report jobs come from a short list, and each has a cheap defense.

The data is late. The overnight load did not finish. Check a load log table before running and fail with a message saying so.

The data is partly wrong. A source system sent a duplicate file. Reconcile totals to an independent source, and compare against the previous period for a sudden jump.

The schema changed. A column was renamed. The query fails loudly, which is what you want, as long as the job alerts someone. Never wrap the query in a handler that swallows errors and writes an empty workbook.

The workbook is corrupt. A writer was not closed, or a sheet name was invalid. Reopen the saved file in the job and read a cell back.

The delivery failed. The mail server rejected a large attachment. Check the file size against the limit and fall back to a link.

Nobody noticed. The job has been failing for three weeks and the report has not arrived. Send a heartbeat message on success too, so silence is a signal.

Write these into a short runbook next to the code. The person on call at two in the morning will not remember the design.

Documentation the reader will actually read

Readers skip long documents and read short, specific ones. The README sheet should fit on one screen and answer five questions: what is this, for which period, from which source, how were the key figures defined and whom do I ask. Put a version or commit reference at the bottom, and the time in UTC with the time zone named. Where a column is easy to misread, add a one-line note in the header cell comment or in a definitions table on the README sheet. A column called net_revenue is clear to the author and ambiguous to a stranger; "Revenue after credit notes, excluding tax" is clear to both.

Keep a changelog for the report as a whole, even a three-line one in the repository: the date, the change and the reason. When a reader asks why this month looks different, you can answer in a sentence.

A short checklist before the first release

Before a report goes to readers for the first time, run through these.

• The query is in version control, parameterized and reviewed.

• Definitions are written down and confirmed by the person who owns the numbers.

• The result reproduces a figure the business already trusts.

• Control totals and row counts are checked in code and fail the job when they do not hold.

• Numbers are numeric cells with formats, dates are dates and identifiers are text.

• User text cannot become a formula.

• The workbook has a README sheet, a frozen header row and sensible widths.

• The job runs on a schedule, alerts on failure and logs its counts.

• The file contains nothing hidden that you did not put there.

• A second person has opened the file on their own machine and agreed it looks right.

That list is short on purpose. Reports earn trust by being correct and consistent, and the checklist covers the failures that cost the most.

When a workbook is the wrong report format

A workbook is the wrong format when the data is too large for a sheet, since Excel holds 1,048,576 rows and slows long before that (when Excel files are too big). It is wrong when many people need the same live numbers, because every copy goes stale the moment it is emailed. It is wrong when the report is a decision aid for executives who will read it on a phone. And it is wrong when the audience needs an audit trail of who changed what, since a file passed around by email has none. In those cases publish from a system of record, and offer the workbook as an export of it.

Where to go next

If you are starting, read designing a report query before touching Excel and version controlling the query behind an Excel report first, because the rest rests on them. For presentation, continue with writing conditional formatting from code, sending charts from a query to an Excel sheet and rounding and formatting numbers for finance readers. For the monthly cycle, read producing a monthly management pack from SQL. For files shared abroad, read handling currency and locale in generated workbooks, and make a README sheet the habit.

The library documentation is the authority on options: the XlsxWriter documentation for formatting and charts, the pandas to_excel reference for the data frame side, and the PostgreSQL documentation on date_trunc for the month bucketing used in the query above. The site's tools are useful for checking small samples of CSV or JSON in the browser.