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.

  • 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

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

    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

    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

  • 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, i am able to download the prices.sql.

    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)

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