Can't highlight lowest and highest points of OHLC candlestick chart in excel

Hello Friends,

I made a candlestick (Open-High-Low-close) chart in excel and facing two problems:
1. Within the chart, I want to put a arrow, text box, marker or any indicator to highlight the lowest and highest point. I tried to add two helper columns to detect the highest and lowest value but could not integrate/combine that series with the existing OHLC chart. Can someone give me an idea on how to do it?
2. When I remove last two records of the data, the last two candle disappears but the chart does not resize. What i want to do is, when the last few records are remove, the existing candle should take up the entire space and make the visible candles fat.
i have attached sample data and chart with this post. Appreciate help on the two points i have mentioned.

Thank you.

chart.png

thanks for you reply. I tried but there is no option for candlestick chart there.

One thing I have found about these built in "specialty" charts is that they work very well when you want no more and no less than what the chart is designed for. As soon as you want to add something or remove something from the specialty chart, their inflexibility becomes apparent.

In most if not all cases, these specialty charts can be easily created as "standard" charts if you work out the details. In the case of a "candlestick" (a kind of box and whisker) chart, you can easily create the chart as a stacked column chart. If you build the chart from a standard chart type, you will find little difficulty in adding additional data series/points to highlight your overall high and low.

Here's a tutorial for box and whisker diagrams that should cover all the steps needed to create your candlestick chart as a stacked column chart: https://peltiertech.com/excel-box-an...ams-box-plots/

I'm not sure I understand your objections to Peltier's tutorial. I can see that your high/low data in C and D are inconsistent. Sometimes column C contains the High and sometimes it contains the Low (same for column D). I expect it will be easier to build the chart if the raw data are more consistent (Column C always has the High value and column D always has the Low value). I also notice that in one case (row 4/3 Jan), your open and close values are both smaller than your "Low" value.

With those observations, here's what I did:

1) Since high and low are inconsistent, I add helper columns so that the high will always be the highest value for the day and the low will always be the lowest value for the day. In L1, I enter "high" and in L2 I enter =MAX($B2:$E2). In M1, I enter "low" and in M2 I enter =MIN($B2:$E2). Copy/paste/fill down.
2) Following Peltier's tutorial, I need a "bottom" column/series that will be the smaller of the open or close value. In N1 I enter "bottom" and in N2 I enter =MIN(B2,E2).
3) There's only one "box" value in this case, so I calculate the absolute value of the difference between open and close. In O1 I enter "box" and In O2 I enter =ABS(B2-E2).
4) For the positive whisker, I need the difference between the high and the higher of open/close. In P1, I enter "whisker+" and in P2 =L2-MAX(B2,E2)
5) For the negative whisker, I need the difference between the smaller of open/close and the low. In Q1, I enter "whisker-" and in Q2 =MIN(B2,E2)-M2. Select N2:Q2 and copy/paste/fill down.
6) For the overall maximum, I enter "maximum" in R1 and in R4 I enter 7. For the overall minimum, I enter "minimum" in S1 and in S3 I enter 1.
7) Now I have all of the data for the chart and I can build the chart. Select B2:B6,N2:O6,R2:S6 -> Insert stacked column chart.
8) Select the minimum and maximum series and change the chart type to "line with marker".
9) Select the "box" series and add positive error bars using the values in P2:P6
10) Select the "bottom" series and add negative error bars using the values in Q2:Q6.
11) Format chart elements as desired

By all means, if you are more comfortable doing this with a Javascript based chart engine, use the programming language that you are more comfortable with. I'm far more comfortable with spreadsheets than Javascript, so I won't be able to help with that approach. If you just had some confusion/misunderstandings about how this works, then see if my outline will help you see your confusion/misunderstandings.

I'm not sure how you got the chart you have either. I'm unable to recreate the chart. Whatever you did, it appears that you are still starting with the open-high-low-close-volume specialty stock chart. As I said before, I find that these specialty charts are useful when I don't want anything other than exactly what the chart template expects, but when I want any additional stuff or any flexibility, it is far easier to go back to a basic chart type and build the equivalent of the specialty chart using basic chart types.

I found the below article from where i took reference from ( last post by @omegastripes ) and played a bit by changing the data. It also has your reply on the post.
https://www.excelforum.com/excel-gen...ine-chart.html

based on this solution, i attached my revised file "Chart1". In this solution, neither open-high-low-close nor box and whiskers were used, It has a combination of line charts on different axis. this is where i am confused. the line and markers series and the ohlc series both start from different directions. i am totally confused and breaking my head over this since a very long time.

the latest solution you provided has different columns for whiskers- and whiskers+ and for some reason, i do not want it that way.

I am not sure if i am able to convey what and how exactly i want it to be.

I want the ohlc series (candles), volume chart and line with markers start from left the right keeping the axis on the right side like how it show up on a stock chart. Preferably I want only the volume chart on a different axis to avoid overlapping.

Now I understand how you got the chart you have.

Now that I understand how you created the chart, I notice that you have deleted the secondary axes, even though 4 of your data series are plotted on the secondary axis system. When you delete the secondary axis system like this, edits to the primary axis system don't always show up in the series that are plotted on the secondary axis system.

The relatively simple "fix" is to show the secondary axis system so that you can readily edit it. In your file I:

1) Select the chart.
2) Show the secondary vertical and secondary horizontal axes.
3) Format all four axes to have the desired formats -- including making sure that both horizontal axes have the same "show categories in reverse order" setting (checked or unchecked, whichever you want).

The challenge with this approach is going to be getting the volume data correct. Using the up-down and high-low bars to get the box and whisker effect creates an inflexibility (the secondary axis system for the line chart series must have exactly four data series on it -- no more and no less). As long as you are content with "scaling" the volume data so that it "fits" into the same vertical axis scale as the OHLC data like you show in your example, there should be no problem. If you want to be able to use the correct volume data without scaling, I don't think this "line with 4 series and up/down high/low bars + column chart" combination chart approach is going to work very well, because it forces you to have your H and L data series on the same, primary axis as the volume data.

Assuming I am understanding everything you want to do, I don't think there is any way to get exactly what you want within the constraints that you have given. I see a few ways forward on this:

1) If you can be content with scaling the volume data, then what you currently have is essentially all that is needed. A few formatting tweaks and the chart is done.
2) It is possible to eliminate the H and L data series and format individual points on the High and Low data series in order to highlight the overall max and min. This approach is difficult to implement, because you either end up manually formatting the highlighted points every time the data changes, or you end up writing a macro script to format the correct data points for you.
3) You can follow my encouragements to build this as a stacked column + line combination chart -- complete with the helper columns that are necessary to the method.

What feels like the best way forward?

3. when i remove last two rows A5:H6, the candles does not take up the chart size. after removing last two entries, i want the 3 candles turn fatter and take the entire chart space.

I realize that I have not, yet, addressed this issue. Once we get the chart built the way you want it, this will be straightforward. If we end up using a macro script, it will be relatively easy to incorporate a "define the series to be the used range in each column" step as part of the macro script. If we don't end up with a macro, we can use dynamic named ranges inside of the series definitions so that the chart only shows the used range. Or we can make sure that the chart data is in a structured table. In any case, I wouldn't worry too much about this part of the problem until you are satisfied with the methods you are going to otherwise use to create, edit, and maintain this chart. Once you are otherwise completely satisfied with the chart, then we can worry about making it scale with the number of data points.

I want the markers to take their place. Can I achieve this without coding or manually changing the highest and lowest value?

I'm not sure I understand the requirement here. As we noted earlier, the problem is that the H and L values belong on the secondary axis, but we cannot actually move them to the secondary axis because our use of the up/down bars and high/low lines will not allow us to have more than 4 data series on the secondary axis. The only way I can see to position the H and L values correctly is to change the values in column G and H. This change doesn't necessarily need to be done manually. Both axes are basic, linear axes, so a simple linear transformation can compute the value for the primary axis that corresponds with a given value on the secondary axis. See if this fits within your requirement of "without coding or manually changing the highest and lowest value:"

1) I need a table in the spreadsheet with the max and min of each axis.

Please Login or Register  to view this content.

2) I will use the TREND() function to perform the transformation. In G2, I edit the formula to be =IF(C2=MAX($B:$E),TREND($U$2:$U$3,$V$2:$V$3,MAX($B:$E),NA()). A similar edit in H2. Copy/paste/fill down.

That should position the H L markers in the correct positions. As data are changed/added/edited, it should continue to work just fine as long as Excel's "auto" algorithm for the axis limits continues to choose the same values for the axis limits of both axes. If Excel ever chooses different axis limits, it will be easy enough to update those values in the spreadsheet, or you can set the axis limits to "fixed" at values of your choosing, and change them manually when change is needed. The only thing that is "off" is that a reader who goes to column G in the spreadsheet expecting to see 152.85 in G28 (or 122.45 in L7) will instead see a value around 9E6 (and 3.7E6). The solution to this problem might be to have helper columns perform the transformation and leave the values/formulas in columns G and H unchanged.

I don't know if that falls under the restriction of "manually changing the highest and lowest values," but, as I said, I don't see a way to do this without changing those values somehow. This approach should automate those changes. The only manual changes will be when the axis limits change. If you have the foresight now to say that 100 to 160 are good choices or to choose better values for the axis limits, you can further minimize how often those manual edits will need to be made.

Again, I don't see a way to do this without changing the values in G and H. Is that an acceptable approach to those changes?

Without a macro script (which you've said you don't want), there is no way to modify Excel's default "auto" algorithm for choosing axis limits. Also, without a macro script, there is no way to automatically link spreadsheet cells and chart axis limits. At this point, you as the programmer have a decision to make. You must decide whether to make this part of the process automatic with a macro script or continue doing it manually without a macro. If you decide that you would rather have it automated and use a macro script, Jon Peltier has a solid script already developed that will link chart axis values to spreadsheet cells: https://peltiertech.com/chart-udf-control-axis-scale/ You would still need to come up with your desired algorithm for the desired axis parameters and program that algorithm into the spreadsheet.

One thing I will add here. Whether you do it manually or choose to use a macro script to automate the process of controlling the axis limits, once you decide that you are going to control the axis limits rather than leave them to Excel's default "auto" algorithm, you will no longer need the "dummy point" in row 2 to force Excel to "auto" choose a higher value for the primary axis max.

At this point, it's your decision how you want to proceed.

There is often a cost-benefit analysis to programming spreadsheets. Is it better to invest time and effort now programming a more automated solution, or is it better to save the time and effort in favor of some manual effort in the future.

I think it might be worth some effort to see if you can use Peltier's UDF without modification. In the limited occasions I've had to use it, his UDF seems pretty robust and adaptable to a variety of scenarios. His UDF has parameters for "sheet name" and "chart name," so, with 25 charts, I would see if you could make 25 copies of the UDF in your spreadsheet, supply the appropriate arguments to each copy of the UDF, and see if you can get his UDF to work for you. That shouldn't require too much of a time investment, and, if you decide it will end up requiring a complete rewrite of the procedure and you don't want to make that investment, you can drop it then.