Main Content

convert2weekly

Aggregate timetable data to weekly periodicity

Description

example

TT2 = convert2weekly(TT1) aggregates data (for example, data recorded daily) to a weekly periodicity.

example

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

Examples

collapse all

Apply separate aggregation methods to related variables in a timetable while maintaining consistency between aggregated results when converting from a daily to a weekly periodicity. You can use convert2weekly to aggregate both intra-daily data and aggregated daily data. These methods result in equivalent weekly aggregates.

Load a timetable (TT) of simulated stock price data and corresponding logarithmic returns. The data stored in TT is recorded at various times throughout the day on New York Stock Exchange (NYSE) business days from January 1, 2018, to December 31,2020. The timetable TT also includes NYSE business calendar awareness. If your timetable does not account for nonbusiness days (weekends, holidays, and market closures), add business calendar awareness by using addBusinessCalendar first.

load('SimulatedStock.mat','TT');
head(TT)
ans=8×2 timetable
            Time            Price     Log_Return
    ____________________    ______    __________

    02-Jan-2018 11:52:11    100.71     0.0070749
    02-Jan-2018 13:23:09    103.11      0.023551
    02-Jan-2018 14:45:30    100.24     -0.028229
    02-Jan-2018 15:30:48    101.37       0.01121
    03-Jan-2018 10:02:21    101.81     0.0043311
    03-Jan-2018 11:22:37    100.17      -0.01624
    03-Jan-2018 14:45:20     99.66    -0.0051043
    03-Jan-2018 14:55:39    100.12     0.0046051

Use convert2daily to aggregate intra-daily prices and returns to daily periodicity. To maintain consistency between prices and returns, for any given trading day, aggregate prices by reporting the last recorded price with "lastvalue" and aggregate returns by summing all logarithmic returns with "sum".

TT1 = convert2daily(TT,'Aggregation',["lastvalue" "sum"]);
head(TT1)
ans=8×2 timetable
       Time        Price     Log_Return
    ___________    ______    __________

    02-Jan-2018    101.37     0.013607 
    03-Jan-2018    100.12    -0.012408 
    04-Jan-2018    106.76     0.064214 
    05-Jan-2018    112.78     0.054856 
    08-Jan-2018    119.07     0.054273 
    09-Jan-2018    119.46      0.00327 
    10-Jan-2018    124.44     0.040842 
    11-Jan-2018    125.63    0.0095174 

Use convert2weekly to aggregate the data to a weekly periodicity and compare the results of two different aggregation approaches. The first approach computes weekly results by aggregating the daily aggregates and the second approach computes weekly results by directly aggregating the original intra-daily data.

tt1 = convert2weekly(TT1,'Aggregation',["lastvalue" "sum"]);   % Daily to weekly
tt2 = convert2weekly(TT ,'Aggregation',["lastvalue" "sum"]);   % Intra-daily to weekly

head(tt1)
ans=8×2 timetable
       Time        Price     Log_Return
    ___________    ______    __________

    05-Jan-2018    112.78      0.12027 
    12-Jan-2018    125.93      0.11029 
    19-Jan-2018    117.67    -0.067842 
    26-Jan-2018     118.8    0.0095573 
    02-Feb-2018    120.85     0.017109 
    09-Feb-2018    123.68     0.023147 
    16-Feb-2018    124.33    0.0052417 
    23-Feb-2018    127.09     0.021956 

head(tt2)
ans=8×2 timetable
       Time        Price     Log_Return
    ___________    ______    __________

    05-Jan-2018    112.78      0.12027 
    12-Jan-2018    125.93      0.11029 
    19-Jan-2018    117.67    -0.067842 
    26-Jan-2018     118.8    0.0095573 
    02-Feb-2018    120.85     0.017109 
    09-Feb-2018    123.68     0.023147 
    16-Feb-2018    124.33    0.0052417 
    23-Feb-2018    127.09     0.021956 

Notice that the results of the two approaches are the same and that convert2weekly reports on Fridays by default. For weeks in which Friday is not an NYSE trading day, the function reports results on the previous business day. In addition, you can use the convert2weekly optional name-value pair argument 'EndOfWeekDay' to specify a different day of the week that ends business weeks.

Input Arguments

collapse all

Data to aggregate to a weekly periodicity, specified as a timetable.

Note

NaNs indicate missing values. Timestamps must be in ascending or descending order.

By default, all days are business days. If your timetable does not account for nonbusiness days (weekends, holidays, and market closures), add business calendar awareness by using addBusinessCalendar first. For example, the following command adds business calendar logic to include only NYSE business days.

TT = addBusinessCalendar(TT);

Data Types: timetable

Name-Value Pair 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: TT2 = convert2weekly(TT1,'Aggregation',["lastvalue" "sum"])

Aggregation method for TT1 data for intra-week or inter-day aggregation, specified as the comma-separated pair consisting of 'Aggregation' and a character vector, string, or function handle applied to all time series in TT1, or a cell vector of character vectors, string vector, or cell vector of function handles the same length as the number of variables in TT1.

The aggregation methods define how data is aggregated over business days in an intra-week or inter-day periodicity. Available aggregation methods are:

  • 'sum' — Sum the values in each week or day.

  • 'mean' — Calculate the mean of the values in each week or day.

  • 'prod' — Calculate the product of the values in each week or day.

  • 'min' — Calculate the minimum of the values in each week or day.

  • 'max' — Calculate the maximum of the values in each week or day.

  • 'firstvalue' — Use the first value in each week or day.

  • 'lastvalue' — Use the last value in each week or day.

All methods listed above omit missing data (NaNs) in direct aggregation calculations. However, in situations in which missing values appear in the first row of TT1, missing values can also appear in aggregated results TT2.

Additionally, aggregation methods can be specified as function handles. To include missing data, specify functions as function handles that include the missing data when aggregating data. Aggregation functions must accept the underlying data stored in TT1 and return an output that is a scalar or a row vector, and must accept empty inputs. Each aggregation function is applied to the corresponding variable and called one at a time. Each variable must contain either a single numeric vector or numeric matrix. For example, consider a daily timetable representing TT1 with three variables.

        Time           AAA       BBB            CCC       
      ___________    ______    ______    ________________
      01-Jan-2018    100.00    200.00    300.00    400.00
      02-Jan-2018    100.03    200.06    300.09    400.12
      03-Jan-2018    100.07    200.14    300.21    400.28
      04-Jan-2018    100.08    200.16    300.24    400.32
      05-Jan-2018    100.25    200.50    300.75    401.00
      06-Jan-2018    100.19    200.38    300.57    400.76
      07-Jan-2018    100.54    201.08    301.62    402.16
      08-Jan-2018    100.59    201.18    301.77    402.36
      09-Jan-2018    101.40    202.80    304.20    405.60
      10-Jan-2018    101.94    203.88    305.82    407.76
      11-Jan-2018    102.53    205.06    307.59    410.12
      12-Jan-2018    103.35    206.70    310.05    413.40
      13-Jan-2018    103.40    206.80    310.20    413.60
      14-Jan-2018    103.91    207.82    311.73    415.64
      15-Jan-2018    103.89    207.78    311.67    415.56
      16-Jan-2018    104.44    208.88    313.32    417.76
      17-Jan-2018    104.44    208.88    313.32    417.76
      18-Jan-2018    104.04    208.08    312.12    416.16
      19-Jan-2018    104.94    209.88    314.82    419.76

The corresponding default weekly results representing TT2 (in which all days are business days and the 'lastvalue' is reported on Fridays) are as follows.

        Time         AAA       BBB            CCC       
      ___________    ______    ______    ________________
      05-Jan-2018    100.25    200.50    300.75    401.00
      12-Jan-2018    103.35    206.70    310.05    413.40
      19-Jan-2018    104.94    209.88    314.82    419.76

The default 'lastvalue' returns the most recent data observed in a given week for all variables in TT1.

Data Types: char | string | cell | function_handle

Method for intra-day aggregation for data in TT1, specified as the comma-separated pair consisting of 'Daily' and a scalar character vector, string, or function handle applied to all time series in TT1, or a cell vector of character vectors, string array, or cell vector of function handles the same length as the number of variables in TT1.

Data Types: char | string | cell | function_handle

Day of the week that ends business weeks, specified as the comma-separated pair consisting of 'EndOfWeekDay' and a string, character vector, or a scalar integer. If a specified end-of-week day in a given week is not a business day, then the preceding business day ends the week.

Data Types: double | char | string

Output Arguments

collapse all

Weekly data, returned as a timetable. The function returns NaNs for variables in TT2 for weeks when no data is recorded on any business days for those variables in TT1. If TT1 is in ascending order, so too is TT2, and if TT1 is in descending order, so too is TT2.

The first date in TT2 is the last business date of the week in which the first date in TT1 occurs, provided TT1 has business dates in that week, otherwise the first date in TT2 is the next end-of-week business date.

The last date in TT2 is the last business date of the week in which the last date in TT1 occurs, provided TT1 has business dates in that week, otherwise the last date in TT2 is the previous end-of-week business date.

Introduced in R2021a