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

 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.

4.18 (22)

4.18 (22)