Calculating Stock Returns Using the ROW_NUMBER function and CTEs

  • eephus101

    SSC Enthusiast

    Points: 138

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

    John R

  • brijen.patel

    SSC Enthusiast

    Points: 152

    I couldn't find prices.sql script file.

  • ChrisM@Work

    SSC Guru

    Points: 186045

    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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • azhar.iqbal499

    SSCrazy

    Points: 2812

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

    Thanks

  • voutmaster

    Mr or Mrs. 500

    Points: 517

    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: 186045

    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.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715845

    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: 715845

    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

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

    Thanks

  • Richard Gibbins

    Old Hand

    Points: 377

    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

    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

    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

    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

    Hi Chris,

    thanks for the feedback. Great idea.

    🙂 Thanks, -John

    John R

  • brendan woulfe

    Hall of Fame

    Points: 3888

    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 20 total)

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