Build a data dashboard with AI: from Excel or SQL

To build a data dashboard with AI, show your data to a coding agent and ask it to write the analysis code: it reads your Excel, CSV or SQL data with pandas, cleans and summarises it, and turns it into an interactive Streamlit dashboard in a few hours. If the dashboard is part of a product, you do the same with Next.js and a charting library. What needs your attention isn’t the code; it’s whether the numbers are right and whether the data stays private.

This post goes step by step from a sales spreadsheet to a dashboard you can share: tools, the first prompt, connecting to a database read-only, checking the AI’s maths and deploying it.

What we’re building

The goal is to replace an Excel report you update by hand every week with a dashboard that updates itself. A typical sales dashboard has: a period total, change versus the previous period, a monthly revenue chart, top products, a breakdown by region or channel, and a date filter.

We’ll do it the vibe coding way: the agent writes the code, you describe what you want to see and check the result. In data work this approach has one condition: you need to know your data well enough to question the numbers it produces.

Which path: Streamlit or Next.js?

The quick path: Python, pandas and Streamlit. One Python file becomes a working web interface. Filters, tables, charts and metric cards take a few lines each. If you need an internal dashboard for yourself, your manager or a small team, this is the shortest route. pandas reads Excel, CSV and SQL equally well.

The product path: Next.js and a charting library. If customers will see the dashboard and you need user login, per-user data permissions and design that fits your brand, a React-based app is the better fit. Recharts, Chart.js and ECharts are common choices. Data is queried on the server, in an API route, and only the summary the user needs reaches the browser.

If you’re unsure, start with Streamlit. Moving to a product is much easier once you know which charts people actually use.

Step 1: setup

A terminal coding agent (Claude Code, Codex, Gemini CLI, Cursor’s CLI and so on) and Python are enough:

mkdir sales-dashboard && cd sales-dashboard
git init
python3 -m venv .venv
source .venv/bin/activate          # Windows: .venv\Scripts\activate
pip install pandas openpyxl streamlit plotly
mkdir data && cp ~/Downloads/sales_2026.xlsx data/
echo "data/" >> .gitignore
echo ".env" >> .gitignore

I keep data/ out of git from the start; customer data has no business in the repository.

Step 2: get to know the data first

The most common mistake is telling the agent “build me a dashboard” straight away. Ask it to understand the data first. This first turn draws nothing; it only reports:

Read data/sales_2026.xlsx with pandas and write me a data report:
- Sheet names, row and column counts per sheet
- Each column’s type, share of empty values and 5 sample values
- Format of date and money columns (any comma decimals or mixed formats?)
- Duplicate rows and likely key columns (such as order ID)
- Anything that looks inconsistent
Don’t change any file and don’t draw charts. Save the report as analysis/data_report.md.

Read that report. Findings like “37 rows in the Amount column came through as text” or “the same order ID appears twice” affect every number that follows. You decide which are errors and which are real (refunds, split orders); don’t let the agent fix the data by guessing.

Step 3: the dashboard prompt

Once the data is clean, ask for the dashboard. Write down the questions it must answer; the agent can suggest the chart types:

Write app.py with Streamlit. Data: data/sales_2026.xlsx, sheet "Sales".

Put cleaning in its own function (load_data), cached with st.cache_data.
Refunds (Amount < 0) count towards revenue and are also shown as a separate metric.

The dashboard should answer:
1. Total revenue, order count and average order value for the selected date range
   (each with change versus the same period last year)
2. Monthly revenue trend (line chart)
3. Top 10 products by revenue (horizontal bar)
4. Revenue by region (bar) with a region filter

Rules:
- All calculations in metrics.py as pure functions.
- Write pytest tests for metrics.py using a small sample dataset with hand-calculated results.
- Format money with the right currency symbol and thousands separators.
When done, run the tests and tell me the streamlit run command.

Keeping the calculations in a separate file of testable functions is the most important part of this prompt. The UI is easy to change; a wrong total function breaks every chart.

pytest -q
streamlit run app.py

Look at the dashboard in the browser, then ask for small changes: “The product labels are cut off, make it horizontal.” “Default the date filter to this month.” Commit after each change so you can step back.

Step 4: connect to the database read-only

If the data lives in your app’s database rather than a spreadsheet, the key rule is: the dashboard’s user can only read. A separate PostgreSQL user:

CREATE ROLE dashboard_reader LOGIN PASSWORD 'a-strong-password-here';
GRANT CONNECT ON DATABASE app TO dashboard_reader;
GRANT USAGE ON SCHEMA public TO dashboard_reader;
GRANT SELECT ON orders, order_items, products TO dashboard_reader;

Put the connection string in .env (DATABASE_URL=...) and tell the agent only the variable name. A few more practical points:

• Grant access only to the tables you need. If the users table holds emails and phone numbers and the dashboard doesn’t need them, it shouldn’t be able to read them.
• Use a read replica or a nightly copy where you can. A heavy query the agent writes can slow down the live system.
• Set a query timeout (statement_timeout in PostgreSQL) and limit result sizes.
• Ask the agent to keep SQL in separate .sql files; it makes queries easier to read and run by hand.

Step 5: check the numbers

This is the most important section. A dashboard’s most dangerous failure isn’t crashing; it’s showing a wrong number in a convincing chart.

Compare the headline totals by hand

Calculate 3–4 key figures another way: a pivot table in Excel, or a simple SQL query you write yourself. March revenue, for example:

SELECT SUM(total) FROM orders
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01'
  AND status <> 'cancelled';

If the dashboard doesn’t match, don’t move on until you can explain the difference. The answer is usually one of: are cancellations included, where are refunds, is tax included, which time zone?

The silent wrong join

This is the SQL mistake AI makes most often and the hardest to spot:

-- WRONG: each order is repeated once per line item
SELECT SUM(o.total)
FROM orders o
JOIN order_items i ON i.order_id = o.id;

An order with three items becomes three rows and o.total is added three times. The query runs fine, the result looks plausible, and revenue is inflated. The fix is to aggregate at the right level: either sum over orders alone, or summarise items per order first and then join. To check, ask the agent:

For each JOIN in this query: is the row count after the join the same as the
row count of the main table before it? Show COUNT(*) and COUNT(DISTINCT o.id).

If the two numbers differ, rows are multiplying somewhere.

Other traps

• Date boundaries: BETWEEN '2026-03-01' AND '2026-03-31' on a timestamp column misses most of 31 March. Use a half-open range.
• Time zones: if the database stores UTC, orders near midnight land on the wrong day.
• Averages of averages: the mean of each region’s average order value is not the overall average order value.
• Missing values: rows with NaN silently drop out of some pandas operations.

Step 6: share and deploy

• Streamlit: for internal use, run it on a server or in a Docker container behind your company network or a login layer. Streamlit has its own hosting service too, but don’t put a dashboard with customer data on a public URL; you should know who has access.
• Next.js: deploy to a platform such as Vercel or your own server. Check authentication, and that each user sees only their own data, on the server; filtering only in the UI is not security.
• Keeping data fresh: for spreadsheet-based dashboards, reading the file from a shared folder or loading it into a database with a small nightly script ends the manual updates.

What the AI commonly gets wrong

• “Fixing” data by guessing: filling blanks with zero, dropping dates it can’t parse. Have it write every cleaning rule out explicitly and approve them.
• Made-up column names: without seeing the schema it invents a column like customer_name. Give it the real schema.
• Number formats: reading 1.234,56 as 1.234. Ask it to show raw and parsed values side by side.
• Charting everything: a 15-chart dashboard is one nobody looks at. Limit it to the questions that matter.
• “I verified it”: an agent testing its own code with its own test can make the same wrong assumption twice. Check one number yourself.

Security and privacy

• Connection details in .env, with .env and data/ out of git.
• Never paste a password or connection string into the chat. Give the agent the variable name.
• Don’t send raw customer data to the model. The agent needs the schema and a few sample rows to write code; anonymise the samples or generate fake ones. The code processes the data on your machine, not the model. Your GDPR (or KVKK) obligations for personal data don’t change.
• Read the commands and SQL the agent runs. Running anything with DELETE, UPDATE or DROP through the dashboard’s user should be impossible anyway; that’s what the read-only user is for.
• Know who can see the dashboard’s URL. Sharing a link is sharing the data.

Doing it with AgentVera

None of the above needs a particular tool. I do this kind of work in AgentVera because it shortens a few database steps:

• Database manager. The database manager connects to PostgreSQL, MySQL/MariaDB and SQLite. A connection you mark “read-only” is opened read-only on the server side too. You can run the agent’s SQL yourself in the SQL editor to compare totals, and use EXPLAIN to spot heavy queries.
• Controlled agent access. If you turn on agent access for a connection, agents that support MCP run queries through the agentvera-db tool; they never see the password, the mode is read-only by default and every query is logged. The “AI” button that turns a request into SQL sends table and column names, not table rows.
• DB Guard. If the agent writes an index or view migration for the dashboard, DB Guard scans it at the end of the turn and flags risks such as locking index builds or data loss.
• Parallel agents and previews. Claude Code can write the SQL and metrics in one pane while Codex works on the UI in another, each in its own git worktree. Preview environments start each worktree’s server on its own port.
• Diff review. Code review gives the metric functions a second look before you merge.

The database manager and agent access are in the Pro and Team plans. For more on checking agent-written code, see AI code review; for working in parallel, see git worktrees for parallel agents.

Quick checklist

• The agent wrote a data report first, and you approved the cleaning rules
• Calculations live in separate, tested functions
• At least 3 headline numbers checked by hand another way
• Row counts checked for every JOIN
• The database connection uses a SELECT-only user
• .env and data/ out of git, no customer data sent to the model
• Dashboard access limited to known people

Conclusion

Building a dashboard with AI speeds up the coding but doesn’t hand off the thinking. The agent writes pandas, SQL and Streamlit code in minutes; deciding which number is right and which data can be shared is still your job. Pick an Excel report you update by hand this week, ask the agent for a data report first, build the first Streamlit dashboard and check three numbers yourself.