# convert2monthly

Aggregate timetable data to monthly periodicity

Since R2021a

## Description

example

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

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

## Examples

collapse all

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

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.

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

Aggregate the price series to a monthly series by reporting the final price in each month.

MonthlyPrice = convert2monthly(DataTimeTable(:,"Price"));
tail(MonthlyPrice)
Time        Price
___________    ______

31-May-2020    227.22
30-Jun-2020    224.29
31-Jul-2020     236.4
31-Aug-2020     227.5
30-Sep-2020    246.77
31-Oct-2020    275.07
30-Nov-2020    298.87
31-Dec-2020    301.04

MonthlyPrice is a timetable containing the final prices for each reported month in DataTimeTable.

You can apply custom aggregation methods using function handles. Specify a function handle to aggregate related variables in a timetable while maintaining consistency between aggregated results when converting from a daily to a monthly periodicity.

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

Include another variable in the data called Simple_Return, which contains the simple (proportional) returns associated with the price series, and examine the first few rows.

DataTimeTable.Simple_Return = exp(DataTimeTable.Log_Return) - 1;  % Log returns to simple returns
Time            Price     Log_Return    Simple_Return
____________________    ______    __________    _____________

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

The price series Price contains absolute measurements, whereas the log and simple returns series, Log_Return and Simple_Return, are the rates 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 and a custom transformation for simple returns.

Create a function to aggregate simple returns.

f = @(x)(prod(1 + x,1,'omitnan') - 1);

Aggregate the data so that the result has an monthly periodicity. For each series, specify the aggregation method that is appropriate for the unit.

TT = convert2monthly(DataTimeTable,Aggregation={'lastvalue' 'sum' f});
Time        Price     Log_Return    Simple_Return
___________    ______    __________    _____________

31-Jan-2018    117.35      0.13462          0.1441
28-Feb-2018    113.52    -0.033182       -0.032637
31-Mar-2018    110.74    -0.024794       -0.024489
30-Apr-2018    105.58    -0.047716       -0.046596
31-May-2018     97.88    -0.075727        -0.07293
30-Jun-2018     99.29     0.014303        0.014405
31-Jul-2018    102.72     0.033962        0.034545
31-Aug-2018    124.99      0.19623          0.2168

The aggregation function for simple returns operates along the first dimension (row) and omits missing data (NaNs).

## Input Arguments

collapse all

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

Aggregation method for TT1 defining how to aggregate data over business days in an intra-month 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, convert2monthly applies the specified method to all time series in TT1. If you specify a string vector or cell vector aggregation, convert2monthly applies aggregation(j) to TT1(:,j); convert2monthly 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-Jan-2018    114.65     229.3    343.95    458.60
.             .         .         .         .
.             .         .         .         .
.             .         .         .         .
28-Feb-2018    129.19    258.38    387.57    516.76
.             .         .         .         .
.             .         .         .         .
.             .         .         .         .
31-Mar-2018    162.93    325.86    488.79    651.72
.             .         .         .         .
.             .         .         .         .
.             .         .         .         .
30-Apr-2018    171.72    343.44    515.16    686.88
.             .         .         .         .
.             .         .         .         .
.             .         .         .         .
31-May-2018    201.24    402.48    603.72    804.96
.             .         .         .         .
.             .         .         .         .
.             .         .         .         .
30-Jun-2018    223.22    446.44    669.66    892.88
The corresponding default monthly results representing TT2 (in which all days are business days and the 'lastvalue' is reported on the last business day of each month) are as follows.
Time         AAA       BBB            CCC
___________    ______    ______    ________________
31-Jan-2018    114.65    229.30    343.95    458.60
28-Feb-2018    129.19    258.38    387.57    516.76
31-Mar-2018    162.93    325.86    488.79    651.72
30-Apr-2018    171.72    343.44    515.16    686.88
31-May-2018    201.24    402.48    603.72    804.96
30-Jun-2018    223.22    446.44    669.66    892.88

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

Day of the month that ends months, specified as a scalar integer with value 1 to 31. For months with fewer days than EndOfMonthDay, convert2monthly reports aggregation results on the last business day of the month.

Data Types: double

## Output Arguments

collapse all

Monthly 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 a month within the sampling time span, convert2monthly returns a NaN for that variable and month in TT2.

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

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

## Version History

Introduced in R2021a