4 Replies Latest reply: Sep 29, 2015 5:14 AM by Sam Jeorge RSS

    Price change as a percentage across the period

    Sam Jeorge

      Greetings DW Community!

       

      My question is regarding this -

       

      Example Workbook Name - How To Use Time Series Data Formats.exw

      Dashboard Name - Displaying Multiple Time Series

      Visualization Name - Multi Series Graph using Identical Time Slices displaying Price Change Across the Period- "The Line Graph at the Very Bottom".

      Here is the online link for the same -

      http://demos.datawatch.com/s1/workbook/#/How%20To%20Use%20Time%20Series%20Data%20Formats/Displaying%20Multiple%20Time%20Series

       

      As you can see, the above mentioned visualization is making use of data column named [ PERIOD CHANGE % ], for plotting the line graph.

       

      Now my question is - How do I calculate this column [ PERIOD CHANGE % ] in my SQL Server Database Table, that has got only these 3 Columns inside it -

      Symbol Name

      Date

      Close Price

      it need to have this forth column [ PERIOD CHANGE % ] - but I do not know how to calculate it.

       

      Can someone please tell me the Exact Formula that will create this forth column [ PERIOD CHANGE % ] inside my SQL database table. Or if it is difficult to do such calculation with SQL Server, then what formula should I use inside Datawatch CALCULATED COLUMNS to get this output ?

       

      Thanks a lot

        • Re: Price change as a percentage across the period
          Theo Klemming

          Hi Sam

           

          Assuming you have daily close prices, you can calculate the percent change in price from one day to the next by using the formula expression in a Calculated Column:

          DELTA_PCNT_NPREV("Close Price", 2)

           

          Note this:

          Make sure you have applied a Timeseries Transform, or the calculation expression will return an error.

          Make sure you enter the column name that has the Close Price within quote marks, although the software will insert it with square brackets when double clicked.

          Make sure you enter 2, not 1, to indicate the span of days you want to use in the calculation. If you enter 1, you will be comparing the close price of each day with itself, i.e. the close price from the same day. The step-count includs the "current" day, so today counts 1, and yesterday counts 2, so to speak. To therefore enter 2, not 1.

           

          best regards, Theo Klemming, Datawatch

            • Re: Price change as a percentage across the period
              Sam Jeorge

              Thank you so much Theo for your reply. But I have become a bit confused.

               

              Please consider this example, for better understanding of the practical scenario -

               

              Close Price of Today is 102

              Close Price of Yesterday was 100

              Change in Price is +2

              % Change in Price is 2 %

               

              Now if I test the formula suggested by you on the above data - DELTA_PCNT_NPREV("Close Price", 2) then that gives wrong output. But if I use 1 instead of 2, then that gives correct output of 2 %

               

              I also tested it using different method available in Datawatch, by making use of this Calculated Column Formula, which is basically doing the same calculation -

              ( [Close Price] - NPREV("Close Price",1)) / NPREV("Close Price",1)

               

              This formula also gives the correct output of 2 % change in value based on the data given in example mentioned above.

               

              I would request you to please confirm this observation that we need to use 1 instead of 2 in the above scenario of calculating percent change, so that there is no confusion in this regards.

               

              Secondly, regarding the Visualization Name - Multi Series Graph using Identical Time Slices displaying Price Change Across the Period- "The Line Graph at the Very Bottom" the link for which is given in my first post. In the Explanations it is mentioned that - "In the final graph we instead look a price change as a percentage across the period. This presents a common scale, allowing trends and correlations across time to be more easily identified".

               

              Are you 100 % sure that we just need to change the Price to Price Change % for getting that kind of visual output, as shown in that visualization ? I was thinking it involved some complex calculation in order to get the common scale, so that we can make correct relative comparisons between different symbols.

               

              Thanks a lot for your support.

               

              Best Regards

                • Re: Price change as a percentage across the period
                  Theo Klemming

                  Hi Sam

                   

                  This is strange. What release version number of Designer are you using?

                  On my system, with Designer 13.3, I get 0% change on each day if i use 1 in the formula. If I use 2, I get the correct percent change between any 2 time points (from one day to the next for example). It would be interesting to find out why we get different results.

                   

                  To achieve a graph where multple series show their day-to-day changes as a percent change, all you need is this calculated column.

                  Datawatch will automatically take care of keeping the values of each series apart, and calculate the percent change for series A based on only values for series A etc. The only condition is that you have the series ID column added to the Breakdown setting of the graph, since this is who you get separate lines/curves for each series.

                   

                  Best regards, Theo Klemming, Datawatch