# Learning Python helps me with Excel

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`

## 12 thoughts on “Learning Python helps me with Excel”

1. Giles Thomas says:

If you’re interested in trying Python actually in the spreadsheet (rather than using your knowledge of it to write better VBA), you might want to check out Resolver One, a spreadsheet that lets you put Python in formulae or in macros, or Dirigible, which is essentially the same thing but online. They’re both at http://www.resolversystems….

(Full disclosure — I work for the company that produces both, so I’m definitely biased in their favour!)

1. Thomas Ott says:

@Giles: Resolver One looks slick! Thanks for the hat tip, I’m going to try the 31 day trail.

1. Giles Thomas says:

Cool, thanks! Drop us a line if you have any questions problems.

1. Thomas Ott says:

@Giles: I noticed that the numpy module can be used with ResolverOne. How about matplotlib for charting?

2. Ron says:

Just for reference, TA Lib has Candllestick Pattern Detection routines http://ta-lib.org/function…., I created a workbook at one time to use all of them. Very Good Stuff! (free)

1. Thomas Ott says:

@Ron: I came across that too! The python code is open source, I believe the excel functions cost \$\$\$

3. Ron says:

Tom I sent a copy of the TA Lib Addin to the Forum. It’s the free version. Were you aware that the Open Office “Excel” uses Python (and javascript, beanshell and basic) for macros?

1. Thomas Ott says:

@Ron: I was not aware that Open Office Excel uses Python for macros. Thanks, I’ll have to check it out!

4. S.Elias says:

Is there a reason for absolute selection? Let me know if you want assistance with VBA.
You can create a function instead of a sub. Also, let me know if you are using office +2007, you can create you own custom ribbion.
You can use Active Cell Address then offset it.

Also, you can use dynamic selection instead of (“P3:P154”): Range(“A3:U” & Range(“A65536”).End(xlUp).Row).Select

1. Thomas Ott says:

@S Elias: The reason why I did it this way is that I’m doing it via a macro call. I download data in TraderXL and then run macros as it downloads the each asset class.

1. S.Elias says:

Macros are great way to start. Most of the time, I record a macro then edit it to make it dynamic. My concern is, at work they use macros all the time and they are not dynamic. That is why they didn’t include some data in calculations. (Range(“P3:P154”).Select)
Same with RC13 method. Try Range(“A3:U” & Range(“A65536”).End(xlUp).Row).Select for dynamic selections and Counting rows/columns before using the RC method. At least you can use the code in different sheets. Also, you can use Custom UI Editor For Microsoft Office to create your own Ribbon in Excel 2007 and above.

I used Openoffice but had to stop because I like to use the code I already wrote at work/school.

1. Thomas Ott says:

S.Elias: That’s how I do it too most of the time. I record a macro and then edit the code!