Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Calculating Stock Returns Using the ROW_NUMBER function and CTEs

By John Ryle,

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:

period ticker close_ year
1 XYZ 222.642 1989
2 XYZ 208.0386 1990
3 XYZ 262.7667 1991
4 XYZ 274.4973 1992
5 XYZ 293.8635 1993
6 XYZ 289.3401 1994
7 XYZ 388.0359 1995
8 XYZ 466.6662 1996
9 XYZ 611.3709 1997
10 XYZ 774.4149 1998
11 XYZ 925.6275 1999
12 XYZ 831.7764 2000
13 XYZ 723.2904 2001
14 XYZ 554.2866 2002
15 XYZ 700.5096 2003
16 XYZ 763.5096 2004
17 XYZ 786.4227 2005
18 XYZ 893.529 2006
19 XYZ 925.0668 2007
20 XYZ 569.0475 2008
21 XYZ 702.513 2009
22 XYZ 792.3132 2010

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:

Year Yearly Return Percent Closing Price
2000 -10.1392 1320.28
2001 -13.0427 1148.08
2002 -23.366 879.82
2003 26.3804 1111.92
2004 8.9935 1211.92
2005 3.001 1248.29
2006 13.6194 1418.3
2007 3.5296 1468.36
2008 -38.4858 903.25
2009 23.4542 1115.1
2010 12.7827 1257.64
2011 0.8595 1268.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:

Year Month Monthly Return Percent Closing Price
2010 1 -3.6974 676.5381
2010 2 2.8514 695.8287
2010 3 5.8796 736.7409
2010 4 1.4759 747.6147
2010 5 -8.1976 686.3283
2010 6 -5.3882 649.3473
2010 7 6.8778 694.008
2010 8 -4.7449 661.0779
2010 9 8.7551 718.956
2010 10 3.6856 745.4538
2010 11 -0.229 743.7465
2010 12 6.53 792.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:

Year Week Weekly Return Percent Closing Price
2010 52 1.0338 1256.77
2010 51 0.283 1243.91
2010 50 1.2811 1240.4
2010 49 2.9687 1224.71
2010 48 -0.861 1189.4
2010 47 0.0434 1199.73
2010 46 -2.1732 1199.21
2010 45 3.5994 1225.85
2010 44 0.0152 1183.26
2010 43 0.5858 1183.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:

prices.sql
Total article views: 5315 | Views in the last 30 days: 6
 
Related Articles
FORUM

Closing Period for Date Hierarchy

Closing Period for Date Hierarchy at any level

FORUM

Update previous close price.

I have a inventory table which contains data of around 3000 inventory and their price for each day.T...

FORUM

Baffling DATEPART("dw"...) difference for day of week

DATEPART("dw",getdate()) returns different values when used in an Expression vs Code

ARTICLE

A Stock Price Correlation Matrix

Building a Stock Price Correlation Matrix Using TVPs, a CROSS JOIN and the PIVOT Function

FORUM

Getting datepart to work for query

Datepart function

Tags
common table expressions    
cte    
row_number()    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones