Skip to main content
By Fruit Stand Team · 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.
The queries run in Snowflake against security_price_history_vw in Fruit Stand’s Historical Stock Prices listing, 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

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

Daily returns

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 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:
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:
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:
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 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:
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:
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. For trailing returns already calculated for every fund and ETF, see the Fund Returns listing.

F.A.Q.

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.
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.
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.
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.
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. The schema is in the dataset docs. Questions? Reach out at contact@fruitstand.dev.