stock formula problem

[excelent]

Hi members

I am trying to write a macro that will give me the results of buying and selling shares.
The formula below basically does the job, but i wanted to have the number of shares a a whole value as you cant purchase a percentage of a share.
i had tried to do this by using the INT function and could get the remainder after a buy, but when trying to add this back to the starting capital at the next buy i had strange results.

Another problem is that i can either use a dollar amount as commision for each trade(as in the formula below) or the same percentage (e.g. 0.5%)of my equity at the buy and at the sell.

I cant figure out how do this as the equity value will change from the buy to the sell, but using the same commision percentage

previously i had the dollar amount in 1 input box and the percentage in another, with a value of 0, then using an iif statement asking iif commisiondollar is > 0, blah blah or iif commisionpercent was >0 blah blah.
no luck as i had to literally try to repeat the whole code in other variables as to get a percentage
this got confusing as i dont yet understand what is happening in the loops so i recieved strange results in the test.

Can there be more than 1 input in an input box as this is part of a larger macro hich will probably have 4 or 5 input boxes?


Option Explicit
Sub profit()


Dim RowNo As Long
Dim FirstCell As Range
Dim commision As Variant
Dim commpercent As Variant
Dim buyValue As Variant
Dim sellvalue As Variant
Dim buyCell As Range
Dim sellCell As Range
Dim newstartcapvalue2 As Variant
Dim startcap As Variant
Dim totalprofit As Variant
Dim profitonly As Variant
Dim numshares As Variant
' Dim remainder As Variant




commision = Application.InputBox("Enter dollar value of commision:", _
, 0, , , , , 1)

Application.ScreenUpdating = False


Set FirstCell = Application.Range("a2")

' Range("M1").Select
' ActiveCell.FormulaR1C1 = " remainder..."
' Cells.EntireColumn.AutoFit
Range("L1").Select
ActiveCell.FormulaR1C1 = " num of shares...."
Cells.EntireColumn.AutoFit
Range("K1").Select
ActiveCell.FormulaR1C1 = " profit........."
Cells.EntireColumn.AutoFit
Range("J1").Select
ActiveCell.FormulaR1C1 = " totalprofit...."
Cells.EntireColumn.AutoFit
Range("I1").Select
ActiveCell.FormulaR1C1 = " startcapital...."
Cells.EntireColumn.AutoFit


startcap = 10000
RowNo = 1
Do
If IsEmpty(FirstCell.Cells(RowNo, 1)) Then Exit Do
' Value in Column E
buyValue = FirstCell.Cells(RowNo, 5)
' Value in Column F
sellvalue = FirstCell.Cells(RowNo, 6)
' Column i
Set buyCell = FirstCell.Cells(RowNo, 9)
' Column j
Set sellCell = FirstCell.Cells(RowNo, 10)
buyCell.Value = startcap - commision
sellCell.Value = Round((((startcap - commision) / buyValue)) * sellvalue - commision, 4)
startcap = sellCell.Value
RowNo = RowNo + 1
Loop


Set FirstCell = Range("A2")
newstartcapvalue2 = 10000
RowNo = 1
Do
If IsEmpty(FirstCell.Cells(RowNo, 1)) Then Exit Do
startcap = FirstCell.Cells(RowNo, 9)
totalprofit = FirstCell.Cells(RowNo, 10)
Set profitonly = FirstCell.Cells(RowNo, 11)
profitonly.Value = Round((totalprofit - startcap - commision), 4)
RowNo = RowNo + 1

Loop

Set FirstCell = Range("A2")
RowNo = 1

Do
If IsEmpty(FirstCell.Cells(RowNo, 1)) Then Exit Do
startcap = FirstCell.Cells(RowNo, 9)
buyValue = FirstCell.Cells(RowNo, 5)
Set numshares = FirstCell.Cells(RowNo, 12)
numshares.Value = Round((startcap / buyValue), 4)
'Set remainder = FirstCell.Cells(RowNo, 13)
'remainder.Value = Round((startcap / buyValue) - Int((startcap / buyValue)), 4)
RowNo = RowNo + 1
Loop

Application.ScreenUpdating = True

End Sub

The data used for this macro is in column e and column f and the first row is the header.

example data.


columnE columnF
buyprice sellprice
10.40 10.55
11.04 10.95
10.80 10.90
10.60 10.40
10.70 10.30


Thanks mike.
This message was edited by excelent on 2002-10-07 08:54
This message was edited by excelent on 2002-10-07 08:56