# stock formula problem

#### excelent

##### Board Regular
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 sellvalue As Variant
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
' Value in Column F
sellvalue = FirstCell.Cells(RowNo, 6)
' Column i
' Column j
Set sellCell = FirstCell.Cells(RowNo, 10)
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)
Set numshares = FirstCell.Cells(RowNo, 12)
numshares.Value = Round((startcap / buyValue), 4)
'Set remainder = FirstCell.Cells(RowNo, 13)
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
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

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Replies
1
Views
3K
Replies
1
Views
1K
Replies
2
Views
462
Replies
0
Views
563
Replies
5
Views
767

1,203,610
Messages
6,056,296
Members
444,855
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back