C
ctallen23 via OfficeKB.com
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
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