Learning Python helps me with Excel

Python, Pyspark, Automation

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. 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. @Giles: Resolver One looks slick! Thanks for the hat tip, I’m going to try the 31 day trail.

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

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

  2. 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. @Ron: I was not aware that Open Office Excel uses Python for macros. Thanks, I’ll have to check it out!

  3. 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. @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. 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.

Comments are closed.