Minimum number of observations

[fboehlandt]

Hello everyone
I have a normalised table containing the following fields:

<Unique ID> <Fund Name> <Date> <Return>

The data are time series denoting the monthly performance of investment funds. Funds can have any number of observations (e.g. March 1997 to June 2005). In addition, some funds can have performance gaps.

I would like to extract those funds that have a continuous performance history of 120 consecutive months between a specific start and end date. As an exmaple, the start date is July 1990 and end date June 2010:

- Fund A: performance history July 1995 to June 2005 (= 120 consecutive observations) INCLUDE
- Fund B: performance history July 1995 to June 2007 (> 120 consecutive observations) INCLUDE
- Fund C: performance history July 1995 to June 2000 (< 120 consecutive observations) EXCLUDE
- Fund D: performance history July 1995 to and June 2000 August 2000 to June 2007 (> 120 observations but gaps) EXCLUDE
- Fund E: performance history July 1985 to June 1995 (= 120 consecutive observations but only 60 after start date) EXCLUDE

Can anyone provide any pointers as to how to build a query around this? Perhaps this needs implementation in VBA. Ideally, I would like to be able to select the start and end date dynamically and then run the query accordingly. Any help is greatly appreciated

Regards

 

[pr2-eugin]

Try this link.. http://www.baldyweb.com/OverLap.htm

 

[spikepl]

For each one fund, you could number the records belonging to it.

#1 is the first record that has the date equal to or more recent than the desired start date. The number is incremented by 1 for each subsequent record whose prior record is one month older. The number is reset to 1 for each subsequent record whose prior record is more than one month older.

The numbering stops when you reach 120 or end of observation period.