Determine Trading Calendar Month

[gerhardl]

Hi,

I have a table (excel file) which shows the Trading Calendar for the year (see attached "TCM"  image). It contains these fields:

Month Start, Month End, Trading Month

e.g:

2013/12/01, 2013/12/28, December 2013

When I load other files (e.g. transaction extracts) they have certain date fields, like "Posted Date", which I load as follows:

DATE(DATE#( [Posted Date], 'DD-MMM-YY')) as [Posted Date],

I need to create new fields that match the Trading Calendar to see in which Month the transaction falls. So in the above example the new field will be called [Posted Date TCM], and if the posted date is 2013/12/28 then the Trading Month is December 2013, but if the Posted Date is 2013/12/29 then the Trading Month is January 2014.

What is the best way to go about this?

If it helps, each month-end is the last Saturday of the month, so using that logic I can possibly bypass the need for loading the excel file?

Thanks,

Gerhard

[jonathandienst]

Hi

I suggest that you use interval match to get the periods into your document. That way you are not hard-coding the rule into your model, but you would need to update the trading months spreadsheet once per year. The code would look like this (assuming your transactions are in a table called Transactions).

T_TradingMonths:

LOAD Month Start,

  Month End,

  Trading Month

From TradingMonths.xlsx (ooxml etc...);

Left Join (Transactions)

IntervalMatch([Posted Date])

LOAD

  Month Start,

  Month End

Resident T_TradingMonths;

Left Join (Transactions)

LOAD * Resident T_TradingMonths;

Drop Table T_TradingMonths;

The last load brings the field into the fact table. If you want to get it into the calendar table instead, change Transactions to the name of your calendar table and [Posted Date] to the calendar data field.

HTH

Jonathan

[marcus_sommer]

There are several possibilities:

1. Using Intervalmatch

2. Create in (a additionally) (link) table for each day the trade month or then per mapping

3. Create within the transaction table the trade month per algorithm

Which is the best? It will depend from your data model, but the order here is rather a recommendation.

- Marcus

[gerhardl]

Thanks. This is not 100% what I had in mind, but it seems to work okay.

On a related note:

How can I determine the last Saturday in any given month?

[marcus_sommer]

Try this:

= date(monthend(date) - (if(num(weekday(monthend(date))) = 5, - 2, num(weekday(monthend(date)))) + 1), 'DD.MM.YYYY')

- Marcus

[gerhardl]

Hi Marcus,

I now have this in my load script, but it returns null:

date(monthend([Posted Date]) - (if(num(weekday(monthend([Posted Date]))) = 5, - 2, num(weekday(monthend([Posted Date])))) + 1), 'DD-MM-YYYY') as [Posted Date Month-End],

[gerhardl]

Okay so I resolved this by doing something different. Instead of having an excel file showing the Month Start and Month End, I created an excel file with 2 fields - Posted Date and Trading Calendar Month-End.

I then auto-filled each date for the last and next 3 years in Column A, and in Column B I calculated that particular day's Trading Calendar Month-End (last Sat of the month) using this formula:

=IF(A2>(DATE(YEAR(A2),MONTH(A2)+1,1))-WEEKDAY(DATE(YEAR(A2),MONTH(A2)+1,1)),(DATE(YEAR(A2),MONTH(A2)+2,1))-WEEKDAY(DATE(YEAR(A2),MONTH(A2)+2,1)),(DATE(YEAR(A2),MONTH(A2)+1,1))-WEEKDAY(DATE(YEAR(A2),MONTH(A2)+1,1)))

I then do a Mapping Load using that table. This is faster than the interval match, and I think can more easily be used in the load script.

[marcus_sommer]

Hi Gerhard,

for me it had worked. I assume that your [Posted Date] won't recognized as date and must be converted per date#() within the expression. The solutions with mapping is definitely the fastest way.

- Marcus