# Excel 2007 : How to create scalable workbook to measure moving average lifetime on an Index

1. ## How to create scalable workbook to measure moving average lifetime on an Index

Dear Excel-forum,

I am currently writing a working paper where I am doing some time series analysis on S&P indexes' average stock lifetime, and are having some difficulties creating scalable and effiecient workbooks.
What I need is a workbook that includes the lifetime of all stocks listed before a series of given dates (monthly) in order to calculate moving average lifetime, turnover rate and actual number of delistings per period.

I have attached a list of listings/delistings for S&P100 with their respective dates and durations. SP100.xlsx

Any help would be greatly appreciated. Many thanks in advance!

/vonborge

2. ## Re: How to create scalable workbook to measure moving average lifetime on an Index

So, I'm not sure I'm on the right track. But try this:

B2 in Sheet1: =COUNTIF(Sheet!\$B\$2:\$B\$200;"<"&A2) and copy down
C2 in Sheet1: =AVERAGEIF(Sheet!\$B\$2:\$B\$200;"<"&A2;Sheet!\$F\$2:\$F\$200) and copy down

What do you mean by scalable? That you will be adding more data below the existing? If yes, then one method is to firstly changing the above formulas to:

B2 in Sheet1: =COUNTIF(Sheet!\$B\$2:\$B\$201;"<"&A2)
C2 in Sheet1: =AVERAGEIF(Sheet!\$B\$2:\$B\$200;"<"&A2;Sheet!\$F\$2:\$F\$201)

And then when receiving new data, insert this above row 201 in your workbook.

3. ## Re: How to create scalable workbook to measure moving average lifetime on an Index

Dear Søren,

Sorry for the late reply. Your answer is kind of correct, but not quite what I was looking for. However, I managed to extract different data that gave me better measure, so figured out how to do it
By scalable I mean that I have data sets with many more observations, so the procedure cannot be restricitve to just that model.

On the other hand, I am still looking for a way to count the monthly additions (i.e. number of stocks per month from the THRU variable) in the attached spread sheet above, if you have any suggestions for that it would be greatly appreciated! Many thanks in advance

Cheers,
Borge

4. ## Re: How to create scalable workbook to measure moving average lifetime on an Index

Perhaps you could do the following instead:

B2 in Sheet1: =COUNTIF(Sheet!B:B;"<"&A2) and copy down
C2 in Sheet1: =AVERAGEIF(Sheet!B:B;"<"&A2;Sheet!F:F) and copy down

5. ## Re: How to create scalable workbook to measure moving average lifetime on an Index

That formula does not give me number of additions per month per year..? All it produces is a decreasing number of additions over time, and the second formula produces what looks to me like a moving average? Sorry if I am not following here...
What I need is a way to count each addition per month per year, i.e. how many listings where made on the index in November 1989 (should be 1), December 1999 (should be 2) and so forth for all months in the dataset. Any ideas?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1