Help required from EXCEL Experts

srilakshmi
Dear Friends,
I want to get Open, High,Low and Close price of any stock i pick from the LTP price which was streaming in my excel sheet for every 1 hour, lets say A2 = Stock Name, LTP is in Cell "B2". I want Open Price in C2, High in D2, Low in E2 and Close price in F2. Here I am able to get High , Low and Close prices. But the problem is how to get the Open Price saved in C2 without getting changed with LTP price. I tried many ways to get the opening Tick of the LTP in C2 cell but could not get.
So i request any excel expert please help me in getting the first tick of the LTP saved in C2.

Thank You
  • GasTurbGuy
    GasTurbGuy edited January 2018
    I'm assuming that before market opens all values in the excel sheet is 0.

    Going off on the format given by yourself, these are the formulas.

    Open:
    =IF(C2=0,B2,C2)

    High:
    =IF(B2>D2,B2,D2)

    Low:
    =IF(B20,B2,F2)

    But I'd say from all of my experience with excel, things will go wrong for some reason or the other. Good luck...
  • GasTurbGuy
    For some weird reason the close formula portion does not show:

    Close:
    =IF(B2>0,B2,F2)
  • GasTurbGuy
    Circular references need to turned on... Google how to do this
  • srilakshmi
    Sir
    Thank you Very much
    Sir please check the formula for
    Low: =IF(B20,B2,F2)
    i am not getting the low price
  • botany02
    botany02 edited February 2018
    @srilakshmi
    Could you explain, what you are trying to achieve?
    Are you trying to form one hour OHLC from ltp?
  • srilakshmi
    @BOTANY02
    yew sir exactly
    because i am not getting 1 hour OHLC data from historic data live while the 1 hour candle is forming. So i am trying to get the hourly OHLC values myself.
  • srilakshmi
    sorry yes not yew
  • srilakshmi
    srilakshmi edited February 2018
    @botany02
    Sir is there any other way where i can get hourly OHLC data live while the candle is forming
  • botany02
    and you want just current hour or all the hours OHLC in separate row?
    Is your system takes OHLC of currently forming candle or only after candle formation is completed?
    Say 0915 candle will have data of 0915 to 1015, when you will utilize this candle after 1015 or while forming itself?
  • GasTurbGuy
    Low
    =IF(B2<F2,B2,F2)
  • srilakshmi
    @botany02
    SIR i want only present forming candle say 9:15 to 10:15 OHL, close is not important.
    at 10:15, again i want OHL of 10:15 to 11:15 candle, and so on.
    i dont want previous completed candles . i want only present running candle OHL sir
  • GasTurbGuy
    GasTurbGuy edited February 2018
    There was an error in the formula, use this
    Low
    IF(OR(B2
  • botany02
    botany02 edited February 2018
    @srilakshmi
    You can't make OHLC from cell value itself, because for OHLC calculation, you need previous value, but excel cell will always have latest value and we don't have any reference to previous value except if you manually enter the previous value in some cell. As mentioned by @GasTurbGuy , circular reference will mess your excel calculation and not advisable to use circular reference.

    The solutions is write a UDF and use dictionaries to manipulate OHLC.
    Will post sample code sometimes later.
  • srilakshmi
    @botany02
    thank you sir for your kind reply. and waiting for the sample code sir
    Thank you very much
  • GasTurbGuy
    GasTurbGuy edited February 2018
    Low
    =IF(OR(B2
  • GasTurbGuy
    I dunno I just can't seem to be able to post the full formula in this forum.

    @Zerodha, guys is there something that in the way this webpage is made that prevents me from typing out excel formulae?
  • srilakshmi
    @botany02 and @GasTurbGuy

    Sir thank you very much for answering my query. sir i used the below VBA code and i am getting open , high , low values , still testing the accuracy but i am getting the values sir.

    Private Sub Worksheet_Calculate()
    If [D2] = 0 And [C2] > 0 Then 'OPEN
    [D2] = [C2]
    End If
    If [C2] > [E2] Then ' HIGH
    [E2] = [C2]
    End If
    If [C2] < [F2] Then ' LOW
    [F2] = [C2]
    End If
    End Sub
  • GasTurbGuy
    GasTurbGuy edited February 2018
    Damn, I didn't know square brackets work for referring cells. Thanks! I learnt something new today.
Sign In or Register to comment.