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’sasof join finds the last trading day on or before the target
date:
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.
F.A.Q.
Should I use close or adjusted close for returns?
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.What's the difference between simple and log returns?
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.
Why is the first daily return null?
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.Why 252 for volatility but 365.25 for annualized return?
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.
Can I calculate returns for every stock at once?
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.