SQL for Financial Analysts: Returns, Moving Averages and Drawdown from a Price CSV

Most price histories arrive as a CSV, and most of the questions about them (returns, trend, worst fall, volatility) are one SQL query each. Window functions make them short. You can run them on the file directly, with no database.

Open the SQL on Your Files

The data

Save this as prices.csv. It is a tiny made-up series, so the numbers are easy to check by hand. Your own file needs a date column and a close column.

trade_date,close
2025-01-02,100.00
2025-01-03,102.00
2025-01-06,101.00
2025-01-07,104.00
2025-01-08,103.00
2025-01-09,99.00
2025-01-10,101.00
2025-02-03,105.00
2025-02-04,107.00
2025-02-05,106.00

Open SQL on Your Files and add prices.csv. Each file becomes a table named after the file, so this one is called prices. The queries below were run in DuckDB, the engine the tool uses, and the results shown are the real output. Use trade_date rather than date for the column name, so it is never confused with a type name.

Daily returns

The simple daily return is today's close divided by the previous close, minus one. LAG reads the previous row in date order.

SELECT trade_date, close,
       ROUND(close / LAG(close) OVER (ORDER BY trade_date) - 1, 4) AS daily_return
FROM prices
ORDER BY trade_date;

The first row is NULL because it has no previous day. The second row is 0.02, because 102 / 100 - 1 is 2 percent, and the third is -0.0098. If your data holds several instruments, add PARTITION BY ticker inside OVER so a return never mixes two tickers.

Moving average

A three-day moving average averages the current row and the two before it.

SELECT trade_date, close,
       ROUND(AVG(close) OVER (ORDER BY trade_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS ma_3
FROM prices
ORDER BY trade_date;

On 2025-01-07 the result is 102.33, which is (102 + 101 + 104) / 3, the average of the three rows ending that day. The first two rows average only the rows that exist, so ignore the first window length minus one values when you use the average as a signal.

Drawdown and maximum drawdown

Drawdown is how far the price is below its highest close so far. A running maximum gives the peak.

SELECT trade_date, close,
       MAX(close) OVER (ORDER BY trade_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_peak,
       ROUND(close / MAX(close) OVER (ORDER BY trade_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - 1, 4) AS drawdown
FROM prices
ORDER BY trade_date;

On 2025-01-09 the close is 99 against a peak of 104, so the drawdown is -0.0481, about minus 4.8 percent. The maximum drawdown is the smallest value in that column:

WITH d AS (
  SELECT close / MAX(close) OVER (ORDER BY trade_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - 1 AS dd
  FROM prices)
SELECT ROUND(MIN(dd), 4) AS max_drawdown FROM d;

It returns -0.0481 for this series.

Monthly returns

Take the last close in each month with arg_max, then compare months.

WITH m AS (
  SELECT date_trunc('month', trade_date) AS month,
         arg_max(close, trade_date) AS month_end_close
  FROM prices GROUP BY 1)
SELECT month, month_end_close,
       ROUND(month_end_close / LAG(month_end_close) OVER (ORDER BY month) - 1, 4) AS monthly_return
FROM m ORDER BY month;

January ends at 101 and February at 106, so the February return is 0.0495. The month-end close is the last available close in the file, which is right even when the month does not end on a weekday.

Volatility

Volatility is the standard deviation of the daily returns, and annualising it by the square root of 252 trading days is a common convention.

WITH r AS (
  SELECT close / LAG(close) OVER (ORDER BY trade_date) - 1 AS ret FROM prices)
SELECT ROUND(STDDEV_SAMP(ret), 4) AS daily_vol,
       ROUND(STDDEV_SAMP(ret) * SQRT(252), 4) AS annualised_vol
FROM r;

For this tiny series that is 0.025 daily and 0.3966 annualised. Nine returns is far too few to mean anything, so treat it as a check that the query works. STDDEV_SAMP ignores the NULL on the first row.

Before you trust the output

Check the data first: sort order, duplicate dates, missing days and prices of zero or below all change the answers. The data quality checks before a backtest cover them. These returns are simple price returns that ignore dividends and splits unless your close is already adjusted. For a large file, CSV vs Parquet for market data explains why Parquet is faster to query, and the same SQL works on a Parquet file. The query result can be charted in the tool (bar, line, scatter or histogram) or downloaded.

What this is not

These are analysis queries on data you supply, not investment advice, and not a backtesting engine. A backtest needs more than returns: costs, position sizing and a clean split between past and future data.

Open the SQL on Your Files

Related tool pages

  • Query a Large CSV with SQL: Run SQL on a CSV of 500 MB or more without a database. Filter, group and export the result. Runs in your browser.
  • Query a CSV File with SQL: Query a CSV file with SQL online. Filter, group, sort and join with DuckDB in your browser. No database, no install and no upload.
  • Run SQL on an Excel File: Run SQL queries on an Excel file without importing it into a database. Join sheets from different files and download the result.
  • Query Parquet Files Online: Open and query Parquet files with SQL in your browser. Preview, filter, aggregate and export to CSV without installing anything.
  • Join CSV Files with SQL: Join two or more CSV files with SQL JOIN: inner, left, right or full. Runs in your browser with DuckDB and nothing is uploaded.
  • DuckDB in Your Browser: Use DuckDB in your browser with no setup. Drop in CSV, Excel, JSON or Parquet files and run analytical SQL on your own computer.

Also try

How to use the SQL on Your Files: step by step

  1. Open SQL on Your Files and add the price CSV.
  2. Paste one of the window-function queries above.
  3. Run it and check a few rows by hand.
  4. Chart the result or download it as CSV, Excel or Parquet.

When to use the SQL on Your Files

  • Calculate daily and monthly returns from a price CSV
  • Add a moving average and a drawdown column
  • Estimate volatility without Python
  • Check a vendor price file with SQL before using it

Example

Input: sales.csv + customers.xlsx

Output: SQL query result

Frequently asked questions

Can I calculate returns in SQL without a database?

Yes. Add the CSV to SQL on Your Files, which runs DuckDB in your browser, and use LAG in a window function to divide each close by the previous close.

How do I calculate drawdown in SQL?

Take the running maximum of the close with MAX(close) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), then divide the close by it and subtract one. The smallest value is the maximum drawdown.

Does it work for several tickers in one file?

Yes. Add PARTITION BY ticker inside each OVER clause so every instrument is calculated on its own.

Tip: Add PARTITION BY ticker inside every OVER clause when one file holds several instruments, so values never leak across tickers.