Tag Excel

Posts: 10

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.

comments

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

comments

Genetik Solver appears to be no more - ITS BACK!

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!

comments

Understanding Fuzzy Trend Following in Excel

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?

The answer is YES!

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.

So how do use his spreadsheet to build your own trend following system? I made this part easy on you, to follow along just download my version of the good Doctor's spreadsheet.

Here's what I did, first I modified his spreadsheet and populated it with 24 trading days of the iShares MSCI Japan Index EWJ and the iShares MSCI Singapore Index EWS ETF's. What I wanted to do is predict EWS's trend (+ for up, - for down) using the data for both ETF's. Next, I changed the trend value to either + or -, in column D, and then changed the "K" cell value to 8.

Changing the number in the "K" cell tells the algorithm how many of your query cell's neighbors it should look at to make its prediction. The spreadsheet then automatically calculated the correct trend value "+" for EWS after I inputed my preferred "K" value.

It's as simple as that! Now you have, in a rudimentary way, the ability to create your own trend following system in Excel using neural net algorithms. Do spend the time learning how this algorithm works because its very powerful and you can easily incorporate it into an ATS or other quantitative analytic trading system.

comments

Neural Market Trends is the online home of Thomas Ott.