Hello Rob
If you could please explain more clearly the exact logic
you are trying to execute, I'd be happy to help..
Thank you!
So do I, but I just can't figure this one out.
I am a grad student. For my research, I need to obtain
the simulated distribution of the *Maximum retracement*
from the
maximum of a stochastic process. So, given a series of
numbers
[the first number is located in A1 and the last number is
located
in column A, row T], at every time t [every row t], we
need to calculate the
maximum of cells ($A$1 : A(T) {A(T) is my notation for the
cell in row t, column A}).
Suppose that at time t*, we observe a "Global Maximum" {in
cell A(t*)}.
This means that if t>t*, all values in the range A(1):A
(t* - 1)
AND the values in the range A(t* + 1):A(t) are smaller
than the value
in cell A(t*). Thus, on day t, the value in A(t*) gives
us the
maximum "up until now" [=day t].
Since we are interested in the retracement, we need to
find a Minimum that
takes place AFTER the day/row t*. Let X = max(A(1):A(t))
and let
N = min(A(t*):A(t)). On every day [on day t], we
calculate the quantity
X-N.
In my example below, t=14. This means that at time 14, we
look back and fill
in the appropriate values in column B:
Row A B Comments
1 1
2 5
3 7
4 10 <- 10=Current Global Maximum
C.G.Max]
5 9
6 6 4 <- 6=C.G. Minimum
7 9
8 17
9 21 <- 21=New C.G. Max, but columnB is
not equal to (21-6)! The minimum must occur After the
Current Global maximum.
10 14
11 16 <- I have just changed the value in A11!
12 10
13 9 12 <- 9=New Current local min
14 10
we need to leave cells B1:B4 blank, because on days 1-4,
we didn't
observe a minimum Following a maximum.
We need to leave cell B5 blank, because the value in
A5=A5=9
is not a local minimum [!!]. We need for this to be a
local minimum, because if you recall
we are interested in the "*Maximum retracement*" [please
see above].
Cell B6 gives us what we are looking for - the maximum
retracement
from the Global Maximum [10] to the local minimum that
follows it [6],
is 4.
In row 9, we obtain a new Global maximum. Thus, we need a
to find
a local minimum that occurs below row 9, before we can
write anything in
column B.
It is important that we don't write the value in cell B10.
This is because for Each Global Maximum, we must have only
One value for
the retracement [we are interested in the *Maximum
Retracement*].
Since t=14 [i.e. "today" is day 14], we know that the
maximum retracement
from the max that had occured on day 9, was 12, because on
day 13,
A13=9 < A10, and so A13 is the min of the range (A10:A14)
= min
(A(t* +1):A(t)).
*********
So, given T observations, we first find all local maximums
maximums, such that
on they day that they occur, they are the Global maximums
[i.e. if a Local maximum
occurs on day t, A(t) is a maximum of range A(1):A(t).
Suppose there are 3 of these maxes,
first max occurs on day t* and the second max occurs on
day t**, and the third
occurs on day t***.
[thus, it follows that A(t*)< A(t**)<A(t***)]
Then we find the local min during the time interval (t*,
t**), say it occurs
on day m*, t* < m* < t**
We record the quantity "=A(t*)-A(m*)" in cell B(m*).
Then we find the local min during the time interval (t**,
t***), which occurs
on day m**, t**< m* < t***
We record "A(t**)-A(m**)" in cell B(m**).
Lastly, we find the local min during the time interval
(t***, T)
that occurs on day m***, t*** < m*** < T
We record A(t***)-A(m***) in cell B(m***).
Of course, we need to be able to do this for any arbitrary
number of maximums
found in T simulated observations...
***********
I spent a lot of time with this problem, but my knowledge
of Excel functions
is just not sufficient to solve problems of this level of
complexity.
Thank you very much for considering this problem.
Regards
Stan
