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.