+ Reply to Thread
Results 1 to 7 of 7

Creating a candlestick chart with a horizontal line - can anyone help?

  1. #1
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    605

    Creating a candlestick chart with a horizontal line - can anyone help?

    Hi all,

    I have the following time series and I'd like to create a visual chart of it. Please see attached Excel file.

    1) I drew a horizontal blue line manually by hand on my charts. It's supposed to be the Open price at 09:31 (I bolded it out in the Excel file).

    Is it possible to have Excel create this automatically?

    2) Is it possible instead of a continous time plot at the X-axis to have Excel show only selected times...? For instance, 09:31, 10:00, 11:00, 12:00, 13:00, 14:00, 15:00 and 16:00?

    I think this would help create a cleaner chart.

    Thanks in advance for any help!

    Best,

    Elijah
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,640

    Re: Creating a candlestick chart with a horizontal line - can anyone help?

    I added a helper column G and placed the formula =$C$2 in G2 and copied it down. Then plotted a new series on the secondary axis and adjusted the min and max bounds of the secondary axis to match with that of primary axis.

    See if that is what you were trying to achieve.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    605

    Re: Creating a candlestick chart with a horizontal line - can anyone help?

    Great! Thanks a lot.

    Do you have any idea if it's possible to also clean up my X-axis with only whole hours for time? Not very important, but I think I'd like it if possible without too much trouble.

    It's embarrassing, but I couldn't quite figure out how you added that series to the chart, but it should be easy I think. Just didn't have time to Google it now as I'm headed out.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    13,921

    Re: Creating a candlestick chart with a horizontal line - can anyone help?

    The attached file shows the whole hours in the X axis. A helper column (H) has been added to the data range. Cell H2 simply gets the time from cell B2 and is formatted [h]:mm
    From H3 down the cells are populated using the formula: =IF(TIME(B3*24,0,0)<>TIME(B2*24,0,0),B3,"")
    The formatting for H3 and down is: [h]
    To add the X axis series
    1) Select the X axis,
    2) Right click,
    3) Choose 'Select Data',
    4) Under the 'Horizontal Axis Labels' select the 'Edit' button,
    5) Paste the following in the 'Axis label range' window: ='Ark1'!$H$2:$H$391
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    605

    Re: Creating a candlestick chart with a horizontal line - can anyone help?

    Thanks a lot, JeteMc.

    Exactly what I wanted. Thanks a lot!

  6. #6
    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
    59,037

    Re: Creating a candlestick chart with a horizontal line - can anyone help?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    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.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    13,921

    Re: Creating a candlestick chart with a horizontal line - can anyone help?

    You're Welcome and thank you for the feedback. As per AliGW's post, please mark the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Horizontal Line in a Bar Chart
    By pedro90 in forum Excel General
    Replies: 1
    Last Post: 11-14-2017, 06:55 AM
  2. Horizontal line on a chart
    By kenelder in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-23-2013, 11:54 AM
  3. Creating horizontal line chart with categories in y-axis
    By riya.arora. in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-09-2013, 04:55 AM
  4. Creating a Weekly Candlestick chart
    By IamNotaDoctor in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-21-2013, 07:45 PM
  5. [SOLVED] Add line to horizontal bar chart
    By Hrafn in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-21-2006, 07:45 AM
  6. Horizontal Line Chart
    By Volsfan in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-08-2005, 08:05 AM
  7. Horizontal line on line chart
    By Lee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-23-2005, 03:06 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