I use Excel extensively at work and I've always wished to write intricate macros but never understood the complexities of Visual Basic. After spending a few weeks learning Python, I've managed to build an Excel formula in VB that creates an "IF" statement formula if a hammer candlestick formation has occurred. I use this as a macro function and apply it automatically to my downloaded data using TraderXl.

The formula is generates a 1 if a hammer has formed or a 0 if not. Assuming you've downloaded 153 rows of a stock's date, open, high, low, close, volume in the A, B, C, D, E and F columns respectively. The output is in column P; you may modify it as you see fit.

Whoop-de-doo, right?

Well here it is:

`Range("P1").Select`

ActiveCell.FormulaR1C1 = "Hammer Formula"

Range("P2").Select

ActiveCell.FormulaR1C1 = _

"=IF(AND((RC[-13]-RC[-12])>(3*(RC[-14]-RC[-11])),(((RC[-11]-RC[-12])/(0.001+(RC[-13]-RC[-12])))>0.6),(((RC[-14]-RC[-12])/(0.001+(RC[-13]-RC[-12])))>0.6)),1,0)"

Range("P2").Select

Selection.Copy

Range("P3:P154").Select

ActiveSheet.Paste