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

# Calculating Stock Index Returns

### Title

Calculating Stock Index Returns

### Post

Hello,

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?

Scotty

• ###### 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.