convert2semiannual

Aggregate timetable data to semiannual periodicity

Syntax

TT2 = convert2semiannual(TT1)

TT2 = convert2semiannual(TT1,Name,Value)

Description

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

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

Examples

Aggregate Timetable Data to Semiannual Periodicity

Apply separate aggregation methods to related variables in a timetable while maintaining consistency between aggregated results when converting to a semiannual periodicity. You can use convert2semiannual to aggregate both intra-daily data and aggregated quarterly data. These methods result in equivalent semiannual 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 convert2quarterly to aggregate intra-daily prices and returns to a quarterly periodicity. To maintain consistency between prices and returns, for any given quarter, aggregate prices by reporting the last recorded price using "lastvalue" and aggregate returns by summing all logarithmic returns using "sum".

TT1 = convert2quarterly(TT,'Aggregation',["lastvalue" "sum"])

TT1=12×2 timetable
       Time        Price     Log_Return
    ___________    ______    __________

    29-Mar-2018     108.9      0.08526 
    29-Jun-2018     96.24     -0.12358 
    28-Sep-2018    111.37      0.14601 
    31-Dec-2018     92.72     -0.18327 
    29-Mar-2019      78.7     -0.16394 
    28-Jun-2019    110.54      0.33973 
    30-Sep-2019    180.13       0.4883 
    31-Dec-2019    163.65    -0.095949 
    31-Mar-2020    177.46     0.081015 
    30-Jun-2020    168.96    -0.049083 
    30-Sep-2020    260.77      0.43398 
    31-Dec-2020    274.75     0.052223 

Use convert2semiannual to aggregate the data to a semiannual periodicity and compare the results of two different approaches. The first approach computes semiannual results by aggregating the quarterly aggregates and the second approach computes semiannual results by directly aggregating the original intra-daily data. Note that convert2semiannual reports results on the last business day of June and December.

tt1 = convert2semiannual(TT1,'Aggregation',["lastvalue" "sum"])  % Quarterly to semiannual

tt1=6×2 timetable
       Time        Price     Log_Return
    ___________    ______    __________

    29-Jun-2018     96.24    -0.038325 
    31-Dec-2018     92.72    -0.037261 
    28-Jun-2019    110.54      0.17579 
    31-Dec-2019    163.65      0.39235 
    30-Jun-2020    168.96     0.031932 
    31-Dec-2020    274.75       0.4862 

tt2 = convert2semiannual(TT ,'Aggregation',["lastvalue" "sum"])  % Intra-daily to semiannual

tt2=6×2 timetable
       Time        Price     Log_Return
    ___________    ______    __________

    29-Jun-2018     96.24    -0.038325 
    31-Dec-2018     92.72    -0.037261 
    28-Jun-2019    110.54      0.17579 
    31-Dec-2019    163.65      0.39235 
    30-Jun-2020    168.96     0.031932 
    31-Dec-2020    274.75       0.4862 

The results of the two approaches are the same because each semiannual period contains exactly two calendar quarters.

Input Arguments

TT1 — Data to aggregate to semiannual periodicity

timetable

Data to aggregate to a semiannual 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 = convert2semiannual(TT1,'Aggregation',["lastvalue" "sum"])

Aggregation — Aggregation method for semiannual period to semiannual periodicity (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 a semiannual period to semiannual periodicity (inter-day aggregation), 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, convert2semiannual applies the specified method to all time series in TT1. If you specify a string vector or cell vector aggregation, convert2semiannual applies aggregation(j) to TT1(:,j); convert2semiannual 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.02    200.04    300.06     400.08
      03-Jan-2018     99.96    199.92    299.88     399.84
          .             .         .         .          .
          .             .         .         .          .
          .             .         .         .          .
      28-Jun-2018     69.63    139.26    208.89     278.52
      29-Jun-2018     70.15     140.3    210.45     280.60
      30-Jun-2018     75.77    151.54    227.31     303.08
      01-Jul-2018     75.68    151.36    227.04     302.72
      02-Jul-2018     71.34    142.68    214.02     285.36
      03-Jul-2018     69.25    138.50    207.75     277.00
          .             .         .         .          .
          .             .         .         .          .
          .             .         .         .          .
      29-Dec-2018    249.16    498.32    747.48     996.64
      30-Dec-2018    250.21    500.42    750.63    1000.84
      31-Dec-2018    256.75    513.50    770.25    1027.00

The corresponding default semiannual results representing TT2 (in which all days are business days and the 'lastvalue' is reported on the last business day of each semiannual period) are as follows.

           Time         AAA       BBB            CCC       
      ___________    ______    ______    ________________
      30-Jun-2018     75.77    151.54    227.31    303.08
      31-Dec-2018    256.75    513.50    770.25   1027.00

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

Output Arguments

TT2 — Semiannual data

timetable

Semiannual data, returned as a timetable. convert2semiannual reports semiannual aggregation results on the last business day of June and December. 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, convert2semiannual returns a NaN for that variable and annual period in TT2.

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

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

Version History

Introduced in R2021a