+ Reply to Thread
Results 1 to 15 of 15

Creating a combo Stock-Candlestick chart & Line Chart

  1. #1
    Registered User
    Join Date
    12-17-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    30

    Creating a combo Stock-Candlestick chart & Line Chart

    Excel doesn't allow you a combo Stock chart & a Line chart.

    There is a 2016 comment (and attached excel sheet) on this forum which seems to have managed to do it.

    I am new here & hence unable to post a link to that comment but this is it - /excel-general/1166181-candle-stick-chart-with-line-chart.html#post5202257

    You can prepend it with this forum's url to get to this comment.

    I read his comments & also looked at the excel sheet he has attached, but I am not able to figure out how he managed it.

    Are there more detailed steps on how to do this?

    I have a stock chart - I have been drawing lines on it manually - multiple non-connected lines - these lines do not pass through or connect any of the points on the candle itself - it's based of other data which I would like to look at together with the candlestick chart.

    Instead of drawing it manually, I want excel to do it.
    Last edited by AliGW; 12-18-2019 at 07:55 AM. Reason: Solved tag added - no need to edit thread title or add solved to post. Thanks.

  2. #2
    Registered User
    Join Date
    12-17-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    30

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    Ok - I have been able to go further here.

    From the original comment who partial URL I have given in my original question

    Take a look at the attached file, there is a Candle Stick Chart with Line Chart created in Excel 2010. To build the chart I added all data series as line chart, moved the series for candle stick (open, high, low, close) to the secondary axis, added high-low lines and up-down bars, made line charts on the secondary axis invisible, then setup primary and secondary vertical and horizontal axes, colors, etc.
    So the trick seems to be to not create a Candle Stick Chart but instead create a line chart & then add Up-Down Bars & High-Low lines to make the Line-Chart look like a candlestick chart. I tried this & it works fine, except that I get lines connecting the high & low points.

    The original comment also has a trick to avoid this - " made line charts on the secondary axis invisible" - However, I can't figure out how to do this - make the line chart on the secondary axis invisible.

    Does anyone know how to do this?

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    58,967

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    See the yellow banner at the top of the page - share your workbook with us.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Registered User
    Join Date
    12-17-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    30

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    Hi Ali,

    I think my account is too new to be allowed to upload stuff. Anyway, I think I have figured out the invisible part also (it's by making the transparency 100%).

    I am away from my worksheet computer currently, but I will try this out later tonight & or tomorrow morning & then upload the final solution if I do have enough permissions to do that.
    Last edited by AliGW; 12-18-2019 at 03:51 AM. Reason: Please don't quote unnecessarily!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    58,967

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    There are no restrictions on uploading attachments - which is what the instructions in the yellow banner, if you follow them correctly, will allow you to do. What you may not do yet is post a link, but that's not what I asked for, anyway. I would not ask you to do something I knew you could not do!

  6. #6
    Registered User
    Join Date
    12-17-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    30

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    These are the steps for Excel 2016 to create a Combo Candlestick(Stock) + Line Chart which excel doesn't allow you to create. The trick is to create Combo Line Charts & then add High-Low Lines & Up-Down bars to make it look like a Candlestick chart.

    1) Create a chart from the following data
    Sheet1!$C$1:$G$11

    2) The Chart should be of type Combo. Each Series should be of type Line Chart
    3) For the Open, High, Low & Close, click on Secondary axis so that these 4 series are on the Secondary Axis
    4) In the chart, using Format Axes, make the minimum & maximum of both primary & secondary axes the same
    5) Add the following 2 Chart elements - High/Low Lines & Up-Down Bars
    6) In the Chart, Click on the High Line, Right Click on Format Data Series & then make it's transparency 100%. This line will become invisible
    7) Repeat (6) for the Low Line, the Open Line & the Close Line - so those lines also disappear.

    You can change colors etc for the lines, Up Bars, Down Bars etc

    In my attached charts, the Up bars are Green, the Down bars are Red. The line I drew using the 5th column is Black
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by ExcelBoy90; 12-18-2019 at 05:33 AM.

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

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    You can add a line to a standard stock chart if you want.

    Create the stock chart excluding the "Line" data series.
    The use Select data to add a new series.
    Delete secondary axis so the line will align to primary axis.

    See attached for example of stock chart and stock chart + line
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  8. #8
    Registered User
    Join Date
    12-17-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    30

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    I can see in your xls that you have done it, but I am unable to do the last 2 steps correctly.

    After creating the stock chart with Sheet1!$C$1:$F$11 (I get the same stock chart as you have), I click on the chart, right click on Select Data & then click on Add
    Then add a new series with series name =Sheet1!$G$1 & series value =Sheet1!$G$2:$G$11

    The moment, I do this, my chart changes into what I have shown below which is quite different?
    Also how do I "Delete secondary axis so the line will align to primary axis." - if you look at my screenshot below it doesn't even have a secondary axis.

    What am I doing wrong?
    Attached Images Attached Images
    Last edited by ExcelBoy90; 12-18-2019 at 06:20 AM.

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

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    Once added you need then select the "Lines" series. It does not show as it is a non formatted line at the base of the floating columns.
    Select the chart, use the Format tab on the ribbon. Within the first group use Current Selection drop down to select the series.
    You can now format it to the secondary axis.
    Select the secondary value axis and press delete

  10. #10
    Registered User
    Join Date
    12-17-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    30

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    Worked. Thanks a million. This is so much easier than my way.

    Just for my benefit (and also for anyone else), let me document the detailed steps after creation of the Stock Chart.

    1) Add the Line Series through Select Data
    2) Click on Format Tab. In the Combo choose the Line Series. This will highlight the points on the Line Series
    3) Right Click on the highlighted points & Click on Format Data Series. Choose Secondary Axis. Also go to Fill & Line in the Format Data Series & choose Solid Line under Line.
    4) Click Design Tab, Add Chart Element, Click on Axes, & uncheck the secondary axis.
    Last edited by ExcelBoy90; 12-19-2019 at 03:58 AM.

  11. #11
    Registered User
    Join Date
    12-17-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    30

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    One more quick question.

    Now I want to add a second line series. I tried it out but again got quite quirky graphs. Is this possible at all?

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

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    This one has an extra 9 series.

    Just add them using the Select Data > Add, as you did for the first extra series
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    12-17-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    30

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    Quote Originally Posted by Andy Pope View Post
    This one has an extra 9 series.

    Just add them using the Select Data > Add, as you did for the first extra series
    I started with your workbook.
    1) In your chart #2 (Stock Chart), I added 2 series (Line & Extra1) simultaneously & followed the steps & everything works just fine like before


    2) Instead I started with Chart#1 (Stock Chart with 1 Line Series) & added Extra1 to it & then chose "Lines" from format Combo, right click & format Data Series, move to secondary axis & then Line-> Solid Line. And I end up with this abomination.

    Attachment 655193

    Is #2 not possible - do I have to add both series together? In which case, I would have to probably recreate any Data Labels, Data Callouts I already have on my Line Series.
    Attached Files Attached Files

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

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    No idea what you did but if I look, using the change chart type dialog, all the stock chart series are on the secondary axis as Lines.

  15. #15
    Registered User
    Join Date
    12-17-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    30

    Re: Creating a combo Stock-Candlestick chart & Line Chart

    Quote Originally Posted by Andy Pope View Post
    No idea what you did but if I look, using the change chart type dialog, all the stock chart series are on the secondary axis as Lines.
    Yeah, I also saw that. I didn't do anything special or anything different.

    - Started with your Chart#1 (Stock Chart with 1 Line Series)
    - Added an extra series (Extra1) through Select Data
    - Chose "Extra1" from format Combo to highlight the points.
    - Right click on the highlighted points & chose format Data Series
    - Moved Extra1 to secondary axis & also chose Line-> Solid Line.

    And redid it from scratch 4-5 times to make sure I didn't do anything wrong. But each time I ended up with this.

+ 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. [SOLVED] How to create line chart with bar chart combo to show for deviation
    By method90 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-16-2019, 11:05 AM
  2. [SOLVED] Creating a candlestick chart with a horizontal line - can anyone help?
    By Elijah in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 12-06-2017, 08:32 AM
  3. How to add DowJones Line Chart on to Yahoo Stock Chart
    By HerryMarkowitz in forum Excel Charting & Pivots
    Replies: 24
    Last Post: 02-10-2015, 05:49 PM
  4. Creating a Weekly Candlestick chart
    By IamNotaDoctor in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-21-2013, 07:45 PM
  5. Stock chart - combo of line and bar chart
    By cw56 in forum Excel General
    Replies: 2
    Last Post: 11-15-2010, 06:19 AM
  6. how to chart stock and line in a chart
    By Valley in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 08-30-2007, 03:33 AM
  7. Line Chart (Stock Intraday Chart)
    By prabs in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-25-2007, 06:19 PM

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