Hi Traders,

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.