Market Profile, Calculate POC (mode) in Excel

X

xs900

I would like to calculate the Point of Control (Mode........i.e. the price that during the timeframe has had the most volume or the price that has traded the most.)

I would like to do it in Excel..........I do not need to see a graphic of the profile, just to be able to calculate the price that has been used the most.

Do you have any ideas?

sample data:

290 08/17/05 16:00 28.65 28.71 28.57 28.62 487500
291 08/17/05 16:30 28.62 28.65 28.55 28.61 251900
292 08/17/05 17:00 28.6 28.66 28.57 28.65 147400
293 08/17/05 17:30 28.64 28.66 28.6 28.6 156200
294 08/17/05 18:00 28.61 28.65 28.6 28.63 130300
295 08/17/05 18:30 28.64 28.64 28.55 28.56 125400
296 08/17/05 19:00 28.56 28.58 28.45 28.46 223100
297 08/17/05 19:30 28.46 28.47 28.25 28.31 782500
298 08/17/05 20:00 28.3 28.43 28.26 28.3 556600
299 08/17/05 20:30 28.31 28.41 28.31 28.37 466500
300 08/18/05 14:30 28.29 28.32 28.12 28.12 375400
301 08/18/05 15:00 28.12 28.21 28.08 28.21 264300
302 08/18/05 15:30 28.21 28.24 28.16 28.22 192100
303 08/18/05 16:00 28.22 28.232 28.13 28.17 157700
304 08/18/05 16:30 28.17 28.21 28.11 28.11 155400
305 08/18/05 17:00 28.12 28.17 28.03 28.04 163900
306 08/18/05 17:30 28.03 28.07 28.02 28.05 130300
307 08/18/05 18:00 28.04 28.1 28.04 28.09 136700
308 08/18/05 18:30 28.1 28.16 28.07 28.07 231500
309 08/18/05 19:00 28.07 28.13 28.04 28.04 137100
310 08/18/05 19:30 28.05 28.12 28.03 28.09 188100
311 08/18/05 20:00 28.09 28.1 28 28.04 249900
312 08/18/05 20:30 28.03 28.14 28.02 28.06 401700
313 08/19/05 14:30 28.23 28.39 28.21 28.28 566300
314 08/19/05 15:00 28.27 28.39 28.23 28.39 239300
315 08/19/05 15:30 28.38 28.38 28.24 28.28 204300
316 08/19/05 16:00 28.28 28.29 28.23 28.23 38907

Only the "High" and "Low" columns are needed I think.

Thanks
xs
 
Here you go!!! This is a basic way to do it without writing a macro.

I inserted the volume right before Open and used a lookup function. Pretty basic but that's what you'll need to do too. Make sure you have the look-up wizard checked.

Tools---Add-In---Lookup Wizard
 

Attachments

Thanks for that effort doublea

not too sure I follow.

the result would be a single price i.e. the longest line if i asembled the "OHLC" data into a bell curve.

Esignal gives the figure above the graphic............however I need to get the whole package just to have this number every day.


see attatched as example
 

Attachments

Think I got a solution.............

Get short term data (i.e.Tick or 1 min etc.) put it in Excel and use the "Mode" function (picks out the number which appears most)

sample data (DELL)

Date Time Last
11/23/2005 09:30:05 30.05
11/23/2005 09:30:40 30.13
11/23/2005 09:30:55 30.12
11/23/2005 09:31:05 30.12
11/23/2005 09:31:40 30.08
11/23/2005 09:31:55 30.09
11/23/2005 09:32:05 30.08
11/23/2005 09:32:20 30.07
11/23/2005 09:32:40 30.09
11/23/2005 09:32:55 30.09
11/23/2005 09:33:05 30.09
11/23/2005 09:33:40 30.05
11/23/2005 09:33:55 30.07
11/23/2005 09:34:05 30.0685
11/23/2005 09:34:20 30.07
11/23/2005 09:34:40 30.04
11/23/2005 09:34:55 30.04
11/23/2005 09:35:05 30.05
11/23/2005 09:35:20 30.03
11/23/2005 09:35:40 30.07
11/23/2005 09:35:55 30.06
11/23/2005 09:36:05 30.06
11/23/2005 09:36:20 30.05
11/23/2005 09:36:40 30.13
11/23/2005 09:36:55 30.11
11/23/2005 09:37:05 30.12
11/23/2005 09:37:40 30.17
11/23/2005 09:37:55 30.15
11/23/2005 09:38:05 30.16
11/23/2005 09:38:20 30.17
11/23/2005 09:38:40 30.08 mode =
11/23/2005 09:38:55 30.132 30.05


any thoughts anyone?

xs
 
Well Vince1 that was useful, very good of you to offer it.

My earlier "mode" method was not really the way I wanted it, this is much better.

Have no experience or knowledge of macros or VB...........but if I had my life should be much easier, think there is a lesson for me there! however learning to code does not excite me at all!

I use the "price based" POC as the closing price of the day and covert the data back to a bar chart. Ideally would like a bar chart with usual high and low and closing price and substitute the open price with the "value" price taken from the POC............and then do analysis of it in Excel. However Excel does not seem to offer OHLC in bar format but rather in Candlestick which sometimes muddles me up! (which is open and close?) there is however a "workaround" for this problem which will be next small project.

didn't mean to say all that.

Thanks for now (might need more help later:) )

xs
 
You must log in or register to reply here.
Back
Top