tag:blogger.com,1999:blog-10867835180415341792016-09-07T21:26:58.351-07:00Exponential Moving AveragePinoreply@blogger.comBlogger7125tag:blogger.com,1999:blog-1086783518041534179.post-77644029702619926452013-04-16T12:41:00.000-07:002013-04-16T12:41:42.663-07:00Calculate Exponential Moving Average in excel<div dir="ltr" style="text-align: left;" trbidi="on"><div style="text-align: justify;">How to calculate <b>Dow Jones Exponential Moving Average</b> - illustrated with data in <b>excel spreadsheet</b>.</div><div style="text-align: justify;"><br /></div><div style="text-align: justify;"><a href="http://www.ziddu.com/download/4335867/DowJonesExponentialmovingaverage.xls.html"><span class="Apple-style-span" style="font-size: large;"><b>Download excel spreadsheet showing exponential moving average calculation from here.</b></span></a></div><div style="text-align: justify;"><br /></div><div style="text-align: justify;">What is <b>exponential moving average</b>(ema) and how is it different from <b>simple moving average</b>(sma)? Simple moving average takes an average of the no of days. So a 20 day <b>sma </b>would calculate an average of all the figures for preceding 20 days. In <b>EMA</b>, we give more weight to recent observations. This is done via using a factor called <b>exponent</b>.</div><div style="text-align: justify;"><br /></div><div style="text-align: justify;">The value for the exponent = 2/(No of days +1)</div><div style="text-align: justify;">So for EMA of 20 days the exponent = .047619</div><div style="text-align: justify;"><br /></div><div style="text-align: justify;">Now we multiply today's closing figure with this exponent, and yesterday's EMA with (1-exponent) to get new ema. Obviously you have to start somewhere, so we start with a simple average for the first 20 data points and then move onto the EMA formula from there. Confusing? Have a look at the <b><a href="http://www.ziddu.com/download/4335867/DowJonesExponentialmovingaverage.xls.html">excel sheet</a></b> it will become a little more clearer as to what it is that I am talking about and how exactly it is calculated.<br /><br />Label: ema excel</div></div>pinoreply@blogger.com2tag:blogger.com,1999:blog-1086783518041534179.post-33103805286085684412012-07-07T13:40:00.000-07:002012-07-07T13:40:37.390-07:00How to calculate Exponential Moving Average in Excel<div dir="ltr" style="text-align: left;" trbidi="on">How to calculate Exponential Moving Average illustrated with an excel example.<br /><br />Have a look at the spreadsheet below. <span style="background-color: white;">Once it opens to download simply press Ctrl+S</span><br /><br /><a href="https://docs.google.com/open?id=0B1Gms0A3xDrLQU0tQXc4ZklpOEU">https://docs.google.com/open?id=0B1Gms0A3xDrLQU0tQXc4ZklpOEU</a><br /><br /><br /><br />Now the formulas used.<br /><br /><br />1. Exponent function value - this is essentially the weight used to weigh each new period.<br /><br />= 2/(1+ no of periods)<br />= 2/(1+20) for a 20 period EMA<br />=2/21<br />=0.0952380952380952<br /><br /><br />2. Actual calculation of EMA<br /><br />= Previous EMA * (1-Exponent) + Current Close * Exponent<br /><br />One clarification here. For calculation of the first EMA we simply use a SMA as value of EMA needs a previous value and thus we need to start somewhere. This does not affect the value of the EMA after the first 5-6 values.<br /><br /><br />For any clarifications or queries, please leave a comment.</div>Pinoreply@blogger.com3tag:blogger.com,1999:blog-1086783518041534179.post-10339034797714256052012-07-07T13:11:00.002-07:002012-07-07T13:19:50.040-07:00Dow Jones Index Daily Historical data<div dir="ltr" style="text-align: left;" trbidi="on">For those looking for all time dow jones historical data one can download the same from here...<br /><br /><a href="https://docs.google.com/open?id=0B1Gms0A3xDrLaW94YzN4NmRnV3c">https://docs.google.com/open?id=0B1Gms0A3xDrLaW94YzN4NmRnV3c</a><br /><br />Once it has happened simply press Ctrl+S to download.</div>Pinoreply@blogger.com0tag:blogger.com,1999:blog-1086783518041534179.post-43215143339921862972012-07-07T12:50:00.001-07:002012-07-07T12:50:20.061-07:00What is the difference between simple moving average and exponential moving average<div dir="ltr" style="text-align: left;" trbidi="on">Both are similar in the sense that both serve the same purpose of trying to smoothen out the volatility of the underlying data to give a sense of the direction and trend of the data.<br /><br />How they differ is in the weightages given to each value. While simple moving average (Sma) gives equal weight to all values, the exponential moving average gives more weightage to recent events and thus is more sensitive to movements in the underlying data than the sma.<br /><br />A simple way to put it would be that a 10 period exponential moving average would behave like a 7 or 8 period simple moving average, but will be smoother and is thus better to use. Overall, Ema's have proved to be more useful than Sma's and thus one will find that many technical indicators use EMAs. Like MACD uses only EMAs and no SMAs.<br /><br />Have a look at the actual calculation of both simple and exponential moving average in another post of mine. Looking at the calculation in excel will improve your understanding of how they are calculated and move. </div>Pinoreply@blogger.com0tag:blogger.com,1999:blog-1086783518041534179.post-79928760500526752362012-07-07T12:45:00.000-07:002012-07-07T12:45:00.762-07:00What is Exponential Moving Average<div dir="ltr" style="text-align: left;" trbidi="on">Exponential Moving Average or EMA is not very different from the Simple Moving Average (SMA). Both are essentially used to smoothen a curve to get a better sense of the underlying trend.<br /><br />How they are different is that the Ema is more sensitive than the Sma. Ema reacts and moves faster than the Sma in following the value of the data. How that actually happens is that in the calculation of Ema, recent events are given greater weight than earlier events and thus it tracks the data much more closely. The Sma on the other hand gives equal weight to all values, and thus is slightly more lagged.<br /><br /><br /></div>Pinoreply@blogger.com0tag:blogger.com,1999:blog-1086783518041534179.post-86312132393738428112009-04-16T20:26:00.000-07:002013-04-16T12:40:46.278-07:00Calculate Dow Jones Moving Average in excel<div dir="ltr" style="text-align: left;" trbidi="on"><div style="text-align: justify;">Someone asked me how does one calculate a moving average of any stock or index. So here's an illustration of <span style="font-weight: bold;">how to calculate moving averages in excel </span>using <span style="font-weight: bold;">dow jones data</span>. Simply open the spreadsheet and follow the calculations, they are very simple to understand. This is the simple moving average.<br /><br /><a href="http://www.ziddu.com/download/4330610/DowJonesMovingAverage.xls.html">You can download the excel spreadsheet from here.</a><br /><br />I have also included charts in another sheet in the same file. It has a couple of <span style="font-weight: bold;">all time historical chart</span> for the <span style="font-weight: bold;">dow </span>in both <span style="font-weight: bold;">log scale</span> and normal scale. There's another chart of the 2007-09 recession and stock market crash along with a comparison with the Great Depression of 1929-32. The same charts also have the <span style="font-weight: bold;">200 day moving average</span> plotted to show how the <span style="font-weight: bold;">200 dma</span> is a key technical level.</div></div>pinoreply@blogger.com0tag:blogger.com,1999:blog-1086783518041534179.post-1086627263354230202009-04-16T13:47:00.000-07:002013-04-16T12:40:50.723-07:00Calculate Dow Jones RSI in ExcelRSI <b>calculation</b> done in excel spreadsheet to illustrate how rsi is calculated. Data is of Dow jones index, daily open, high, low, close since 1928, when the index started. <div><br /></div><div><a href="http://www.ziddu.com/download/4326900/Dowjonesrsicalculation.xls.html">You can download the excel .xls file from </a><a href="http://www.ziddu.com/download/4326900/Dowjonesrsicalculation.xls.html">here</a></div><div><br /></div><div><br /></div><div>Its very simple. Just download excel sheet and go through the formulas. In case you dont understand anything just leave a comment and I will write a post explaining exactly how to do the calculation.</div>pinoreply@blogger.com0