# Tag Excel

Posts: 10

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:

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

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.

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.

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```

Wow,

Well at least its presence on the Internet. It appears to be gone and I can't locate the .XLA file anywhere to save my life.  I wrote about this neat little Excel add-in a long time ago in my Genetic Algorithm Excel Addin post and since then its remained amazingly popular.  I sure hope the developer reposts it soon but I'm not holding my breath.

In case you're interested, there is another slicker Excel add-in for genetic max and min problems from www.xlpert.com.  The only thing is that it costs \$35.

FWIW, I actually bought this XLA from the developer because I wanted something to poke around with in Excel without building something in Rapidminer.  Of course you can do the same in Rapidminer, but I'm a tinkerer and a poker and this satisfied my curiosity. :)

UPDATE: The Genetic Algorithm Excel Addin is BACK! The developer moved websites and uploaded it back online again! Thanks Noyan!

Building an asset trend following system is quite easy to do if you've read my tutorials. You gather your data, assign trend values (UP, DOWN), and then run it through a classification algorithm like YALE's IBK operator. Doing this is what some people call Fuzzy trend analysis and its quite easy to do if you use YALE, but what if you don't have the time to learn YALE? Is there another way to do it, perhaps using Excel?

Before I direct you to a place where you can learn how build a classification trend following model in Excel, we have to understand what the classification algorithm is and how it works. The classification algorithm I use is called "Knn." Knn stands for "K nearest neighbor" and the best explanation I've found for what it is and how it works is from Kardi Teknomo's website:

K-nearest neighbor is a supervised learning algorithm where the result of new instance query is classified based on majority of K-nearest neighbor category. The purpose of this algorithm is to classify a new object based on attributes and training samples. The classifiers do not use any model to fit and only based on memory. Given a query point, we find K number of objects or (training points) closest to the query point. The classification is using majority vote among the classification of the K objects. Any ties can be broken at random. K Nearest neighbor algorithm used neighborhood classification as the prediction value of the new query instance. [via Kardi Teknomo PhD]

If you spend some time on Doctor Teknomo's site, you'll find his fantastic tutorial, complete with his spreadsheet examples, explaining how to us Knn in Excel to make predictions.