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

 

[yousufj56]

https://drive.google.com/file/d/1UD8WY5LK-zKI-K_meJgolqB1j-RdELI9/view?usp=sharing

Please let me know if you cannot load it

 

[sheetspread]

The code is case sensitive, so change Buy and Sell to BUY and SELL. I'll check the rest to be sure it's ok.

 

[sheetspread]

Tested several lines at random, seems to work. (changed range("O1") at the end to BAQ1 of course)

 

[yousufj56]

Thanks! It works perfect.