Market Profile, Calculate POC (mode) in Excel

Discussion in 'Technical Analysis' started by xs900, Nov 25, 2005.

  1. xs900

    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
     
    #1     Nov 25, 2005
    Share
  2. doublea

    doublea

    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
     
    #2     Nov 25, 2005
    Share
  3. xs900

    xs900

    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
     
    #3     Nov 25, 2005
    Share
  4. xs900

    xs900

    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
     
    #4     Nov 27, 2005
    Share
  5. Vince1

    Vince1

    Hope this helps++
     
    • tpos.xls
      File size:
      637.5 KB
      Views:
      2,790
    #5     Dec 3, 2005
    Share
    Grantx likes this.
  6. xs900

    xs900

    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
     
    #6     Dec 3, 2005
    Share