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

T

Trader11

New Member
Joined
Feb 25, 2011
Messages
25
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.
 
Sort by date Sort by votes
Peter,

Spreadsheet crashes with various errors.

So before you reply, please give me one more day, and I will send you the spreadsheet so that you can see what's going on.

Thank you so much.
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Trader11 said:
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?
Click to expand...
I was just using column N to determine how amny rows were used in that part of the sheet. Can column Q and column N contain different numbers of rows of data? I assumed not (since column Z uses data from both) so I didn't separately check column Q.

I don't wish to receive your actual workbook - I have tried that before generally with disastrously time-consuming consequences. You can certainly post small screen shots from it (my signature block below has three methods) but if we can't resolve the problem here without the actual workbook then perhaps it is too complex a problem for a free public forum like this.
 
Upvote 0
I understand, I hesitated that day. I've just implemented the code. But I guess I need to check it over the upcoming days to validate whether it works correctly. I will keep posting here the results.

Peter thanks so much for your efforts so far.
 
Upvote 0
Hi,

Unfortunately problems again.. (Cross referencing, dates not updating etc.)

Please find a copy paste application of a small part of my worksheet. (As an option under your signature)


<TABLE style="WIDTH: 1299pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1728 border=0><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 135pt; mso-width-source: userset; mso-width-alt: 6582" width=180><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><TBODY><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD class=xl89 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 217pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 24pt; BACKGROUND-COLOR: transparent" width=289 colSpan=2 height=32>
STOCK DATA​
</TD><TD class=xl88 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 312pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=415 colSpan=5>
TRADING​
</TD><TD class=xl89 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 281pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=374 colSpan=4>
HISTORICAL PRICE ​
</TD><TD class=xl88 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 151pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=201 colSpan=2>
RSI​
</TD><TD class=xl89 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 222pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=295 colSpan=3>
MACD​
</TD><TD class=xl90 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 116pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=154 colSpan=2>
RECOMMENDATION​
</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl95 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>
TICKER​
</TD><TD class=xl96 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
NAME​
</TD><TD class=xl97 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
PRICE​
</TD><TD class=xl97 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
chg​
</TD><TD class=xl96 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
% chg​
</TD><TD class=xl95 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
VOL.​
</TD><TD class=xl98 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
AVG. VOL. 30D​
</TD><TD class=xl99 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
52WK LOW​
</TD><TD class=xl98 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
Dist. 52WK LOW​
</TD><TD class=xl100 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
52WK HIGH​
</TD><TD class=xl96 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
Dist. 52WK HIGH​
</TD><TD class=xl101 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
RSI 3D​
</TD><TD class=xl102 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
RSI 14D​
</TD><TD class=xl95 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
MACD (12,26)​
</TD><TD class=xl97 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
MACD SGNL (9)​
</TD><TD class=xl98 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
DIFF MACD​
</TD><TD class=xl103 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
RSI & MACD​
</TD><TD class=xl104 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
DATE​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl92 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>NTHOL TI Equity</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> NET HOLDING AS</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 1.42</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 0.01</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 0.71%</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 464,496 </TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 2,164,222 </TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 1.04</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 27.0%</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 1.70</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> -19.7%</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 77.20</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 70.88</TD><TD class=xl72 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 0.0250 </TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 0.0099 </TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 0.0152</TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">
SELL​
</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl92 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>PTOFS TI Equity </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> PETROL OFISI AS-A SHS</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 7.10</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> -0.18</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> -2.47%</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 772,218 </TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 950,525 </TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 5.12</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 27.9% </TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 7.36</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> -3.7%</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 96.66</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 82.59</TD><TD class=xl72 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 0.0942 </TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 0.0780</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 0.0162</TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">
SELL​
</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl92 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ADNAC TI Equity</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> ADANA CIMENTO-C</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 0.76</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 0.02</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 2.70%</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 1,791,990 </TD><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 5,997,776 </TD><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 0.52</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 31.6%</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 1.10</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> -44.7%</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 34.70</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> 38.46</TD><TD class=xl72 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> -0.0221</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> -0.0179</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> -0.0042</TD><TD class=xl76 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl93 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl105 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>AEFES TI Equity</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> ANADOLU EFES BIRACILIK VE</TD><TD class=xl79 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> 21.85</TD><TD class=xl79 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> -0.30 </TD><TD class=xl80 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> -1.35%</TD><TD class=xl81 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> 18,621 </TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> 384,012 </TD><TD class=xl78 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> 15.00</TD><TD class=xl83 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> 31.4%</TD><TD class=xl79 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> 23.95</TD><TD class=xl84 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> -9.6%</TD><TD class=xl79 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> 72.83</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> 58.42</TD><TD class=xl86 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> 0.1432</TD><TD class=xl87 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> -0.0261</TD><TD class=xl87 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> 0.1693</TD><TD class=xl106 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl107 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

Columns start with A ends with R (17 columns)

Formula under M (RSI 14D):
=BDP($A3,"RSI_14D")

Formula under P (DIFF MACD):
=BDP($A3,"MACD2")

Formula under Q (RSI & MACD):
=IF(AND(M3<=30,P3>0.02),"BUY",IF(AND(M3>=70,P3<0.02),"SELL",""))

Notes: =bdp() is a Bloomberg Terminal function. Formulas under columns M and P change daily and after market close and represent that day's figures.

As you can see; for NTHOL TI Equity and PTOFS TI Equity "SELL" recommendations are given (under column Q), for the other remaning two; no recommendations.

So again, I'd like to see at which date such recommendation was given. The dates should be static (not sure about the term "static") so that I can see how many days have passed since the recommendation was first given. In addition, if no recommendation is given, the date cell should be blank.

I repeated myself a little bit, you already know what I want to accomplish here, I just want to clarify as much as I can.

Please don't hesitate to ask any questions for clarification.

Thanks a lot.
 
Upvote 0
I'm finding this a bit difficult to keep track of in that the columns seem to keep changing. :eeek:

However, I'm still trying and I have looked back over the thread again and noticed this
Trader11 said:
Data update daily.
Click to expand...
So if the data just updates daily, then presumably we only have to run this procedure (once) daily. Is that correct?

For now I'll assume so and go with the Column A:R layout as posted above.

What I'm suggesting is to remove any 'event' code (Worksheet_Change, Worksheet_Calculate etc) and just have a standard macro that is run manually once a day, or as often as you like.

In the VB window remove any event code and then use the menu to ..
Insert|Module
.. and put the code below in that new Module.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Update()<br>    <SPAN style="color:#00007F">Dim</SPAN> tDay <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> LastFormula <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LastDate <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> num <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> aFormulas, aResults, aOldData<br> <br>    <SPAN style="color:#00007F">Const</SPAN> FirstRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 3<br><br>    tDay = <SPAN style="color:#00007F">Date</SPAN><br>    LastFormula = Range("Q" & Rows.Count).End(xlUp).Row<br>    LastDate = Range("R" & Rows.Count).End(xlUp).Row<br>    num = LastFormula - FirstRow + 1<br>    a<SPAN style="color:#00007F">For</SPAN>mulas = Range("Q" & FirstRow).Resize(num).Value<br>    aResults = Range("R" & FirstRow).Resize(num).Value<br>    aOldData = Range("AB" & FirstRow).Resize(num).Value<br>    For r = 1 <SPAN style="color:#00007F">To</SPAN> num<br>        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> aFormulas(r, 1)<br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> = aOldData(r, 1)<br>            <br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>                aResults(r, 1) = tDay<br>                aOldData(r, 1) = aFormulas(r, 1)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Range("R" & FirstRow).Resize(num).Value = aResults<br>    <SPAN style="color:#00007F">If</SPAN> LastDate > LastFormula <SPAN style="color:#00007F">Then</SPAN><br>        Range("R" & LastFormula + 1 & ":R" & LastDate).ClearContents<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    Columns("AB").ClearContents<br>    Range("AB" & FirstRow).Resize(num).Value = aOldData<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Then in your sheet when you have had the daily data updated ..
Developer tab*|Macros|select the 'Update' macro|Run

* If you do not have the Developer tab visible then
Office icon|Excel Options|Popular|Show Developer tab in the Ribbon|OK

The following day when you have opened your file and had the data updated, you would run the macro again.

Notes:
1. I am still using column AB to record the column Q formula results at the time the code is run. These values are used the next time the code is run to see if the formula results have changed.

2. I am not sure what you want to happen when a formula result changes from BUY or SELL to blank. What should happen in column R?

a) The current date is entered? (This is what my code currently does because column Q has changed)

b) The existing date in the column remains?

c) The cell is cleared to blank?
 
Upvote 0
Thank you Peter. I think this time we cracked it, it seems pretty good. But of course, I will need to check it tomorrow if it is smooth indeed.

Yes, that's correct, the values which impact column Q (namely, RSI 14D and MACD) update themselves daily. I will open the spreadsheet everyday (once) and then will run the macro so that I can see "which stocks to buy/sell today"

I have copied column Q and pasted values into column S hence have changed the "AB"s in the code into "S".

Well it is better if the column R is cleared to blank when the formula in column Q is blank. (I don't wish to see any dates if no recommendation is given.) Could you please adjust this?
 
Upvote 0
Trader11 said:
Well it is better if the column R is cleared to blank when the formula in column Q is blank. (I don't wish to see any dates if no recommendation is given.) Could you please adjust this?
Click to expand...
Try adding the blue section of code where shown
Rich (BB code): 
Case Is = aOldData(r, 1)

Case Is = vbNullString
    aResults(r, 1) = vbNullString
    aOldData(r, 1) = vbNullString

Case Else
 
Last edited:
Upvote 0
Hey Peter,

I waited a bit to confirm if the macros work correctly, apparently they do! I also modified the same code for different spreadsheets.

Thank you so much for your help, I really appreciate it.

Take care! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA to create simple stock control
Replies
2
Views
289
maiwarits
M
L
  • Solved
Consolidating Stock Backtest Data into Table
Replies
1
Views
421
LakeDog
L
J
  • Question Question
Using Sheets().Select in a Worksheet_Activate() macro
2
Replies
10
Views
471
jdun
J
H
  • Question Question
Compare Demand Data to Stock Data
Replies
6
Views
558
Bosquedeguate
Bosquedeguate
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
254
Pingaware
P

Forum statistics

Threads
1,224,596
Messages
6,179,807
Members
452,943
Latest member
Newbie4296

Share this page

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