3 Replies Latest reply on Nov 12, 2019 5:43 PM by swaroop.gantela

    Calculate YTD and MTD returns from price series

    Joao Turolla

      Hi All,

       

      I hope this message finds you well.

       

      My question should be simple to answer but I've already spent some time trying to do it and yet didn't manage to figure this one out.

       

      I have a database basically organized like this:

       

      DateAssetPrice
      01/01/2018FOO1256.234331
      01/01/2018BAR1156.22145

       

       

      And what I need is to calculate the YTD, MTD and Day-to-date returns for that time series, creating an output like this one:

       

      Asset1dMTDYTD
      FOO0.33%2.44%157.33%
      BAR0.15%1.23%87.58%

       

      It's worth noticing that this returns should be calculated by doing (Price Today - (Price in the Last day From Previous Period [day, month, year])/(Price in the Last day From Previous Period [day, month, year]).

       

      For example, if I am talking about year-to-date today (2019-11-08) I should do price today - price in 2018-12-31/ price in 2018-12-31.

       

      Also, I do not want to plot dates in my views. Just the value for 1d, MTD and YTD from TODAY, as shown in the second table.

       

      Can anyone help me with this one?

       

      Thank you, very much.

        • 1. Re: Calculate YTD and MTD returns from price series
          swaroop.gantela

          Joao,

           

          I think it should be doable with DATETRUNC calculations.

           

          I made some assumptions:

          -you have a price for everyday?

          -you are looking to see the difference from today's price using the TODAY() function?

           

          If so, then today's price is:

          IF [Date]=TODAY() THEN [Price] END

           

          And the price at the last day of the previous year is:

          IF [Date] = DATEADD( 'day', -1, DATETRUNC( 'year', TODAY() ) ) THEN [Price] END

           

          Cannot subtract these two because they are on different rows, so need to fix across the whole asset.

          You can use a Level of Detail calculation (Overview: Level of Detail Expressions - Tableau )

          as for example today's price:

          { FIXED [Asset] : MAX( IF [Date]=TODAY() THEN [Price] END ) }

           

          Likewise fix the last year price.

           

          Then the YTD will be just the ratio that you described.

           

          Similarly for MTD and 1D.

           

          Please see workbook v10.3 attached in the Forum Thread.

          322448ytd.png

          1 of 1 people found this helpful
          • 2. Re: Calculate YTD and MTD returns from price series
            Joao Turolla

            Hi, Swaroop.

             

            That is a great solution. Still, I have a few questions about it.

             

            Is it really necessary to create an entire column with the same numbers? That sounds like a canon shot to kill an ant.

             

            And the only catch here is that your first assumption is not true, (about having a price for each day) as I only have prices for workdays, how can I edit the formula to not get the last day - 1 but the last day - 1 if not null?

             

            Edit: If it helps, I am attaching the database. Note that the US Asset data series is complete while the other assets are just defined in workdays.

             

            Thank you!

            • 3. Re: Calculate YTD and MTD returns from price series
              swaroop.gantela

              Joao,

               

              Sorry for not clarifying, the extra column was only for demonstration purposes.

              The Level of Detail calculation allows for just the final result as in sheet "summary table" of the attached.

               

              With regards to the missing days of data, I think that can be handled programmatically.

               

              Today is 11/12/2019.

              So the Target Date for last year is

              DATE(DATEADD('day',-1,DATETRUNC('year',TODAY())))

              which will return 12/31/2018

               

              In the fake dataset attached (sorry I didn't open your dataset yet),

              the only dates with price data are 11/27/2018, 11/28/2018, and 1/2/2019.

              So I am assuming that you are wanting the 11/28/2018 price.

               

              That is done by first finding the difference between all dates and the target date:

              DATEDIFF('day',[Date],[Target Date])

               

              Then finding the maximum date with a non-negative difference:

              {MAX(IF [Difference from Target Date]>=0 THEN [Date] END)}

               

              Then to get the Last Year Price it is:

              IF [Date]={MAX(IF [Difference from Target Date]>=0 THEN [Date] END)}

              THEN [Price]

              END

               

              Those calculations are laid on the sheet called "date difference" of the attached.