Can I Make This Computation Shorter?

[Steve]

I have a column (A) of stock prices. In the next column I copy down the
formula:

=(A7/(MAX($A$5:A7))-1)^2
all the way to
=(A57/(MAX($A$5:A57))-1)^2

I then sum the new column (B) of values before further computations (you may
recognize this as "Drawdown squared" leading to the "Ulcer Index").

My question is: Can I miss out column B and go directly from the list of
stock prices to the summation of the values for drawdown squared? I tried to
make this into some sort of array, without success.

Steve

 

[Rick Rothstein \(MVP - VB\)]

I think this formula will give you the summation directly...

=SUMPRODUCT((A7:A20/(MAX($A$5:$A$6,A7:A20))-1)^2)

Rick

 

[Steve]

Rick,

Thanks for the reply, although this doesn't give the same answer as I get
going the long way.

Is this part of the expression (MAX($A$5:$A$6,A7:A20)) correct?

Steve

 

[T. Valko]

Is this part of the expression (MAX($A$5:$A$6,A7:A20)) correct?

It's the same as - MAX(A5:A20)

Based on how your original formula is written, this won't work.

=SUMPRODUCT((A7:A20/(MAX($A$5:$A$6,A7:A20))-1)^2)

Your original formula is evaluating a (potentially) different MAX value
every cell it's copied down.

=(A7/(MAX($A$5:A7))-1)^2

Whereas, the SUMPRODUCT formula is using the *same* max value throughout.

 

[Steve]

Biff,

You are correct, my original expression checks for the MAX value for all the
preceeding values as it moves down the column (thus increasing the range
each row).

So, is it not possible to replicate my result without the intermediate
column of data?


Steve

 

[Sandy Mann]

Steve,

Rick's formula returns the same result for me but only if the whole range is
filled in. I assume that you are copying your formula down as you enter
data, otherwise your formula returns the value 1 which will distort you
count.

If I may suggest a modification to Rick's excellent formula:

=SUMPRODUCT((A7:OFFSET(A7,COUNT(A7:A57)-1,)/(MAX($A$5:$A$6,A7:OFFSET(A7,COUNT(A7:A57),)-1))-1)^2)

works for me proviced that there are no gaps in nthe data

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Steve,

Perhaps I did not make it clear but with the Offset() modification the
formula then works the same as your formula and the sum as data is entered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

I have to thank you for the attempted rescue effort, but I believe you are
being fooled by the sample data you used in the same way I was. My formula
worked fine for the sample data I used initially and so I posted my formula.
However, if you move the maximum value in column A down the list (say in
A50), I think you will see your formula stops working correctly in the same
way mine did. Sorry, but I just tried it and that does seem to be the case.

Rick

 

[Sandy Mann]

Ah yes, I see what you mean. All the individual returns are correct until
the maximum value then they are wrong from then on. Now that has got me
puzzled. I look forward to your analysis of what is wrong and how to fix
it.

As a side minor point Do you really need A5 & A6 to be Alsolute references?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Steve]

I hope the table below makes the calculation clearer. This is the simplest
form but with the most columns.
Here are the column formulae:

Prices = stock price
Peak = IF(A3>B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2
Column D is then summed at the bottom

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120

Sum DD^2 1484


After this I realized I could go directly from the prices column to the
Ddown^2 column using the formula:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate sum
DD^2.

Steve

 

[Steve]

Try Again!

I hope the table below makes the calculation clearer. This is the simplest
form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3>B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3
54
148 166 -10.9
120

Sum DD^2
1484


After this I realized I could go directly from the prices column to the DD^2
column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate sum
DD^2.

Steve

 

[Sandy Mann]

As Biff intimated, MAX returns only one value. The best that I can come up
with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Steve]

Sandy,

I've never used a UDF before. However, I copied your code into a new module
in VBA. It appeared as it should when I hit the paste function button and
selected it.

However, the window which opens says: "This function takes no arguments".

If I try to insery a range of cells into =SquareIt() , then I get #VALUE!
returned.

I assume I'm doing something wrong?

Steve

 

[Sandy Mann]

Mmmmm.....

It works for me in XL97 without a range, calling it as =SquareIt(),
or with a range if I change the Function tittle to

Function SquareIt(R As Range)

and calling it as =SquareIT(A2:A1000) or even =SquareIT(A1)

even although I don't use the range in the code

Are you by any chance using XL2007?


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Steve]

Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as =SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your UDF?

Steve

 

[Sandy Mann]

The rage is defined in the code:

For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

goes down Column A, [ the 1 in the Cells(x,1) ], until it finds an empty
cell then it copies the value of the counting variable x into the variable n
to preserve it.

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

this again goes down Column A but now only until the Row number that it
found above.

Post back or send me an e-mail and I will send a sample workbook for you to
see.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Thnking about it some more, you don't need to check for the lasr data row:

Function SquareIt2()
Application.Volatile

Dim x As Long
Dim Temp As Double


For x = 3 To Rows.Count
If Cells(x, 1).Value = "" Then Exit For
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt2 = Temp

End Function

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

The rage is defined in the code:

For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

goes down Column A, [ the 1 in the Cells(x,1) ], until it finds an empty
cell then it copies the value of the counting variable x into the variable
n to preserve it.

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

this again goes down Column A but now only until the Row number that it
found above.

Post back or send me an e-mail and I will send a sample workbook for you
to see.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as
=SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your UDF?

Steve

 

[Steve]

Sandy,

It works!

The problem was that I didn't have a blank cell under the last price in
column A (I had the title of the summation cell...).

Many thanks for your help with this.

Steve

The rage is defined in the code:

For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

goes down Column A, [ the 1 in the Cells(x,1) ], until it finds an empty
cell then it copies the value of the counting variable x into the variable
n to preserve it.

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

this again goes down Column A but now only until the Row number that it
found above.

Post back or send me an e-mail and I will send a sample workbook for you
to see.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as
=SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your UDF?

Steve

 

[Sandy Mann]

Glad that you got it working. You can still leave the title in that cell,
just test for it in the cell. For instance if it did say
"Summation of Ddown^2"
(without the quotes) then change the UDF to

Function SquareIt2()
Application.Volatile

Dim x As Long
Dim Temp As Double

For x = 3 To Rows.Count
If Cells(x, 1).Value = "Summation of Ddown^2" Then Exit For
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt2 = Temp

End Function




--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy,

It works!

The problem was that I didn't have a blank cell under the last price in
column A (I had the title of the summation cell...).

Many thanks for your help with this.

Steve

The rage is defined in the code:

For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

goes down Column A, [ the 1 in the Cells(x,1) ], until it finds an empty
cell then it copies the value of the counting variable x into the
variable n to preserve it.

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

this again goes down Column A but now only until the Row number that it
found above.

Post back or send me an e-mail and I will send a sample workbook for you
to see.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as
=SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your UDF?

Steve

Mmmmm.....

It works for me in XL97 without a range, calling it as =SquareIt(),
or with a range if I change the Function tittle to

Function SquareIt(R As Range)

and calling it as =SquareIT(A2:A1000) or even =SquareIT(A1)

even although I don't use the range in the code

Are you by any chance using XL2007?


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Sandy,

I've never used a UDF before. However, I copied your code into a new
module in VBA. It appeared as it should when I hit the paste function
button and selected it.

However, the window which opens says: "This function takes no
arguments".

If I try to insery a range of cells into =SquareIt() , then I get
#VALUE! returned.

I assume I'm doing something wrong?

Steve






As Biff intimated, MAX returns only one value. The best that I can
come up with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Try Again!

I hope the table below makes the calculation clearer. This is the
simplest form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3>B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120


Sum DD^2 1484


After this I realized I could go directly from the prices column to
the DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate
sum DD^2.

Steve

 

[Steve]

Sandy,

At the risk of really pi$$ing you off, one further question.

You've solved the problem for a column of prices in column A. I can also
adjust your UDF for another column, say, column D.

But, what if I have 50 columns of prices (for 50 different stocks). I now
want to use your UDF at the head of each column to calculate =SquareIt2()
for that particular column.

It seems I need to replace 'cells(x,1)' with some relative referrence that
says "start 3 rows down in the same column" or some such.

Any thoughts?

Steve

Glad that you got it working. You can still leave the title in that cell,
just test for it in the cell. For instance if it did say
"Summation of Ddown^2"
(without the quotes) then change the UDF to

Function SquareIt2()
Application.Volatile

Dim x As Long
Dim Temp As Double

For x = 3 To Rows.Count
If Cells(x, 1).Value = "Summation of Ddown^2" Then Exit For
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt2 = Temp

End Function




--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy,

It works!

The problem was that I didn't have a blank cell under the last price in
column A (I had the title of the summation cell...).

Many thanks for your help with this.

Steve

The rage is defined in the code:

For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

goes down Column A, [ the 1 in the Cells(x,1) ], until it finds an empty
cell then it copies the value of the counting variable x into the
variable n to preserve it.

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

this again goes down Column A but now only until the Row number that it
found above.

Post back or send me an e-mail and I will send a sample workbook for you
to see.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as
=SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your UDF?

Steve

Mmmmm.....

It works for me in XL97 without a range, calling it as =SquareIt(),
or with a range if I change the Function tittle to

Function SquareIt(R As Range)

and calling it as =SquareIT(A2:A1000) or even =SquareIT(A1)

even although I don't use the range in the code

Are you by any chance using XL2007?


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Sandy,

I've never used a UDF before. However, I copied your code into a new
module in VBA. It appeared as it should when I hit the paste function
button and selected it.

However, the window which opens says: "This function takes no
arguments".

If I try to insery a range of cells into =SquareIt() , then I get
#VALUE! returned.

I assume I'm doing something wrong?

Steve






As Biff intimated, MAX returns only one value. The best that I can
come up with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Try Again!

I hope the table below makes the calculation clearer. This is the
simplest form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3>B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120


Sum DD^2 1484


After this I realized I could go directly from the prices column to
the DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate
sum DD^2.

Steve