15 Replies Latest reply: May 15, 2014 10:05 AM by adonis _ RSS

    Dates and grouping

    adonis _

      Hi All

       

      I have 2 questions

       

      1) I want to create a process where i can calculate the date going back 3 business dates without including the weekends.

      IE: Monday thru Friday.

      The process would do the following

      I need a formula that would create T-1, T-2, & T-3, where T = Trade Date.

      However if you do that, with weekends in play that would include Saturday & Sunday which I want to exclude.

       

      2) I need a formula that will allow me to Link mixed Buy and Sell transactions for a given Cusip together when there are different BUSINESS UNITs.  Normally, what would happen is i have a filter that takes all Business Units together and parse them out to different people.  However due to errors in assigning Business Units, I need to be able to group all like CUSIP Transactions together.  Any help here would be appreciated.

        • Dates and grouping
          Olly Bond

          Hello adonis,

           

          There's a defined function in v10 called WorkingDays which requires a little setup (tell Monarch what day the week starts on, define public holidays etc) but then will do what you need out of the box.

           

          As for the second part of your request, if you could post a sample between and tags I'd be happy to have a look.

           

          Best wishes,

           

          Olly

            • Dates and grouping
              adonis _

              Olly

              Can you give me an assist on what an example of the following would be.

               

              Working_Days(startdate,enddate)

               

              would i use the value of 2-6 for Monday thru Friday?

              or do i type Monday, Friday inside that?

                • Dates and grouping
                  Nick Osdale-Popa

                  To get 3 working days prior to a given date, use this formula:

                  If(Weekday(-3,2)>=5,[Your Date Field]-5,[Your Date Field]-3)[/code]

                   

                  For the function Weekday(date[,startday]) where startday defines when the week starts (1=Sun, 2=Mon). We specify Mon (2) so that Sat and Sun become 6 & 7 respectively.

                   

                  If we subtract 3 days from a given date and WeekDay returns 5 (Fri) or greater, we know we are "subtracting" over a weekend and thus must subtract 5 days to make up for that weekend, otherwise we just subtract the original 3 days from the given date.

                   

                  Hope that helps!

                   

                  Alternatively, you could write the formula as:

                  [Your Date Field]-If(Weekday(-3,2)>=5,5,3)[/code]

                  Same principle, just a little shorter.

              • Dates and grouping
                adonis _

                I will try it out, much appreciated Nick, have a great day.

                  • Dates and grouping
                    adonis _

                    Hi Nick and others

                     

                    My problem is that if i used the formula, it identifies the Date parameter, but i need to create a process to extract them in 3 parts.

                     

                    IE:  I have 300 records, Based on the T1, T2, T3 that would let us say break it down as T1 (50 Records), T2 (100 Records) and T3 (150 Records)

                     

                    I need to create an extraction which works by taking my Trade Date and where the parameter is T+1 then only extract those records.

                    SO if trades are ie: 8/24, 8/23. 8/23, etc the trades show up in the groupings below based on using today's 8/25 date

                    The formulas would kind of do the following:

                    If(Weekday(-3,2)>=5,[Trade Date]-5,[Trade Date]-3)

                    If(Weekday(-2,2)>=5,[Trade Date]-5,[Trade Date]-2)

                    If(Weekday(-1,2)>=5,[Trade Date]-5,[Trade Date]-1)

                     

                    so in theory Tab 1 would take all T1 trades, Tab 2 would have T2 trades while Tab 3 would have T+3and greater

                     

                    If I use those 3 as fields, it will only calculate the date in each field, and the extraction would not work, how do I extract based on meeting exact criteria where Trade Date is T1, vs displaying the date in a field in addition to the T2, T+3?

                    please help.

                    Thanks

                    Adonis

                  • Dates and grouping
                    adonis _

                    can anyone please help, this is frustrating.  I need to be able to extract data based on the T1, T2, T3 criteria based on the trade date that is in place.

                    The T1, T2, T3 dates show the date at hand, but now I need to age them.

                    Example as follows:

                     

                    The formula doesn't help if i cant pull the trades.

                     

                    In theory the Trade Date = yesterday (8/25/09) is the T+1,

                    while trade Date = 8/24/09 is T+2,

                    and anything from friday 8/21/09 and backwards would fall in T+3 category.

                    Please let me know how I can extract the data.

                    Not sure the formula above helps.

                      • Dates and grouping
                        Olly Bond

                        Hello Adonis,

                         

                        Without sample data, it's so much harder to help.

                         

                        But if you try using the Today() function, you'll find that you can get intervals by subtracting dates from one another in Monarch.

                         

                        Best wishes,

                         

                        Olly

                          • Dates and grouping
                            adonis _

                            The problem i have with date is as mentioned, i need to take out the Weekend out of the equation.

                            Would you have a sample process that will enable me to calculate the following:  (anything greater then 3 would be grouped up as >=T+3)

                             

                            Trade Date

                            8/25/2009   (in theory this would be T+1 from today())

                            8/24/2009   (T+2 from Today())

                            8/21/2009   (T+3 from Today())

                            8/20/2009   (T+3 from Today())

                            8/17/2009   (T+3 from Today())

                            8/19/2009   (T+3 from Today())

                            8/18/2009   (T+3 from Today())

                            8/10/2009   (T+3 from Today())

                             

                             

                            do you have a sample formula that would take into account the dates below and link them up to today's date excluding weekends?

                            In theory it would subtract today's date -1, -2, -3 (excluding weekends) and if it matches the below date then it can be pulled?

                            If(Weekday(-3,2)>=5,[Trade Date]-5,[Trade Date]-3)

                             

                            If(Weekday(-2,2)>=5,[Trade Date]-5,[Trade Date]-2)

                             

                            If(Weekday(-1,2)>=5,[Trade Date]-5,[Trade Date]-1)

                             

                             

                            is it also possible to add a parameter for excluding holidays?

                              • Dates and grouping
                                Olly Bond

                                Hello Adonis

                                 

                                Your profile suggests you have v10. This includes user defined functions. If you are in the table view, and go to the Data menu, select the User Defined Functions option, then click on import from the dialog box, select the default external model, then select the following functions:

                                 

                                WorkingDays

                                 

                                Click OK. You'll see that it imports all the required additional functions:

                                 

                                WorkingDaysComputeDaysOffInRange (and about 10 others)

                                 

                                (comment)... This is essentially a list of holidays that must be edited before starting to use the Working_Days user defined function.

                                 

                                Here are the other user defined functions that also need to be edited before using Working_Days:

                                 

                                WorkingDaysFirstValidDateForWorkingDays

                                WorkingDaysIsWorkingDay

                                WorkingDaysLastValidDateForWorkingDays

                                 

                                WorkingDaysComputeDaysOffInRange(startdate,enddate) Returns the number of days off in the range of dates from startdate to enddate. Days off are defined as days that would ordinarily be working days, but are not worked because they are holidays or vacation days. This must be edited before using the Working_Days user defined function.

                                 

                                (/comment)

                                 

                                It's really just a matter of working through. Once you know the number n of working days between Today() and TradeDay, then for a given trade day you can return T1, T2, T+3... with a simple lookup field or even a nested if:

                                 

                                if(WorkingDays(TradeDate;Today())>=3;"T+3";

                                if(WorkingDays(TradeDate;Today())=2;"T+2";

                                if(WorkingDays(TradeDate;Today())>=1;"T+1";"Error")))

                                 

                                HTH,

                                 

                                Olly

                                  • Dates and grouping
                                    adonis _

                                    Hi Olly

                                    should I click on weak or strong connection?

                                     

                                    should it be weak strong or none?

                                     

                                    Grant do you have a sample?

                                      • Dates and grouping
                                        Olly Bond

                                        Hello Adonis,

                                         

                                        Strong links mean that Monarch will only keep a pointer to the source functions in your model. The functions will not be in your model. Every time you run the model, Monarch will need to have access to the source UserDefinedFunctions.xmod model in the right position, and any changes made in this model will affect yours.

                                         

                                        Weak links will mean that the functions are copied in to your model, but that Monarch will alert you if there are newer versions of the functions in the source model.

                                         

                                        No link means that the functions are copied over and the link is lost. This ensures that your model is portable but also, if you are planning on building lots of models where you define your Working Days parameters, could lead to you repeating work.

                                         

                                        As a first attempt, I'd try "none" and get on with the job, failing that, try "weak".

                                         

                                        Best wishes,

                                         

                                        Olly

                                          • Dates and grouping
                                            adonis _

                                            I was wondering rather then building some intense complicated process.

                                            What I was thinking is I could perform the following functionality by changing the process using today's date to identify the previous trade dates.  Then what I can do is create a process to say if Trade Date = T1 then show those, then if Trade Date = T2 then show only those, and Trade Date >= T+3 to show those trades.  Using below 3 would create the Group by that Grant mentioned, but now I need to be able to extract those parameters.

                                            I thought a Filter was the way to go but it appears that somehow records don't match vs. what the filter I had in place, 197 to 195 records (2 missing)

                                             

                                            This is what I was thinking:

                                            That would = T+3

                                            If(Weekday(today()-3,2)>=5,today()-5,today()-3)

                                            However this does not include the items > 3 days,

                                            how can I adjust this formula to be >=3?

                                             

                                            This would = T+2

                                            If(Weekday(today()-2,2)>=5,today()-5,today()-2)

                                             

                                            This would = T+1

                                            If(Weekday(today()-1,2)>=5,today()-5,today()-1)

                                      • Dates and grouping
                                        Grant Perkins

                                        If you use the formula to establish whether the record it T1, T2 or T+3 you can also use it to set a flag to identify the record by those categories.

                                         

                                        This would give you a grouping flag. You really only need T1 and T2 (based on your description) since anything else would be T+3 (assuming there would not be any transactions at a weekend).

                                         

                                        Grant