> ## Documentation Index
> Fetch the complete documentation index at: https://fruitstand.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# How to Calculate Stock Returns From Daily Prices in SQL

> How to calculate stock returns from daily prices in SQL: daily, log, cumulative, monthly, annualized and rolling returns, with tested queries and pitfalls.

*By [Fruit Stand Team](https://fruitstand.dev/about.md) · November 14, 2026 · Guides*

To calculate a stock's daily return in SQL, divide each day's adjusted
close by the previous day's with `lag()`, partitioned by ticker, and
subtract 1. For any period, divide the ending adjusted close by the starting
one, and annualize with `power(end / start, 365.25 / days) - 1`. Adjusted
prices make splits and dividends count.

Below are tested queries for each kind of return, from daily to rolling
one-year, with the results they give and the mistakes that produce wrong
numbers.

> [!NOTE]
>
> The queries run in Snowflake against `security_price_history_vw` in Fruit
> Stand's [Historical Stock Prices
> listing](https://app.snowflake.com/marketplace/listing/GZTYZTG3LNFA),
> which we publish. They were run on November 14, 2026, and work on any
> table with a ticker, a date and an adjusted close.

## The formulas at a glance

| Return | Formula | In SQL |
| - | - | - |
| Daily | today / yesterday − 1 | `adj / lag(adj) over (partition by code order by date) - 1` |
| Log | ln(today / yesterday) | `ln(adj / lag(adj) over (...))` |
| Over a period | end / start − 1 | `max_by(adj, date) / min_by(adj, date) - 1` |
| Compounded from daily returns | product of (1 + r) − 1 | `exp(sum(ln(1 + r))) - 1` |
| Annualized (CAGR) | (end / start)^(365.25 / days) − 1 | `power(end / start, 365.25 / days) - 1` |
| Annualized volatility | stdev of daily returns × √252 | `stddev(r) * sqrt(252)` |

`adj` is the adjusted close. Every query below uses it, for the reason in
the next section.

## Daily returns

```sql theme={null}
select date, close, adjusted_close,
       close / lag(close) over (partition by code order by date) - 1 as close_return,
       adjusted_close / lag(adjusted_close) over (partition by code order by date) - 1 as total_return
from security_price_history_vw
where exchange_code = 'US' and code = 'NVDA'
  and date between '2024-06-05' and '2024-06-12'
order by date;
```

| Date | Close | Return from `close` | Return from `adjusted_close` |
| - | -: | -: | -: |
| June 7, 2024 | \$1,208.88 | −0.09% | −0.09% |
| June 10, 2024 | \$121.79 | −89.93% | +0.75% |
| June 11, 2024 | \$120.91 | −0.72% | −0.71% |

Nvidia's 10-for-1 split took effect after June 7, 2024, and the stock
traded at the new price from June 10. From `close`, it lost 90% that day; from `adjusted_close`, it rose 0.75%, which is what happened.
`adjusted_close` also adds back dividends, so its returns are total
returns. On June 11 the two differ by 0.01 points: it was the ex-dividend
day for Nvidia's \$0.01 dividend. [Adjusted Close vs. Close](https://fruitstand.dev/blog/adjusted-close-vs-close.md) explains
how the adjustment works.

The `partition by code` matters as soon as the table holds more than one
ticker. Without it, `lag()` compares the first day of one stock with the
last day of the stock before it.

## Log returns, and why you can't add simple returns

Daily returns compound; they don't add up. Over 2025:

```sql theme={null}
with daily as (
    select code, date,
           adjusted_close / lag(adjusted_close) over (partition by code order by date) - 1 as r,
           ln(adjusted_close / lag(adjusted_close) over (partition by code order by date)) as log_r
    from security_price_history_vw
    where exchange_code = 'US' and code in ('SPY', 'AAPL', 'KO')
      and date between '2024-12-31' and '2025-12-31'
)
select code,
       round(sum(r), 4) as sum_of_simple,
       round(exp(sum(ln(1 + r))) - 1, 4) as compounded,
       round(sum(log_r), 4) as sum_of_logs,
       round(exp(sum(log_r)) - 1, 4) as from_logs
from daily
group by code
order by code;
```

| Ticker | Sum of daily returns | Compounded (correct) | Sum of log returns | From log returns |
| - | -: | -: | -: | -: |
| AAPL | 13.82% | 9.05% | 8.67% | 9.05% |
| KO | 16.01% | 15.60% | 14.50% | 15.60% |
| SPY | 18.17% | 17.72% | 16.31% | 17.72% |

Adding Apple's daily returns overstates its 2025 return by almost 5
points; the more volatile the stock, the bigger the error. Log returns do
add up, which is why they're used in statistics and models, but a sum of
log returns isn't a percentage return until you take `exp()` of it and
subtract 1. Both routes give the same compounded answer.

The window starts on December 31, 2024, so the first return in the sum is
January 2's. Start a period on the last trading day before it, not on its
first day, or you lose a day of return.

## Cumulative return and growth of \$10,000

For the return between two dates you don't need daily returns at all: the
ratio of the adjusted closes is the compounded return. To chart how \$10,000
would have grown, divide every day by the first:

```sql theme={null}
select code, date,
       round(10000 * adjusted_close / first_value(adjusted_close) over (partition by code order by date), 2) as value_of_10k
from security_price_history_vw
where exchange_code = 'US' and code in ('SPY', 'AAPL', 'KO')
  and date >= '2015-12-31'
qualify date in ('2015-12-31', '2020-12-31', '2025-12-31')
order by code, date;
```

| Ticker | Dec 31, 2015 | Dec 31, 2020 | Dec 31, 2025 |
| - | -: | -: | -: |
| AAPL | \$10,000 | \$54,425 | \$114,550 |
| KO | \$10,000 | \$15,039 | \$22,283 |
| SPY | \$10,000 | \$20,199 | \$39,466 |

Drop the `qualify` line to get every day, for a chart. `qualify` filters
after the window runs, so `first_value` still sees the full history.

## Monthly and yearly returns

A month's or year's return runs from the last trading day of the previous
period to the last trading day of this one. `max_by(adjusted_close, date)`
picks the last price in each group:

```sql theme={null}
with year_end as (
    select code, year(date) as yr, max_by(adjusted_close, date) as adj
    from security_price_history_vw
    where exchange_code = 'US' and code in ('SPY', 'AAPL', 'KO')
      and date between '2020-01-01' and '2025-12-31'
    group by code, year(date)
)
select code, yr,
       round(adj / lag(adj) over (partition by code order by yr) - 1, 4) as annual_return
from year_end
qualify annual_return is not null
order by code, yr;
```

| Year | SPY | AAPL | KO |
| - | -: | -: | -: |
| 2021 | 28.73% | 34.65% | 11.37% |
| 2022 | −18.18% | −26.40% | 10.61% |
| 2023 | 26.18% | 49.01% | −4.43% |
| 2024 | 24.89% | 30.71% | 8.88% |
| 2025 | 17.72% | 9.05% | 15.60% |

The query starts in 2020 so that 2021 has a base year; `qualify` drops
2020's empty row. SPY's five returns match the dividends-reinvested
returns on [Total Real Returns](https://totalrealreturns.com/n/SPY) to the
hundredth of a point.

For monthly returns, group by `last_day(date)` instead of `year(date)`.
The row for the current month holds the return so far.

## Annualized return and volatility

Annualize a multi-year return by raising the growth ratio to the power of
one over the number of years. Volatility is the standard deviation of daily
returns, scaled by the square root of 252, the usual number of trading
days in a year:

```sql theme={null}
with px as (
    select code, date, adjusted_close,
           adjusted_close / lag(adjusted_close) over (partition by code order by date) - 1 as r
    from security_price_history_vw
    where exchange_code = 'US' and code in ('SPY', 'AAPL', 'KO')
      and date between '2015-12-31' and '2025-12-31'
)
select code,
       round(max_by(adjusted_close, date) / min_by(adjusted_close, date) - 1, 4) as total_return,
       round(power(max_by(adjusted_close, date) / min_by(adjusted_close, date),
                   365.25 / datediff(day, min(date), max(date))) - 1, 4) as annualized_return,
       round(stddev(r) * sqrt(252), 4) as annualized_volatility
from px
group by code
order by code;
```

| Ticker | 10-year total return | Annualized return | Annualized volatility |
| - | -: | -: | -: |
| AAPL | 1,045.5% | 27.61% | 29.04% |
| KO | 122.8% | 8.34% | 18.06% |
| SPY | 294.7% | 14.71% | 18.01% |

Using calendar days (`365.25 / days`) rather than a count of rows keeps
the result right when the data has holidays or gaps. Don't annualize
periods shorter than a year: a good quarter turns into an implausible
yearly rate.

## Rolling one-year returns

A rolling return compares each day with the same day a year earlier. A
year ago is often a weekend or holiday, so match on dates rather than rows.
Snowflake's `asof join` finds the last trading day on or before the target
date:

```sql theme={null}
with px as (
    select code, date, adjusted_close
    from security_price_history_vw
    where exchange_code = 'US' and code = 'SPY'
      and date >= '2024-10-01'
),
base as (
    select p.code, p.date, p.adjusted_close,
           b.date as base_date, b.adjusted_close as base_adj
    from px p
    asof join px b
        match_condition (dateadd(year, -1, p.date) >= b.date)
        on p.code = b.code
)
select code, date, base_date,
       round(adjusted_close / base_adj - 1, 4) as return_1y
from base
where date >= '2026-11-06'
order by date;
```

For November 9, 2026, it matches November 7, 2025, the Friday before a
year-ago Sunday, and returns 13.39%. A window of 252 rows would drift a
few days whenever a year has more or fewer trading days. Change `year` to
`month` or `day` in `dateadd` for other windows.

## Mistakes that give wrong returns

* **Using `close`.** Every split shows up as a crash or a jump, and
  dividends are left out, so long-run returns come out too low.
* **No `partition by`.** One ticker's first return is computed against
  another ticker's last price.
* **Adding daily returns.** Compound them with `exp(sum(ln(1 + r)))`, or
  take the ratio of the end and start prices.
* **Starting on the period's first day.** A 2025 return measured from
  January 2 misses January 2's move. Start from the last close before.
* **Counting rows as days.** 252 rows aren't always a year, and a ticker
  with missing days makes it worse. Use dates, as in the annualized and
  rolling queries.

For the same ideas applied to a trading strategy, with drawdowns, see [How
to Backtest a Trading Strategy in Snowflake
SQL](https://fruitstand.dev/blog/backtest-trading-strategy-snowflake.md). For trailing returns
already calculated for every fund and ETF, see the [Fund Returns
listing](https://fruitstand.dev/datasets/fund-returns.md).

## F.A.Q.

> [accordion] Should I use close or adjusted close for returns?
>
> Adjusted close. It accounts for splits and dividends, so returns across those dates are correct and include income. Use `close` only for the traded price on a given day.

> [accordion] What's the difference between simple and log returns?
>
> A simple return is today / yesterday − 1. A log return is ln(today / yesterday). Log returns add across days; simple returns compound. For returns you report, convert log returns back with exp() − 1.

> [accordion] Why is the first daily return null?
>
> `lag()` has no earlier row for the first date in each partition. Start the query one trading day before the period you want, or filter the null out.

> [accordion] Why 252 for volatility but 365.25 for annualized return?
>
> Volatility scales with the number of returns in a year, about 252 trading days. Annualized return scales with time, so it uses calendar days between the first and last date.

> [accordion] Can I calculate returns for every stock at once?
>
> Yes. Drop the ticker filter and keep `partition by code`. The same queries run across every US stock, ETF and mutual fund in the listing.

To run these queries on every US stock, ETF and mutual fund, start a free
30-day trial of the [Historical Stock Prices listing on the Snowflake
Marketplace](https://app.snowflake.com/marketplace/listing/GZTYZTG3LNFA).
The schema is in the [dataset docs](https://fruitstand.dev/datasets/historical-stock-prices.md).
Questions? Reach out at [contact@fruitstand.dev](mailto:contact@fruitstand.dev).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.