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 29^th. We can’t simply compare December 31^st, 2009 to December 31^st, 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:

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:

(P_t – P_t-1)/P_t-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:

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:

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:

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.