After reading the Japanese Candlestick Charts in Excel post, Gene asks me in an email,
I’m trying to graph candlestick charts in Excel for 10 minute candles. Excel seems to allow daily only with its stock templates. Can you point me to any resources for creating intraday candle charts?
Of course, being the smart person Gene is, he figured out that even before I could send an email back (he used box plots to mimic 10 minute candles)
But there should be a way to make intraday candlestick charts using the regular stock chart in excel. Isn’t it?
Well, there is a way.
As an aside, you might wonder what an intraday candlestick is?
Well, I am no day trading expert nor a technical analyst of stock markets, but statistically speaking, there can be high, low, open and close values for any given interval. Although, on a busy day, you might find that close of a 10 minute interval is equal to open of the next 10 minute interval. But, these days, the stock markets are seldom busy (unless you are busy selling 😉 ). Again, I digress. So going back to the intraday candlestick charts,
1. First get the intraday stock price movement data
2. Now, Create a regular candlestick stock chart
When you are done, the chart should look something like this:
3. Select date axis (horizontal axis) and press ctrl+1 (or right click and go to format axis)
And change the axis type from “date” to “text”
Also, set the interval between labels to 3 or something like that. This will reduce the label clutter.
That is all. You will now have an intraday candlestick chart that looks like this:
Download the intraday candlestick chart template and play with it.
11 Responses to “Who is the most consistent seller? [BYOD]”
The Date column in the sample file is Text not Dates
[…] http://chandoo.org/wp/2015/02/18/calculating-consistency-in-excel/?utm_source=feedburner&utm_med… […]
Great Chandoo. Keep it up, Looking forward more from BYOD..
Thanks
With Excel 2013 the pivot table could be connected to the data model which provides a distinct count.
This will do for invoice count
=COUNTIF(F:F,H12)
Instead of
=COUNTIFS(sales[SELLER],$H12)
Excellent document. How did you make the last graphic? Witch app. Thanks for answer.
Can someone tell me what =countif(sales[date],sales[date]) is counting? The value is 19. Its found in the =SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))
Hi Chris,
=countif(sales [date],sales[date]) function is counting the unique dates in the table.
Vândalo
Excellent document!
Can you explain more about the calculation on Weighted consistency? More specific the small number is 0,00001 ?
How come the number should be smaller if there is more sellers?
Hi,
Not understood this formula: {=SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))}
Please explain.
Thanks.