Restrict Multiple Order Firing in Excel – Upstox API This article is basically meant for Excel Beginners, you may already aware most of the things mentioned here. Repeated firing of Order in Excel is the major concern for all Excel Users. By the way, it’s not a bug or issue with the Excel, that’s the way how Excel works and it must work like that only. Here is what happening, Whenever a cell value changes, Excel will recalculate all cell formula’s that have reference to that cell. If it doesn’t, you will not get correct result. Example:- Cell A1 = 10 Cell A2 = 10 Cell A3 = Sum (A1+A2) If A1 value changes, then Excel must recalculate formula in A3, so that you will get the correct sum of A1 & A2. If it doesn’t recalculate on A1 change, you will get wrong result in A3. Here the notable point is, at any point of time, the Cell A3 has the latest result only, you do not have any reference what is the value of A3 before A1 change. How Repeated Order firing happens:- Let us take a simple Breakout Trading system. BUY when LTP >= 155.55 Cell A1 = 154.65 'Receives Ltp from RTD Server & Keeps on Changing throughout the market hours Cell A2 = if(A1 >= 155.55, "BUY", "NO BUY") 'Simple If Condition that will display "BUY" when Ltp >= 155.55 else will display "NO BUY" Cell A3 = If (A2 = "BUY",PlaceOrder("NSE_EQ","BHEL","B","M",1),"") In A3, I have a simple If condition, that will call the PlaceOrder Function when the A2 display “BUY” Here, you can see A3 depends on A2 and A2 depends on A1 So any changes in A1 will recalculate formula in A2 and any change in the A2 will recalculate formula in A3. Before going further let us see what UDF is. There are two types of Functions in Excel Built in Functions like Sum, Average etc UDF (User Defined functions using VBA) If you need anything beyond the Excel built-in Functions, then you can create your own functions using VBA and call it directly from the Excel Cell like built-in functions. All the Order Functions used in UpstoxXL are UDF’s only. Example:- Suppose, You want to add two values, v1 and v2. Calculate 1% on the sum value. Add that 1% to v1. Output the result. The above is possible using the built-in functions, but this will be lengthy and complex formula Like this, = (sum (v1 + v2) * 1/100) + v1 ‘Using Excel Built-In functions Instead, you can wrap this Complex calculation inside a UDF. Like this Function My_Function (Value1 as integer, Value2 as Integer) Dim x x = Value1 + Value2 x = x * 0.01 x = x + Value1 My_Function = x End Function In a cell where you want the result, just call the UDF =My_Function (v1, v2) ‘Using UDF As you can see, you can combine complex calculations in UDF, which will make your calculation and coding easy. Using UDF with Upstox API. Since Upstox API is HTTP restful API, there is no built-in functions that will make HTTP calls from Excel. So we end up with creating a custom user defined functions that will communicate with Upstox Server pass order details. That’s enough. Let’s come to the topic again. Repeating some sentences again Cell A3 = If (A2 = "BUY",PlaceOrder("NSE_EQ","BHEL","B","M",1),"") In A3, I have a simple If condition, that will call the PlaceOrder Function when the A2 display “BUY” Here, you can see A3 depends on A2 and A2 depends on A1 So any changes in A1 will recalculate formula in A2 and any change in the A2 will recalculate formula in A3. Since the LTP changes continuously, this will force recalculation in A2 and in turn A3. Suppose, At 10:00:00 am Ltp is 155.55 Now the Formula in Cell A2 will display “BUY” as the Ltp >= 155.55 (Our Buy Level in Example) Once the A2 display “BUY”, Formula in Cell A3 will call the PlaceOrder Function, thus your order is placed to Upstox. On the next second, 10:00:01 am Again the Ltp is 155.60 Since the A1 value changed, Excel will calculate Formula in A2. A2 will re-display “BUY” as still the Ltp >= 155.55 (Our Buy Level in Example) As A2 changed, Excel will calculate formula in A3 A3 will again place order as A2 value is “BUY”. This chain keeps on going until the Ltp goes below 155.55, so that A2 will not display “BUY”.If Ltp goes up and breaks 155.55, then again order placement will happen. This is how it works and it must work like that only, so that Excel will output always correct result. Well the above is good for data analysis, where we want the correct result i.e. we want 10 + 10 = 20 not 15, but in our case we don’t want this behavior. We can’t change the Excel behavior and should not attempt also. As far as the Excel concern, “PlaceOrder” or “Any” function is just a math calculation, so it recalculates it to produce correct result. How we will restrict the repeated order firing..? You can not refer any other call values to check order placed status, Because, that cell value will also keep on change, so you will not know the previous value in that cell. Then what is the workaround…? We need to move out of the Cell as it impossible to restrict Orders by referring some other cell value except where you are inputting the cell value manually. Manually entered value in a cell will not change. We just need some reference, which will not change on Excel Recalculation and holds reference to the Order Status. There are different methods to do this, Like storing order status in Text File or XML file like “BOUGHT” Read that text file before placing the order If the text value equals “BOUGHT”, then we already placed order, so do not place order just exit the method else PlaceOrder. This method is good for 10 – 15 symbols, but if you want to track say 200 symbols, Then creating text file for 200 symbols and reading text files on every order place will affect performance and latency. The most efficient method is using dictionary to store order status and retrieve this before order place. Since these details are stored in memory, it will be easily accessed and will have better performance. Let me explain with live example:- First open Visual Basic Editor Go to –> Tools –> References Select Microsoft Scripting Runtime (This reference required for Dictionary) Declare a dictionary at the Global Level in your module. i.e. Put the below line of code at the top of your public module Public Dict_BuySell_Status As New Scripting.Dictionary In simple word, a dictionary is nothing but a collection of unique keys and their corresponding values. We just created an empty dictionary. The above creates a new dictionary with name ‘Dict_BuySell_Status’ You can create multiple dictionaries as per your requirement. Now we will modify our PlaceOrder Function to Place only one order per signal. Public Function PlaceSimpleOrder_BuySell(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") On Error GoTo ErrHandler: 'As I said, Dictionary is a collection of Unique Keys and their corresponding values 'So, you will not get duplicate keys 'We will use TrdSym as unique key as it different for each scrip’s 'First we will check whether any key with name as TrdSym is present in the dictionary 'If not we will add the key with empty value If Not Dict_BuySell_Status.Exists(TrdSym) Then Dict_BuySell_Status.Add TrdSym, "" End If 'Now retrieve the Order Status Dim OrderStatus As String OrderStatus = Dict_BuySell_Status(TrdSym) 'This will return the value of Key TrdSym 'If the order status equals the Trans then, we already placed order for that Trans 'Exit the Function, don't place order again If OrderStatus = Trans Then Exit Function End If 'if the Order Status is not equal to Trans then 'Place the Order 'Store the Trans as value to the Key TrdSym 'So that next time it will return the Trans 'Adding trans to dictionary key Dict_BuySell_Status.Item(TrdSym) = Trans 'Place the Order PlaceSimpleOrder_BuySell = Upstox.PlaceSimpleOrder(Exch, TrdSym, Trans, OrdType, Qty, ProdType, LmtPrice, TrgPrice, val) Exit Function ErrHandler: PlaceSimpleOrder_BuySell = Err.Description End Function What happens…..? If we apply this functions in the above example, When you first time get “BUY” Signal, the function will be called Order Status will return empty string as this is the first time it gets “BUY” on that particular symbol So, it will place the Order and store the “BUY” in the Dictionary Key “TrdSym” When the function is called second time, this time The order status will return “BUY” and our Trans is also “BUY”, so this clearly indicates, we already placed “BUY” order, so we will skip order placement. That’s it we restricted the Order Placement When you get “SELL” signal after “BUY”, It will be placed because the The Order Status will return ‘BUY” but the Trans is “SELL” So it will place “SELL” order once. This way “BUY” and “SELL” will go alternatively The above is just a sample method; you may apply any logic as per your requirement. For “SHORT” & “COVER”, You can create another Dictionary and another Function, So that “SHORT” and “COVER” will go alternately. Note:- Universally, there are only two kinds of Trades, “BUY” & “SELL”. Exchanges and OMS knows only “BUY” & “SELL”.The words “SHORT” & “COVER” are used in Trading System to distinguish normal “SELL” & “BUY” order respectively. Hope this clears. ChokS .Net Programmer. AFL Coder. Author of KiteDotNet & KiteXL.