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"); |
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"); |
Table functions for filtering rows w.r.t. dates are available in finaquant® protos also for other periods like quarter and year.
Date sampling
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.
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"); |
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"); |
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"); |
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
Copyright secured by Digiprove © 2013 Tunc Ali Kütükcüoglu
- Table functions for date filtering and sampling are available in releases 1.02 and higher. [↩]