+ Reply to Thread
Results 1 to 7 of 7

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

  1. 12-02-2017, 10:28 AM #1
    Elijah
    Elijah is offline
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    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
    Reply With QuoteRegister To Reply
  2. 12-02-2017, 10:56 AM #2
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,648

    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.
    Reply With QuoteRegister To Reply
  3. 12-02-2017, 02:02 PM #3
    Elijah
    Elijah is offline
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    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.
    Reply With QuoteRegister To Reply
  4. 12-04-2017, 07:31 PM #4
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,532

    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.
    Reply With QuoteRegister To Reply
  5. 12-06-2017, 02:03 AM #5
    Elijah
    Elijah is offline
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

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

    Thanks a lot, JeteMc.

    Exactly what I wanted. Thanks a lot!
    Reply With QuoteRegister To Reply
  6. 12-06-2017, 02:07 AM #6
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,431

    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" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  7. 12-06-2017, 08:32 AM #7
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,532

    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. [SOLVED] Horizontal Line Chart
    By Volsfan in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-08-2005, 08:05 AM
  7. [SOLVED] 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1