# convert2quarterly

Aggregate timetable data to quarterly periodicity

Since R2021a

## Description

example

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

example

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

## Examples

collapse all

Apply separate aggregation methods to related variables in a timetable while maintaining consistency between aggregated results when converting to a quarterly periodicity. You can use convert2quarterly to aggregate both intra-daily data and aggregated monthly data. These methods result in equivalent quarterly 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.

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

Use convert2monthly to 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"]);
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 convert2quarterly to aggregate the data to a quarterly periodicity and compare the results of two different approaches. The first approach computes quarterly results by aggregating the monthly aggregates and the second approach computes quarterly results by directly aggregating the original intra-daily data. Note that convert2quaterly reports results on the last business day of each quarter.

DTTQuarterly1 = convert2quarterly(DTTMonthly1,Aggregation=["lastvalue" "sum"]);     % Monthly to quarterly
DTTQuarterly2 = convert2quarterly(DataTimeTable,Aggregation=["lastvalue" "sum"]);   % Intra-daily to quarterly
Time        Price     Log_Return
___________    ______    __________

31-Mar-2018    110.74      0.07664
30-Jun-2018     99.29     -0.10914
30-Sep-2018    105.42     0.059908
31-Dec-2018     84.26     -0.22405
31-Mar-2019    112.93      0.29286
30-Jun-2019    169.77      0.40768
30-Sep-2019    148.97      -0.1307
31-Dec-2019    153.22      0.02813
Time        Price     Log_Return
___________    ______    __________

31-Mar-2018    110.74      0.07664
30-Jun-2018     99.29     -0.10914
30-Sep-2018    105.42     0.059908
31-Dec-2018     84.26     -0.22405
31-Mar-2019    112.93      0.29286
30-Jun-2019    169.77      0.40768
30-Sep-2019    148.97      -0.1307
31-Dec-2019    153.22      0.02813

The results of the two approaches are the same because each quarter contains exactly three calendar months.

## Input Arguments

collapse all

Data to aggregate to a quarterly 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.

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

Aggregation method for TT1 data defining how to aggregate data over business days in an intra-quarter 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, convert2quarterly applies the specified method to all time series in TT1. If you specify a string vector or cell vector aggregation, convert2quarterly applies aggregation(j) to TT1(:,j); convert2quarterly 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
.             .         .         .         .
.             .         .         .         .
.             .         .         .         .
31-Mar-2018    162.93    325.86    488.79    651.72
.             .         .         .         .
.             .         .         .         .
.             .         .         .         .
30-Jun-2018    223.22    446.44    669.66    892.88
.             .         .         .         .
.             .         .         .         .
.             .         .         .         .
30-Sep-2018    232.17    464.34    696.51    928.68
.             .         .         .         .
.             .         .         .         .
.             .         .         .         .
31-Dec-2018    243.17    486.34    729.51    972.68
The corresponding default quarterly results representing TT2 (in which all days are business days and the 'lastvalue' is reported on the last business day of each quarter) are as follows.
Time         AAA       BBB            CCC
___________    ______    ______    ________________
31-Mar-2018    162.93    325.86    488.79    651.72
30-Jun-2018    223.22    446.44    669.66    892.88
30-Sep-2018    232.17    464.34    696.51    928.68
31-Dec-2018    243.17    486.34    729.51    972.68

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

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

collapse all

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

convert2quarterly reports quarterly aggregation results on the last business day of March, June, September, and December.

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

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

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

## Version History

Introduced in R2021a