4 Replies Latest reply on Feb 7, 2019 12:12 PM by Steven Holden

    Creating Yearly Returns from Monthly Stock Returns

    Steven Holden

      Hi all,

       

      Hoping you can help.  I have a bunch of stocks and their monthly returns that I have loaded in the Tableau.  In excel the way to calculate the cumulative returns between any time period is to find the product of the sum of (1+return)-1.  I have managed to recreate this in Tableau from the help forums using this brilliant formula:

       

      (EXP(RUNNING_SUM(LN(([Fund Return +1]))))-1)

       

      This works perfectly for creating time series of cumulative returns.  What I need to do is then to create measures based on these returns for discrete time periods, such as 2014 returns or 3-year returns.  I'm not great at Tableau formulas so this has stumped me.  I have attached a packaged workbook with how far I have got, plus an excel sheet to explain the calculations for yearly returns.

       

      Thanks to anyone who can help.

       

      Steve.

        • 1. Re: Creating Yearly Returns from Monthly Stock Returns
          Jim Dehner

          Hi

          If I understand you want to have a start date and then a period to review and the return values will begin on the start date

          see the attached there  are 2 examples - one between date and one 3 years from the start

          I added 2 parameters and then base filters on the parameters

          then the between date is simply

          the 3 year is

           

          place them on the viz set to true

           

           

           

          you can make the filters what ever you want or use a parameter to set the number of years ahead

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Creating Yearly Returns from Monthly Stock Returns
            Steven Holden

            Hi Jim,

             

            Thanks very much for taking the time to look into this, really appreciated.  However, this is not quite what I was looking for.  Instead of adjusting the cumulative time series chart for a date range, I was looking to create discrete measure calculations for each date range to create a chart such as this:

             

            and this:

             

             

            At the moment I am performing the calculations in excel as per my attachment and then pulling them into Tableau, but I'd rather skip that step and do it all in Tableau using the return data.

             

            Hope you can help.

             

            Steve.

            • 3. Re: Creating Yearly Returns from Monthly Stock Returns
              Jim Dehner

              sorry your post doesn't read that way

              I would have told you

              you are dealing with table calculations that are at the bottom of the order of operation which means that they are done after all the filters and all the other calculations (including LODS)

              each table calculation needs a table - to look at different time periods as you suggest would require a series of sheets - one for each time period that you would then bring together on a dashboard

              Each sheet has its own underlying table - you would hid most of the rows/columns in the viz but the underlying table would still be need to complete the viz

              Jim

              • 4. Re: Creating Yearly Returns from Monthly Stock Returns
                Steven Holden

                ok thanks Jim,

                 

                Surprised it's so difficult to replicate something that is so simple in excel.  I'm assuming I have to hide the data I don't want to see on each sheet which causes issues when the data is updated.  I think best to perform calculations in excel and link both data sources.

                 

                Steve.