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.
@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.
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?
@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
@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.
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
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...
Close:
=IF(B2>0,B2,F2)
Thank you Very much
Sir please check the formula for
Low: =IF(B20,B2,F2)
i am not getting the low price
Could you explain, what you are trying to achieve?
Are you trying to form one hour OHLC from ltp?
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.
Sir is there any other way where i can get hourly OHLC data live while the candle is forming
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?
=IF(B2<F2,B2,F2)
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
Thank you sir
Low
IF(OR(B2
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.
thank you sir for your kind reply. and waiting for the sample code sir
Thank you very much
=IF(OR(B2
@Zerodha, guys is there something that in the way this webpage is made that prevents me from typing out excel formulae?
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