Nowadays people wish to participate in equity markets to improve their long-term earnings and plan their taxes. One of the participants in our Excel VBA training program had a question about investing in certain bank stocks and he had a good strategy. He had studied the balance sheet, profit and loss statement, cash flow statement, earnings per share (EPS) growth and had also calculated the price to earning per share ratio (P/E). He wanted to now have a quick and easy way to highlight those companies (banks) in which he could make a good return. He had the data of the intra-day, weekly, quarterly, yearly, 3-yearly and 5-yearly returns. Watch the video below to see how a simple and efficient looping process can help him make a decision:
Watch the video on YouTube.
Below is the complete Excel VBA code:
Dim ws As Worksheet
Dim i As Long, lastrow As Long
Set ws = Worksheets(“Sheet1”)
lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To lastrow
If Cells(i, 7).Value > 20 And Cells(i, 8).Value > 20 And Cells(i, 9).Value > 20 Then
Cells(i, 7).Interior.Color = RGB(102, 255, 102)
Cells(i, 7).Interior.Color = xlNone
Of course, we can tweak the code to highlight other data.