convert2monthly

Aggregate timetable data to monthly periodicity

Syntax

TT2 = convert2monthly(TT1)

TT2 = convert2monthly(TT1,Name,Value)

Description

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

Aggregate Timetable Data to Monthly Periodicity

Apply separate aggregation methods to related variables in a timetable while maintaining consistency between aggregated results when converting to a monthly periodicity. You can use convert2monthly to aggregate both intra-daily data and aggregated daily data. These methods result in equivalent monthly aggregates. Lastly, you can aggregate results on a specific day of each month (for example, the 15th), rather than the default end of the month.

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 daily periodicity. To maintain consistency between prices and returns, for any given trading day aggregate prices by reporting the last recorded price using "lastvalue" and aggregate returns by summing all logarithmic returns using "sum".

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

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

    02-Jan-2018    101.37     0.013607 
    03-Jan-2018    100.12    -0.012408 
    04-Jan-2018    106.76     0.064214 
    05-Jan-2018    112.78     0.054856 
    08-Jan-2018    119.07     0.054273 
    09-Jan-2018    119.46      0.00327 
    10-Jan-2018    124.44     0.040842 
    11-Jan-2018    125.63    0.0095174 

Use convert2monthly to aggregate the data to a monthly periodicity and compare the results of two different approaches. The first approach computes monthly results by aggregating the daily aggregates and the second approach computes monthly results by directly aggregating the original intra-daily data. Note that although convert2monthly reports results on the last business day of each month by default, you can report monthly results on the 15th of each month by using the optional name-value pair argument 'EndOfMonthDay'.

tt1 = convert2monthly(TT1,'Aggregation',["lastvalue" "sum"],'EndOfMonthDay',15); % Daily to monthly
tt2 = convert2monthly(TT ,'Aggregation',["lastvalue" "sum"],'EndOfMonthDay',15); % Intra-daily to monthly

head(tt1)

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

    12-Jan-2018    125.93      0.23056 
    15-Feb-2018    120.55    -0.043662 
    15-Mar-2018    113.49     -0.06035 
    13-Apr-2018    112.07    -0.012591 
    15-May-2018    110.47     -0.01438 
    15-Jun-2018     99.06     -0.10902 
    13-Jul-2018     95.74     -0.03409 
    15-Aug-2018     99.94     0.042934 

head(tt2)

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

    12-Jan-2018    125.93      0.23056 
    15-Feb-2018    120.55    -0.043662 
    15-Mar-2018    113.49     -0.06035 
    13-Apr-2018    112.07    -0.012591 
    15-May-2018    110.47     -0.01438 
    15-Jun-2018     99.06     -0.10902 
    13-Jul-2018     95.74     -0.03409 
    15-Aug-2018     99.94     0.042934 

Notice that the results of the two approaches are the same. For months in which the 15th is not an NYSE trading day, the function reports results on the previous business day.

Use Custom Aggregation Method to Convert Timetable Daily Data to Monthly Periodicity

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 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 add another variable to TT that contains the simple (proportional) returns associated with the prices in TT and examine the first few rows.

TT.Simple_Return = exp(TT.Log_Return) - 1;  % Log returns to simple returns
head(TT)

ans=8×3 timetable
            Time            Price     Log_Return    Simple_Return
    ____________________    ______    __________    _____________

    02-Jan-2018 11:52:11    100.71     0.0070749         0.0071  
    02-Jan-2018 13:23:09    103.11      0.023551       0.023831  
    02-Jan-2018 14:45:30    100.24     -0.028229      -0.027834  
    02-Jan-2018 15:30:48    101.37       0.01121       0.011273  
    03-Jan-2018 10:02:21    101.81     0.0043311      0.0043405  
    03-Jan-2018 11:22:37    100.17      -0.01624      -0.016108  
    03-Jan-2018 14:45:20     99.66    -0.0051043     -0.0050913  
    03-Jan-2018 14:55:39    100.12     0.0046051      0.0046157  

Create a function to aggregate simple returns and compute the monthly aggregates. To maintain consistency between prices and returns, for any given month, aggregate prices by reporting the last recorded price by using "lastvalue" and report logarithmic returns by summing all intervening logarithmic returns by using "sum".

Notice that the aggregation function for simple returns operates along the first (row) dimension and omits missing data (NaNs). For more information on custom aggregation functions, see timetable and retime. When aggregation methods are a mix of supported methods and user-supplied functions, the 'Aggregation' name-value pair argument must be specified as a cell vector of methods enclosed in curly braces.

f = @(x)(prod(1 + x,1,'omitnan') - 1);      % Aggregate simple returns
tt = convert2monthly(TT,'Aggregation',{'lastvalue' 'sum' f});
head(tt)

ans=8×3 timetable
       Time        Price     Log_Return    Simple_Return
    ___________    ______    __________    _____________

    31-Jan-2018    122.96      0.20669          0.2296  
    28-Feb-2018    121.92    -0.008494       -0.008458  
    29-Mar-2018     108.9     -0.11294        -0.10679  
    30-Apr-2018    110.38     0.013499         0.01359  
    31-May-2018     99.02     -0.10861        -0.10292  
    29-Jun-2018     96.24    -0.028477       -0.028075  
    31-Jul-2018     97.15    0.0094111       0.0094555  
    31-Aug-2018    101.51     0.043901        0.044879  

Input Arguments

TT1 — Data to aggregate to monthly periodicity

timetable

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.

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

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

EndOfMonthDay — Day of the month that ends months

last business day of month (default) | integer with value 1 to 31

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

TT2 — Monthly data

timetable

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.