How to exclude products or assign validity dates?

Table-valued functions in finaquant .net libraries

How to exclude products or assign validity dates?

Postby susanK » 11 Dec 2014, 11:15

Hi,
my problem is a bit complicated, I will try to explain it in a simple way.

In a data table we have financial products (say products) with validity dates (valid_from). I need to process this table in order to obtain a new updated table, like this:

* products whose validity dates are older than a certain date, say "January 1st, 2002" must be excluded from the table.
* products that don't have any validity date must be assigned a constant validity date, say "January 1st, 2010"

Can I do this with the excel add-in of finaquant?

Susan
susanK
 
Posts: 2
Joined: 13 Nov 2013, 18:11

Filtering and Transforming Table Rows in Excel

Postby tunc » 17 Dec 2014, 20:03

Yes you can, by using two table-valued functions subsequently:

  1. Filter Rows with UDF (User Defined Function)
  2. Transform Rows with UDF (User Defined Function)
Image

Note that an empty date value is transformed to integer 0 (as serial day number, 0.1.1900 in date format) when read by finaquant excel add-in. So, an empty date value means "integer value of date = 0" in our filtering logic.

Note also that the integer value (i.e. serial day number) of date 1.1.2002 = 37257 (you can check this with the worksheet function VALUE in excel).

Step 1: Filter (include/exclude) table rows using table function Filter Rows with UDF
You will include only rows with integer date value >= 37257 OR integer date value = 0

That is, your UDF will look like as follows (valid C# code):
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. return NA["valid_from"] >= 37257 || NA["valid_from"] == 0;
GeSHi ©

Image

Step 2: Transform (update) table rows using table function Transform Rows with UDF
Set integer date value = 40179 (1.1.2010 in date format) if it was 0 before
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. if (NA["valid_from"] == 0) NA["valid_from"] = 40179;
GeSHi ©

Image

You may see below all the three tables:
Image

ProdDate: Initial input table before filtering and transformation.
ProdDate2: Intermediate resultant table after filtering.
ProdDate3: Final resultant table after filtering and transformation.

Tunc
tunc
 
Posts: 25
Joined: 22 Jul 2014, 19:29
Location: Switzerland


Return to Table Functions

Who is online

Users browsing this forum: No registered users and 1 guest

cron