Stock Market data issue
 

Hi guys:

I currently have 18 years of (almost) daily stock market return data
from 1963-1981. My goal is to find the mean daily return for both the
first nine days of the month (the "first day" of each month is counted
as the last day of the previous month plus the first eight days of the
current month) and the last nine days of each month (ending on the
actual last day of the month). The problem I am having is that the
data is not complete (some months have 10 days of data, some months of
20), therefore I cannot use a simple counting or 'If...then' statement
to separate the "first nine days" and the "last nine days" from the
rest of the data. Once I have this information it would be easy to
calculate the mean return values. I currently have four columns: one
for year (e.g. "1964"), one for the month, (e.g. "1", "2"..."12"), one
for the date, (e.g. "1", "2", "3"..."31"), and one for the returns.

I've already consulted with some 'experts' on campus but nobody seems
to be able to help. Does anyone have any suggestions? This is driving
me nuts.

Thanks in advance!

Stock Market data issue
 

Formulas assume dates are in A1:A15, returns in B1:B15.

For an arithmetic mean,

=SUMPRODUCT(B1:B15*((DAY(A1:A15+1)=1)+(DAY(A1:A15) <9)))/SUMPRODUCT((DAY(A1:A15+1)=1)+(DAY(A1:A15)<9))

However, since yields are relative to the previous days, it might make
sense to compute the geometric average, in which case you should use
the following *array* formula:

=PRODUCT((1+B1:B15)+(IF((DAY(A1:A15+1)=1)+(DAY(A1: A15)<9);0;-B1:B15)))^(1/SUM(IF((DAY(A1:A15+1)=1)+(DAY(A1:A15)<9);1;0)))-1

An array formula must be entered with Shift+Ctrl+Enter.

In this case, geometric average is the nth root of P(1+values) for n
values, and then subtract 1. P(X) means the product of all elements of
X.

Does this help?

Kostis Vezerides

Stock Market data issue
 

Thank you for your reply Kostis, that wasn't actually the crux of what
I was looking for. I know how to calculate arithmetic and geometric
yield - the issue I faced was more one of formatting where I needed to
find a way 'count' from specific cells both forward and backward. It's
difficult to explain clearly over the internet so I digress from
confusing anyone (and myself) further.

I actually figured out a work-around using two nested If, Then
statements and then just averaged the two.

Thank you again for your assistance - people like you make this forum
great! :-)

Stock Market data issue
 

Thank you for your kind words. And to think that I made the mistake
often made by responders here, I read only the first half of your post.
In particular, I did not see that you had your data broken in 4
columns. Is this the issue? If so, my formula can be readjusted for
your case.

What you say in your reply is now confusing me. If you can elaborate
what you mean by 'count' from specific cells forward. Without being
sure, one function that 'counts' forward from a specific cell, say 5
cells, is

OFFSET(A1,0,0,5,1)

Here, if the specific cell is A1, the specific call to OFFSET returns a
range of 5 cells starting from A1. Sometimes, in some calculations, you
need to use

N(OFFSET(A1,0,0,5,1))

Does this help? If you want you can return with a more specific
explanation.

Kostis