A complicated Data Clean Up Problem

[VGaleoto]

Hi all,

I am new to this message board and am slightly above novice at excel after learning just how much the program can do. However, I have come across a very common request. I am trying to take all of the staggered data in each month and move it all to one line as long as it matches the columns of "Counterparty" Strike/Price" and "Trade Date". I have tried multiple things, i don't feel like indexmatch is capable of this. I have had some trouble thinking of the proper VBA code to achieve this. This is just a picture of a portion of a very large file. I would ultimately like to create a macro that will be able to do this for the whole worksheet. Is anyone able to offer any guidance?

 

[alansidman]

Show us the format you want. I am confused by what you are asking.

 

[VGaleoto]

Excel VBA Unwind Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Trade ID	Trade Date	Underlying	Strategy	Strike/Price	Counterparty	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20
3	184735	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A	1,050,000
4	184736	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A	600,000
5	184737	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A	(1,050,000)
6	184739	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A	(600,000)
7	184786	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A		1,085,000
8	184787	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A		620,000
9	184788	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A		(1,085,000)
10	184790	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A		(620,000)
11	184837	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A			1,050,000
12	184838	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A			600,000
13	184839	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A			(1,050,000)
14	184841	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A			(600,000)
15	184888	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A				1,085,000
16	184889	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A				620,000
17	184890	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A				(1,085,000)
18	184892	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A				(620,000)
19	184937	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A					1,085,000
20	184938	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A					620,000
21	184939	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A					(1,085,000)
22	184941	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A					(620,000)
23	184986	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A						1,050,000
24	184987	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A						600,000
25	184988	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A						(1,050,000)
26	184990	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A						(600,000)
27	185033	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A							1,085,000
28	185034	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A							620,000
29	185035	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A							(1,085,000)
30	185037	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A							(620,000)
31	185080	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A								1,050,000
32	185081	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A								600,000
33	185082	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A								(1,050,000)
34	185084	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A								(600,000)
35	185122	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A									1,085,000
36	185123	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A									620,000
37	185124	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A									(1,085,000)
38	185126	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A									(620,000)
39	185434	4/16/2018	NYMEX Henry Hub (LD)	Fixed Swaps	2.765	Bank B	(150,000)
40	185475	4/16/2018	NYMEX Henry Hub (LD)	Fixed Swaps	2.765	Bank B		(155,000)
41	185516	4/16/2018	NYMEX Henry Hub (LD)	Fixed Swaps	2.765	Bank B			(150,000)
42	185550	4/16/2018	NYMEX Henry Hub (LD)	Fixed Swaps	2.765	Bank B				(155,000)
43	185581	4/16/2018	NYMEX Henry Hub (LD)	Fixed Swaps	2.765	Bank B					(155,000)
44	185604	4/16/2018	NYMEX Henry Hub (LD)	Fixed Swaps	2.765	Bank B						(150,000)
45	185623	4/16/2018	NYMEX Henry Hub (LD)	Fixed Swaps	2.765	Bank B							(155,000)
46	185639	4/16/2018	NYMEX Henry Hub (LD)	Fixed Swaps	2.765	Bank B								(150,000)
47	185654	4/16/2018	NYMEX Henry Hub (LD)	Fixed Swaps	2.765	Bank B									(155,000)
48	211271	4/13/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank B	(1,500,000)	(1,550,000)	(1,500,000)	(1,550,000)	(1,550,000)	(1,500,000)	(1,550,000)	(1,500,000)	(1,550,000)
49	211272	4/13/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank B	1,500,000	1,550,000	1,500,000	1,550,000	1,550,000	1,500,000	1,550,000	1,500,000	1,550,000
50	216077	10/5/2018	NYMEX Henry Hub (LD)	Fixed Swaps	2.895	Bank C Bank	(450,000)	(465,000)	(450,000)	(465,000)	(465,000)	(450,000)	(465,000)	(450,000)	(465,000)
51	216078	10/5/2018	NYMEX Henry Hub (LD)	Fixed Swaps	2.89	Bank C Bank	(450,000)	(465,000)	(450,000)	(465,000)	(465,000)	(450,000)	(465,000)	(450,000)	(465,000)
52	216079	10/5/2018	NYMEX Henry Hub (LD)	Fixed Swaps	2.885	Bank F	(450,000)	(465,000)	(450,000)	(465,000)	(465,000)	(450,000)	(465,000)	(450,000)	(465,000)
53	216080	10/5/2018	NYMEX Henry Hub (LD)	Fixed Swaps	2.88	Bank D	(450,000)	(465,000)	(450,000)	(465,000)	(465,000)	(450,000)	(465,000)	(450,000)	(465,000)
54	216748	10/19/2018	NYMEX Henry Hub (LD)	Long Puts	2.65	Bank D	1,500,000	1,550,000	1,500,000	1,550,000	1,550,000	1,500,000	1,550,000	1,500,000	1,550,000
55	216750	10/19/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	2.95	Bank D	(1,500,000)	(1,550,000)	(1,500,000)	(1,550,000)	(1,550,000)	(1,500,000)	(1,550,000)	(1,500,000)	(1,550,000)
56	216751	10/19/2018	NYMEX Henry Hub (LD)	Long Puts	2.65	Bank E Capital	1,500,000	1,550,000	1,500,000	1,550,000	1,550,000	1,500,000	1,550,000	1,500,000	1,550,000
57	216753	10/19/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	2.95	Bank E Capital	(1,500,000)	(1,550,000)	(1,500,000)	(1,550,000)	(1,550,000)	(1,500,000)	(1,550,000)	(1,500,000)	(1,550,000)
Sheet1

The format with the dates on top and keeping all of the headers the same. I am sorry i'm unable to be as clear as I need to be. I basically want to try to create a VBA or Macro recording that will put all of the staggered volumes on one line. Then after the volumes are on one line, the rest of the lines are deleted because they are no longer needed. I'm sorry if i'm being unclear, i'm not sure how else to describe it.

 

[VGaleoto]

Excel VBA Unwind Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Trade ID	Trade Date	Underlying	Strategy	Strike/Price	Counterparty	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20
3	184735	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A	1,050,000	1,085,000	1,050,000	1,085,000	1,085,000	1,050,000	1,085,000	1,050,000	1,085,000
4	184736	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A	600,000	620,000	600,000	620,000	620,000	600,000	620,000	600,000	620,000
5	184737	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A	(1,050,000)	(1,085,000)	(1,050,000)	(1,085,000)	(1,085,000)	(1,050,000)	(1,085,000)	(1,050,000)	(1,085,000)
6	184739	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	3	Bank A	(600,000)	(620,000)	(600,000)	(620,000)	(620,000)	(600,000)	(620,000)	(600,000)	(620,000)
Sheet1

using just these two trades as an example. It would end up looking like this for the whole worksheet.

 

[VGaleoto]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3	Sum of Value					Attribute
4	Trade Date	Underlying	Strategy	Strike/Price	Counterparty	04/01/2020	05/01/2020	06/01/2020	07/01/2020	08/01/2020	09/01/2020	10/01/2020	11/01/2020	12/01/2020	Grand Total
5	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	2.8	Bank A	1650000	1705000	1650000	1705000	1705000	1650000	1705000	1650000	1705000	15125000
6			Short Calls (collars)	3	Bank A	-1650000	-1705000	-1650000	-1705000	-1705000	-1650000	-1705000	-1650000	-1705000	-15125000
7	Grand Total					0	0	0	0	0	0	0	0	0	0
8
9
10
11
12
13
14
15
Sheet5

How was this performed?

 

[CA_Punit]

1. Create a Excel Table of The Data
2. Load it into Power Qwery.
3. Unpivot the Data.
4. Load it into Excel
5. Use Pivot table


Also
Create a Helper colomn using Stategy and Atrribute colown as shown below

Book1
	A	B	C	D	E	F	G	H	I	J
1	Trade ID	Trade Date	Underlying	Strategy	Helper Colomn	Indexing	Strike/Price	Counterparty	Attribute	Value
2	184735	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	Long Puts43834	1	2.8	Bank A	04/01/2020	1050000
3	184736	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	Long Puts43834	2	2.8	Bank A	04/01/2020	600000
4	184737	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	Short Calls (collars)43834	1	3	Bank A	04/01/2020	-1050000
5	184739	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	Short Calls (collars)43834	2	3	Bank A	04/01/2020	-600000
6	184786	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	Long Puts43835	1	2.8	Bank A	05/01/2020	1085000
7	184787	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	Long Puts43835	2	2.8	Bank A	05/01/2020	620000
8	184788	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	Short Calls (collars)43835	1	3	Bank A	05/01/2020	-1085000
9	184790	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	Short Calls (collars)43835	2	3	Bank A	05/01/2020	-620000
10	184837	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	Long Puts43836	1	2.8	Bank A	06/01/2020	1050000
11	184838	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	Long Puts43836	2	2.8	Bank A	06/01/2020	600000
12	184839	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	Short Calls (collars)43836	1	3	Bank A	06/01/2020	-1050000
13	184841	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	Short Calls (collars)43836	2	3	Bank A	06/01/2020	-600000
14	184888	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	Long Puts43837	1	2.8	Bank A	07/01/2020	1085000
15	184889	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	Long Puts43837	2	2.8	Bank A	07/01/2020	620000
16	184890	4/16/2018	NYMEX Henry Hub (LD)	Short Calls (collars)	Short Calls (collars)43837	1	3	Bank A	07/01/2020	-1085000
Sheet1

Cell Formulas
Range	Formula
E2:E16	E2	=D2&I2
F2:F16	F2	=COUNTIFS($E$1:E2,E2)

 

[CA_Punit]

Now create a Pivot table as shown below

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3	Sum of Value					Attribute
4	Trade Date	Underlying	Strategy	Indexing	Strike/Price	04/01/2020	05/01/2020	06/01/2020	07/01/2020	08/01/2020	09/01/2020	10/01/2020	11/01/2020	12/01/2020
5	4/16/2018	NYMEX Henry Hub (LD)	Long Puts	1	2.8	1050000	1085000	1050000	1085000	1085000	1050000	1085000	1050000	1085000
6				2	2.8	600000	620000	600000	620000	620000	600000	620000	600000	620000
7			Short Calls (collars)	1	3	-1050000	-1085000	-1050000	-1085000	-1085000	-1050000	-1085000	-1050000	-1085000
8				2	3	-600000	-620000	-600000	-620000	-620000	-600000	-620000	-600000	-620000
9
10
Sheet2

 

[VGaleoto]

Thank you very much!!! I'll give this a shot.