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 displayBUY

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

  1. Built in Functions like Sum, Average etc
  2. 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 displayBUY

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)

0_1503072008603_Tools.png

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

ChokS

.Net Programmer. AFL Coder. Author of KiteDotNet & KiteXL.

9 Comments

  • deepak says:

    Indeed very helpful. I was trying KiteXL and this code hanging the excel what should i do ? I am comparing one scrip LTP with my BUY value and placing BO order. All this is under the ‘Do while Loop’ and commandbutton click function. Thanks a lot.

    • Admin Admin says:

      Hey Deepak,
      Excel is single threaded, you cannot use ‘Do While Loop’, it will block the UI until the ‘Do’ exits.
      You just need to write simple if formula to see the breakout.

  • kanchan gupta says:

    I am Getting An Error called compile Error Variable Not defined what should I do

  • kanchan gupta says:

    It’s giving an error in excel when placing an order that Object Doesn’t Support This Property Or method Pls help sir

  • Manish Gupta says:

    Please confirm if all keys in “Dict_BuySell_Status” dictionary created using this code will be reset to blank when Excel file is restarted (erasing all history of order placed in previous session, if any). If not so then how can this be handled. Because then in nest session, orders will not fired for those stocks for which orders were placed in previous session.

    • Manish Gupta says:

      Also I got error message “Gui Error” on Upstox Pro portal for all test orders placed after incorporating above changes. And I was not able to place order again for those stocks for which I tried to place order even after closing Excel sheet and restarting again. Does it mean that we can not place order again in future using excel for the these stocks?

    • Admin Admin says:

      Manish,
      All the dictionary keys are stored in memory, so if excel is closed all key details will be erased.

  • rupesh k says:

    Public Function PlaceOCO(ByVal Exch As String, ByVal TrdSym As String, ByVal Trans As String, ByVal Qty As Integer, ByVal LmtPrice As Double, _
    ByVal SqOffValue As Double, ByVal StoplossValue As Double, Optional ByVal TrailTicks As Integer, Optional ByVal OrdType As String = “L”, _
    Optional ByVal TrgPrice As Double, Optional ByVal cTag As String = “LAST”)
    On Error GoTo ErrHandler:
    If Not Dict_BuySell_Status.Exists(TrdSym) Then
    Dict_BuySell_Status.Add TrdSym, “”
    End If

    Dim OrderStatus As String
    OrderStatus = Dict_BuySell_Status(TrdSym)

    If OrderStatus = Trans Then
    Exit Function
    End If

    Dict_BuySell_Status.Item(TrdSym) = Trans

    PlaceOCO = Upstox.PlaceOCO(Exch, TrdSym, Trans, Qty, LmtPrice, SqOffValue, StoplossValue, TrailTicks, OrdType, TrgPrice, cTag)
    Exit Function
    ErrHandler:
    PlaceOCO = Err.Description
    End Function

    In your this code you have taken trans a filter stocks to place order single time.But now i am working on price croosover strategy above certain level i got signal.one time and this signal change again because of price goes down for a short period of time and then trans change in that time.and order places again.please tell me solution how can i make excel to work only as soon as signal comes. and in short what else can i be taken in place of trans for restricting multiple order firing.

Leave a Reply

Your email address will not be published.