Filtering and sampling tables by dates

All the C# code examples presented on this page can be found in the related demo function of MS Visual Studio project FinaquantProtosStarter which can be downloaded at the product page of the free .NET library finaquant® protos.
Assume, you have a data table containing daily prices of different products in each entry, and you want to exclude all rows of table associated with weekend days. That is, you want to have only entries with business days from Monday to Friday in your table. Then you need a date filter.

Assume, you have a data table containing temperatures measured at different locations almost daily (with some days missing), and you want to have only month-end measurements to calculate the yearly volatility of temperature for each location. If a measurement is missing on the last day of a certain month, the nearest previous temperature should be taken as a proxy value for the month-end day. In such a case, you need a date sampler with search options like previous (backward), next (forward) and nearest as shown below with examples.

Date-related variations like “last Monday value of each month”, “quarterly month-end averages”, “monthly business-day maximums” and so on can be obtained with date samplers and filters of finaquant® protos1 with the help of the table aggregation functions wherever necessary.

Date filtering

Following example shows how rows of a table can be filtered for a date range and for certain month-days within this range. Month-days in the numeric vector AllowMonthDays can be positive or negative up to ±28; -1 means last day of the month.

Example 1: Allow first, 15’th and last day of each month

// Allow first, 15'th and last day of each month; allow all week-days
DateField = "date";
FirstDayOfRange = DateFunctions.DayToNumber(25, 2, 2010);
LastDayOfRange = DateFunctions.DayToNumber(20, 10, 2010);
AllowMonthDays = NumVector.CreateVectorWithElements(1, 15, -1);     // -1 means last day of month
AllowWeekDays =  NumVector.CreateEmptyVector();     // empty vector means "allow all week-days"
// filter table
FilteredTbl = MatrixTable.FilterDatesMonthly(TestTable, DateField, FirstDayOfRange, 
    LastDayOfRange, AllowMonthDays, AllowWeekDays);
// view filtered table
MatrixTable.View_MatrixTable(FilteredTbl, "CASE 1: Allow first, 15'th and last day of each month; allow all week-days");

Date filtered table: Allow first, 15'th and last day of each month

Following example shows how month-day and week-day conditions can be applied simultaneously on the input table.

Example 2: First and last Mondays of each month

DateField = "date";
FirstDayOfRange = DateFunctions.DayToNumber(25, 2, 2010);
LastDayOfRange = DateFunctions.DayToNumber(20, 10, 2010);
AllowMonthDays = NumVector.CreateVectorWithElements(1, 2, 3, 4, 5, 6, 7, -1, -2, -3, -4, -5, -6, -7);
AllowWeekDays = NumVector.CreateVectorWithElements(1);  // allow Monday only
// filter table
FilteredTbl = MatrixTable.FilterDatesMonthly(TestTable, DateField, FirstDayOfRange, 
    LastDayOfRange, AllowMonthDays, AllowWeekDays);
// insert date-related numeric attribute weekday
FilteredTbl = MatrixTable.InsertDateRelatedAttribute(FilteredTbl, DateField, "weekday", 
    DateRelation.DayOfWeek);
// view filtered table
MatrixTable.View_MatrixTable(FilteredTbl, "CASE 2: First and last Mondays of each month");

Date filtered table: First and last Mondays of each month

Table functions for filtering rows w.r.t. dates are available in finaquant® protos also for other periods like quarter and year.

Date sampling

Date sampler: Target and Source Dates

Source dates are the dates that are available in a table. Target dates are the dates that are to be searched after in the table.

If a target date is found in the table (i.e. exact match of target and source dates), there is no need to search for a proxy source date. If a target date is not found, a source date needs to be searched for with the given search logic (previous, nearest, next).

MaxDistance is another important sampling parameter, which is the maximum allowable distance in days between target and source days.

The TestTable shown below will be used for the following examples. Note that the field source_date has values with 5-day intervals between subsequent dates.
TestTable before date sampling

Example 1: Sample first, 15’th and last days of each month. Search logic: Previous date

// First, 15'th and last days of each month within given range; allow all week-days
SourceDateField = "source_date";
TargetDateField = "target_date";
FirstDayOfRange = DateFunctions.DayToNumber(25, 2, 2010);
LastDayOfRange = DateFunctions.DayToNumber(20, 10, 2010);
AllowMonthDays = NumVector.CreateVectorWithElements(1, 15, -1);     // -1 means last day of month
AllowWeekDays = NumVector.CreateEmptyVector();    // empty vector means allow all week-days
search_logic = SearchLogic.Previous;              // search backwards in time for a source date
MaxDistance = 30;
PeriodCond = PeriodCondition.None;
// sample table for dates
SampledTbl = MatrixTable.SampleDatesMonthly_A(TestTable, SourceDateField, TargetDateField, 
    FirstDayOfRange, LastDayOfRange, search_logic, MaxDistance,
    AllowMonthDays, AllowWeekDays, PeriodCond);
// view sampled table in GridViewer
MatrixTable.View_MatrixTable(SampledTbl, "Case 1: First, 15'th and last days of each month, search logic: previous");

TestTable after sampling, example 1

As seen in the resultant table above, exact matches were found for the target dates 1.4.2010 and 1.5.2010. Proxy source dates had to be searched for other target dates with the search logic previous (backwards in time).

Example 2: First and last Mondays of each month. Search logic: Nearest date

// First and last Mondays of each month, search logic: Nearest Date
SourceDateField = "source_date";
TargetDateField = "target_date";
FirstDayOfRange = DateFunctions.DayToNumber(25, 2, 2010);
LastDayOfRange = DateFunctions.DayToNumber(20, 10, 2010);
AllowMonthDays = NumVector.CreateVectorWithElements(1, 2, 3, 4, 5, 6, 7, -1, -2, -3, -4, -5, -6, -7);
AllowWeekDays = NumVector.CreateVectorWithElements(1);
search_logic = SearchLogic.Nearest;
MaxDistance = 30;
PeriodCond = PeriodCondition.None;
// sample table for dates
SampledTbl = MatrixTable.SampleDatesMonthly_A(TestTable, SourceDateField, TargetDateField, 
    FirstDayOfRange, LastDayOfRange, search_logic, MaxDistance,
    AllowMonthDays, AllowWeekDays, PeriodCond);
// insert week-day of target date into table
SampledTbl = MatrixTable.InsertDateRelatedAttribute(SampledTbl, TargetDateField, "week_day_target", 
    DateRelation.DayOfWeek);
// view sampled table in GridViewer
MatrixTable.View_MatrixTable(SampledTbl, "Case 2: First and last Mondays of each month, search logic: nearest");

TestTable after sampling, example 2

Notice that some source dates come before target dates, some after in time, because the search logic is this time nearest.

One more and the last example below. For fun, let’s make it slightly more complicated than the others.

Example 3: First and last Mondays of each month. All source dates must be business days (Monday-Friday). Search logic: Nearest date

“All source dates must be business days” means, weekend days must be excluded from the table before sampling for target days.

Following code also demonstrates how a simple date range pre-filtering could be applied on the table before sampling in order to improve sampling performance.

// First and last Mondays of each month; source date must be a business day (Monday-Friday)		
SourceDateField = "source_date";
TargetDateField = "target_date";
FirstDayOfRange = DateFunctions.DayToNumber(25, 2, 2010);
LastDayOfRange = DateFunctions.DayToNumber(15, 7, 2010);
AllowMonthDays = NumVector.CreateVectorWithElements(1, 2, 3, 4, 5, 6, 7, -1, -2, -3, -4, -5, -6, -7);
AllowWeekDays = NumVector.CreateVectorWithElements(1);
search_logic = SearchLogic.Nearest;
MaxDistance = 30;
PeriodCond = PeriodCondition.None;
 
// Prefilter table for the required date range for improved performance.
// For demonstration purposes only; it might be useful only if the date range of the input table 
// is really large compared to the required range to be sampled.
 
// determine filter range as a function of sampling parameters
int FirstFilterDay, LastFilterDay, FirstTargetDay, LastTargetDay;
 
DateFunctions.FindDateRange(DatePeriod.Month, StartPeriodNo: 2, StartYear: 2010,
	EndPeriodNo: 7, EndYear: 2010, SearchLog: search_logic, MaxDistance: MaxDistance,
	FirstFilterDay: out FirstFilterDay, LastFilterDay: out LastFilterDay,
	FirstTargetDay: out FirstTargetDay, LastTargetDay: out LastTargetDay);
 
// apply date range filter on TestTable
MatrixTable FilteredTable = MatrixTable.DateRangeFilter(TestTable, SourceDateField, 
    FirstFilterDay, LastFilterDay);
 
// exclude weekend days (Saturday and Sunday) from table
FilteredTable = MatrixTable.FilterDatesMonthly(FilteredTable, SourceDateField, 
    FirstFilterDay, LastFilterDay, 
    AllowMonthDays: NumVector.CreateEmptyVector(),      
    AllowWeekDays: NumVector.CreateVectorWithElements(1, 2, 3, 4, 5)
    );
 
// sample table for dates
SampledTbl = MatrixTable.SampleDatesMonthly_A(FilteredTable, SourceDateField, TargetDateField, 
    FirstDayOfRange, LastDayOfRange, search_logic, MaxDistance,
    AllowMonthDays, AllowWeekDays, PeriodCond);
 
// insert week-day of target and source dates into table
SampledTbl = MatrixTable.InsertDateRelatedAttribute(SampledTbl, TargetDateField, "week_day_target", 
    DateRelation.DayOfWeek);
SampledTbl = MatrixTable.InsertDateRelatedAttribute(SampledTbl, SourceDateField, "week_day_source", DateRelation.DayOfWeek);
 
// view sampled table in GridViewer
MatrixTable.View_MatrixTable(SampledTbl, "Case 3: First and last Mondays of each month; business source dates only");

TestTable after sampling, example 3

Notice that there is not a single source date in the resultant table above as a weekend day.
Written by: Tunç Ali Kütükçüoglu

Digiprove sealCopyright secured by Digiprove © 2013 Tunc Ali Kütükcüoglu
  1. Table functions for date filtering and sampling are available in releases 1.02 and higher. []
This entry was posted in Calculation engine and tagged , , , . Bookmark the permalink.

Leave a Reply