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.