Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
stock returns/average/formatting
Hi everyone. thank you for looking over my problems. I am trying to have
macros calculate monthly stock returns and average the stock prices as well. I can't get a macro that will calculate the return based off of the p0, p1 stock equation. p0 = prior price, p1 = current price. My equation calculates the returns but once the equation gets to a cell that has nothing yet in it, I either get # value or it will return what cell b25 has in it. Also, the sub procedure is set up to accomodate many tasks simultaneously. The procedure adds a % to cells that do not have anything in them yet. How can I get an equation that will make cells blank, not zero. Is it possible to set the procedures to a range since these stocks, dates and returns will grow pending person's objective. I am trying to have a range that can loop to determine stock returns, which cells get the percent sign, and leaving those cells blank should the prior stock price, p0, not equal a value. Function stockreturns(p1 As Variant, p0 As Variant) As Double Dim cellval As Double stockreturns = Log(p1 / p0) End Function 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%" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Next col Next row End Sub much appreciated! first time poster, long time reader. chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
stock returns/average/formatting
You can use .clearcontents to remove data from a cell.
example cells.clearcontenets. checkingg for empty cells use isempty(cells(1,"A")) or the inverse not isempty(cells(1,"A")) "ctallen23" wrote: Hi everyone. thank you for looking over my problems. I am trying to have macros calculate monthly stock returns and average the stock prices as well. I can't get a macro that will calculate the return based off of the p0, p1 stock equation. p0 = prior price, p1 = current price. My equation calculates the returns but once the equation gets to a cell that has nothing yet in it, I either get # value or it will return what cell b25 has in it. Also, the sub procedure is set up to accomodate many tasks simultaneously. The procedure adds a % to cells that do not have anything in them yet. How can I get an equation that will make cells blank, not zero. Is it possible to set the procedures to a range since these stocks, dates and returns will grow pending person's objective. I am trying to have a range that can loop to determine stock returns, which cells get the percent sign, and leaving those cells blank should the prior stock price, p0, not equal a value. Function stockreturns(p1 As Variant, p0 As Variant) As Double Dim cellval As Double stockreturns = Log(p1 / p0) End Function 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%" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Next col Next row End Sub much appreciated! first time poster, long time reader. chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting a 4 Series Stock Chart | Charts and Charting in Excel | |||
Formatting STOCK charts | Charts and Charting in Excel | |||
3 data stock graph with average | Charts and Charting in Excel | |||
calculate weighted average of stock cost | Excel Worksheet Functions | |||
AVERAGE function returns #DIV/0! error | Excel Discussion (Misc queries) |