rollingreturns

Period-over-period rolling returns or differences from prices

Syntax

returns = rollingreturns(TT)

returns = rollingreturns(___,Name,Value)

Description

returns = rollingreturns(TT) calculates period-over-period (PoP) returns or differences from corresponding prices. For each date in timetable TT, the return represents the PoP return of the corresponding price compared to the price one Period earlier.

returns = rollingreturns(___,Name,Value) specifies options using one or more optional name-value pair arguments in addition to the input arguments in the previous syntax.

Examples

Year-over-Year Returns from Daily Prices with Business Calendar Awareness

This example shows how to compute year-over-year rolling returns for five years of simulated daily prices and also includes business calendar awareness.

Simulate five years of daily prices and store the result in the timetable TT. Then, use isbusday to retain only data for New York Stock Exchange (NYSE) business dates.

rng(200,'twister')
time = (datetime(2014,1,1):caldays:datetime(2018,12,31))';
price = 100 + 0.1*(0:numel(time) - 1)'.*cumsum(randn(numel(time),1)/100); 
price = round(price*100)/100;                 % Round prices to the nearest penny
TT = timetable(price,'RowTimes',time,'VariableNames',{'Prices'});
TT = TT(isbusday(TT.Properties.RowTimes),:);  % Retain only NYSE business days
head(TT,10)

ans=10×1 timetable
       Time        Prices
    ___________    ______

    02-Jan-2014       100
    03-Jan-2014       100
    06-Jan-2014    100.01
    07-Jan-2014    100.02
    08-Jan-2014    100.02
    09-Jan-2014    100.04
    10-Jan-2014    100.06
    13-Jan-2014    100.11
    14-Jan-2014    100.12
    15-Jan-2014    100.12

Use addBusinessCalendar to add NYSE business calendar awareness. The business calendar logic determines if the date of the previous period is a business date, and if it is not, then the most recent business day preceding that date is found. For example, since 21-May-2016 is a Saturday and 22-May-2016 is a Sunday, year-over-year prices for Monday 22-May-2017 are compared to Friday 20-May-2016.

TT = addBusinessCalendar(TT);  % Add NYSE business calendar

Compute the year-over-year returns and display the last few prices and corresponding returns.

returns = rollingreturns(TT, 'Period', calyears);
tail([TT returns])

ans=8×2 timetable
       Time        Prices    Prices_Return_1y
    ___________    ______    ________________

    19-Dec-2018    212.68        0.16941     
    20-Dec-2018    215.54        0.19024     
    21-Dec-2018    217.66        0.18648     
    24-Dec-2018    221.42        0.20882     
    26-Dec-2018    224.81        0.21473     
    27-Dec-2018    222.17        0.19897     
    28-Dec-2018    224.63        0.19142     
    31-Dec-2018    224.37        0.19206     

Year-over-Year Returns from Monthly Prices with End-of-Month Dates

Economic data is often reported on the last day of each month or quarter. So end-of-month ambiguities can arise when computing period-over-period returns for periods that exceed the periodicity at which data is reported.

Simulate five years of daily prices and store the result in the timetable TT.

rng(200,'twister')
time = (datetime(2014,1,1):caldays:datetime(2018,12,31))';
price = 100 + 0.1*(0:numel(time) - 1)'.*cumsum(randn(numel(time),1)/100); 
price = round(price*100)/100;         % Round prices to the nearest penny
TT = timetable(price,'RowTimes',time,'VariableNames',{'Prices'});
head(TT,10)

ans=10×1 timetable
       Time        Prices
    ___________    ______

    01-Jan-2014       100
    02-Jan-2014       100
    03-Jan-2014       100
    04-Jan-2014       100
    05-Jan-2014    100.01
    06-Jan-2014    100.01
    07-Jan-2014    100.02
    08-Jan-2014    100.02
    09-Jan-2014    100.04
    10-Jan-2014    100.06

Create a new timetable by sampling TT on the last day of each month to mimic monthly reporting.

monthEndDates = dateshift(TT.Time(1):calmonths:TT.Time(end),'end','month'); 
TT = TT(monthEndDates,:);   % Sample TT at end-of-month dates
head(TT,10)

ans=10×1 timetable
       Time        Prices
    ___________    ______

    31-Jan-2014    100.47
    28-Feb-2014    100.93
    31-Mar-2014       102
    30-Apr-2014    102.28
    31-May-2014    103.22
    30-Jun-2014    103.92
    31-Jul-2014     102.2
    31-Aug-2014    104.79
    30-Sep-2014    103.11
    31-Oct-2014    105.29

Display a subset of the dates and compare a direct calculation of the dates in previous months to those shifted to the end of the month in which the previous period occurs.

dates = timerange(datetime(2016,2,29),datetime(2017,2,28),'month');
[TT.Time(dates) (TT.Time(dates) - calyears) dateshift(TT.Time(dates) - calyears,'end','month')]

ans = 13x3 datetime
   29-Feb-2016   28-Feb-2015   28-Feb-2015
   31-Mar-2016   31-Mar-2015   31-Mar-2015
   30-Apr-2016   30-Apr-2015   30-Apr-2015
   31-May-2016   31-May-2015   31-May-2015
   30-Jun-2016   30-Jun-2015   30-Jun-2015
   31-Jul-2016   31-Jul-2015   31-Jul-2015
   31-Aug-2016   31-Aug-2015   31-Aug-2015
   30-Sep-2016   30-Sep-2015   30-Sep-2015
   31-Oct-2016   31-Oct-2015   31-Oct-2015
   30-Nov-2016   30-Nov-2015   30-Nov-2015
   31-Dec-2016   31-Dec-2015   31-Dec-2015
   31-Jan-2017   31-Jan-2016   31-Jan-2016
   28-Feb-2017   28-Feb-2016   29-Feb-2016

Examine these results and notice that the dates in the second and third columns of the last row differ. Specifically, when the current date in the first column is 28-Feb-2017 the dates in the second and third columns differ because 2016 is a leap year. More generally, the dates differ whenever the month of the previous period has more days than the current month for which returns are computed. In this example, end-of-months dates present the following ambiguity. When the current date of interest is 28-Feb-2017, should subtracting one calendar year produce 28-Feb-2016 or 29-Feb-2016?

The correct answer depends on the application, and both approaches are valid use cases. This problem is exacerbated, for example, when working with end-of-monthly price data and computing month-over-month returns. To address the end-of-month ambiguity, the rollingreturns function supports an EndOfMonth flag.

returns = rollingreturns(TT, 'Period', calyears, 'EndOfMonth', true);

The EndOfMonth flag ensures that the rollingreturns function uses the correct end-of-month date of each calendar month. In this example, the return on 28-Feb-2017 is correctly computed from the price reported 29-Feb-2016 rather than 28-Feb-2016.

[TT(dates,:) returns(dates,:)]

ans=13×2 timetable
       Time        Prices    Prices_Return_1y
    ___________    ______    ________________

    29-Feb-2016    135.59         0.21671    
    31-Mar-2016    138.47         0.25052    
    30-Apr-2016    131.44         0.11598    
    31-May-2016    129.34        0.083068    
    30-Jun-2016    133.86        0.077865    
    31-Jul-2016    132.78        0.046253    
    31-Aug-2016    140.32         0.11871    
    30-Sep-2016    136.52        0.087549    
    31-Oct-2016    141.27         0.10652    
    30-Nov-2016    140.76          0.1053    
    31-Dec-2016    135.96        0.057643    
    31-Jan-2017    129.52       0.0099025    
    28-Feb-2017    136.36       0.0056789    

Input Arguments

TT — Input timetable of prices

timetable

Input timetable of prices, specified as a timetable. The timetable TT must satisfy the following conditions:

• All observations in TT must be associated with whole dates specified as datetimes with no HH:MM:SS time component (no time-of-day component).

• TT dates must be sorted in ascending order.

• TT must have no duplicate dates.

• Each variable in TT must contain either a single numeric vector or a numeric matrix of prices. For example, suppose TT contains three variables of daily prices.

           Time        Price1    Price2         Prices     
        ___________    ______    ______    ________________
        24-Dec-2018    221.42    442.84    221.42    442.84
        25-Dec-2018    220.62    441.24    220.62    441.24
        26-Dec-2018    224.81    449.62    224.81    449.62
        27-Dec-2018    222.17    444.34    222.17    444.34
        28-Dec-2018    224.63    449.26    224.63    449.26
        29-Dec-2018    225.36    450.72    225.36    450.72
        30-Dec-2018    226.73    453.46    226.73    453.46
        31-Dec-2018    224.37    448.74    224.37    448.74

  The corresponding daily returns are formatted as three returns for the three price variables.

          Time        Price1_Return    Price2_Return       Prices_Return    
        ___________    _____________    _____________   _____________________
        24-Dec-2018           NaN              NaN            NaN         NaN
        25-Dec-2018     -0.003613        -0.003613      -0.003613   -0.003613
        26-Dec-2018      0.018992         0.018992       0.018992    0.018992
        27-Dec-2018     -0.011743        -0.011743      -0.011743   -0.011743
        28-Dec-2018      0.011073         0.011073       0.011073    0.011073
        29-Dec-2018      0.003249         0.003249       0.003249    0.003249
        30-Dec-2018      0.006079         0.006079       0.006079    0.006079
        31-Dec-2018     -0.010409        -0.010409      -0.010409   -0.010409

Note

To include business-calendar-awareness and account for nonbusiness days (for example, weekends, holidays, market closures), you must first use the addBusinessCalendar function to populate a custom property for the input TT. For example, to add business calendar logic to include only NYSE business days, you can use TT = addBusinessCalendar(TT).

Data Types: timetable

Name-Value Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside quotes. You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: returns = rollingreturns(TT,'Period',calweeks(1),'EndOfMonth',true,'Method','continuous')

Period — Period to compute period-over-period returns

TT.Properties.TimeStep (default) | scalar calendar duration

Period to compute period-over-period returns, specified as the comma-separated pair consisting of 'EndMonthRule' and a scalar calendar duration (for example, caldays, calweeks, or calmonths).

The default is the time step defined in TT (TT.Properties.TimeStep), but only when TT.Properties.TimeStep is not NaN. If TT.Properties.TimeStep is NaN, then Period is required.

Data Types: double

EndOfMonth — End-of-month flag indicates whether prices at current date are compared to last date of month for previous Period

false (default) | value of true or false

End-of-month flag indicates whether the prices at the current date are compared to last date of month for the previous Period, specified as the comma-separated pair consisting of 'EndOfMonth' and a scalar logical value of true or false.

• If you set EndOfMonth to true (logical 1), meaning that the current prices are compared to end-of-month prices of the previous Period.

• If you set EndOfMonth to false (logical 0), meaning that the current prices are compared to prices recorded on the actual date of the previous Period.

Note

The EndOfMonth flag is intended to address end-of-month date calculations when computing the dates of a previous Period one or more months in the past.

For example, suppose you have monthly prices reported at the end of each month and want to compute year-over-year returns (that is, Period = calyears(1)). When the current date of interest is 28-Feb-2017, setting EndOfMonth = true (logical 1) ensures that returns computed for 28-Feb-2017 compare the prices on 28-Feb-2017 to those on 29-Feb-2016 rather than 28-Feb-2016.

Similarly, suppose you have monthly prices reported at the end of each month and want to compute month-over-month returns (that is, Period = calmonths(1)). When the current date of interest is 30-Apr-2020, setting EndOfMonth = true (logical 1) ensures that returns computed for 30-Apr-2020 compare the prices on 30-Apr-2020 to those on 31-Mar-2020 rather than 30-Mar-2020.

Data Types: logical

Method — Method for computing returns from prices

'simple' (default) | character vector with value 'simple', 'continuous', or 'difference'

Method for computing returns from prices, specified as the comma-separated pair consisting of 'Method' and a scalar character vector.

• 'simple' — Compute simple (proportional) returns: R(t) = P(t)/P(t-period) - 1.

• 'continuous' — Compute continuous (logarithmic) returns: R(t) = log(P(t)/P(t-period)).

• 'difference' — Compute differences (period-over-period changes): R(t) = P(t) - P(t-period)

Data Types: char

Output Arguments

returns — Period-over-period decimal returns or differences

timetable

Period-over-period decimal returns or differences, returned as a timetable of the same size and format as the input argument TT. The returns or differences in row t are associated with the tth date in TT and represent the return or difference of the tth price P(t) relative to the price in the previous period P(t-period). If the date in the previous period is not found in TT, then the result is NaN to indicate a missing value.

Variable names in the output append _Return or _Difference to the variable names in TT for returns and differences, respectively, followed by the period used in the period-over-period results. For example, if TT has a variable named ABC and week-over-week returns are computed for a Period of calweeks(1), the corresponding output variable is named ABC_Returns_1w.

rollingreturns is an aggregation function in which the frequency at which prices are recorded must equal or exceed that at which returns or differences are computed. For example, daily prices can be used to compute daily, weekly, or monthly returns, but computing daily returns from weekly or monthly prices generally makes no sense.

Algorithms

Period-over-period results are computed for every date in TT as follows:

1. For each date t in TT, the date t-period is computed.

   • If date t-period is a business date, then this date is the date "one period ago"

   • If date t-period is not a business date, then each calendar day preceding t-period is examined repeatedly until a business date is found, at which point this date is the date "one period ago," or the preceding date occurs prior to the first date in TT, at which point no previous business date exists in TT.

2. If the date "one period ago" is found in TT, then the corresponding price P(t-period) is recorded and the return R(t) is computed. However, if the date "one period ago" is not found in TT, then the previous price P(t-period) is assumed missing (that is, an implicit NaN), and the return R(t) = NaN.

3. The previous steps are repeated until the date t-period precedes the first date found in TT, at which point the algorithm terminates.