How to get quarterly average of month-end values

Table-valued functions in finaquant .net libraries

How to get quarterly average of month-end values

Postby crishna » 02 Dec 2013, 21:47

Hi, for an estimation problem I need to get quarterly averages of month-end values.

There can be many values, any measure like length, temperature or price, in a month. The last available value of a month should be taken into account for calculating quarterly averages. At the end, I need to have an average value for each quarter.

How is that possible with table functions? thanks.. Crishna
crishna
 
Posts: 3
Joined: 02 Dec 2013, 21:40

Quarterly average of month-end values

Postby finaquant » 11 Dec 2013, 22:21

By using (1) Date Sampling, and (2) Aggregation functions of the .NET libraries finaquant® protos (non-commercial) or finaquant® calcs (commercial) you can obtain quarterly averages of month-end values.

The most critical part of the solution is date sampling. You can find a related demo function in the Visual Studio project FinaquantCalcsStarter (or FinaquantProtosStarter) that you can download at the product links given above (see related downloads).

Image

Steps:
1) Obtain the sampled table with the last (latest) available month-end dates. The example code below samples an input table for last available month-end dates (i.e. searches backwards in time if the last day of the month is not available) of year 2010.
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. SourceDateField = "source_date";
  2. TargetDateField = "target_date";
  3. FirstDayOfRange = DateFunctions.DayToNumber(1, 1, 2010);
  4. LastDayOfRange = DateFunctions.DayToNumber(31, 12, 2010);
  5. AllowMonthDays = NumVector.CreateVectorWithElements(-1);  // -1 means last day of month
  6. AllowWeekDays = NumVector.CreateEmptyVector();  // empty vector means allow all week-days
  7. // AllowWeekDays = NumVector.CreateEmptyVector(1,2,3,4,5);  // allow only work-days
  8. search_logic = SearchLogic.Previous;  // search backwards in time for a source date
  9. // sample table for dates
  10. SampledTbl = MatrixTable.SampleDatesMonthly_A(TestTable, SourceDateField, TargetDateField, FirstDayOfRange, LastDayOfRange,
  11.     search_logic, MaxDistance, AllowMonthDays, AllowWeekDays, PeriodCond);
  12. // view sampled table in GridViewer
  13. MatrixTable.View_MatrixTable(SampledTbl, "Last available day of each month in 2010, search logic: previous");
GeSHi ©


2) Insert a new numeric attribute named quarter into sampled table, and obtain its values depending on target_date.

You can use row-by-row processing (see table function MatrixTable.TransformRowsDic) and the date function DateFunctions.Quarter for this purpose.

You have now a table with sampled dates (target_date) and corresponding quarter value for each date.

3) Exclude date column (target_date) and aggregate table with respect do attribute quarter with "average" as the standard aggregation function. See table function MatrixTable.AggregateAllKeyFigures in help page for Table Aggregation Functions.
User avatar
finaquant
 
Posts: 61
Joined: 16 Sep 2013, 18:37

Re: How to get quarterly average of month-end values

Postby crishna » 12 Dec 2013, 16:31

Thanks a lot. I could follow date sampling, but I didn't understand the aggregation.

Could you please explain that briefly? Thanks.
crishna
 
Posts: 3
Joined: 02 Dec 2013, 21:40

#C code for quarterly average of month-end values

Postby finaquant » 13 Dec 2013, 20:39

You may find an example C#/.NET code below including the aggregation part.

Create a test table (SalesTable) with source dates
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. // define metadata
  2. MetaData md = MetaData.CreateEmptyMetaData();
  3. MetaData.AddNewField(md, "category", FieldType.TextAttribute);
  4. MetaData.AddNewField(md, "product", FieldType.TextAttribute);
  5. MetaData.AddNewField(md, "source_date", FieldType.DateAttribute);
  6. MetaData.AddNewField(md, "target_date", FieldType.DateAttribute);
  7. MetaData.AddNewField(md, "sales", FieldType.KeyFigure);
  8. MetaData.AddNewField(md, "quarter", FieldType.IntegerAttribute);
  9.  
  10. // define table structure
  11. var TestTableFields = TableFields.CreateEmptyTableFields(md);
  12. TableFields.AddNewField(TestTableFields, "category");
  13. TableFields.AddNewField(TestTableFields, "product");
  14. TableFields.AddNewField(TestTableFields, "source_date");
  15. TableFields.AddNewField(TestTableFields, "sales");
  16.  
  17. // create test table by combinating field values
  18. // ... random values for key figures
  19.  
  20. // text attributes
  21. TextVector CategoryVal = TextVector.CreateVectorWithElements("Economy", "Luxury", "Sports");
  22. TextVector ProductVal = TextVector.CreateVectorWithElements("Car", "Bus", "Motor");
  23. // numeric attributes
  24. NumVector DateVal = NumVector.CreateSequenceVector(
  25.     StartValue: DateFunctions.DayToNumber(5, 9, 2009), Interval: 5, nLength: 100);
  26.  
  27. // initiate field value dictionaries
  28. var TextAttribValues = new Dictionary<string, TextVector>();
  29. var NumAttribValues = new Dictionary<string, NumVector>();
  30. // assign a value vector to each field
  31. TextAttribValues["category"] = CategoryVal;
  32. TextAttribValues["product"] = ProductVal;
  33. NumAttribValues["source_date"] = DateVal;
  34.  
  35. // default range for all key figures
  36. KeyValueRange DefaultRangeForAllKeyFigures = KeyValueRange.CreateRange(2000, 10000);
  37.  
  38. // create test table
  39. var SalesTable = MatrixTable.CombinateFieldValues_B(TestTableFields,
  40.     TextAttribValues, NumAttribValues, DefaultRangeForAllKeyFigures);
  41.  
  42. // round all key figures to 2 digits after decimal point
  43. SalesTable = MatrixTable.TransformKeyFigures(SalesTable, x => Math.Round(x, 2),
  44.     InputKeyFig: null, OutputKeyFig: null);
  45.  
  46. // view table
  47. MatrixTable.View_MatrixTable(SalesTable, "SalesTable before date sampling");
GeSHi ©


Sample table for month-end dates: Search backwards if a month-end day is not available
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. // Sample dates: month-end days, search backwards (previous)
  2. String SourceDateField = "source_date";
  3. String TargetDateField = "target_date";
  4. int FirstDayOfRange = DateFunctions.DayToNumber(1, 1, 2010);
  5. int LastDayOfRange = DateFunctions.DayToNumber(31, 12, 2010);
  6. NumVector AllowMonthDays = NumVector.CreateVectorWithElements(-1); // -1 means last day of month
  7. NumVector AllowWeekDays = NumVector.CreateEmptyVector();  // empty vector means allow all week-days
  8. SearchLogic search_logic = SearchLogic.Previous; // search backwards in time for a source date
  9. int MaxDistance = 30;
  10. PeriodCondition PeriodCond = PeriodCondition.None;
  11.  
  12. // sample table for dates
  13. MatrixTable SampledTbl = MatrixTable.SampleDatesMonthly_A(SalesTable, SourceDateField, TargetDateField, FirstDayOfRange, LastDayOfRange,
  14.     search_logic, MaxDistance, AllowMonthDays, AllowWeekDays, PeriodCond);
  15. // view sampled table in GridViewer
  16. MatrixTable.View_MatrixTable(SampledTbl, "Month-end dates of 2010, search logic: previous");
GeSHi ©

Image

Insert related field "quarter" into sampled table
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. // insert new field "quarter" into SampledTbl
  2. SampledTbl = MatrixTable.InsertDateRelatedAttribute(SampledTbl, "target_date", "quarter",
  3.     DateRelation.Quarter);
  4.  
  5. // view sampled table with quarters
  6. MatrixTable.View_MatrixTable(SampledTbl, "Month-end dates of 2010 with Quarters");
GeSHi ©


Aggregate to get quarterly month-end averages
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. // fields to be excluded before aggregation
  2. TextVector ExcludedFields = TextVector.CreateVectorWithElements("target_date", "source_date");
  3.  
  4. // exclude fields
  5. SampledTbl = SampledTbl.ExcludeColumns(ExcludedFields);
  6.  
  7. // aggregate sales with Avg
  8. MatrixTable ResultTbl = MatrixTable.AggregateAllKeyFigures(SampledTbl, null, AggregateOption.nAvg);
  9.  
  10. // view table with quarterly averages
  11. MatrixTable.View_MatrixTable(ResultTbl, "Quarterly averages of month-end Sales");
GeSHi ©

Image

Note that because we are aggregating month-end values from a single year (2010) to obtain quarterly averages in this example, we don't need an additional field "year". For aggregation over multiple years an additional field "year" would be necessary.
User avatar
finaquant
 
Posts: 61
Joined: 16 Sep 2013, 18:37


Return to Table Functions

Who is online

Users browsing this forum: No registered users and 1 guest

cron