variable cell referencing for simple data points

[Guest]

I have reached my limited capacity in excel, but am looking to do a variable
referencing based on historical data. The data is simply the date and
equity(simple number). I am trying to calculate both in dollar terms and
time the amount from equity peak to low before going to a new equity peak. I
am trying to search historical data for the greatest drawdown in equity terms
and in time terms before equity goes to new highs. It appears to have to
start with first drawdown and look for forward first for new equity highs,
and then for drawdowns greater than previously idenitified.

10/1/2004 $99,978 start equity date
10/4/2004 $99,515
10/5/2004 $99,453
10/6/2004 $99,565
10/7/2004 $99,415
10/8/2004 $99,799
10/11/2004 $99,374
10/12/2004 $98,907
10/13/2004 $101,012
10/14/2004 $102,090
10/15/2004 $100,899
10/18/2004 $99,483
10/19/2004 $100,966
10/20/2004 $101,023
10/21/2004 $100,677
10/22/2004 $99,841
10/25/2004 $99,442
10/26/2004 $99,757
10/27/2004 $102,637
10/28/2004 $101,809
10/29/2004 $101,871
11/1/2004 $103,115
11/2/2004 $102,706
11/3/2004 $102,479
11/4/2004 $103,535
11/5/2004 $104,707
11/8/2004 $104,001
11/9/2004 $103,731
11/10/2004 $105,221 equtiy peak
11/11/2004 $104,251
11/12/2004 $103,493
11/15/2004 $102,860
11/16/2004 $102,731
11/17/2004 $102,357
11/18/2004 $102,542
11/19/2004 $101,214
11/22/2004 $99,638
11/23/2004 $99,590
11/24/2004 $99,277 (equity low before hitting new equity high on 12/6/04)
11/26/2004 $100,066 (drawdown of $5944 from 11/10 to 11/26)
11/29/2004 $101,576
11/30/2004 $102,195
12/1/2004 $105,199
12/2/2004 $106,869
12/3/2004 $105,211
12/6/2004 $105,856
12/7/2004 $105,325
12/8/2004 $106,592
12/9/2004 $105,947
12/10/2004 $106,274
12/13/2004 $106,643
12/14/2004 $108,199
12/15/2004 $110,199
12/16/2004 $109,858
12/17/2004 $110,152
12/20/2004 $109,414
12/21/2004 $109,834
12/22/2004 $111,097
12/23/2004 $111,372
12/27/2004 $112,974 new equity high greater than 11/10/04
12/28/2004 $111,851
12/29/2004 $111,626
12/30/2004 $110,233
12/31/2004 $111,166
1/3/2005 $108,272
1/4/2005 $111,275
1/5/2005 $110,929
1/6/2005 $110,605
1/7/2005 $108,853
1/10/2005 $107,282
1/11/2005 $108,189
1/12/2005 $107,950
1/13/2005 $108,159
1/14/2005 $108,348
1/18/2005 $109,914
1/19/2005 $109,153
1/20/2005 $109,529
1/21/2005 $110,368
1/24/2005 $110,583
1/25/2005 $109,976
1/26/2005 $108,508
1/27/2005 $107,742
1/28/2005 $106,765 equity low before hitting new equity high on 2/22/05
1/31/2005 $107,867 (drawdown of $6209 greater than previous drawdown of
$5944 in 11/2004)
2/1/2005 $107,542
2/2/2005 $108,349
2/3/2005 $107,677
2/4/2005 $108,251
2/7/2005 $108,936
2/8/2005 $109,321
2/9/2005 $107,787
2/10/2005 $108,598
2/11/2005 $108,689
2/14/2005 $108,114
2/15/2005 $108,846
2/16/2005 $109,219
2/17/2005 $108,679
2/18/2005 $109,068
2/22/2005 $113,001 new equity high greater than 12/27/04
2/23/2005 $110,648

I would truly appreciate any input for this variable referencing issue????

pno1

1/1/

 

[Bernie Deitrick]

pno1,

Please explain why the in-line commented values are not peaks and lows.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,

thanks for your reply. You are correct in that they are peaks and lows. I
am trying to find the largest peak to valley in the data set in terms of
dollars and also in number of days.

Between 11/10 and 11/24, we experienced a drawdown of $5944. This was teh
lowest point in $ terms before a new equity high greater than the first high
of $105221 on 11/10. The new high of $106869 on 12/6 completes the drawdown
of the first cycle. It is this dollar amount and also time in days that I am
going forward to determine if there are any periods where we incur a bigger $
drop in equity or in number days between old high and new equity high. When
looking at the data, assume it is a new number everyday, hence I have the
privelege of looking at a data set today but I want to keep analyzing the
data as it is collocted.

So, I know the biggest drawdown as of 11/24 is $5944. Then on 12/6, I
exceeded the 11/10 equity peak. so it would be a new high if you displayed
the data in a chart. I am now looking for any setback from the new high on
12/6 or any later date that is greater than $5944. I continued to make new
equity highs until 12/27 when I reached $112974. I then can manually
calculate a new drawdown between 12/27 and 1/28 of $6209. I cannot confirm
that this is a new max drawdown until we exceed the 12/27 equity of $112974.
On 2/22, we reach a new equity high of $113001 which then confirms that the
first drawdown in November of $5944 has been exceeded.

If you are familiar with a bar chart or a simple graph, if would simply be
the largest peak to vally $ amount that I am trying to measure and assuming
that over time it (the graph/chart) continues to make new equity highs, or an
uptrending graph.

I hope this can explain what I am trying to do. I am looking for the
biggest drawdown, whether it is over 1 month, 1 year, or 10 years.

thanks for your help once again.

dave "pno1"

 

[Guest]

Bernie,

I figured out how to get max $ amt, but not the number of days. I did a
formula where I took (todays equity-max(fixed start date equity:todays
equity)) I then did a min of this data column to get the worst drawdown. It
appears to work, and in the end seemed simple, but just have to get in the
right mind set for programming I guess. Still and trying to figure out way
to count # of days.

Thanks for replying to me. Will definitely use this site more often.

 

[Bernie Deitrick]

Dave,

You still need to have a better criteria than that. The largest peak to
valley is simply MAX(dataset)-MIN(dataset), but you seem to want to do more
than than, finding transients. Perhaps you could define what a major peak
or major valley is: some percent greater than or less than previous or
future values (within some timeframe), so that it can be expressed
mathematically.

HTH,
Bernie
MS Excel MVP