+ Reply to Thread
Results 1 to 13 of 13

how to plot a moving average in a chart that has X values in reverse order

  1. #1
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    Question how to plot a moving average in a chart that has X values in reverse order

    I'd like to plot a stock chart with open/high/low/close bars ("candlesticks" format) and add a moving average to it. Because of the nature of the data and how I use it, the chart X axis has to be plotted in "reverse order". Once I do that, however, excel calculates the moving average values not starting from the right-most value on the chart, but from the left-most. How would I change this default setting?

    Attached is an example of what I'm looking for. I'd like to avoid using 2 charts overlayed on top of one another as my final product is quite large and doing this takes memory and processing speed.

    Thank you.
    Attached Files Attached Files
    Last edited by luv2glyd; 06-01-2011 at 02:01 PM.
    You either quit or become really good at it. There are no other choices.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: how to plot a moving average in a chart that has X values in reverse order

    Hi,

    John Peltier has a detailed description here http://peltiertech.com/Excel/Charts/StockChartPlus.html

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    Re: how to plot a moving average in a chart that has X values in reverse order

    Thanks, Richard. There are some good examples there, and I may be able to use this if what I'm looking for is not possible. I wanted to see if Excel can automatically generate the moving average in "in reverse" without me having to have a separate column of data for it and plotting it. Is that possible?

    Thanks

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: how to plot a moving average in a chart that has X values in reverse order

    Hi,

    I don't understand your concern.

    There is no standard Excel chart that can take 5 ranges and use a stock H/L/O/C for 4 of them and a moving average line series for the other. You'll therefore need to generate the 5th range.

    Does the attached help where I've used a helper column to reverse the order of the moving average and then plot the helper column.

    Regards
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    Re: how to plot a moving average in a chart that has X values in reverse order

    Ok, I think we're on the right track. Could you explain how you combined the stock chart with a scatter chart for the moving average? I have not been able to do that. Also, the values of the moving average do not seem to correspond with the values on the Y-axis of the chart for some reason..... How could I fix this?

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: how to plot a moving average in a chart that has X values in reverse order

    Hi,

    Try the attached instead. The original was using the 2nd x axis and the reverse order averages which I realise are not required and just complicate the chart.

    To create the composite chart just follow the steps that Jon Peltier describes in his article. Note particularly his comment that once you've added the 5th series don't be put off by the sight of the data looking wrong. That's where he says most people give up at that stage. Just follow him through to the end and you'll get the chart in the example attached.

    Regards
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    Re: how to plot a moving average in a chart that has X values in reverse order

    I tried following the example, though in excel 2007, it does not look like it's possible to combine the stock chart with any other chart type. I imported the moving average series, but was unable to change its chart type to XY scatter (or any other chart type) - Excel just gives me an error message saying it can not combine some chart types. Any idea how to get around this?

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: how to plot a moving average in a chart that has X values in reverse order

    Hi,

    It's difficult I know, but if you follow Exactly the steps outlined by Jon you'll produce the chart I showed you since that's exactly how I did it.

    I think the problem may be associated with using Excel 2007. I think I have encountered this difficulty before when using Excel 2007 so for these composite charts I generally start with Excel 2003 and when the chart is complete only then do I open the workbook in 2007.

    It may be worth checking with Andy Pope in this forum to see if he knows how to overcome this difficulty. Send him a PM and point him to this thread if he doesn't spot it and advise.

    Regards

  9. #9
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    Re: how to plot a moving average in a chart that has X values in reverse order

    OK, will do!. Thank you.

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: how to plot a moving average in a chart that has X values in reverse order

    Use the select data dialog to add another series.
    Set the new series to the calculated MA values in G5:G46

    Select the new series and move to the secondary axis
    apply secondary horizontal axis and plot in reverse.
    delete secondary value axis to force new series to use primary axis.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  11. #11
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    Re: how to plot a moving average in a chart that has X values in reverse order

    Great! Thank you. That worked great.

  12. #12
    Registered User
    Join Date
    04-30-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: how to plot a moving average in a chart that has X values in reverse order

    hi, how to i go about adding moving average on top of the candlestick chart even though it is on different series
    i can't seem to add it, excel is saying that can't be done

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: how to plot a moving average in a chart that has X values in reverse order

    vb765,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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