|||

Learning Python helps me with Excel

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

Up next Happy Holidays and Happy New Year! I’m taking the rest of the month off from blogging and hopefully tie up a few loose ends before the year is over. That said, I wanted to wish my A Botched "R" plugin installation in Rapidminer - Solution I decided to install the “R” plugin in Rapidminer recently and seriously botched the process.  I botched it so bad that Rapidminer got stuck in an
Latest posts Revisiting GOOG, GE, NE, IYR from 2007 The Ye Old Blog List Motorola: Then and Now EWM Redux Testing for mean reversion with Python & developing simple VIX system - Talaikis unsorted - Tadas Talaikis Blog Steps to calculate centroids in cluster using K-means clustering algorithm - Data Science Central Basics of Statistical Mean Reversion Testing - QuantStart Algorithmic trading in less than 100 lines of Python code - O’Reilly Media Interpreting Machine Learning Models Microsoft the AI Powerhouse Investing in the S&P500 still beats AI Trading Microsoft makes a push to simplify machine learning | TechCrunch 10 Great Articles On Python Development — Hacker Noon Introduction to Keras Democratising Machine learning with H2O — Towards Data Science Getting started with Python datatable | Kaggle Phone Addiction Machine Learning Making Pesto Tastier 5 Dangerous Things You Should Let Your Kids Do The Pyschology of Writing Investing in 2019 and beyond TensorFlow and High Level APIs Driving Marketing Performance with H2O Driverless AI Machine Learning and Data Munging in H2O Driverless AI with datatable Making AI Happen Without Getting Fired Latest Musings from a Traveling Sales Engineer The Night before H2O World 2019 Why Forex Trading is Frustrating Functional Programming in Python Automatic Feature Engineering with Driverless AI Ray Dalio's Pure Alpha Fund