1 Reply Latest reply on Oct 29, 2012 9:11 AM by philmodjunk

    Calculating Stock Index Returns



      Calculating Stock Index Returns




           I have a simple database with monthly stock market index returns for a variety of indexes. I have a table with the indexes and a child table with the index id and the start date (8/31/2012) and the end date (9/30/12) and a return figure 2.58%.  I would like to calculate a geometric mean for different periods of time 3 months, 4 months, 1 year, 3 years, 5 years, 10 years, etc.

           I am a little lost how to go about doing this?


           Thanks for your help.


        • 1. Re: Calculating Stock Index Returns

               Your example shows a one month interval from 8/31 to 9/30.

               Are the time intervals always the same?

               And how do you define a month? 30 days? the time from the end of month 1 to the end of month 2?

               Relationships can be set up to match to different sets of records by Stock ID and time interval, then a summary field that computes the average and that is defined in your child table can be referenced from the context of your parent table in order to compute the arithmatic mean.

               To compute a geometric mean can't be done with a summary field, nor a built in aggregate function. To get the geometric mean would require an expression such as:

               Evaluate ( Substitute ( List ( ChildTable::Index ) ; ¶ ; " * " ) ) ^ ( 1/ Count ( ChildTable::Index ) )

               PS: I looked up the info on this type of mean from Wikipedia.