convert2weekly

Aggregate timetable data to weekly periodicity

Since R2021a

Syntax

TT2 = convert2weekly(TT1)

TT2 = convert2weekly(___,Name,Value)

Description

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

TT2 = convert2weekly(___,Name,Value) uses additional options specified by one or more name-value arguments.

Examples

Aggregate Timetable Data to Weekly Periodicity

Load the simulated stock price data and corresponding logarithmic returns in SimulatedStockSeries.mat.

load SimulatedStockSeries

The timetable DataTimeTable contains measurements recorded at various, irregular times during trading hours (09:30 to 16:00) of the New York Stock Exchange (NYSE) from January 1, 2018, through December 31, 2020.

For example, display the first few observations.

head(DataTimeTable)

            Time            Price     Log_Return
    ____________________    ______    __________

    01-Jan-2018 11:52:48       100     -0.025375
    01-Jan-2018 13:23:13    101.14      0.011336
    01-Jan-2018 14:45:09     101.5     0.0035531
    01-Jan-2018 15:30:30    100.15      -0.01339
    02-Jan-2018 10:43:37     99.72    -0.0043028
    03-Jan-2018 10:02:21    100.11     0.0039033
    03-Jan-2018 11:22:37    103.96      0.037737
    03-Jan-2018 13:42:27    107.05       0.02929

DataTimeTable does not include business calendar awareness. If you want to account for nonbusiness days (weekends, holidays, and market closures) and you have a Financial Toolbox™ license, add business calendar awareness by using the addBusinessCalendar function.

Aggregate the price series to a weekly series by reporting the final price in each week.

WeeklyPrice = convert2weekly(DataTimeTable(:,"Price"));

WeeklyPrice is a timetable containing the final prices for each reported week in DataTimeTable.

Specify Aggregation Method for Each Variable

This example shows how to specify the appropriate aggregation method for the units of a variable. It also shows how to use convert2weekly to aggregate both intra-day data and aggregated daily data, which result in equivalent weekly aggregates.

Load the simulated stock price data and corresponding logarithmic returns in SimulatedStockSeries.mat.

load SimulatedStockSeries

The price series Price contains absolute measurements, whereas the log returns series Log_Return is the rate of change of the price series among successive observations. Because the series have different units, you must specify the appropriate method when you aggregate the series. Specifically, if you report the final price for a given periodicity, you must report the sum of the log returns within each period.

To understand how to maintain consistency among aggregation methods, use two approaches to aggregate DataTimeTable so that the result has a weekly periodicity.

1. Pass DataTimeTable directly to convert2weekly.

2. Aggregate DataTimeTable so that the result has a daily periodicity by using convert2daily, then pass the result to convert2weekly.

In both cases, specify reporting the last price and the sum of the log returns for each period.

Directly aggregate the data so that the result has a weekly periodicity. For each series, specify the aggregation method that is appropriate for the unit.

aggmethods = ["lastvalue" "sum"];
WeeklyTT1 = convert2weekly(DataTimeTable,Aggregation=aggmethods)

WeeklyTT1=157×2 timetable
       Time        Price     Log_Return 
    ___________    ______    ___________

    05-Jan-2018    110.69       0.076188
    12-Jan-2018    119.91       0.080008
    19-Jan-2018     116.6      -0.027992
    26-Jan-2018    118.51       0.016248
    02-Feb-2018    120.03       0.012744
    09-Feb-2018    117.07       -0.02497
    16-Feb-2018    117.06    -8.5423e-05
    23-Feb-2018    116.72     -0.0029087
    02-Mar-2018    109.98      -0.059479
    09-Mar-2018    110.27      0.0026334
    16-Mar-2018    107.35      -0.026837
    23-Mar-2018    112.78       0.049344
    30-Mar-2018    110.27      -0.022507
    06-Apr-2018    105.27      -0.046403
    13-Apr-2018    106.01       0.007005
    20-Apr-2018    107.93       0.017949
      ⋮

WeeklyTT1 is a timetable containing the weekly data. Price is a series of the final stock prices for each week, and Log_Return is the sum of the log returns for each week.

Aggregate the data in two steps: aggregate the data so that the result has a daily periodicity, then aggregate the daily data to weekly data. For each series, specify the aggregation method that is appropriate for the unit.

DailyTT = convert2daily(DataTimeTable,Aggregation=aggmethods);
tail(DailyTT)

       Time        Price     Log_Return 
    ___________    ______    ___________

    24-Dec-2020    286.35     -0.0067521
    25-Dec-2020    286.26    -0.00031435
    26-Dec-2020    285.68     -0.0020282
    27-Dec-2020    285.61    -0.00024506
    28-Dec-2020    294.36       0.030176
    29-Dec-2020    300.44       0.020445
    30-Dec-2020    303.84       0.011253
    31-Dec-2020    301.04     -0.0092581

WeeklyTT2 = convert2weekly(DailyTT,Aggregation=aggmethods)

WeeklyTT2=157×2 timetable
       Time        Price     Log_Return 
    ___________    ______    ___________

    05-Jan-2018    110.69       0.076188
    12-Jan-2018    119.91       0.080008
    19-Jan-2018     116.6      -0.027992
    26-Jan-2018    118.51       0.016248
    02-Feb-2018    120.03       0.012744
    09-Feb-2018    117.07       -0.02497
    16-Feb-2018    117.06    -8.5423e-05
    23-Feb-2018    116.72     -0.0029087
    02-Mar-2018    109.98      -0.059479
    09-Mar-2018    110.27      0.0026334
    16-Mar-2018    107.35      -0.026837
    23-Mar-2018    112.78       0.049344
    30-Mar-2018    110.27      -0.022507
    06-Apr-2018    105.27      -0.046403
    13-Apr-2018    106.01       0.007005
    20-Apr-2018    107.93       0.017949
      ⋮

DailyTT is a timetable with daily periodicity. Price is a series of the final stock prices for each day, and Log_Return is the sum of the log returns for each day.

WeeklyTT1 and WeeklyTT2 are equal.

convert2weekly reports results on Fridays by default. For weeks during which Friday is not a trading day in the NYSE, the function reports results on the previous business day. You can use the name-value argument EndOfWeekDay to specify a different day of the week that ends business weeks.

Input Arguments

TT1 — Data to aggregate to weekly periodicity

timetable

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

Each variable can be a numeric vector (univariate series) or numeric matrix (multivariate series).

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 Arguments

Specify optional pairs of arguments as Name1=Value1,...,NameN=ValueN, where Name is the argument name and Value is the corresponding value. Name-value arguments must appear after other arguments, but the order of the pairs does not matter.

Example: TT2 = convert2weekly(TT1,'Aggregation',["lastvalue" "sum"])

Aggregation — Aggregation method for TT1 data for intra-week or inter-day aggregation

"lastvalue" (default) | "sum" | "prod" | "mean" | "min" | "max" | "firstvalue" | character vector | function handle | string vector | cell vector of character vectors or function handles

Aggregation method for TT1 defining how data is aggregated over business days in an intra-week or inter-day periodicity, specified as one of the following methods, a string vector of methods, or a length numVariables cell vector of methods, where numVariables is the number of variables in TT1.

• "sum" — Sum the values in each year or day.

• "mean" — Calculate the mean of the values in each year or day.

• "prod" — Calculate the product of the values in each year or day.

• "min" — Calculate the minimum of the values in each year or day.

• "max" — Calculate the maximum of the values in each year or day.

• "firstvalue" — Use the first value in each year or day.

• "lastvalue" — Use the last value in each year or day.

• @customfcn — A custom aggregation method that accepts a table variable and returns a numeric scalar (for univariate series) or row vector (for multivariate series). The function must accept empty inputs [].

If you specify a single method, convert2weekly applies the specified method to all time series in TT1. If you specify a string vector or cell vector aggregation, convert2weekly applies aggregation(j) to TT1(:,j); convert2weekly applies each aggregation method one at a time (for more details, see retime). 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 latest observed value in a given week for all variables in TT1.

All methods omit missing data (NaNs) in direct aggregation calculations on each variable. However, for situations in which missing values appear in the first row of TT1, missing values can also appear in the aggregated results TT2. To address missing data, write and specify a custom aggregation method (function handle) that supports missing data.

Data Types: char | string | cell | function_handle

Daily — Intra-day aggregation method for TT1

"lastvalue" (default) | "sum" | "prod" | "mean" | "min" | "max" | "firstvalue" | character vector | function handle | string vector | cell vector of character vectors or function handles

Intra-day aggregation method for TT1, specified as an aggregation method, a string vector of methods, or a length numVariables cell vector of methods. For more details on supported methods and behaviors, see the 'Aggregation' name-value argument.

Data Types: char | string | cell | function_handle

EndOfWeekDay — Day of week that ends business weeks

"Friday" (weeks end on Friday) (default) | scalar integer with value 1 through 7 | "Sunday" | "Monday" | "Tuesday" | "Wednesday" | "Thursday" | "Friday" | "Saturday" | character vector

Day of the week that ends business weeks, specified as a value in the table.

Value	Day Ending Each Week
"Sunday" or 1	Sunday
"Monday" or 2	Monday
"Tuesday" or 3	Tuesday
"Wednesday" or 4	Wednesday
"Thursday" or 5	Thursday
"Friday" or 6	Friday
"Saturday" or 7	Saturday

If the specified end-of-week day in a given week is not a business day, the preceding business day ends that week.

Data Types: double | char | string

Output Arguments

TT2 — Weekly data

timetable

Weekly data, returned as a timetable. The time arrangement of TT1 and TT2 are the same.

If a variable of TT1 has no business-day records during an annual period within the sampling time span, convert2weekly returns a NaN for that variable and annual period in TT2.

If the first week (week1) of TT1 contains at least one business day, the first date in TT2 is the last business date of week1. Otherwise, the first date in TT2 is the next end-of-week business date of TT1.

If the last week (weekT) of TT1 contains at least one business day, the last date in TT2 is the last business date of weekT. Otherwise, the last date in TT2 is the previous end-of-week business date of TT1.

Version History

Introduced in R2021a