How to exclude products or assign validity dates?

[susanK]

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

[tunc]

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)

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 ©

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 ©

You may see below all the three tables:


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

Tunc