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

yousufj56

Board Regular
Joined
May 22, 2014
Messages
51
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
 
Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Excel 2010
ABCDEFGHIJKLMNOPQRS
1QQQQQQQQQQQQWWWWWWWWWWWWEEEEEEEEEEEE
21/1/2018135612134154196733SellTickerDateBuySell
31/6/201893436Sell627988916891SellQQQ2/22/20186457
41/13/20185439582010096Buy873734WWW1/13/20182038
51/28/201849793056228979EEE2/22/20182448
61/29/2018181885509527317422
72/9/2018819913796920417852
82/22/2018641035Buy635112248160Buy
93/9/2018429799546815801160
103/13/201816520384573Sell323
113/28/2018485475455385628540Buy
124/11/2018496330242630659754
134/19/2018573666Sell216273548297
144/24/201831485352353489125Sell
155/1/201829112062149371822
165/3/2018613486Sell934382528521
175/4/2018426316317970227090
185/19/2018895136796712575593
195/27/2018717392961882275217
Sheet7 (4)
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
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.

Code:
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
 
Upvote 0
Here's a VBA solution:


Excel 2010
ABCDEFGHIJKLM
1DateQQQQQQQQQQQQ1WWWWWWWWWWWW1EEEEEEEEEEEE1
21/1/2018135612134154196733Sell
31/6/201893436Sell627988916891Sell
41/13/20185439582010096Buy873734
51/28/201849793056228979
61/29/2018181885509527317422
72/9/2018819913796920417852
82/22/2018641035Buy635112248160Buy
93/9/2018429799546815801160
103/13/201816520384573Sell323
113/28/2018485475455385628540Buy
124/11/2018496330242630659754
134/19/2018573666Sell216273548297
144/24/201831485352353489125Sell
155/1/201829112062149371822
165/3/2018613486Sell934382528521
175/4/2018426316317970227090
185/19/2018895136796712575593
195/27/2018717392961882275217
Sheet1


Code:
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
OPQR
1QQQ12/22/20186457
2WWW11/13/20182038
3EEE12/22/20182448
Sheet1
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
It works even without the 1s (which I just added for testing purposes) and buy/sell signals in a particular column:


Excel 2010
ABCDEFGHIJKLMNOPQR
1DateQQQQQQQQQQQQWWWWWWWWWWWWEEEEEEEEEEEEQQQ2/22/20186457
21/1/2018135612134154196733SellWWW
31/6/201893436Sell627988916891SellEEE2/22/20182448
41/13/20185439582010096873734
51/28/201849793056228979
61/29/2018181885509527317422
72/9/2018819913796920417852
82/22/2018641035Buy635112248160Buy
93/9/2018429799546815801160
103/13/201816520384573323
113/28/2018485475455385628540Buy
124/11/2018496330242630659754
134/19/2018573666Sell216273548297
144/24/201831485352353489125Sell
155/1/201829112062149371822
165/3/2018613486Sell934382528521
175/4/2018426316317970227090
185/19/2018895136796712575593
195/27/2018717392961882275217
Sheet1
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top