VBA to extract data based on first occurrence in column; Trading Analysis

[yousufj56]

Hi,

I have an excel file with 1500 columns and each column has 4500 rows of data. Here is an example of what my spreadsheet looks like:

https://imgur.com/a/n9mZ7

Here is what i'm looking to do.

1. I want excel to look at every 4th column starting from B. So the first column excel would look at is column E.

2. From this 4th column, I want excel to retrieve the Column header (in the first row of that column).

3. Then, i want excel to look for the first occurrence of the word "Buy" in the column.

4. If a buy cell is found in the column, then retrieve the value of the column that is 3 columns to the left of the "Buy" cell.

5. In addition to that, I want the the corresponding value from that row but in Column A (i have the date there).

6. Then repeat the same thing for the word "Sell". Making sure that the "Sell" that is taken into consideration is the first "Sell" that has occurred in a row after "Buy". (not before the "Buy").

Essentially what I want to do is, get all the buy and sell points for given stocks so I could analyze to see if the signal is profitable or not.

Wondering if someone can help me figure this out?

Thanks

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		QQQ	QQQ	QQQ	QQQ	WWW	WWW	WWW	WWW	EEE	EEE	EEE	EEE
2	1/1/2018	13	56	12		13	41	54		19	67	33	Sell			Ticker	Date	Buy	Sell
3	1/6/2018	9	34	36	Sell	62	79	88		91	68	91	Sell			QQQ	2/22/2018	64	57
4	1/13/2018	54	39	58		20	100	96	Buy	87	37	34				WWW	1/13/2018	20	38
5	1/28/2018	49	79	30		56	2	2		89	7	9				EEE	2/22/2018	24	48
6	1/29/2018	18	18	85		50	95	27		31	74	22
7	2/9/2018	81	99	13		79	69	20		41	78	52
8	2/22/2018	64	10	35	Buy	63	51	12		24	81	60	Buy
9	3/9/2018	42	97	99		54	68	15		80	11	60
10	3/13/2018	1	65	20		38	45	73	Sell	3	2	3
11	3/28/2018	48	54	75		45	53	85		62	85	40	Buy
12	4/11/2018	49	63	30		24	26	30		65	97	54
13	4/19/2018	57	36	66	Sell	21	62	73		54	82	97
14	4/24/2018	31	48	5		35	23	53		48	91	25	Sell
15	5/1/2018	29	11	20		62	1	49		37	18	22
16	5/3/2018	61	34	86	Sell	93	43	82		52	85	21
17	5/4/2018	42	63	16		31	79	70		22	70	90
18	5/19/2018	89	51	36		79	67	12		57	55	93
19	5/27/2018	71	73	92		96	18	82		27	52	17
Sheet7 (4)

Cell Formulas
Range	Formula
P3	=INDEX($B$1:$M$1,ROW(A1)*4)
Q3	=INDEX($A$2:$A$19,MATCH(R$2,INDEX($B$2:$M$19,,ROW(A1)*4),0))
R3	=INDEX(INDEX($B$2:$M$19,,(ROW(A1)*4)-3),MATCH(R$2,INDEX($B$2:$M$19,,ROW(A1)*4),0))
S3	=INDEX(INDEX($B$2:$M$19,,(ROW(A1)*4)-3),MATCH($S$2,INDEX(INDEX($B$2:$M$19,,ROW(A1)*4),MATCH(R$2,INDEX($B$2:$M$19,,ROW(A1)*4),0)):INDEX(INDEX($B$2:$M$19,,ROW(A1)*4),18),0)+MATCH(R$2,INDEX($B$2:$M$19,,ROW(A1)*4),0)-1)

change the 18 to whichever row your range ends -1

With so much data this may run slowly, if at all. Paste as a value if you can after.

 

[yousufj56]

Thanks for this reply. Sorry, to bother you but do you think we can get this to work with VBA rather than using functions? I was thinking something like the code below. This code finds a value that is listed in sheet2 (buy or sell) and then using offset, it gets the desired corresponding value.

Sub FindIt()


Application.ScreenUpdating = False


       Dim lRow As Long
       Dim fValue As Range


lRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row


For Each cell In Sheet1.Range("A1:A" & lRow)
         Set fValue = Sheet2.Columns("C:C").Find(cell.Value)
           If fValue Is Nothing Then GoTo NextCell
             If cell.Offset(0, 1).Value = fValue.Offset(0, 3).Value Then
               cell.Offset(0, 5).Copy
                 Sheet2.Range("E14").PasteSpecial xlPasteValues
                   End If
NextCell:
Next cell


Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

 

[sheetspread]

Here's a VBA solution:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	QQQ	QQQ	QQQ	QQQ1	WWW	WWW	WWW	WWW1	EEE	EEE	EEE	EEE1
2	1/1/2018	13	56	12		13	41	54		19	67	33	Sell
3	1/6/2018	9	34	36	Sell	62	79	88		91	68	91	Sell
4	1/13/2018	54	39	58		20	100	96	Buy	87	37	34
5	1/28/2018	49	79	30		56	2	2		89	7	9
6	1/29/2018	18	18	85		50	95	27		31	74	22
7	2/9/2018	81	99	13		79	69	20		41	78	52
8	2/22/2018	64	10	35	Buy	63	51	12		24	81	60	Buy
9	3/9/2018	42	97	99		54	68	15		80	11	60
10	3/13/2018	1	65	20		38	45	73	Sell	3	2	3
11	3/28/2018	48	54	75		45	53	85		62	85	40	Buy
12	4/11/2018	49	63	30		24	26	30		65	97	54
13	4/19/2018	57	36	66	Sell	21	62	73		54	82	97
14	4/24/2018	31	48	5		35	23	53		48	91	25	Sell
15	5/1/2018	29	11	20		62	1	49		37	18	22
16	5/3/2018	61	34	86	Sell	93	43	82		52	85	21
17	5/4/2018	42	63	16		31	79	70		22	70	90
18	5/19/2018	89	51	36		79	67	12		57	55	93
19	5/27/2018	71	73	92		96	18	82		27	52	17
Sheet1

Sub buyselldata()
Dim x As Variant, y As Variant, i%, j%, k%, z%
x = Cells(1).CurrentRegion
ReDim y(((UBound(x, 2) - 1) / 4) - 1, 3)
z = 0
For i = 5 To UBound(x, 2) Step 4
y(z, 0) = x(1, i)
For j = 1 To UBound(x, 1)
If x(j, i) = "Buy" Then
y(z, 2) = x(j, i - 3)
y(z, 1) = x(j, 1)
For k = j To UBound(x, 1)
If x(k, i) = "Sell" Then
y(z, 3) = x(k, i - 3)
j = UBound(x, 1)
Exit For
Else
End If
Next k
Else
End If
Next j
z = z + 1
Next i
Range("o1").Resize(UBound(y, 1) + 1, UBound(y, 2) + 1) = y
End Sub

Excel 2010
	O	P	Q	R
1	QQQ1	2/22/2018	64	57
2	WWW1	1/13/2018	20	38
3	EEE1	2/22/2018	24	48
Sheet1

 

[yousufj56]

Thanks, i just tried this but it only outputs the Tickers in Column O. It doesn't give any of the other details you have in your second spreadsheet example.

Am i doing something wrong? I've setup the excel sheet in the same manner as you have in first screenshot.

 

[sheetspread]

You're sure it's exact? No hidden columns?

 

[yousufj56]

No hidden columns but here are the differences:

1. You have a slight difference in your headers due to the "1" at the end of the tickers. Mine don't have the 1.
2. Not every column that has "QQQ1" will have any buy or sell signals.

I wonder if point number 2 will cause an issue?

Here is a screenshot of what I have: https://imgur.com/a/AW6TA

 

[sheetspread]

It works even without the 1s (which I just added for testing purposes) and buy/sell signals in a particular column:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date	QQQ	QQQ	QQQ	QQQ	WWW	WWW	WWW	WWW	EEE	EEE	EEE	EEE		QQQ	2/22/2018	64	57
2	1/1/2018	13	56	12		13	41	54		19	67	33	Sell		WWW
3	1/6/2018	9	34	36	Sell	62	79	88		91	68	91	Sell		EEE	2/22/2018	24	48
4	1/13/2018	54	39	58		20	100	96		87	37	34
5	1/28/2018	49	79	30		56	2	2		89	7	9
6	1/29/2018	18	18	85		50	95	27		31	74	22
7	2/9/2018	81	99	13		79	69	20		41	78	52
8	2/22/2018	64	10	35	Buy	63	51	12		24	81	60	Buy
9	3/9/2018	42	97	99		54	68	15		80	11	60
10	3/13/2018	1	65	20		38	45	73		3	2	3
11	3/28/2018	48	54	75		45	53	85		62	85	40	Buy
12	4/11/2018	49	63	30		24	26	30		65	97	54
13	4/19/2018	57	36	66	Sell	21	62	73		54	82	97
14	4/24/2018	31	48	5		35	23	53		48	91	25	Sell
15	5/1/2018	29	11	20		62	1	49		37	18	22
16	5/3/2018	61	34	86	Sell	93	43	82		52	85	21
17	5/4/2018	42	63	16		31	79	70		22	70	90
18	5/19/2018	89	51	36		79	67	12		57	55	93
19	5/27/2018	71	73	92		96	18	82		27	52	17
Sheet1

 

[yousufj56]

I tried it on your data set and it works but it doesn't on my own...so wierd. Any ideas on how i can trouble shoot?

 

[sheetspread]

Can you post a sample or send me a file?