Order Requests You can perform all kind of order request such as Place, Modify & cancel etc from within the Excel Cell . Place Regular, BO & CO orders within the cell by Just inputing the respective Function Below are the Excel Functions Syntax for Order Requests. These Functions are User Defined Functions which You can call from within the Excel Cell .
'Below Order Functions will Return OrderId in the Cell Itself 'Qty should be No of Shares to Buy/Sell incase of EQ 'No of Lots to Buy/Sell incase of FUT NOT Lot Size 'For Example, To Buy 1 Lot of Nifty Just pass 1 not 75 'Exch - NSE|NFO|BSE|BFO|MCX|CDS|MCX 'Trans - BUY|SELL 'OrdType - MARKET|LIMIT|SL|SL-M 'ProdType - MIS|CNC|NRML 'Trade Symbol - As it is in Instrument List 'Val - DAY|IOC
After Market Order =PlaceAmo(Exch, TrdSym, Trans, OrdType, Qty,ProdType, LmtPrice, TrgPrice, val) =CancelAmo(OrderId)
'Postion Convertion 'Position Type - day or overnight (case Sensitive)
Position Conversion =ConvertPosition(Exch, TrdSym, Trans, Qty,PositionType, OldProduct, NewProduct)
Checking Order Details 'Returns Complete Order Details Like Qty Filled, Time, Avg Price etc =GetOrderDetails(OrderId)
'Returns One of the Order Status OPEN|TRIGGER PENDING|CANCELLED|REJECTED =GetOrderStatus(OrderId)
Subscribing Quotes To get Realtime Quote, You need to Subscribe Quotes for the Symbol 'This is Equivalent to Adding Symbols in Market Watch in Pi. 'If You don't add symbol in Pi Market Watch, You will not get quotes in Excel 'Same way you have to call this functions in cell to get Quotes =SubscribeQuotes(Exch, TrdSymbol)
'Unsubscribes from Quotes, when You No Longer Require Quotes =UnSubscribeQuotes(Exch, TrdSymbol)
Yes...It can be used...But this Library is Specifically for Excel & It doesn't have QuotesReceivedEvent, instead has RTD server for Excel.
You can try the http://www.howutrade.in/docs/KiteXL/html/KiteXL.xlsm This sample Excel Sheet has all methods of Kite Connect wrapped in VBA. Make sure You input your api key & secret in dashboard sheet. & also enable macros.
i tried it in vb6, and can call the quotes using it. but let me know how can i get streaming quotes of websocket can i use winsock control for that ? let me know plz....
can i make use the below link using winsock.. if yes then how ? wss://websocket.kite.trade/?api_key=xxx&user_id=AB0012&public_token=xxxx ??
@YOGESH
You can do most of the things that are available in trading platform (like order book, trade book, position conversion etc) in KiteXL.
Regarding basket order, you can do that with VBA. If it is difficult in VBA, you can do that in some other language and integrate with KiteXL.
You can just try sample excel file to explore KiteXL.
@botany02 many thanks for your info.how can we take trade book or order book on one more excel sheet.so that we can place conditions by refering to the cells in these sheets. for example ... if i want to make a simple automation like.....buy 1 sbi at ltp say 250and keep a trailing stoploss at say 1 rupee .now i just want to double this stoploss quantity and again put the same condition for my further position because when you reverse your trade with double qty you square off your original position and create a fresh position withsame qty. dear sir can you guide me if we can do it.
@botany02 what exactly is Publisher type in create app option. The key and secret code generated from the same is sufficient to login even if not trade?
@Kailash I downloaded KiteXL.xlsm from the link and ran the setup too. But when I login through excel, it does take me to kite login page but then shows HTTP500 error page (after I enter 2FA details) - "This error (HTTP 500 Internal Server Error) means that the website you are visiting had a server problem which prevented the webpage from displaying."
@YOGESH For restricting order placements, we need to use VBA. I'll modify the place order function to restrict multiple order placements even if the cell formula is recalculated. will post the code later.
@YOGESH That's risky..Pulling Trade Book & Order Book in real-time will result in error. If you want to know the order status you need to use post-backs
@YOGESH Here is the code to restrict order placement. Below code will restrict multiple orders.. and works as follows.. Example:- If you placed a BUY order in AXISBANK, it will not place a another BUY order until a SELL order is placed after BUY and vice versa.
The Below example is for PlaceRegularOrder Function only but similar for other order variety(I'll leave that you)
Step One:- Add the below line of code at the top of the module and below the line Option Explicit.
Dim Dict_PlaceRG As New Scripting.Dictionary
Step Two:- Goto Menu (Visual Basic Editor) --> Tools --> Add Reference Select Microsoft Scripting Runtime and Press Ok
Step Three:- Just replace your existing PlaceRegularOrder function with the below one That's it... Now,try placing the BUY order in any symbol multiple times, it will place only one BUY Order and change the trans to SELL
Public Function PlaceRegularOrder(Exch As String, TrdSym As String, Trans As String, OrdType As String, Qty As Integer, _ ProdType As String, Optional LmtPrice As Double = 0, Optional TrgPrice As Double = 0, Optional val As String = "DAY") As String On Error GoTo ErrHandler:
If Kite Is Nothing Then PlaceRegularOrder = "User Not Logged-In" Exit Function End If
Dim LastOrder As String If Not Dict_PlaceRG.Exists(UCase(TrdSym)) Then Dict_PlaceRG.Add UCase(TrdSym), UCase(Trans) LastOrder = "" Else LastOrder = Dict_PlaceRG(UCase(TrdSym)) End If
If LastOrder = UCase(Trans) Then PlaceRegularOrder = "Duplicate Order" Exit Function End If
Sir many thanks for your suggestion.some how i am not getting the required result.But sir if we can refresh the orderbook or if we can get the trade book refreshed in real time then i think we can use " if and match "function to confirm the completed orders . like "= IF(AND(GIVEN PRICE IS SAY > BID PRICE , MATCH("COMPLETED",R:R,0)<0,MATCH("AXISBANK",R:R,0)<0,MATCH(505,R:R,0)<0),PLACEREGULAR ORDER....,0) please revert thanks yogesh k
@YOGESH Pulling Order Book or Trade Book in real time is never possible. Your approach will not work.. With built in Excel Formula, I'm afraid you will never make it.. You have no option other than using VBA.
My Antivirus (McAfee) has reported that the site http://www.howutrade.in is in the high risk category. KIndly share the experience of those who have used this site.
@kitcosree
I'm not sure,why McAfee reports our site as risk.
But we are not collecting any personal and financial information through our site.
our library's are safe to use.
You can scan @ virustotal.com(multiple AV scanner).
You can also use glasswire software to check network activities of a application if you suspect.
Glasswire will give a detailed report on which network that application has connected and how much data transferred.
Could you please add disclosed qty as well to the BO order format? Also, is the square off value, stoploss value and trailing stoploss value absolute values or in terms of tick size?
Yes Trailing stoploss also absolute value.. Yes, You can place orders directly from the Excel itself when you call the Function PlaceBO from the cell, your order will be directly placed and will return order number.No other required. Note:- Strings to be enclosed in double quotes. Ex:- =PlaceBO("NSE", "AXISBANK", "BUY", 1,450.65, 2,1.5,1 )
Can I place an order in any cell of the "Trading System" worksheet? Is there any limit on the number of orders i can place? By when will the next release with disclosed qty be released?
I'm using the KiteXL excel workbook. I've entered GetRTD("NSE","AUTOIND","data_variable") and the subscribequotes column shows true, but all the values are zeros. Kindly help
Tried again to download the excel setup file. McAfee says it quarantined an infected file from the download in progress and download is paused for user confirmation whether to proceed or get back to safety!
While logging in using sample excel sheet it says api call not for authenticated user pop up. But if you try logging in it says user already logged in. how much time it takes for symbol download?
@Ami The size of the instruments list is appx 6.5MB.. Download time will depend on your internet speed.. In my system, it takes only < 30s to download..(10Mbps)
You can place, Modify and cancel orders with KiteXL. It supports most of the functions of a trading terminal..like order book etc.. Pls refer our documentation for more info . https://www.howutrade.in/docs/kitexl/
I think you didn't get me..... what I wanted to do is.....if will code a specific condition for buy and sell based on high and low......and as soon as the condition get satisfied.....and I get buy or sell signal........it automatically buy or sell through my account......without human intervention......with KITE API.???
We have re-compiled our installer and tested at https://www.virustotal.com/ Now McAfee will not block the installer. Our installer has 2/60 detection ratio.
@botany02 dear sir in the Positions sheet do we get real time update of positions or we have to press positions button to uptade our positions please guide
@botany02 On the Kite Connect API site, it is mentioned " Never embed the api_secret in a mobile app or client side application, web or otherwise. Similarly, access_token should never be exposed publically." Why the KiteXL file has the provision to give api secret ?
Moreover, After successful login i am getting error as "Invalid checksum". Please help.
@paragsatpute Yaa.. its true..you never put your api_secret in such a way that is accessible or visible to everyone. But KiteXL is intended to be use for personal.So until unless your pc is compromised,there will be no issue.
KiteXL Spreadsheet is just an example to show how to use the KiteXL library. You can pass your api_key & api_secret directly to the vba function or popup a window to input api_key manually when you press login instead of retrieving it from the excel cell value.
For "Invalid checksum" error, close all excel sheets and try login again and also open excel as admin.
You should take extra precaution only when you plan to distribute your app to others. Suppose, you designed a Trading system in KiteXL and you want to give that to your friend and will use your api_key & api_secret. In this case, you should implement a wise method to pass the api_key & api_secret to the login function so that the api_key & api_secret is not accessible to your friend.If your friend uses his own key then no need to do anything at your end.
Hi, I tried KiteXL, but receiving an error "Invalid Checksum". I tried closing excel, restarting system and log in again. but the results are same. I have a publisher access. Please advise if I should have "Connect" access instead of publisher for this. Thanks
Hi @navin_nandakumar, Kite Publisher is just a javascript button you can include on your websites for placing orders. In order to access orderbook, positions, holdings, and placing the order, you need to have Kite Connect app. Please go to developers console and create Kite Connect App.
Order Requests
You can perform all kind of order request such as Place, Modify & cancel etc from within the Excel Cell .
Place Regular, BO & CO orders within the cell by Just inputing the respective Function
Below are the Excel Functions Syntax for Order Requests.
These Functions are User Defined Functions which You can call from within the Excel Cell .
'Below Order Functions will Return OrderId in the Cell Itself
'Qty should be No of Shares to Buy/Sell incase of EQ
'No of Lots to Buy/Sell incase of FUT NOT Lot Size
'For Example, To Buy 1 Lot of Nifty Just pass 1 not 75
'Exch - NSE|NFO|BSE|BFO|MCX|CDS|MCX
'Trans - BUY|SELL
'OrdType - MARKET|LIMIT|SL|SL-M
'ProdType - MIS|CNC|NRML
'Trade Symbol - As it is in Instrument List
'Val - DAY|IOC
Regular Order
=PlaceRegularOrder(Exch, TrdSym, Trans, OrdType, Qty,ProdType, LmtPrice, TrgPrice, val)
=ModifyRegularOrder(Exch, TrdSym, OrderId, OrdType,Qty, LmtPrice, TrgPrice)
=CancelRegularOrder(OrderId)
Cover Order
=PlaceCO(Exch, TrdSym, Trans, Qty,stoploss_Price)
=ModifyCOSl(Exch, TrdSym, OrderId, ParentOrderId,TrgPrice)
=ExitCO(OrderId, ParentOrderId)
Bracket Order
=PlaceBO(Exch, TrdSym, Trans, Qty,LmtPrice, SqOffValue,StoplossValue, TrailValue)
=ModifyBOTgt(Exch, TrdSym, OrderId, ParentOrderId,LmtPrice)
=ModifyBOSl(Exch, TrdSym, OrderId, ParentOrderId,TrgPrice)
=ModifyBOMain(Exch, TrdSym, OrderId, Qty, LmtPrice)
=ExitBO(OrderId, ParentOrderId)
=CancelBOMain(OrderId)
After Market Order
=PlaceAmo(Exch, TrdSym, Trans, OrdType, Qty,ProdType, LmtPrice, TrgPrice, val)
=CancelAmo(OrderId)
'Postion Convertion
'Position Type - day or overnight (case Sensitive)
Position Conversion
=ConvertPosition(Exch, TrdSym, Trans, Qty,PositionType, OldProduct, NewProduct)
Checking Order Details
'Returns Complete Order Details Like Qty Filled, Time, Avg Price etc
=GetOrderDetails(OrderId)
'Returns One of the Order Status OPEN|TRIGGER PENDING|CANCELLED|REJECTED
=GetOrderStatus(OrderId)
Subscribing Quotes
To get Realtime Quote, You need to Subscribe Quotes for the Symbol
'This is Equivalent to Adding Symbols in Market Watch in Pi.
'If You don't add symbol in Pi Market Watch, You will not get quotes in Excel
'Same way you have to call this functions in cell to get Quotes
=SubscribeQuotes(Exch, TrdSymbol)
'Unsubscribes from Quotes, when You No Longer Require Quotes
=UnSubscribeQuotes(Exch, TrdSymbol)
can it be used for visual basic 6 ?
Yes...It can be used...But this Library is Specifically for Excel &
It doesn't have QuotesReceivedEvent, instead has RTD server for Excel.
You can try the http://www.howutrade.in/docs/KiteXL/html/KiteXL.xlsm
This sample Excel Sheet has all methods of Kite Connect wrapped in VBA.
Make sure You input your api key & secret in dashboard sheet. & also enable macros.
but let me know how can i get streaming quotes of websocket
can i use winsock control for that ?
let me know plz....
can i make use the below link using winsock.. if yes then how ?
wss://websocket.kite.trade/?api_key=xxx&user_id=AB0012&public_token=xxxx ??
OI is not available in Websocket.
So can't add OI.
Present version doesn't support historical data, We will include this in next release.
Kite Connect for excel is by @botany02. May be he can help you with it.
You can do most of the things that are available in trading platform (like order book, trade book, position conversion etc) in KiteXL.
Regarding basket order, you can do that with VBA. If it is difficult in VBA, you can do that in some other language and integrate with KiteXL.
You can just try sample excel file to explore KiteXL.
@botany02
many thanks for your info.how can we take trade book or order book on one more excel sheet.so that we can place conditions by refering to the cells in these sheets.
for example ... if i want to make a simple automation like.....buy 1 sbi at ltp say 250and keep a trailing stoploss at say 1 rupee .now i just want to double this stoploss quantity and again put the same condition for my further position because when you reverse your trade with double qty you square off your original position and create a fresh position withsame qty.
dear sir can you guide me if we can do it.
Could U pls share your redirect_url..?
Dear sir how to prevent repeat orders which are based on value of a cell.
For restricting order placements, we need to use VBA.
I'll modify the place order function to restrict multiple order placements even if the cell formula is recalculated.
will post the code later.
That's risky..Pulling Trade Book & Order Book in real-time will result in error.
If you want to know the order status you need to use post-backs
Here is the code to restrict order placement.
Below code will restrict multiple orders.. and works as follows..
Example:-
If you placed a BUY order in AXISBANK, it will not place a another BUY order until a SELL order is placed after BUY and vice versa.
The Below example is for PlaceRegularOrder Function only but similar for other order variety(I'll leave that you)
Step One:-
Add the below line of code at the top of the module and below the line Option Explicit.
Step Two:-
Goto Menu (Visual Basic Editor) --> Tools --> Add Reference
Select Microsoft Scripting Runtime
and Press Ok
Step Three:-
Just replace your existing PlaceRegularOrder function with the below one
That's it...
Now,try placing the BUY order in any symbol multiple times, it will place only one BUY Order and change the trans to SELL
Let me know how it works.
like "= IF(AND(GIVEN PRICE IS SAY > BID PRICE , MATCH("COMPLETED",R:R,0)<0,MATCH("AXISBANK",R:R,0)<0,MATCH(505,R:R,0)<0),PLACEREGULAR ORDER....,0)
please revert
thanks
yogesh k
Pulling Order Book or Trade Book in real time is never possible.
Your approach will not work..
With built in Excel Formula, I'm afraid you will never make it..
You have no option other than using VBA.
You should use http://127.0.0.1
Could you try changing your redirect url to https://zerodha.com in Developer console.
& try login again
I'm not sure,why McAfee reports our site as risk.
But we are not collecting any personal and financial information through our site.
our library's are safe to use.
You can scan @ virustotal.com(multiple AV scanner).
You can also use glasswire software to check network activities of a application if you suspect.
Glasswire will give a detailed report on which network that application has connected and how much data transferred.
We are in the process of revamping our website.
Atlast we too developers only.
Could you please add disclosed qty as well to the BO order format?
Also, is the square off value, stoploss value and trailing stoploss value absolute values or in terms of tick size?
Absolute values..
We will add dis qty in the next release
Can I place orders directly from this excel sheet or do i need additional pemissions for the same
Yes, You can place orders directly from the Excel itself
when you call the Function PlaceBO from the cell, your order will be directly placed and will return order number.No other required.
Note:- Strings to be enclosed in double quotes.
Ex:- =PlaceBO("NSE", "AXISBANK", "BUY", 1,450.65, 2,1.5,1 )
For minimum trail value , refer zerodha.com/z-connect/tradezerodha/zerodha-trader-software-version/bracket-orders-trailing-stoploss-sl
If you want your bracket order to be executed immediately, then pass higher limit price for BUY and vice versa for SELL.
Is there any limit on the number of orders i can place?
By when will the next release with disclosed qty be released?
Tried again to download the excel setup file. McAfee says it quarantined an infected file from the download in progress and download is paused for user confirmation whether to proceed or get back to safety!
(I am not able to attach the screen shot)
regards,
-Sreekuamr
Does Kite has a provision for this?
thanks and regards,
-Sreekumar
But if you try logging in it says user already logged in.
how much time it takes for symbol download?
The size of the instruments list is appx 6.5MB..
Download time will depend on your internet speed..
In my system, it takes only < 30s to download..(10Mbps)
You can place, Modify and cancel orders with KiteXL.
It supports most of the functions of a trading terminal..like order book etc..
Pls refer our documentation for more info .
https://www.howutrade.in/docs/kitexl/
what I wanted to do is.....if will code a specific condition for buy and sell based on high and low......and as soon as the condition get satisfied.....and I get buy or sell signal........it automatically buy or sell through my account......without human intervention......with KITE API.???
We have re-compiled our installer and tested at https://www.virustotal.com/
Now McAfee will not block the installer. Our installer has 2/60 detection ratio.
dear sir
in the Positions sheet do we get real time update of positions or we have to press positions button to uptade our positions
please guide
It is mandatory that user has to log in at least once a day. Please don't automate login
On the Kite Connect API site, it is mentioned " Never embed the api_secret in a mobile app or client side application, web or otherwise. Similarly, access_token should never be exposed publically."
Why the KiteXL file has the provision to give api secret ?
Moreover, After successful login i am getting error as "Invalid checksum". Please help.
Yaa.. its true..you never put your api_secret in such a way that is accessible or visible to everyone.
But KiteXL is intended to be use for personal.So until unless your pc is compromised,there will be no issue.
KiteXL Spreadsheet is just an example to show how to use the KiteXL library.
You can pass your api_key & api_secret directly to the vba function or popup a window to input api_key manually when you press login instead of retrieving it from the excel cell value.
For "Invalid checksum" error, close all excel sheets and try login again and also open excel as admin.
You should take extra precaution only when you plan to distribute your app to others.
Suppose, you designed a Trading system in KiteXL and you want to give that to your friend and will use your api_key & api_secret. In this case, you should implement a wise method to pass the api_key & api_secret to the login function so that the api_key & api_secret is not accessible to your friend.If your friend uses his own key then no need to do anything at your end.
Thanks
Kite Publisher is just a javascript button you can include on your websites for placing orders.
In order to access orderbook, positions, holdings, and placing the order, you need to have Kite Connect app. Please go to developers console and create Kite Connect App.