Very Basic Stock Screening VBA Code -Essential for Stock Traders-

Trader11 · Feb 26, 2011

Hello,

I need help on a VBA code -which I think is a very basic one.

I have cells that display, "BUY", "SELL" or "-" based on some conditions. I would like to create a VBA code so that a corresponding cell would show the date when the signals i.e. "BUY", "SELL" or "-" are triggered for the given cell. When the signal disappears (due to change in the linked condition) the date should disappear as well. As some might have guessed this is for Stock Screening hence I have some hundred rows.

Also I've saw threads that read, "copy this code and paste it to the screen where it says 'view code' and then it should run automatically", for example, but I'm struggling with that. Will I need to click on "Run" function? And how will I apply this code on the column I like.

Thank you so much in advance.

Trader11 · Feb 28, 2011

Thanks Peter. Yes, that's the problem quite frankly.

What would you suggest?

Thanks

Peter_SSs · Feb 28, 2011

You could use the Worksheet_Calculate event but this may have drawbacks in that it may possibly trigger too often and/or have too many formula cells to evaluate and consequently impact on your sheet's performance.

Anyway, you could give this a try. Remove the other code and replace it with this.

Test in a copy of your workbook.

 Private Sub Worksheet_Calculate() Dim rFormulas As Range, rCel As Range On Error Resume Next Set rFormulas = Columns("Z").SpecialCells(xlCellTypeFormulas) On Error GoTo 0 If Not rFormulas Is Nothing Then For Each rCel In rFormulas Select Case UCase(rCel.Value) Case "BUY", "SELL" rCel.Offset(, 1).Value = Format(Date, "short date") Case Else rCel.Offset(, 1).ClearContents End Select Next rCel End If End Sub

Trader11 · Feb 28, 2011

Thanks Peter,

The code does work, but it took more than 30 minutes to evaluate before I aborted. I have 15000 cells in total -though minority of them linked together.

Would you recommend any other code?

Thanks.

Peter_SSs · Mar 1, 2011

Not sure, tell us more about ..
- What you have in your sheet,
- What the formula in column Z is,
- Are there any other cells (apart from the ones shown in the formula in column Z) that influence the column Z formula? That is, do any of the cells referenced by the col Z formula also contain formulas?
- How are the cells that influence the col Z formula changed? Also, how often are they changed and how many are changed at the same time?

Trader11 · Mar 1, 2011

-I have around 9000 cells in total almost all are imported from Bloomberg Terminal (current price, volume etc.). The functions read like: =bdp(.....)
Data update daily.

-The formula in column Z is:
=IF(AND(N3<=30,Q3>0.02),"BUY",IF(AND(N3>=70,Q3<0.02),"SELL",""))

-As you can see, apart from Cells C and N, nothing influences any cell in column Z.

-Cells N and C are again imported from Bloomberg Terminal and update themselves once when I open the spreadsheet i.e. daily.

Peter_SSs · Mar 1, 2011

Try this. Remove any previous worksheet change or worksheet calculate code and try this worksheet change code. It should be quicker but whether it will be fast enough remains to be seen.

I have assumed that you would manually format column AA with the date format you want so the code doesn't have to do that as well.

(It isn't relevant to my code but your descriptions about columns C and N don't seem to match you formula that clearly depends on columns Q and N)

 Private Sub Worksheet_Change(ByVal Target As Range) Dim aFormulas, aResults Dim r As Long, num As Long Dim tDay As Date tDay = Date aFormulas = Range("Z3", Range("Z" & Rows.Count).End(xlUp)).Value num = UBound(aFormulas, 1) ReDim aResults(1 To num, 1 To 1) For r = 1 To num If Len(aFormulas(r, 1)) > 0 Then aResults(r, 1) = tDay End If Next r Application.EnableEvents = False Range("AA3").Resize(num).Value = aResults Application.EnableEvents = True End Sub

Trader11 · Mar 1, 2011

That C should be Q, my mistake.

It seems to work for now. Let me track this for couple days to check if everything works fine.

I'll write the results here.

Peter, thank you very much.

Trader11 · Mar 3, 2011

Hi,

I opened the file today, the macro doesn't work. I still see the same recommendation with today's date (it should be the date when it first recommended me either buy or sell).

I also doubt if the recommendation and the date would dissappear, based on new conditions which change everyday.

Please advise, your help is very much appreciated.

Thanks

Peter_SSs · Mar 4, 2011

I think we may have confusion between Excel's (and vba coder's) interpretation of 'change' in a worksheet and your interpretation of it. From reading your last posts I think this may be happening:

When you open the sheet on a new day the values in columns N & Q are entered, though many will appear the same. Excel (and the Worksheet_Change event) see all those cells as chaned because fresh (though maybe not different) values have been entered. You appear to be interested when the value in the cell changes to a different value from the previous value in the cell.

I've used an extra helper column to record existing values from column Z. When anything in columns N, Q changes, the formula in col Z re-evaluates. This new code should now only act on that row if the new value in column Z differes from the previous value (that is now stored in col AB.

As a starting point to test this new code, copy the whole of column Z and use Paste Special... Values to paste into column AB (in corresponding rows).

Then try this code. I'm still not sure it is exactly what you want but it might get it to where you can start trying things yourself a bit more.

 Private Sub Worksheet_Change(ByVal Target As Range) Dim aFormulas, aResults, aOldData Dim LRN As Long, LRZ As Long, LRAB As Long Dim r As Long, num As Long Dim tDay As Date Const FirstRw As Long = 3 tDay = Date LRN = Range("N" & Rows.Count).End(xlUp).Row LRZ = Range("Z" & Rows.Count).End(xlUp).Row LRAB = Range("AB" & Rows.Count).End(xlUp).Row num = IIf(LRN > LRZ, LRN, LRZ) num = IIf(LRAB > num, LRAB, num) - FirstRw + 1 aFormulas = Range("Z" & FirstRw).Resize(num).Value aResults = Range("AA" & FirstRw).Resize(num).Value aOldData = Range("AB" & FirstRw).Resize(num).Value For r = 1 To num Select Case aFormulas(r, 1) Case Is = aOldData(r, 1) Case Else aResults(r, 1) = tDay aOldData(r, 1) = aFormulas(r, 1) End Select Next r Application.EnableEvents = False Range("AA3").Resize(num).Value = aResults Range("AB3").Resize(num).Value = aOldData Application.EnableEvents = True End Sub

Trader11 · Mar 4, 2011

Peter,

Thanks a lot again.

I'm checking the code but can't find "Q" in any rows. Column Q has also impact on Column Z.

But the code seems to consist only "N". Am I missing something?

Thanks.

Very Basic Stock Screening VBA Code -Essential for Stock Traders-

Trader11

New Member

Trader11

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Trader11

New Member

Peter_SSs

MrExcel MVP, Moderator

Trader11

New Member

Peter_SSs

MrExcel MVP, Moderator

Trader11

New Member

Trader11

New Member

Peter_SSs

MrExcel MVP, Moderator

Trader11

New Member

Similar threads

Forum statistics

Share this page

Very Basic Stock Screening VBA Code -Essential for Stock Traders-

New Member

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

New Member

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock