Calculating Stock Returns Using the ROW_NUMBER function and CTEs

Question

Post reply

Calculating Stock Returns Using the ROW_NUMBER function and CTEs

eephus101

SSC Enthusiast

Points: 138
More actions
October 17, 2011 at 10:11 pm

#245485

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

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

You must be logged in to reply to this topic. Login to reply

brijen.patel SSC Enthusiast Points: 152 More actions · Answer 1

brijen.patel

SSC Enthusiast

Points: 152

October 17, 2011 at 11:17 pm

#1395692

I couldn't find prices.sql script file.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Nice, concise, easily-readable article.

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}

For fast, accurate and documented assistance in answering your questions, please read this article.
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

azhar.iqbal499 SSCrazy Points: 2812 More actions · Answer 3

Looks nice article.Please upload prices.sql script file.

Thanks

voutmaster Mr or Mrs. 500 Points: 517 More actions · Answer 4

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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}

For fast, accurate and documented assistance in answering your questions, please read this article.
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

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 6

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

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 7

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.

brijen.patel SSC Enthusiast Points: 152 More actions · Answer 8

brijen.patel

SSC Enthusiast

Points: 152

October 18, 2011 at 7:30 am

#1395917

Thanks, i am able to download the prices.sql.

Thanks

Richard Gibbins Old Hand Points: 377 More actions · Answer 9

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

David Walker-278941 Ten Centuries Points: 1064 More actions · Answer 10

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.

voutmaster Mr or Mrs. 500 Points: 517 More actions · Answer 11

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.

eephus101 SSC Enthusiast Points: 138 More actions · Answer 12

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

eephus101 SSC Enthusiast Points: 138 More actions · Answer 13

Hi Chris,

thanks for the feedback. Great idea.

🙂 Thanks, -John

John R

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 14

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