Experimenting with Google Sheets

Google Sheets, and Google Documents for that matter, are turning into some really robust tools. If I were to start a company, I would use those tools for writing documents and building spreadsheets.

I was experimenting with the data import functionality of Google Sheets. I wanted to import some stock data and there is a native function called GOOGLEFINANCE. All I needed to do was enter =GOOGLEFINANCE(AAPL) and I would automatically get the current price of Apple, Inc.

You can take it to the next level and auto load in OHLCV data by just doing:

=GOOGLEFINANCE(“AAPL”,“all”,TODAY()720,TODAY(),“weekly”)

appl-google-sheet-raw-ohlcv-data

Similar functionality to Excel but the data import is so much nicer.

Google Sheet Charts

One of things that turned me off a few years ago were how clunky the charting capability was in Google Charts. It appears that this problem is being corrected.

Using the raw Google Sheet data for $AAPL, I was able to generate two charts: a candlestick chart and a volume & close chart.

aapl-candlestick

 

AAPL Close vs Volume

Generating the candlestick chart was a bit tough though. I had to, in order, select the Open, Low, Close and High prices. That was a bit strange and Excel does this better.

However, the do let you do scripting and marcos, which is similar to what Excel does in Visual Basic, but this is very Javascript like.

TL;DR

Simply put, I like where Google is going with its Docs/Sheets/Charting offering. You can try playing with this here.

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