# Calculating Stock Returns Using the ROW_NUMBER function and CTEs

• Comments posted to this topic are about the item Calculating Stock Returns Using the ROW_NUMBER function and CTEs

John R

• I couldn't find prices.sql script file.

However, I'd change the key query because it reads the prices table twice. This reads it once:

`;WITH PriceYearly_CTE2 AS`

`(SELECT`

`Period = ROW_NUMBER() OVER (ORDER BY [year]),`

`ticker,`

`close_,`

`[year]`

`FROM (`

`SELECT`

`MyRow = ROW_NUMBER() OVER(PARTITION BY YEAR(date_) ORDER BY date_ DESC),`

`ticker,`

`close_,`

`YEAR(date_) AS [year]`

`FROM prices`

`) d`

`WHERE MyRow = 1`

`)`

`SELECT * FROM PriceYearly_CTE2`

Cheers

ChrisM

βWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.β - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Thanks

• There are two different ways to calculate weekly returns. The methodology you're using implies holding for an entire week (using weekly prices). A more complicated methodology would compound daily, I'd be interested in seeing a CTE solution for it if one exists.

• pvoutov (10/18/2011)

There are two different ways to calculate weekly returns. The methodology you're using implies holding for an entire week (using weekly prices). A more complicated methodology would compound daily, I'd be interested in seeing a CTE solution for it if one exists.

Supply some sample data in an easily-consumed format, a sample of required output...and wait a while.

βWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.β - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• My apologies. I've uploaded the prices.sql file now. You can download it from the end of the article.

• pvoutov (10/18/2011)

There are two different ways to calculate weekly returns. The methodology you're using implies holding for an entire week (using weekly prices). A more complicated methodology would compound daily, I'd be interested in seeing a CTE solution for it if one exists.

If you have a question about a method or problem not in the article, please post a question in the T-SQL forum for your version instead.

Thanks

• Interesting technique. Thanks.

I am curious, though, about why you chagned the formula from (P(t) - P(t-1))/P(t-1) to

(P(t+1) - P(t))/P(t). I realize it amounts to the same thing in most cases but I did find it a bit disconcerting when reading the code.

Thanks,

Richard

• Yes, as someone else mentioned, it's probably more efficient to order the dates descending, and then look for row_number() = 1 from the results, instead of looking for the max(date). I have done this myself.

• Forgive me if this sounded like a request. I was trying to point out that you're only calculating an arithmetic weekly return, which is not what's typically used in monitoring weekly returns. Compound/cumulative returns are more interesting and harder to calculate. Your daily calculation is great.

• Hi richard,

Thanks for the feedback. Sorry about that. I mixed formulas between drafts of the article. You are right though, it should produce the same results.

regards,

-John:-)

John R

• Hi Chris,

thanks for the feedback. Great idea.

π Thanks, -John

John R

• John, nice article. For the next one if you could show us a way to predict future stock moves that would be great. π

Viewing 15 posts - 1 through 15 (of 19 total)