Calculating Five-Day Stock Returns and Daily Percentile Ranks
Summary
The document presents a SQL query for calculating a stock’s five-day price return and ranking that return against other stocks on the same date. It uses a window function to retrieve each instrument’s close from five trading rows earlier, then computes the percentage change. A second window function assigns a percentile rank across instruments for each date, which could support cross-sectional screening or factor analysis.
The example filters the table to one date and includes a one-day close ratio as an additional field. It does not explain the original calculation problem, define the data table or functions, or show query output. In particular, filtering to a single date before calculating the lag may prevent the window function from seeing earlier rows, depending on how the SQL engine evaluates the query. The example therefore illustrates the intended calculations, but does not establish that the query returns valid five-day results as written.
Key ideas
- A five-day return can be calculated by comparing the current close with the close five rows earlier for the same instrument.
- A percentile rank can compare each instrument’s return with other instruments on a given date.
- Window partitions separate each instrument’s price history and each date’s cross-sectional ranking.
- Filtering the input to one date may leave the lag function without the earlier observations it needs.
Tags
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.