In my last post, I have discussed the most important Excel function when creating Excel-based applications for personalized backtesting and stock screening - the web query.

Macros has made Excel programming a lot simpler for me. Well, I cannot remember every function in Excel. Now, I can simply do a macro record and wala! I will show you what I mean.

Let’s say I need to color a cell red.

1. From the menu bar, select “Tools” -> “Macro” -> “Record New Macro”

2. Enter a name (”ColorExample” in my example) for this macro that we are going to record and hit “OK” and recording will be started

3. Select the cell that we wish to color red (cell B4 in my example)

4. Click on the “Fill Color” icon from the menu bar and choose your desired color (red in my example)

5. You would have noticed that the selected cell has turned red. This is the end of our recording!

6. To stop recording, select “Tools” -> “Macro” -> “Stop Recording” from the menu bar.

7. To see the source codes that have been automatically generated from our recording, select “Tools” -> “Macro” -> “Visual Basic Editor”

8. Double-click on “Module1″ to see the codes! As simple as this!

Now to re-play the macro action that we have recorded:

1. Clear the same cell of red color. Click on the “Fill Color” icon from the menu bar and choose “No fill”

And the cell B4 will be in clear again

2. Select “Tools”-> “Macro” -> “Macros” from the menu bar

3. Click to select the macro that we wish to play back (ColorExample in my example) and hit “Run”

4. And cell B4 will be colored red again

There we go, the 2nd most important function for Excel programming for collecting trading -related data.

By the way, some of your Excel may be configured differently from mine. Do drop me a comment if you cannot find any menu bar commands or icons that any of the above steps has illustrated.

Advertisement: Crazy cows moo-ocks!