convert2annual

Aggregate timetable data to annual periodicity

Syntax

TT2 = convert2annual(TT1)

TT2 = convert2annual(___,Name,Value)

Description

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

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

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 (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

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".

TT1 = convert2monthly(TT,'Aggregation',["lastvalue" "sum"]);
head(TT1)

ans=8×2 timetable
       Time        Price     Log_Return
    ___________    ______    __________

    31-Jan-2018    122.96      0.20669 
    28-Feb-2018    121.92    -0.008494 
    29-Mar-2018     108.9     -0.11294 
    30-Apr-2018    110.38     0.013499 
    31-May-2018     99.02     -0.10861 
    29-Jun-2018     96.24    -0.028477 
    31-Jul-2018     97.15    0.0094111 
    31-Aug-2018    101.51     0.043901 

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' name-value pair argument for convert2annual.

tt1 = convert2annual(TT1,'Aggregation',["lastvalue" "sum"])  % Monthly to annual

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

    31-Dec-2018     92.72    -0.075586 
    31-Dec-2019    163.65      0.56815 
    31-Dec-2020    274.75      0.51813 

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

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

    31-Dec-2018     92.72    -0.075586 
    31-Dec-2019    163.65      0.56815 
    31-Dec-2020    274.75      0.51813 

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.

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

'Aggregation' — Aggregation method for TT1 data for annual aggregation

'lastvalue' (default) | character vector with value 'sum', 'mean', 'prod', 'min', 'max', 'firstvalue', or 'lastvalue' | string with value "sum", "mean", "prod", "min", "max", "firstvalue", or "lastvalue"

Aggregation method for TT1 data for annual 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 a year to an annual periodicity. Available aggregation methods are:

• '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.

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 the aggregated results TT2.

Additionally, you can specify aggregation methods 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
          .             .         .         .         .
          .             .         .         .         .
          .             .         .         .         .
      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 weekly results representing TT2 (in which all days are business days and the 'lastvalue' is reported on the last business day of each year) are as follows.

          Time         AAA       BBB            CCC       
      ___________    ______    ______    ________________
      31-Dec-2018    256.75    513.50    770.25   1027.00

Data Types: char | string | cell | function_handle

'Daily' — Method for intra-day aggregation for data in TT1

no daily aggregation (default) | character vector | string | function handle | cell array of character vectors | string array | vector of function handles

Method for intra-day aggregation for data in TT1, specified as the comma-separated pair consisting of 'Daily' and a character vector, string scalar, 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.

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 | character vector with value 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August','September','October', 'November', or 'December' | string with value "January", "February", "March", "April", "May", "June", "July", "August","September","October", "November", or "December"

Month that ends annual periods, specified as the comma-separated pair consisting of 'EndOfYearMonth' and a string, character vector, or a scalar integer.

Data Types: double | char | string

Output Arguments

TT2 — Annual data

timetable

Annual data, returned as a timetable. The function returns NaNs for variables in TT2 for annual periods 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 annual period (year) in which the first date in TT1 occurs, provided TT1 has business dates in that annual period (year), otherwise the first date in TT2 is the next end-of-year-period business date.

The last date in TT2 is the last business date of the annual period (year) in which the last date in TT1 occurs, provided TT1 has business dates in that annual period (year), otherwise the last date in TT2 is the previous end-of-year-period business date.