VBA to transfor set of data from column to rows

[danielalvz]

Hello I have a complex task that I was told that required VBA, i started to attempt it using formulas but did not get very far, there is a detailed the description of what i need accomplish in the mini sheet below, it is challanging but if any one could get it done it will be greatly appreaciated

working copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1												Date (wrong format)	Date	Symbol	Bias	Quantity	Price BOT	Price SLD	Realized P&L	Entry Time	Exit Time	Commission	Trade ID
2	BOT	VERU	50	15.795		13:39:16	20220414	1	1	short		20220414	14-Apr-22	VERU	short	50	15.795	15.715	-6.01	13:39:16		1	1
3	SLD	VERU	50	15.715	-6.01	13:39:49	20220414	1.01	1	short		20220414	14-Apr-22	VERU	Short	50	13.59	13.42	-10.51	13:51:24		1.01	2
4	SLD	VERU	50	13.42		13:51:24	20220414	1.01	2	Short		20220414	14-Apr-22	VERU	Long	50	14.045	14.16	3.74	13:55:24		1	3
5	BOT	VERU	50	13.59	-10.51	13:53:29	20220414	1	2	Short		20220414	14-Apr-22	VERU	Short	50	14.55	14.57	-1.01	14:10:50		1	4
6	BOT	VERU	50	14.045		13:55:24	20220414	1	3	Long		20220414	14-Apr-22	VERU	Short	50	14.995	15.67	31.74	14:21:06		1	5
7	SLD	VERU	50	14.16	3.74	14:00:31	20220414	1.01	3	Long		20220414	14-Apr-22	VERU	Short	50	17.06	17.39	14.49	14:27:31		1	6
8	BOT	VERU	50	14.55		14:10:50	20220414	1	4	Short
9	SLD	VERU	50	14.57	-1.01	14:18:24	20220414	1.01	4	Short
10	BOT	VERU	50	14.995		14:21:06	20220414	1	5	Short		Extract the information from table A to H into L to V information to extract for every trade: Symbol, QTY, Price Bought or Sold, P&L, Time in and out, Date and commission - a trade is defined by the P&L: for example the first trade in the table was Veru bought at 15.795 and sold at 15.715, the trade is completed as the P&L of -6.01 appears on the 3 row next to SLD and the next trade starts ( this trade resulted in a loss this is why the P&L is negative) -the trade could be long or long defined by whether under the Action column BOT appears first, for example in the first trade BOT appears first meaning a LONG, the second trade after this one which in this case was using the same Symbol was short, define as SLD appears first ( currently the column N bias is wrong is should read Long then Short.... - BOT could also appear multiple time meaning that the symbol was bought more than one time before exiting the trade ( selling SLD the trade a hypothetical sample is presented in rows 26 through 28. In this case the symbol was BOT twice 50 shares each and then sold once for the total amount of shares bought initially of 100. This is case of Long ( as bought appears first). This only show two but it could be more - the same will be true for short as exemplified in rows 30 through 32. Please note there will not be space between rows is it only to be able to better show these cases. - as well there will be the time that an a postion was exit in more than one SLD ( in the case of a long) exemplified in rows 34 through 37, if this a case the P&L will show for each SLD row - same will be true in the case of a short exemplified in rows 40-43 Columns paratemters, need to be adjusted to accommodate 300 rows of data (initial formula set for only 13 row) Date, correct as it it now with the correct format adjusted in the next column Symbol, correct as it is now Bias, incorrectly set up does not display the information as defined previously Quantity: it needs to be able to accommodate in the case there is more than one BOT in the case of a long or more that one SLD in case of a short. in the case of the first example, it will be the sum of the qty in row 26 and 27. Please do not get confused the SLD qty in row 28 is the same qty so it does not need to be counted. In case of short, it will be the same the sum of the qty in row 30 and 31 Price BOT or sold (depending on whether is short or long) this is the price at which the trade was bough or the entry in case of long or sold in case of short, for example, the first trade row 2 through 3, the BOT price ( or entry) was 15.795. the same will be true for short for example the rows 4 trough 5 are short because SLD comes first, the entry price in this case is 13.42. -please note if there is more than one BOT (or sold if comes first) as it is the case for example in row 26 -27 ( or 30-31 in case of a short) the entry price will need to be the an the sum product of the quatity and the price BOT divided by the total number of shares BOT Price SLD or BOT (depending on whether is short or long) this is the price at which the trade was exit, for example the row 2-3 the exit price was 15.715. the same will whole true for short for example rows 4 trough 5 the exit price was 13.59 please note if there is more than one exit the final exit price shoul be the sum product of the exit price and the quatity divided by the total tumber of shares. Realized P&L, is correct although needs to accomodate if the trade there is more that one P&L which are the case for the sample trade in rows 34-37 and 40-43, it will be the some of these values Entry time: the time at which the trade was taken, if there is more that one BOT in the case of a long or SLD in the case of a short is simply the first BOT or SLD Exit time: the time at which the trade was exit, if there is more than one BOT in the case of a long or SLD in the case of a short is simply the last time that appears BOT or SLD Commission: it is the sum of all BOT and SLD in each trade
11	SLD	VERU	50	15.67	31.74	14:21:29	20220414	1.01	5	Short
12	BOT	VERU	50	17.06		14:27:31	20220414	1	6	Short
13	SLD	VERU	50	17.39	14.49	14:27:46	20220414	1.01	6	Short
14	BOT	PLUG	10	27.0364		13:56:28	20220419	1
15	SLD	PLUG	10	26.885	-3.52	14:05:10	20220419	1
16	BOT	AXSM	10	40.58		14:15:55	20220419	1
17	SLD	AXSM	10	39.91	-8.7	14:20:49	20220419	1
18	BOT	PLUG	10	27.2		14:23:07	20220419	1
19	SLD	PLUG	10	27.3	-1	14:26:58	20220419	1
20	SLD	AXSM	10	39.25		14:31:36	20220419	1
21	BOT	PLUG	50	27.41		14:46:51	20220419	1
22	SLD	PLUG	50	27.26	-9.51	14:52:31	20220419	1.01
23	BOT	AXSM	10	38.96	0.9	14:52:48	20220419	1
24
25				example only
26	BOT	VERU	50	15.795		13:39:16	20220414	1
27	BOT	VERU	50	15.795		14:39:16	20220414	1	Long
28	SLD	VERU	100	15.715	-6.01	13:39:49	20220414	1.01
29
30	SLD	VERU	50	15.795		13:39:16	20220414	1
31	SLD	VERU	50	15.795		14:39:16	20220414	1	short
32	BOT	VERU	100	15.715	-6.01	13:39:49	20220414	1.01
33
34	BOT	VERU	50	15.795		13:39:16	20220414	1
35	BOT	VERU	50	15.795		14:39:16	20220414	1	long
36	SLD	VERU	50	15.715	-6.01	13:39:49	20220414	1.01
37	SLD	VERU	50	15.715	-6.01	14:39:49	20220414	1.01
38
39
40	SLD	VERU	50	15.795		13:39:16	20220414	1
41	SLD	VERU	50	15.795		14:39:16	20220414	1	short
42	BOT	VERU	50	15.715	-6.01	13:39:49	20220414	1.01
43	BOT	VERU	50	15.715	-6.01	14:39:49	20220414	1.01
44
45
46
47
48
Sheet1

Cell Formulas
Range	Formula
L2:L7,O2:O7	L2	=INDEX(G$2:G$13,MATCH($W2,$I$2:$I$13,0))
M2:M7	M2	=DATE(LEFT(L2,4),MID(L2,5,2),RIGHT(L2,2))
N2:N7	N2	=INDEX(B$2:B$13,MATCH($W2,$I$2:$I$13,0))
P2:P7	P2	=SUMIFS($C$2:$C$13,$I$2:$I$13,W2,$A$2:$A$13,"BOT")
Q2:Q7	Q2	=AVERAGEIFS($D$2:$D$13,$I$2:$I$13,W2,$A$2:$A$13,"BOT")
R2:R7	R2	=AVERAGEIFS($D$2:$D$13,$I$2:$I$13,W2,$A$2:$A$13,"SLD")
S2:S7	S2	=INDEX(E$2:E$13,MATCH($W2,$I$2:$I$13,0)+1)
T2:T7,V2:V7	T2	=INDEX(F$2:F$13,MATCH($W2,$I$2:$I$13,0))
W2	W2	=IFERROR(__xludf.DUMMYFUNCTION("unique(I2:I13)"),1)
W3	W3	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),2)
W4	W4	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),3)
W5	W5	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),4)
W6	W6	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),5)
W7	W7	=IFERROR(__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""),6)
I2:I13	I2	=COUNT(E$2:E2)+IF(E2="",1,0)
J4:J13	J4	=IF(ISNUMBER(E3),IF(A3="SLD","Short","Long"),J3)