profit/loss calculator

[franky]

Hi guys.

I am struggling with a problem and would appreciate any help.
Basically I want to make a system that can keep track of profit loss
on stock/bond transactions.I have made a userform where I input values
and choose whether a transaction is BUY or SELL. From here I want my
model to:

1. scan column A to see if a position in the respective paper already
exists by matching a value from the userform.

2. If a position exists a new "average price" should be calculated.
Say I already have a position in 1 bond which I purchased for 100. Now
I buy another bond at 110. The new average price should be 105 (100/2
+ 110/2) or (Average price/Total amount + new price/Total amount)

This value should then overwrite the existing "average price" value in
the same row in a column to the right.

Now if I later sell an amount say the 1 bound at 90. My total loss
will be -15 (90-105)*1 or (price - average price)*total amount. This
loss should be put into a "profit loss" column in the same row - and
pooled together with any other existing profit/loss.

Hope I have made my problem clear enough. Many thanks in advance.

 

[Joel]

Can you post your spreadsheet. Are you adding a new row to your spreadsheet
for the new BUY? You need to both rows to make the calculation. You should
have all purchases for the same bond on adjacent rows of the spreadsheet.
The average price should be on the last row of each bond and the calculation
should look at each row of the bond to make that calculation.

 

[franky]

Hi Joel.

Thanks for your response. I´ll try to make my intentions more clear.
My sheet is arranged in the following fashion.

A B
C D
E F
ISIN CODE AMOUNT VALUE CURRENT
PRICE AVERAGE PRICE PROFIT / LOSS


I input the "isin code", "purchased amount" and "current price" into
the userform and indicate whether it is "buy" or "sell" transaction.
The model then searches column A for the "isin code" to see if a
position already exists.

If no position exists then the input values should be put into the
respective columns in the first empty row (ISIN CODE, AMOUNT, VALUE
etc.).

If a position exists and it is a "buy" transaction then a new "average
price" should be calculated as:

Average price/amount + current price/amount

by finding the respective values in the same row. This value should
then overwrite any existing value in the colunmn "average price".
Obviously "average price" will be equal to "current price" the first
time a take a position in a paper. Current price will be a bloomberg
or reuters link which live updates - I will however input this value
myself in the userform.

If it is a sell transaction then the profit / loss should be
calculated as:

(current price - average price) * amount.

Any profit/loss should be put into the "profit loss" column in the
same row - and pooled together with any other existing profit/loss so
my total profit loss is calculated.

I hope this made things more clear or please write me back. Many
thanks in advance.

 

[franky]

That didn´t come out right. I`ll try again. My columns are arranged as
follows:

A:
ISIN CODE
B:
AMOUNT
C:
VALUE
D:
CURRENT PRICE
E:
AVERAGE PRICE
F:
PROFIT / LOSS

 

[Joel]

The code is pretty simple. I just did the basics. If you need additional
help let me know. the variable "c" represent column A data. To get the
other columns use offset. See code below.


Sub updatebond()

'get code from your userform
ISINCODE = "abc"
Set c = Columns("A:A").Find(what:=ISINCODE, _
LookIn:=xlValues)
If c Is Nothing Then
LastRow = Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
Range("A" & NewRow) = ISINCODE
Range("E" & NewRow) = CurrentPrice

Else
'code already in table. Calculate new Avarage Price
c.Offset(rowoffset:=0, columnoffset:=4) = AvaragePrice
End If

End Sub

 

[franky]

Hi again Joel

Thanks alot! I am on the right track. I can get it to insert the
values and use the values from my userform in the above posted
formulas. But how do i get values from my sheet into my formulas?? Say
I want to use the value in the sell offset from the ISIN code in the
"amount" column in the following formula

Average price/amount + current price/amount

I´ve tried by defing:

c.Offset(rowoffset:=0, columnoffset:=2) = Amount

And then using this value in the formula.

But this simply overwrites the cell value with zero instead of taking
the value and using it in the formula.

 

[Joel]

You have to build tthe functions as strings Ranges can either be strings or
ranges. You can also build the formulas as A1 addressing or R1C1 address.

1) Exmple 1
Range("A1").formula = "=sum(B5:C10)"

2) Example 2
Range("A1").formulaR1C1 = "=sum(R5C2:R10C3)"

3) Example 3
set myRange = Range("B5:C10")
Range("A1").formula = "=sum(" & myRange & ")"