SQLServerCentral Article

Calculating Stock Returns Using the ROW_NUMBER function and CTEs

,

In the following exercise, we’ll calculate yearly, as well as monthly and weekly stock returns from a daily pricing table. Making these calculations is complicated by the fact that, due to holidays and weekends, markets are not always open on the last day of a period. For example, when New Year ’s Eve is on a Sunday, the last trading day of the year will be Friday, December 29th. We can’t simply compare December 31st, 2009 to December 31st, 2010 using a typical pricing table. In these instances, utilizing the row_number function and common table expressions(CTEs) can help overcome this temporal concern.

Let’s start by building a pricing table. Please utilize the attached script (prices.sql) to build the price table and populate it with some historic pricing data, in this case for a sample large cap US stock index we’ll call XYZ. The table contains columns for the company’s ticker, and prices at open, high, low and closing values. For this exercise we’ll focus only on the closing price (CLOSE_).

After building the table, let’s create a query using a common table expression(CTE) to pull the closing price on the last trading day of each year:

WITH PriceYearly_CTE
AS
 (select row_number() OVER (ORDER BY DATE_) AS period,
 ticker, 
 close_,
 datepart(yyyy,date_) AS year
 from prices 
where date_ IN (
                --get last pricedate of each period
                select MAX(Date_) as a 
                 FROM prices
                 group by datepart(yyyy,date_)
               )
)
--query the CTE
select * 
from PriceYearly_CTE

This yields the following results:

periodtickerclose_year
1XYZ222.6421989
2XYZ208.03861990
3XYZ262.76671991
4XYZ274.49731992
5XYZ293.86351993
6XYZ289.34011994
7XYZ388.03591995
8XYZ466.66621996
9XYZ611.37091997
10XYZ774.41491998
11XYZ925.62751999
12XYZ831.77642000
13XYZ723.29042001
14XYZ554.28662002
15XYZ700.50962003
16XYZ763.50962004
17XYZ786.42272005
18XYZ893.5292006
19XYZ925.06682007
20XYZ569.04752008
21XYZ702.5132009
22XYZ792.31322010

Within the subquery, the MAX aggregate function is used to get the last price date of each period(in this case the year). The ROW_NUMBER function allows us to rank each period in chronological order. From there, we can use a self-join to obtain the yearly return via the formula:

(Pt – Pt-1)/Pt-1

Where:

  • P = Closing Price for the period
  • t = time period(in this case the year)

The select query joins the time period to the prior time period as seen below(Place this code below PriceYearly_CTE):

select
  a.Year,
  CONVERT(NUMERIC(18,4),100*(a.Close_- b.Close_)/b.Close_) as [Yearly Return Percent],
  a.Close_ AS [Closing Price]
 from PriceYearly_CTE a
  JOIN PriceYearly_CTE b
    ON a.PERIOD = b.PERIOD+1
 WHERE a.Year >=2000

Yearly Return Results:

YearYearly Return PercentClosing Price
2000-10.13921320.28
2001-13.04271148.08
2002-23.366879.82
200326.38041111.92
20048.99351211.92
20053.0011248.29
200613.61941418.3
20073.52961468.36
2008-38.4858903.25
200923.45421115.1
201012.78271257.64
20110.85951268.45

From here, we can see each year’s return as per below. Using the same logic we can also determine monthly returns:

--Get Monthly Returns As Percentage
WITH PriceMonthly_CTE
AS
(select 
  row_number() OVER (ORDER BY DATE_) AS Period,
  ticker, 
  close_, 
  datepart(yyyy,date_) AS YEAR,
  datepart(mm,date_) AS MONTH
 from Prices where date_ IN (
        --get last pricedate of each period
        select MAX(Date_) as a 
         FROM Prices
          group by datepart(yyyy,date_),datepart(mm,date_)
      ))
select 
  a.Year, a.Month,
  CONVERT(NUMERIC(18,4),100*(a.Close_- b.Close_)/b.Close_) as [Monthly Return Percent],
  a.Close_ AS [Closing Price]
 from PriceMonthly_CTE a 
  JOIN PriceMonthly_CTE b
    ON a.Period = b.Period+1
 WHERE a.Year >=2010

Monthly Return Results for 2010:

YearMonthMonthly Return PercentClosing Price
20101-3.6974676.5381
201022.8514695.8287
201035.8796736.7409
201041.4759747.6147
20105-8.1976686.3283
20106-5.3882649.3473
201076.8778694.008
20108-4.7449661.0779
201098.7551718.956
2010103.6856745.4538
201011-0.229743.7465
2010126.53792.3132

And weekly returns(this query pulls the last ten weeks of 2010):

WITH PriceWeekly_CTE
AS
(select 
  row_number() OVER (ORDER BY DATE_) AS PERIOD, 
  ticker, 
  close_,
  datepart(yyyy,date_) AS YEAR, 
  datepart(WK,date_) AS Week
 from prices_ 
 where date_ IN (
     --get last pricedate of each period
     select MAX(Date_) as a 
      FROM Prices_
      where datepart(WK,date_) <53
      group by datepart(yyyy,date_),datepart(WK,date_)
    )
)
select top 10 
  a.Year, 
  a.Week,
  CONVERT(NUMERIC(18,4),100*(a.Close_- b.Close_)/b.Close_) as [Weekly Return Percent],
  a.Close_ AS [Closing Price]
 from PriceWeekly_CTE a
  JOIN PriceWeekly_CTE b
    ON a.PERIOD = b.PERIOD+1
 WHERE a.YEAR >=2010
 order by a.Year, a.Week DESC

Results:

YearWeekWeekly Return PercentClosing Price
2010521.03381256.77
2010510.2831243.91
2010501.28111240.4
2010492.96871224.71
201048-0.8611189.4
2010470.04341199.73
201046-2.17321199.21
2010453.59941225.85
2010440.01521183.26
2010430.58581183.08

By utilizing ROW_NUMBER and common table expressions, we have successfully dealt with some important temporal database problems, and have calculated returns over yearly, monthly and weekly time periods. Using this methodology, we can consider many other important financial calculations that can be made over varied timelines.  

Resources

Rate

4.18 (22)

You rated this post out of 5. Change rating

Share

Share

Rate

4.18 (22)

You rated this post out of 5. Change rating