Hello.

I'm having a tough time generating code for a simple program to average stock
prices for each stock symbol column and then given the range of dates with
prices, automatically calculate percentages for the stock price returns (p0 -
beginning stock price, p1 - ending stock price). I've listed some code below.
my Sub program calculates percentages and returns, but once I get to the
bottom of the row, i either get 0.000% across the columns or #value. the
rows below do not have stock prices yet. How can I get a program to
calculate prices (log p1/p0) and not return 0.000% or # value in the bottom
rows that do not contain stock prices given the dates? below gives you an
idea.

Prices AAPL ABT AMAT AMGN AMZN BDX
BDK BMY
9/1/2006 76.98 47.4 17.58 71.53 32.12 69.97 78.23
23.92
8/1/2006 67.85 47.54 16.76 68.03 30.83 68.8 72.
25 20.88
7/3/2006 67.96 46.63 15.58 69.71 26.89 65.07 69.18 23.01
6/4/2006 ----- no prices ----

returns = LN(9-1-2006 / 8-1-2006)
8/1/2006 -0.162% 1.933% 7.301% -2.439% 13.673% 5.574%
4.342% -9.714 %
7/3/2006 0.000% 0.000%, etc..... #value


Sub FillRange2()
Dim row As Integer
Dim col As Integer
Dim stockmax As Integer
stockmax = Range("b2")

For row = 0 To 11
For col = 0 To stockmax - 1

If Range("b25") < vbEmpty Then
Range("b25").Copy ActiveCell.Offset(row, col)

End If

Sheets("Sheet1").Range("b25").Offset(row, col).NumberFormat = "0.000%"
Next col
Next row

End Sub
I would need a stock price for 6/4/2006 to calculate the returns. How can I
get rid of these percetages or # value without having a lower cell without a
price... such as with 6/4/2006?

Any help would be appreciated. Is there a way for the program to determine
on its own the range? Currently I have this set to a 12X12 because errors
occur with anything greater than this.

Thanks. chris

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200707/1