+ Reply to Thread
Results 1 to 13 of 13

exponential moving average

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    exponential moving average

    I've found a few examples of methods to calculate an exponential moving average, but they all seem a bit clunky. Anyone know of a simple method to calculate this?

    Thanks so much!

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: exponential moving average

    I would be curious what other examples you have seen and what makes them "clunky". I also wonder if there are multiple ways to compute EMA.

    This website has a sample spreadsheet, and their EMA seems very simple: http://stockcharts.com/school/doku.p...oving_averages The formula they appear to be using is smoothingconstant*(currentprice-previousEMA)+previousEMA. I am not sure how we are defining "clunky" but this does not seem clunky to me.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: exponential moving average

    Hi, yes I had looked at that formula earlier today. It requires two "helper" columns (seen in my view as "clunky"). I suppose I could wrap all this together into one column, but was hoping Excel itself offered some kind of function to shorten the formula.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: exponential moving average

    I see. As one who really likes to use helper columns, I found the proffered solution anything but clunky. Three columns with three simple formulas. The SMA column, of course does not really figure into the EMA calculation.

    The only thing that I thought was kind of clunky about this spreadsheet was column F. Column F is just a constant value all the way down. Unless there were some reason to have multiple copies of the smoothing constant (perhaps to have the smoothing constant change during the course of the analysis), I would have put a single copy of =2/11 into a single cell, then used an absolute reference in the formula of column F (eliminating one of the helper columns, again, assuming there was no reason for having the option for a changing smoothing constant). If column G is the essential calculation, and column E is not part of the calculation for column G, is column F the only helper column that could be eliminated?

    I am not aware of a built in EMA function, nor am I aware of a good "search engine" that would find such a function in Excel's long list of built in functions. Here's a simple list of functions: https://support.office.com/en-us/art...d90033e188#bm5 you can browse through and see if you find one.

    Beyond that, it might just be a difference of opinion of what constitutes "clunky". I know that many of the other users here get much more excited about eliminating helper columns. Perhaps one of them will chime in.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: exponential moving average

    I am an avid supporter of simple-easy-to-edit-formulas across multiple columns over the the mega-formula-often-massive-arrays-difficult-if-not-impossible-to-read-in-one-column solutions.

    Columns are free, and can be easily hidden.

    My 2 cents.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  6. #6
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: exponential moving average

    Quote Originally Posted by MrShorty View Post
    I know that many of the other users here get much more excited about eliminating helper columns.
    Catch me on a slow day and I am one such user. Using the link provided by MrShorty in post 2, I think the following formula will work when posted in J3 (the first row of data) and filled down.

    =IF(COUNTA($D$3:$D3)<$K$2,"",IF(COUNTA($D$3:$D3)=$K$2,AVERAGE(OFFSET($D3,0,0,$K$2*-1,1)),(2/($K$2+1))*($D3-J2)+J2))

    It relies only on the price values starting in D3 and the period of days you'd like considered, a variable I added in cell K2. My results seem to line up correctly for the 10 day sample and I think they should be correct as you vary the days, but I only learned about EMA this morning, so you might want to double check a few values with smaller/larger time periods. Take a look at the sample to see if it is sufficiently de-clunkified:
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: exponential moving average

    Another way, using the initialization described at https://en.wikipedia.org/wiki/Moving...moving_average

    B
    C
    D
    K
    L
    1
    Days:
    2
    Date
    Price
    10
    3
    1
    24-Mar-10
    22.2734
    22.2734
    K3: =IF(B3=1, D3, 2 / (K$2+1) * D3 + (K$2-1) / (K$2+1) * K2)
    4
    2
    25-Mar-10
    22.1940
    22.2590
    5
    3
    26-Mar-10
    22.0847
    22.2273
    6
    4
    29-Mar-10
    22.1741
    22.2176
    7
    5
    30-Mar-10
    22.1840
    22.2115
    8
    6
    31-Mar-10
    22.1344
    22.1975
    9
    7
    1-Apr-10
    22.2337
    22.2041
    10
    8
    5-Apr-10
    22.4323
    22.2456
    11
    9
    6-Apr-10
    22.2436
    22.2452
    12
    10
    7-Apr-10
    22.2933
    22.2540
    13
    11
    8-Apr-10
    22.1542
    22.2358
    14
    12
    9-Apr-10
    22.3926
    22.2643
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: exponential moving average

    Many thanks everyone. I think in my case, over the years I've developed an idea that every cell with a value gets recalculated each time Solver runs an iteration. Thus, it is my understanding that... the fewer cells in a workbook, the faster Solver will run. Since I use Solver every day, and its going through perhaps 20,000 iterations, I'm always looking for ways to reduce the # of cells/columns/worksheets in my workbook.

    And that's how I came around to asking the question on the EMA.

    I always appreciate the input from this forum. Thanks!

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: exponential moving average

    That's a wrong idea.

    Solver doesn't recalculate any formulas; it triggers calculation by changing cell values, and Excel decides what needs calculating as a result.

    Here, the difference would be trivial, but it would make more sense to calculate alpha and 1-alpha once, instead of calculating them 20,000 times down the column.
    Last edited by shg; 09-19-2017 at 07:23 PM.

  10. #10
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: exponential moving average

    Oh, thanks. Sometime in the past I had read that it recalculates every cell. I realize now that this is wrong.

    But while I have not done any empirical tests, Solver seems to run faster if I have, say, 20,000 cells in a workbook, vs 40,000.

    (Note that I'm not calculating Alpha. I'm solving for the highest Sharpe (or Sortino or whatever other metric we want to optimize for), by adjusting such constants as the EMA period, risk per trade etc. Then testing on various instruments and time frames to see how robust the trading strategy is.)

    Again, thanks.

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: exponential moving average

    You are calculating alpha in order to calculate the EMA -- alpha is the 2/(numPeriods+1) term, and 1-alpha is the (numPeriods-1)/(numPeriods+1) term.

    Solver seems to run faster if I have, say, 20,000 cells in a workbook, vs 40,000.
    It shouldn't make any difference unless (a) those cells are in the calculation chain for whatever Solver is calculating, or (b) they contain volatile formulas, which is just bad design.
    Last edited by shg; 09-19-2017 at 07:22 PM.

  12. #12
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: exponential moving average

    OK thanks. I was using the term in the lingo of financial analysis, where alpha measures irregularities in performance as related to risk-free returns.

    As for volatile formulas I try to keep them at a minimum. But my workbook is cobbled together over months of trial and error, using different approaches and formulas to find a robust trading strategy. I try to clean it up now and then, but I'm sure an expert could find ways to trim it down. But we're getting off-topic here...

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: exponential moving average

    Glad you got it sorted. Onward and upward.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Having average (%), define values with exponential trendline
    By sarstep1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-01-2017, 11:53 AM
  2. Replies: 1
    Last Post: 10-20-2014, 03:20 PM
  3. Exponential Moving Average Macro
    By erock24 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-25-2009, 11:27 PM
  4. Replies: 3
    Last Post: 03-20-2009, 02:48 AM
  5. Charts 5 day moving average, 10 day moving average
    By monalisa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-28-2008, 09:50 PM
  6. Exponential Average
    By steven723 in forum Excel General
    Replies: 2
    Last Post: 11-06-2008, 03:26 PM
  7. Moving average of a moving average in the same cell
    By philroberts1983 in forum Excel General
    Replies: 8
    Last Post: 09-16-2008, 07:36 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1