7 Replies Latest reply on Jan 29, 2019 6:35 PM by Zhouyi Zhang

    calculate return and volatility using time series dataset

    He Ye

      hello all,

       

      I am new to Tableau and am trying to create a dashboard to display stock portfolio return and volatility data. I have browsed through many Tableau learning materials but haven't found a solution.

      Would appreciate if the community could provide some guidance. Attached is the CSV export for the data I have in my SQL database. Below is how I hope the table looks like and the features I hope it contains:

       

      Capture.PNG

       

      1. Allow user to set an as-of-date so we can view the portfolio information as of that date.     (this is already done via creating an 'as of date' parameter and reference it in my filter on "business_date" column)

       

      2. The mtm_value column displays each book_id's mtm_value as of 'as of date' user picked    (in case a book_id's mtm_value is NULL on the selected date, display the latest non-null mtm_value before as of date).  I managed to show mtm_value column so far, I don't know how to let Tableau go back to earlier mtm_value if the mtm_value on selected date is null.

       

      3. Once User selects the as of date, 1 year and 3 years return columns are calculated as the percentage difference of mtm_value between as-of-date and as-of-date minus 1 year (3 years) for that book_id respectively.  

      I hoped to use this formula to create a calculated field to extract out mtm_value 1 year ago so I could create another field to calculated the percentage difference.

      { FIXED [book_id], [business_date]=[As of date] - 365: MIN([mtm_value])} but it is not working, the result doesn't return mtm_value one year ago, it returns the mininum mtm_value for each book across all time period. [As of date] is the parameter I created for first point.

       

      4. The volatility column is probably the most challenging part which I have no clue at all yet. The math steps I want to use to calculate 3 year volatility is the following:

           step1: get the mtm_value time series for each book_id for the period [As of date] minus 3 years  to [As of date].

           step2: using the mtm_value time series from step 1 to calculate the daily percentage change of mtm_value for the 3 year period for each book_id.

           step3: Calculate the sample standard deviation of the daily percentage change time series from step 2.

           step4: multiply step 3's output with an annualizing factor of sqrt(252).

       

      Thanks everyone in advance.