LTP in excell

hir0406 · Dec 4, 2018

hello,
I need little help in excel.
in zerodha Pi we can send our market watch to excel via "link to excel" button.
now as it sends live data feed so LTP of any script keeps changing and it is so fast that i can't write it down, but I need to record first 3 LTP on the script.
can someone help me with this?
Thanks.

Romeo1998 · Dec 5, 2018

Hello Hiren bhai,
in my code i have tested with current time and not with rtd price...
in the excel sheet where the ltp is coming, in that sheet's vba use this code....
and in this line
If Not Intersect(Target, Range("a1")) Is Nothing Then
i have used a1 cell, but u use the cell where the LTP price is in......
in my code, whenever value in a1 changes... it will save value in a2, then a3, n then a4.... n then it will stop saving....
Best of Luck

Code:

Option Explicit
  Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1")) Is Nothing Then
   Set Target = Range("a1")
   If Range("A:A").End(xlUp).Offset(1, 0) = "" Then
   Range("A:A").End(xlUp).Offset(1, 0) = Target
   ElseIf Range("A:A").End(xlUp).Offset(1, 0) <> "" And Range("A1").End(xlDown).Row <= 3 Then
   Range("A1").End(xlDown).Offset(1, 0) = Target  
   End If
   End If  
End Sub

hir0406 · Dec 6, 2018

Romeo1998 said:

Hello Hiren bhai,
in my code i have tested with current time and not with rtd price...
in the excel sheet where the ltp is coming, in that sheet's vba use this code....
and in this line
If Not Intersect(Target, Range("a1")) Is Nothing Then
i have used a1 cell, but u use the cell where the LTP price is in......
in my code, whenever value in a1 changes... it will save value in a2, then a3, n then a4.... n then it will stop saving....
Best of Luck

Code:

Option Explicit
  Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1")) Is Nothing Then
   Set Target = Range("a1")
   If Range("A:A").End(xlUp).Offset(1, 0) = "" Then
   Range("A:A").End(xlUp).Offset(1, 0) = Target
   ElseIf Range("A:A").End(xlUp).Offset(1, 0) <> "" And Range("A1").End(xlDown).Row <= 3 Then
   Range("A1").End(xlDown).Offset(1, 0) = Target
   End If
   End If
End Sub

hello,
thanks a ton for answering my request and coding it for me, but their is one more small issue i have and it is that i am very basic in excel. i mean just copy and paste.... if i provide you that excel file of PI then can you please do this coding in that or if you explain step by step then i will try to do it.
thanks

Romeo1998 · Dec 6, 2018

Hello bhai,
i had a look at the excel file.... LTP is in b2,b3,b4,b5, b6 and b7.....
now where do u want to save the first 3 ltp of these 6 cells, i can save in v1 to v18 cells....
or in some other cells ?

hir0406 · Dec 6, 2018

Romeo1998 said:

hello,
can you save them in b14 to b32
thanks

Romeo1998 · Dec 6, 2018

hir0406 said:

Hello bhai,
I tried to upload macro enabled workbook with .xlsm extension, but not allowed

in ThisWorkbook use this code, whenever workbook is opened, it will clear contents of cells b14-b31

Code:

Option Explicit

Private Sub Workbook_Open()
    Application.EnableEvents = False
        Sheet1.Range("b14:b31").Clear
    Application.EnableEvents = True
End Sub

in sheet1(default mw) use this code

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b2")) Is Nothing Then
   Set Target = Range("b2")
   If Range("b14") = "" Then
   Range("b14") = Target
   ElseIf Range("b15") = "" Then
   Range("b15") = Target
   ElseIf Range("b16") = "" Then
   Range("b16") = Target
   End If
   End If

   If Not Intersect(Target, Range("b3")) Is Nothing Then
   Set Target = Range("b3")
   If Range("b17") = "" Then
   Range("b17") = Target
   ElseIf Range("b18") = "" Then
   Range("b18") = Target
   ElseIf Range("b19") = "" Then
   Range("b19") = Target
   End If
   End If

   If Not Intersect(Target, Range("b4")) Is Nothing Then
   Set Target = Range("b4")
   If Range("b20") = "" Then
   Range("b20") = Target
   ElseIf Range("b21") = "" Then
   Range("b21") = Target
   ElseIf Range("b22") = "" Then
   Range("b22") = Target
   End If
   End If

   If Not Intersect(Target, Range("b5")) Is Nothing Then
   Set Target = Range("b5")
   If Range("b23") = "" Then
   Range("b23") = Target
   ElseIf Range("b24") = "" Then
   Range("b24") = Target
   ElseIf Range("b25") = "" Then
   Range("b25") = Target
   End If
   End If

   If Not Intersect(Target, Range("b6")) Is Nothing Then
   Set Target = Range("b6")
   If Range("b26") = "" Then
   Range("b26") = Target
   ElseIf Range("b27") = "" Then
   Range("b27") = Target
   ElseIf Range("b28") = "" Then
   Range("b28") = Target
   End If
   End If

   If Not Intersect(Target, Range("b7")) Is Nothing Then
   Set Target = Range("b7")
   If Range("b29") = "" Then
   Range("b29") = Target
   ElseIf Range("b30") = "" Then
   Range("b30") = Target
   ElseIf Range("b31") = "" Then
   Range("b31") = Target
   End If
   End If
 
End Sub

You will have to save this file as macro enabled workbook
on top left, click on file, save as, n then macro enabled workbook

Best of Luck

hir0406 · Dec 6, 2018

Romeo1998 said:

Hello bhai,
I tried to upload macro enabled workbook with .xlsm extension, but not allowed

in ThisWorkbook use this code, whenever workbook is opened, it will clear contents of cells b14-b31

Code:

Option Explicit

Private Sub Workbook_Open()
    Application.EnableEvents = False
        Sheet1.Range("b14:b31").Clear
    Application.EnableEvents = True
End Sub

in sheet1(default mw) use this code

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b2")) Is Nothing Then
   Set Target = Range("b2")
   If Range("b14") = "" Then
   Range("b14") = Target
   ElseIf Range("b15") = "" Then
   Range("b15") = Target
   ElseIf Range("b16") = "" Then
   Range("b16") = Target
   End If
   End If

   If Not Intersect(Target, Range("b3")) Is Nothing Then
   Set Target = Range("b3")
   If Range("b17") = "" Then
   Range("b17") = Target
   ElseIf Range("b18") = "" Then
   Range("b18") = Target
   ElseIf Range("b19") = "" Then
   Range("b19") = Target
   End If
   End If

   If Not Intersect(Target, Range("b4")) Is Nothing Then
   Set Target = Range("b4")
   If Range("b20") = "" Then
   Range("b20") = Target
   ElseIf Range("b21") = "" Then
   Range("b21") = Target
   ElseIf Range("b22") = "" Then
   Range("b22") = Target
   End If
   End If

   If Not Intersect(Target, Range("b5")) Is Nothing Then
   Set Target = Range("b5")
   If Range("b23") = "" Then
   Range("b23") = Target
   ElseIf Range("b24") = "" Then
   Range("b24") = Target
   ElseIf Range("b25") = "" Then
   Range("b25") = Target
   End If
   End If

   If Not Intersect(Target, Range("b6")) Is Nothing Then
   Set Target = Range("b6")
   If Range("b26") = "" Then
   Range("b26") = Target
   ElseIf Range("b27") = "" Then
   Range("b27") = Target
   ElseIf Range("b28") = "" Then
   Range("b28") = Target
   End If
   End If

   If Not Intersect(Target, Range("b7")) Is Nothing Then
   Set Target = Range("b7")
   If Range("b29") = "" Then
   Range("b29") = Target
   ElseIf Range("b30") = "" Then
   Range("b30") = Target
   ElseIf Range("b31") = "" Then
   Range("b31") = Target
   End If
   End If

End Sub

You will have to save this file as macro enabled workbook
on top left, click on file, save as, n then macro enabled workbook

Best of Luck

THANKS A TON

hir0406 · Dec 8, 2018

hello Romeo,
I asked the same question in zerodha forum https://tradingqna.com/t/recording-ltp-in-excel/51105/2
someone has replied to the post with the link http://www.vbforums.com/showthread....l-recording-data-with-Real-Time-Data-Feed-DDE
hope this helps you because I tho dint understood anything in that.

mesuteryilmaz · Dec 11, 2018

Romeo1998 said:

Hello Hiren bhai,
in my code i have tested with current time and not with rtd price...
in the excel sheet where the ltp is coming, in that sheet's vba use this code....
and in this line
If Not Intersect(Target, Range("a1")) Is Nothing Then
i have used a1 cell, but u use the cell where the LTP price is in......
in my code, whenever value in a1 changes... it will save value in a2, then a3, n then a4.... n then it will stop saving....
Best of Luck

Code:

Option Explicit
  Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a1")) Is Nothing Then
   Set Target = Range("a1")
   If Range("A:A").End(xlUp).Offset(1, 0) = "" Then
   Range("A:A").End(xlUp).Offset(1, 0) = Target
   ElseIf Range("A:A").End(xlUp).Offset(1, 0) <> "" And Range("A1").End(xlDown).Row <= 3 Then
   Range("A1").End(xlDown).Offset(1, 0) = Target 
   End If
   End If 
End Sub

if I want to copy down all of the cells values in the row whenever a cell updates in that row.
For example Column B (time stamp) updates whenever new data arrives. So the code should copy all columns next empty rows.
How can I modify the above code to do such task?

Romeo1998 · Dec 11, 2018

Hello Bhai,
i am assuming that u r taking live data in excel....
I am also facing this problem...
i have asked this question here..
https://www.mrexcel.com/forum/excel...rksheet_change-event-problem.html#post5190714

Thread starter	Similar threads	Forum	Replies	Date
A	How did BEL 130CE LTP was at 1.85 for April expiry when the equity closed at 131.05?	Options	3	Jun 10, 2018
P	Question on LTP and Volume being shown as 0 in the 'market watch' widget of SBI SMART	Beginners Guide	0	Nov 11, 2015
P	how to get yesterday LTP last traded price in afl code ??	AmiBroker	3	Oct 19, 2015
J	Yesterday's LTP vs Today's CMP	General Trading & Investing Chat	8	Oct 9, 2012
S	Difference between Limit Price and SLTP	Introductions	0	Feb 1, 2012

LTP in excell

hir0406

Active Member

Romeo1998

Well-Known Member

hir0406

Active Member

Attachments

Romeo1998

Well-Known Member

hir0406

Active Member

Romeo1998

Well-Known Member

hir0406

Active Member

hir0406

Active Member

mesuteryilmaz

New Member

Romeo1998

Well-Known Member

Similar threads