• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to automate a task? Working file attached

Dear All,

In the Excel sheet attached i have some completed transactions and on the right side u will find the Equity Curve data. I want only the completed transaction to appear in the Equity Curve. My idea is as soon as i enter the S.Price whether its Profit or loss the value should be updated in the "AMT" column as well as the "Dates" and if its Profit or Loss" in the notes it should either come "PROFIT OR LOSS".

Whether it can be done....just help me how to do it.

Regards,

Sonjoe Joseph
(Working file attached)
 

Attachments

  • Working file.xlsx
    9.6 KB · Views: 9
Care to explain the logic of updating the Equity Curve? I see no relationship between the two tables for the example presented.
 
Care to explain the logic of updating the Equity Curve? I see no relationship between the two tables for the example presented.


In the file you can see B & D are the completed transactions were the B was sold at Rs.122 and D was sold at Rs.112. So this transactions has to come in the Equity Curve filling up the particular dates, Not;es if is "Profit or a Loss" and AMT. Hope now u got the idea. Then i can plot an Equity Curve chart hassle free.
 
Maybe a Worksheet Change Event.

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long, tRow As Long
    If Target.Column = 7 Then
        lr = Range("K" & Rows.Count).End(xlUp).Row + 1
        tRow = Target.Row
        Range("K" & lr) = Range("C" & tRow)
        Range("M" & lr) = Range("I" & tRow)
    End If
End Sub
 
Back
Top