|
I can't say enough good things about an Excel clone called GNUMERIC (download it for free!) and I'll be basing this tutorial on Gnumeric rather than on Excel. If you do use Excel, you may find that it chokes when you attempt to plot too many indicators together at the same time; Gnumeric doesn't seem to mind at all! (Did I mention that Gnumeric is FREE? ;-)
In this article we will learn the basics of Moving Averages and how to generate them, with particular emphasis on the Exponential Moving Average (EMA).
Let's begin!
Take a look at the picture at the top of this article. It shows a green price line rising steeply, but with a few jagged areas and pauses where the price drops down a bit, then picks up and resumes its upward trend.
That graph is derived from real Sugar futures prices from the winter/spring of 2008. When the price of sugar was about 10 cents per pound (bottom left of chart), I bought a Call Option with a strike price of about 11 cents. It cost me around $400 if memory serves. By the time the price reached the upper right-hand corner of the graph (about 14 cents/lb) about a month and a half later, I had already sold that option for a profit of about $900. Sugar definitely tasted sweeter that day!
Traders love steady upward trends like that one, and Options traders love 'em whether they're going up or down! Money can be made in either direction.
But did you notice that large jagged area about 1/3 of the way in from the left? When the price suddenly dropped, I almost bailed out -- after all, how could I be sure that the price would ramp all the way up to 14 cents a month or two later? I can't know the future, can I?
Well, no, but one of the assumptions we technical traders make is that where there is some momentum building up, it will probably continue for a while. Moving averages look at where the price has been, smooth out many of the sharp ups and downs, and sort of 'predict' where the price COULD be in the near future based on where it has been in the recent past.
Traders love moving averages because they "average out" the "noise" -- some of the randomness -- in a price series and clarify the underlying trend, if there is one. If you're an electronics engineer, you will recognize the moving average as the equivalent of a Low-Pass Filter. Momentary price glitches don't last long by definition, so they tend to get filtered out. Long-term trends are passed through the filter, unaffected by it. (Since time and frequency are the inverse of one another, you can see that passing a long period of time is equivalent to passing a low frequency, in engineering terms.)
The red line in the graph is a 10-day Exponential Moving Average of the raw prices in green. In general, we can say that if the green price line is ABOVE the red average line, prices are trending upward and we should have BOUGHT (or "gone long") as early as possible, with the goal of SELLING (cashing out) as soon as the price has peaked and begun to fall back into a downward trend. We notice that in a downtrend, the green price line is generally BELOW the red moving average.
A moment ago I pulled a little trick on you. I described the green price line as being either ABOVE or BELOW the red average line. But that's really just an optical illusion, or an illusion of perception. The red line is actually to the RIGHT of the green line at all times!
Look at it again. Can you see that the red EMA is always to be found to the RIGHT of the green price line? That's because it's always "late" -- traders call this a "lagging indicator." Our intuition tells us that it would be nice if we could shift the whole red line over to the left just a bit, so that it sits "in the middle" of the green plot. Some software actually does shift it over -- or, more accurately, shifts the green line over to the right so that they appear to coincide. But notice that it's only in appearance.
The reason we can never really HONESTLY cause the red EMA to be shifted to the left is that THIS WOULD IMPLY THAT THE FILTER COULD PREDICT THE FUTURE!
I know that sounds a bit over-dramatic, but it's true nonetheless. Since an average has to "look backwards" at all the numbers that came BEFORE, and add them up and divide by the number of them, then the "answer" cannot precede the "question" (i.e., the numbers that came before, which contribute to the answer). Pretty profound when you think about it.
So why do traders "hate" moving averages? Because we are all "trying" to predict the future (our assumptions about tomorrow's likely price) based on a consensus from the past! The price HAS been rising recently, so I am willing to bet that it will KEEP ON rising in the near future. And usually it does. But sometimes it drops suddenly, and we can debate all day long about the "scare" in the markets that caused traders' "fear" to outweigh their "greed" and suddenly tip the scales against us. In the field they would call that Fundamental Analysis; I belong to the camp that believes in Technical Analysis-- i.e., let the market price tell me what I should do. I don't necessarily care why the market did what it did (except when there is really important economic or market news that is impacting prices much more than usual). When a Fundamentalist accuses me of simply "reacting" to past price movement, and not being in touch with "root causes" -- which implies that the Fundamentalist thinks he can predict the future based on having the "inside scoop" on present market conditions -- I respond that his prognostications are really no better than mine, when I make short-term assumptions based on recent price trends.
Now as we're desperately trying to predict the (likely) future by studying the immediate past (moving averages), Professor Know-It-All is smirking on the sidelines, because he has always said that the Markets are just random "Brownian motion" -- noise. So at ANY time, tomorrow's price could pop up or down without rhyme or reason.... and so the good Professor sees us technical traders as perennial fools chasing after the pot of gold at the end of the rainbow.
But our electronics engineer knows something that the statistics guy doesn't... All noise can be filtered. And the more you filter out the high frequencies (shorter time periods) the more "trend-like" and more patterned and less random the noise begins to look. So we can ride random waves in the surf, as long as they are big enough to last long enough to carry us several hundred feet to shore. Their origin was "random" but their existence, while it lasted, was definitely a "trend," and a good surfer rides it as long as he can. The price we pay for low-pass filtering is that we have more phase-lag (time delay, lagging average line). So one thing we would like to be able to do is to find a loophole that enables us to, er, "cheat" Time. We will take that up in a future article. No pun intended.
The other thing traders hate about Moving Averages is when the surf isn't particularly good-- no big waves but just some choppy seas. In trading it's called being "whip-sawed". Moving averages work great when there's an underlying trend, but the boat just keeps getting rocked back and forth when seas are calm to choppy. The trader goes broke paying commissions to his or her broker (that's why they're called "brokers" I suspect) as the price chops up and down just enough to get the trader to keep switching sides (buy -- sell -- buy -- sell).
There are basically 3 kinds of Moving Averages: Simple, Weighted, and Exponential. I will describe simple and exponential moving averages here.
The simple moving average works just like the method of calculating an average you were taught in school. For example, if you want to average 3 numbers (let's say 3, 7, and 9), you would add the numbers together and then divide that sum by how many numbers there are. So the formula would be (3+7+9)/3 = 6.333.
On a price chart, you would calculate the simple average of today's closing price [I use closing prices and ignore the open, high, and low of the day], yesterday's, and the day before yesterday's. Even though that's only 2 days back, it's 3 day's total when you count today. You then plot that point on your Gnumeric spreadsheet. For each new day, you repeat the procedure, connecting all the points into one continuous plot of the moving average (except that days 1 and 2 of your data cannot be computed -- i.e., the average line has to start AFTER them, on day 3, since days 2 and 1 don't have any "two-days-back" data to work with.
So if your spreadsheet column A contains the raw price data -- let's say 180 days' worth -- then you could calculate your Simple Moving Average in column B by skipping down 2 rows and entering your average formula at cell B3:
"=(A1+A2+A3)/3" [leaving the quotes off and pressing the Enter key].
But there's an easier way: just use Gnumeric's (or Excel's) pre-made Average function, which would be typed in like this:
"=Average(A1:A3)" .... after which you simply highlight the cell containing that formula, COPY it, then skip down to the next cell below it (B4) and highlight that and ALL cells after that in column B, to the end of the price data in column A.
Now PASTE what you copied, and the spreadsheet will automatically put a copy of the average formula, adjusted for the day and 2 days back from it, into each cell.
At this point you should have two columns (A with prices and B with slower-varying averages of column A) and you can click on the CHART icon at the top, to create a graph similar to the picture at the start of this article.
(I like to use green or white for my raw prices and red for my moving average -- but that's just me.)
So what's wrong with using the Simple Moving Average formula all the time? And how does an Exponential Moving Average differ?
Well, the problem with simple averages is that they assign every number making them up as having the same importance, or "weight". But that is unrealistic when we are talking about daily market price activity. To illustrate, what we had for breakfast today is naturally more important than what we had 10 or 20 days ago. We tend to forget after a while, all other things being equal. So we are unconsciously assigning more WEIGHT to today's (and recent) breakfasts than we are to what we had for breakfast 30 days ago. Likewise, what the market is doing NOW is more important than what it was doing last month, and we don't expect the day-to-day prices changes from a month ago to have much influence on today's moving average. But we DO expect the price fluctuations over the last few days to matter.
Exponential Moving Averages take the above into consideration, and that's the kind of average I use almost exclusively. An EMA starts right away on the graph (you don't have to skip 29 rows and start on the 30th like you would for a simple 30-day average) and flows smoothly.
Now we come to the heart of this article: How to create your own Exponential Moving Average (EMA) from the raw price data in your Gnumeric spreadsheet.
The first thing we have to do is decide how many days' worth of averaging we want.
Let's assume a 10-day EMA. [By the way, if you're trading shorter time frames, such as in FOREX where you might be on a one day, one hour, or one minute time period, this EMA formula still works. We just call it a "10-period" EMA instead of a "10-day" EMA.]
Here's the magic formula:
EMA = (ndays - 1) / (ndays + 1) * yesterday's price + 2 / (ndays + 1) * today's price
Let's re-write that using a 10-day period:
EMA = (10 - 1) / (10 + 1) * yesterday + 2 / (10 + 1) * today
EMA = 9 / 11 * yesterday + 2 / 11 * today
What this equation is saying is that we're going to start with about 81.8% of the "memory" of yesterday's price, and "update" it slightly by adding a fraction -- about 18.2% -- of today's price. This gives us a slowly-varying moving average that "smooths out" the bumps in the raw price.
One more example: A 30-day EMA of a stock price would be
EMA = 29 / 31 * yesterday + 2 / 31 * today
(The "2" in the second fraction is a constant. The asterisk "*" means "multiply by".)
Our electronics engineer stares at this equation and recognizes a moment-by-moment description of a capacitor charging and discharging electrical current, smoothing [integrating] abrupt variations in voltage. A capacitor stores ("remembers") its most recent charge; an EMA "remembers" recent prices along with seeing the current price.
Now let's put that 10-day EMA into column B of our Gnumeric Spreadsheet.
Column A already has 180 raw prices, each entry representing one day's closing price. The cells holding the prices are called cells A1 - A180.
At cell B1 we don't have any data from "yesterday", so we will just have to make it identical to the first raw price. In cell B1 we type, "=A1" (without the quotes) and press Enter.
At cell B2 we are ready to type in our Magic EMA formula:
= 9 / 11 * B1 + 2 / 11 * A2
where B1 is yesterday's price and A2 is today's price.
Now, highlight cell B2 and COPY its contents to the clipboard, move down to the empty cell B3 and PASTE, then press the Enter key. B3 should contain an 'updated' version of the formula with its rows incremented by 1:
= 9 / 11 * B2 + 2 / 11 * A3
Repeat for all subsequent rows by clicking and dragging until you have a highlighted column B all the way from B3 to B180. Paste and hit Enter, and all 180 rows of column B should have a duplicate of our Magic Formula in them, with updated row numbers in each cell.
When you create a graph with Gnumeric, you do it by first highlighting the entire "A" column (raw prices) then clicking the chart icon at the top of the page. Choose "line chart", press the Insert button at lower right, and then left click your mouse and "drag open" a chart on a blank section of your spreadsheet. It will default to a navy blue plot of your daily prices.
Right-click on the graph and select Properties, and you will see "Series1" as one of the components of your new graph. Clicking on that allows you to replace "Series1" with a meaningful name like "Prices", and to select another color and thickness of the price line, if you choose. You will also notice that Series1 has a "range" of "Sheet1!$A:$A". Click "Apply" and your customized price chart should be on screen.
Going into Properties once again, and selecting "Prices", you can now click "Add" and tell Gnumeric you would like another plot named "EMA10", with a range of "Sheet1!$B:$B". Select a color and click Apply, and you should have a price chart with a jagged price in one color, plus a smoother 10-day EMA line of a different color.
Play around for a few minutes and you will discover much more about how Gnumeric lets you customize things, than I can explain in wordy paragraphs!
One problem with moving averages is that they're fixed-period indicators. I.e., a 30-day EMA will work well when prices are trending gradually over 30 days or so. But for sudden, short-term price rallies and drops, the 30-day average is too slow and can't keep up -- the time lag guarantees that you will be whip-sawed if you try to trade the markets with it.
A solution to this is to plot TWO or more EMA's in addition to raw prices. This gives you some "confirmation" or reinforcement in your trading decisions. For example, you might have a 10-day and a 30-day EMA along with your price graph. When the stock price shoots up above the 10-day EMA, it may be time to go long or buy a Call option. When the 10-day EMA now crosses above the 30-day EMA, you have a confirmation that the overall trend is UP and that you should be buying. One of the popular early trading methods involving a price and two moving averages was the system invented by Richard Donchian, which used a 5-day short-term average and a 20-day long-term average.
The idea of moving averages taken to the ultimate can be found in the "Guppy" trading system often discussed among FOREX traders. The Guppy system uses multiple sets of closely-spaced, color-coded moving averages that twist and turn like ribbons, with crossover points that help traders visualize the best places where the market appears to be reversing.
Another class of indicator is the "MACD" (Moving Average Convergence Divergence) which uses at least 3 EMA's in such a way that much of the nasty "time lag" that plagues moving averages is eliminated by a couple of mathemagical tricks. The MACD indicator is immensely popular among traders and I will cover that in another article.
What you can do is limited only by your imagination and willingness to sit down and learn to make Gnumeric (or any other spreadsheet with graphing capability) do whatever that fertile mind of yours can come up with.
So now you know the gory details on one of the most important tools in the technical trader's bag of tricks. Wasn't THAT bad, was it?
In the near future I hope to be able to write more articles like this one, because I know there are people out there just like me, who want to understand technical indicators enough to be able to "roll their own."
Happy trading!
Learn about a "Magic Code" that Forces Any Web Site to Make Money!
Hit Counter by Digits |
If you trade Stocks, Commodities, FOREX or Options and have always wanted to know how to calculate your own technical indicators using a spreadsheet like Excel, you've come to the right place! Who says you have to buy expensive chart analysis software when you can make your own price charts for free? True, you may not have all the features the "big boys" use, but if you're a techno-geek cheapskate (like me), it's nice to know you can create your own indicators like Moving Averages, MACD, RSI and others, with the spreadsheet you already have on your pc. "Rolling your own" gives you complete control over your analysis and lets you customize, optimize, and even create new indicators to help you make trading decisions with an "edge."
