January 5, 2007

Willow Solutions, Inc. – Back Testing a Trading Strategy with a Dynamic Moving Average

Filed under: finance — michaelangela @ 11:29 am

This is part of the quest to understand how people earn with the stock market using trend following techniques. It’s harder to actually earn the cash for the investing though! But… one step at a time. So this is about using using Excel to create charts for Exponential Moving Averages. This came about as we were learning about Simple Moving Averages. Exponential Moving Averages track the price closer than Simple Moving Averages.

Why go through all of this? It’s about trends. Of course with stocks and funds, etc., you want to “buy low, sell high”. But it’s almost impossible to know how to predict when to do that. So don’t predict, follow what the market is actually doing. That’s why these are trend following solutions.

One very cool tools are the new interactive Yahoo! Finance charts. They are fun to play with but can be CPU intensive. This example shows the performance of the Dow Jones and the S&P 500 over the past year. Very impressive growth… to me at least. Over 10% for both of them. Another interactive chart is from StockCharts. It is Java based so you need to have Java installed. But it’s fast as well, though it doesn’t have the other indicators on the chart itself. But it’s very useful nevertheless. This is a comparison chart between the S&P 500 and the Dow Jones.

Both show the overall gist though. There have been tremendous gains. More quickly visible on the Stock Charts graphs (by dragging the slider at the bottom all the way to the left) is the huge drop beginning in 2000 and ending around early 2003. That must have been painful, especially if you were using a buy and hold strategy. If you were using trend following, and you were following relatively closely, you would have lost some money. But, on every upswing, you would have made good gains. So that’s the trick: catch it on the upswing. Sell on the downswing, but early enough to catch it. It’s not as easy as it sounds though as there are “whipsaws”, rapid oscillations which could trigger unnecessary activity. If not tracked correctly, i.e. a good time frame for that actual investment, be it stock or mutual fund, you could lose more money as well. So it’s all a trade off.

That’s what leads to this post: Back Testing. Taking an idea an test it on historical data. Have to see how hard that is to do… Well nothing free and easy! 🙂

Willow Solutions, Inc. – Back Testing a Trading Strategy with a Dynamic Moving Average:

In this Willow Tip we are going to discuss how to add a dynamic moving average to a stock price chart which displays the high, low and close. You create a dynamic moving average by creating within the formula a reference to a single cell. This single cell holds the number of days for the moving average. Once this is done the moving average will recalculate when you change the value in a single cell e.g. from a 30 day moving average to a 20 day moving. The moving average on the chart will adjust accordingly. This lets you visually see how often the moving average is crossed and when a buy or sell signal is generated.


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: