Kite Connect for Excel (3rd party library)

Kailash
@botany02 has released a Kite Connect plugin for Excel that'll let you analyse and trade from Excel sheets.

Read mode here: http://www.howutrade.in/docs/KiteXL/html/Introduction.htm
Downloads here: http://www.howutrade.in/docs/KiteXL/html/Downloads.htm
  • botany02
    botany02 edited December 2016
    User Defined Functions List...For Reference...

    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)

  • k365
    great

    can it be used for visual basic 6 ?
  • botany02
    @k365

    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.
  • k365
    k365 edited December 2016
    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 ??
  • nagavardhana chari
    it is very nice, thankful and grateful Mr.Kailash
  • Anirban
    Can you please add open interest field also
  • botany02
    botany02 edited January 2017
    @Anirban
    OI is not available in Websocket.
    So can't add OI.
  • desiredoom
    is there any way to get historic data via KiteXL or vba
  • botany02
    @desiredoom
    Present version doesn't support historical data, We will include this in next release.
  • YOGESH
    Dear kailash can place basket orders and if so then can we autofill the price fields of this order based on the functions thru streaming quotes
  • YOGESH
    more over can we see completed orders ie trade book.
  • sujith
    Hi @YOGESH,
    Kite Connect for excel is by @botany02. May be he can help you with it.
  • botany02
    @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.
  • YOGESH

    @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.
  • kapilaggr
    @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?
  • sujith
    @kapilaggr, Checkout more info about Kite Publisher here.
  • Mukesh_Kumar
    Today the "GetRTD(Exch, Trdsymbol, "Last")" not working. sometimes showing 0 sometime showing price.(missed a profit of Rs.10000/- due to this :(
  • kapilaggr
    @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."
  • kapilaggr
    @Kailash @sujith please help with above query, thanks.
  • botany02
    @kapilaggr
    Could U pls share your redirect_url..?
  • YOGESH
    @botany02
    Dear sir how to prevent repeat orders which are based on value of a cell.
  • botany02
    @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
    Thanx sir just one more thing- how can the order book and tradebook be refreshed in real time
  • botany02
    @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
  • botany02
    botany02 edited February 2017
    @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

    Dict_PlaceRG.Item(UCase(TrdSym)) = UCase(Trans)

    PlaceRegularOrder = Kite.PlaceRegularOrder(Exch, TrdSym, Trans, OrdType, Qty, _
    ProdType, LmtPrice, TrgPrice, val)

    Exit Function
    ErrHandler:
    PlaceRegularOrder = Err.Description
    Exit Function
    End Function


    Let me know how it works.


  • YOGESH
    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
  • botany02
    botany02 edited February 2017
    @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.
  • botany02
    botany02 edited February 2017
    @kapilaggr
    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
  • kitcosree
    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.

  • botany02
    @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.

    We are in the process of revamping our website.

    Atlast we too developers only.
  • kapilaggr
    @botany02 problem remains even after trying the redirect url u mentioned
  • derickgeorge89
    =PlaceBO(Exch, TrdSym, Trans, Qty,LmtPrice, SqOffValue,StoplossValue, TrailValue)

    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?
  • botany02
    botany02 edited March 2017
    @derickgeorge89
    Absolute values..

    We will add dis qty in the next release
  • derickgeorge89
    Evn trailing stoploss is absolute value?

    Can I place orders directly from this excel sheet or do i need additional pemissions for the same
  • botany02
    botany02 edited March 2017
    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 )

    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.
  • derickgeorge89
    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?
  • derickgeorge89
    derickgeorge89 edited March 2017
    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
  • kitcosree
    @botany02

    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
  • kitcosree
    Zerodha Pi has a provision to get the live data stream to excel which is very useful for paper trading.

    Does Kite has a provision for this?

    thanks and regards,
    -Sreekumar
  • Ami
    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?
  • botany02
    botany02 edited March 2017
    @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)
  • Krishan007
    IS IT POSSIBLE TO **AUTO-PUNCH** ORDERS THROUGH THIS....IF YES THEN HOW.....PLSS HELP
  • botany02
    @Krishan007

    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/
  • Krishan007
    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.???
  • botany02
    Yes..........
  • Krishan007
    OK...then can u plss contact me on my [email protected] or 7503416813....and brief me how can I do it......????
  • botany02
    @kitcosree

    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.
  • YOGESH
    @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
  • YOGESH
    dear sir please guide about the how to use the postback
  • YOGESH
    dear sir please guide about the how to use the postback
  • balrajo
    Can you please confirm if I can pass Access Token and Public token to login function rather than login using browser?
  • sujith
    Hi @balrajo,
    It is mandatory that user has to log in at least once a day. Please don't automate login
  • paragsatpute
    paragsatpute edited June 2017
    @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.
  • botany02
    botany02 edited June 2017
    @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.
  • navin_nandakumar
    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
  • sujith
    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.
This discussion has been closed.