Not a question on "candlestick charts", but here is a solution that works. (Numbers 11)

Good afternoon, all.

Long time listener, first time caller.

With all of the questions over the years where more folks are getting involved in their own financial tracking, the "candlesticks" are one of the easier ways to get the days stock information in one metric.

This being my 7th iteration over time from OCD counting pennies every time I spent ANY money, then pen and paper and such through other programs that A) I am too cheap to subscribe to (or otherwise pay for) or B) dont have the functionality or customization I am looking for.

(data randomized to demonstrate sample)

Far from finished, I continue to add bits or tweak bits as my learning improves. Enter, stock and portfolio tracking.

Candlesticks are neat and fancy and Excel-y and Numbers just doesnt do it. Not satisfied with that answer and Apple clearly doesnt care so much (unless it is in a newer update) It does occur to me a number of other combined bits from other solutions can work together in a solution for those that still want one. I couldn't find an answer, anyways.

Here is what I did to get a workable candlestick-y chart. Scatter chart was the best solution it seems, I couldnt get the rest to work out quite right.

With Table 3 as such: Header rows not necessary, but lables dont hurt anything.

Using A1 in the header row, I put the ticker and filled in the row with a Date and "Stockh" for the details. As I realized I could use the error bars, I changed the low and high to figure the actual difference in the prices. (Postive Error = High : Negative Error = Low)

Using the second series for the close I had to add the date again. Sharing the X values did not allow me to customize the error bars, so this had to be done.

Highlight chart, edit values, select the series with the OPEN price, then edit Y Axis Error Bars

Once that was set up...

Almost a candlestick chart...In my case, this is how this particular scheme works. You can, of course, set up your own colors. I just used the default as I am still fiddling with it.

1) The blue dot is the open.

2) The green dot is the close.

3) The body is the distance from the open, which it is on a real candlestick, to the close. This just isnt a box that is or is not shaded depending on the position (over or under) of the green dot in relation to the blue dot.

4) Using the error bars, I can draw the shadows to show the trading range for the day.

So, in the sample, we have two days of gains followed by an almost great day three, which started as a gap up, but ended closing just above the previous close. If this were a box plot, I imagine we could color it red to note the close was lower than the open, but there it is. The details are all still there. Then day four opened lower but gained well to close above the previous close again. We could even add a trend line for the green to show the overall trend for the close (or the open for that matter)

If I could figure this with a box plot it could be better, but such is my effort for the time being. Box plots also not seeming to be a thing Apple likes to tinker with it seems, I couldnt find that answer either.

I mentioned I couldnt get it to work with a line chart or other series because of the error bars, but if there is another way (particularly a box plot) I would be very interested in hearing about it.

And, I know what some of you may be thinking and this point, "If you would just send the Python in...(or AppleScript even)" Next on my list, I think. I get too far with this and this poor old sheet is gonna start bogging down. It may ultimately come down to good old fashioned coding. Training first, fancy stuff later.

However, for a quick peek at a 30 day sample for research or review, not a bad idea. Small chunks of data would work well with what I have, but once I get to my full and real portfolio and analysis thereof, its likely gonna choke on that data after a year or two. Especially considering contributions, taxes and other withdrawals.

Then again, who says I have to have daily data on every stock, investment and whatever? In the end, it really is about the total and when I can exit stage left even. I can simply do quartely or monthly or whatever timeframe I need it to be and change the table accordingly. As long as the dates match in each X column it works.

So, there you have it. A candlestick-y chart that the whole family can enjoy.

Thank you for reading.

Have a great morning, afternoon, evening. (Circle one)

Hawklin Lane,

Rookie Spreadsheet Development

Expert Self Development

===

"I will not let my schooling interfere with my education."

--- Samuel Clemens (Mark Twain)---