sort out the trades in sequence

[deezina07]

Hello,

I have attached an Excel file. In this file, in Sheet 1, I have trades (When to Buy and when to Sell) as well as the price (in Column A). In sheet two I have columns I need to fill.

In sheet two the first column is "Trade Number". Then there is "Buy Price", "Sell Price", "Profit/Loss", "Number of trades Won", and "Number of trades Lost".

I want the Excel file to pull the first "Buy" signal from sheet 1 and enter it as the first trade in the first column of the second sheet. Then, I want Excel to take the price at the first buy signal, and put it at "Buy Price". Then I want Excel to pull the first sell signal and record its price in "Sell Price". Repeat the same method for trade 2, that is, record the next buy signal as trade number "2", record its value in "Buy Price", and so on.....eventually, I want to calculate the number of trades won, that is, if Profit/loss (which is equal to "Sell Price-Buy Price") equals positive, then it is a win. If positive value is negative, then it is a loss. I want to add all the number of trades won and all the number of trades lost in the next columns.

If anyone can help me out with this I will appreciate it.

[Glenn Kennedy]

The columns in sheet 1 are not labelled. I have no idea what is what. There are no manually calculated expected results in sheet 2. I have no idea what you want to see. Please amend your sample sheet and repost.

[deezina07]

I have attached the file with the changes made. The first column is the price of the stock. The second column is 1 if there is a trade running or 0 if there is no trade running. Third column is the signal, whether to Buy the Sock or Sell.

I want to pick the trades sequentially, that is, the first buy signal is recorded as "1" in "Trade Number" in the second sheet, with the price corresponding to the buy signal in buy price, whereas the subsequent sell signal is recorded as the sell price for the first trade.
Then the next buy signal is recorded as "2" in the "Trade Number", with the price corresponding to the buy signal as "Buy Price" and, subsequently, the next price at the sell signal in the "sell price", and so on....

In "holding period" in Sheet 2, I want to know the number of days the stock was held, that is, the number of rows between the first buy signal, and the first sell signal....the number of days between the second buy signal and the subsequent sell signal...and so on.

I will appreciate if someone can help me out with this.

[Glenn Kennedy]

You have attached the same sheet!! There are no manually calculated results... Upload the correct one...

[deezina07]

Apologies for this. I have attached the file again.

Thanks

[deezina07]

Can someone guide me here, please?

[AliGW]

Based on your sample (which may not be truly representative, but is all I have to work with):

Excel 2016 (Windows) 32 bit

	B	C	D	E
2	=SMALL(IF(Sheet1!$D$8:$D$28="Buy",Sheet1!$A$8:$A$28),Sheet2!A2)	=SMALL(IF(Sheet1!$D$8:$D$28="Sell",Sheet1!$A$8:$A$28),Sheet2!A2)	=INDEX(Sheet1!$B$2:$B$28,MATCH(Sheet2!B2,Sheet1!$A$2:$A$28,0))	=INDEX(Sheet1!$B$2:$B$28,MATCH(Sheet2!C2,Sheet1!$A$2:$A$28,0))

Sheet: Sheet2

The first two formulae may need to be entered with CTRL+SHIFT+ENTER. Drag copy all down.

[Glenn Kennedy]

If you would prefer a formula that does NOT require array entry, this works too:

=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$D$2:$D$30)/(Sheet1!$D$2:$D$30="Buy"),ROWS(B$2:B2))),"")

it needs to be adjusted a little for the sell sate and for the buy/sell amounts - I have done this in the file. I have also tidied up the formulae in the yellow shaded areas. see sheet.

[deezina07]

Thank You!

When I plug the formula in B2 while pressing ctrl+shift+enter, I get numbers in the column but not dates. I will highly appreciate if you can upload a file with the formulae, so I can see what I am doing incorrectly.

I have attached the file to show where the problem is arising.

Also, if I don't know the number of trades in the data set, I cannot write "Trade Number" in Sheet 2, Column A. Other formulae are dependent what is in Column A (Sheet 2).

Can the formula be written in a manner that it captures the first trade and writes 1 in Column A. Then it captures the second trade and writes 2 in Column A.

Again, Thank You. I highly appreciate this on your part.

[deezina07]

Got it.

Thank You.

I appreciate this.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[deezina07]

The only issue I am having is that when I enter the above mentioned code in a new file in Excel with the same data, it gives me numbers instead of dates in "Date Initiated" and "Date Terminated". The rest comes out fine. I have attached the file in which I pasted the code in order to note the error I am getting.

[Glenn Kennedy]

As I said in the PM. Format as date.

[AliGW]

Change the cell formatting to Short Date - yours are set to General. The numbers are date serial numbers.