convert2annual

Aggregate timetable data to annual periodicity

Since R2021a

Syntax

TT2 = convert2annual(TT1)

TT2 = convert2annual(TT1,Name,Value)

Description

TT2 = convert2annual(TT1) aggregates data (for example, recorded daily or weekly data) to annual periodicity.

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

Examples

Aggregate Timetable Data to Annual Periodicity

Applyseparate aggregation methods to related variables in a timetable while maintaining consistency between aggregated results when converting to an annual periodicity. You can use convert2annual to aggregate both intra-daily data and aggregated monthly data. These methods result in equivalent annual aggregates.

Load a timetable (DataTimeTable) of simulated stock price data and corresponding logarithmic returns. The data stored in DataTimeTable 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 DataTimeTable 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("SimulatedStockSeries.mat","DataTimeTable");
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

First, aggregate intra-daily prices and returns to a monthly periodicity. To maintain consistency between prices and returns, for any given month aggregate prices by reporting the last recorded price using "lastvalue" and aggregate returns by summing all logarithmic returns using "sum".

DTTMonthly1 = convert2monthly(DataTimeTable,Aggregation=["lastvalue" "sum"]);
head(DTTMonthly1)

       Time        Price     Log_Return
    ___________    ______    __________

    31-Jan-2018    117.35      0.13462 
    28-Feb-2018    113.52    -0.033182 
    31-Mar-2018    110.74    -0.024794 
    30-Apr-2018    105.58    -0.047716 
    31-May-2018     97.88    -0.075727 
    30-Jun-2018     99.29     0.014303 
    31-Jul-2018    102.72     0.033962 
    31-Aug-2018    124.99      0.19623 

Use convert2annual to aggregate the data to an annual periodicity and compare the results of the two different aggregation approaches. The first approach computes annual results by aggregating the monthly aggregates and the second approach computes annual results by directly aggregating the original intra-daily data. Notice that by default, convert2annual reports results on the last business day of December. To change the month that ends annual periods, use the EndOfYearMonth optional name-value argument for convert2annual.

DTTAnnual1 = convert2annual(DTTMonthly1,Aggregation=["lastvalue" "sum"])   % Monthly to annual

DTTAnnual1=3×2 timetable
       Time        Price     Log_Return
    ___________    ______    __________

    31-Dec-2018     84.26     -0.19664 
    31-Dec-2019    153.22      0.59797 
    31-Dec-2020    301.04      0.67537 

DTTAnnual2 = convert2annual(DataTimeTable,Aggregation=["lastvalue" "sum"])  % Intra-daily to semiannual

DTTAnnual2=3×2 timetable
       Time        Price     Log_Return
    ___________    ______    __________

    31-Dec-2018     84.26     -0.19664 
    31-Dec-2019    153.22      0.59797 
    31-Dec-2020    301.04      0.67537 

The results of the two approaches are the same because each annual period contains exactly 12 calendar months.

Input Arguments

TT1 — Data to aggregate to annual periodicity

timetable

Data to aggregate to an annual 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 = convert2annual(TT1,'Aggregation',["lastvalue" "sum"])

Aggregation — 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

Aggregation method for TT1 defining how to aggregate data over business days in a year to an annual 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, convert2annual applies the specified method to all time series in TT1. If you specify a string vector or cell vector aggregation, convert2annual applies aggregation(j) to TT1(:,j); convert2annual applies each aggregation method one at a time (for more details, see retime). For example, consider an input daily timetable 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
          .             .         .         .         .
          .             .         .         .         .
          .             .         .         .         .
      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

By default, convert2annual applies the aggregation method "lastvalue", which reports for each variable the values of the last business day of each year. The aggregated annual results are as follows:

TT2 = convert2annual(TT1)

TT2 =

  1×3 timetable

          Time         AAA       BBB            CCC       
      ___________    ______    ______    ________________
      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

EndOfYearMonth — Month that ends annual periods

"December" (weeks end on Friday) (default) | integer with value 1 to 12 | "January" | "February" | "March" | "April" | "May" | "June" | "July" | "August" | "September" | "October" | "November" | "December" | character vector

Month that ends annual periods, specified as a value in this table.

Value	Month Ending Each Year
"January" or 1	January
"February" or 2	February
"March" or 3	March
"April" or 4	April
"May" or 5	May
"June" or 6	June
"July" or 7	July
"August" or 8	August
"September" or 9	September
"October" or 10	October
"November" or 11	November
"December" or 12	December

Data Types: double | char | string

Output Arguments

TT2 — Annual data

timetable

Annual 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, convert2annual returns a NaN for that variable and annual period in TT2.

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

If the last annual period (yearT) of TT1 contains at least one business day, the last date in TT2 is the last business date of yearT. Otherwise, the last date in TT2 is the previous end-of-year-period business date of TT1.

Version History

Introduced in R2021a