Restrict multiple order firing in Excel VBA

Below VBA code can be used to restrict multiple order firing. We use dictionary keys to store the ‘Order Placed Status’ and check this key, before placing next order. With this code one can place Buy and Sell orders alternatively.

You need to add reference to ‘Microsoft Scripting Runtime’ in VBA Project
Menu –> Tools –> References –> ‘Microsoft Scripting Runtime’

Declaring Dictionary: (at the top of the module)

Option Explicit
'Add references to the below in your VBA Project
'Menu --> Tools --> References --> Microsoft Scripting Runtime

Public Dict_OrderRestrict_BuySell As New Scripting.Dictionary


Public Function PlaceOCO_BuySell(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", _
                                 Optional ByVal SaltKey As String = "") As String
    On Error GoTo ErrHandler:
'We have to use dictionaries to store the order placed status
'Check this key before placing next order
'SaltKey is used to place multiple orders with same Trans
'SaltKey should be set to "" incase you are placing only single order per Trans

'SaltKey example usage
'Assume you want to place 3 Buy orders at 3 differenet levels
'Order1 = PlaceOCO_BuySell(Exch,......,"O1")
'Order1 = PlaceOCO_BuySell(Exch,......,"O2")
'Order1 = PlaceOCO_BuySell(Exch,......,"O3")

'SaltKey can be any string to identify a order uniquely

'Validation of parameters
    If Exch = vbNullString Then PlaceOCO_BuySell = "NULLEXCH": Exit Function
    If TrdSym = vbNullString Then PlaceOCO_BuySell = "NULLTRDSYM": Exit Function
    If Trans = vbNullString Then PlaceOCO_BuySell = "NULLTRANS": Exit Function
    TrdSym = UCase$(TrdSym)
    Trans = UCase$(Trans)
    SaltKey = UCase$(SaltKey)
    If (Trans <> "B" And Trans <> "S") Then PlaceOCO_BuySell = "INVALIDTRANS": Exit Function
    If Qty <= 0 Then PlaceOCO_BuySell = "NULLQTY": Exit Function

    'create a unique key
    Dim DictKey As String
    DictKey = TrdSym & "-" & SaltKey & "-" & "OCO"

    'Add key if not exists
    If Not Dict_OrderRestrict_BuySell.Exists(DictKey) Then Dict_OrderRestrict_BuySell.Add DictKey, ""

    'Check last order status
    Dim LastOrderStatus As String
    LastOrderStatus = Dict_OrderRestrict_BuySell(DictKey)

    'If the last order status equals the Trans then, we already placed order for that Trans
    'Exit the Function, don't place order again
    If LastOrderStatus = Trans Then PlaceOCO_BuySell = "REPEATORDER": Exit Function

    'If the lastorderstatus is not equal to trans, then this is the first order
    'Place the order and store the order status in dictionary

    Dim OrderStatus As String
    OrderStatus = Upstox.PlaceOCO(Exch, TrdSym, Trans, Qty, LmtPrice, SqOffValue, StoplossValue, TrailTicks, OrdType, TrgPrice, CTag)

    'Adding trans to dictionary key
    Dict_OrderRestrict_BuySell.Item(DictKey) = Trans
    PlaceOCO_BuySell = OrderStatus
    Exit Function
    PlaceOCO_BuySell = Err.Description
End Function

Was this article helpful?

Related Articles


  1. Avatar SHASHI KANT

    Thanks for your support. I checked and found that there are some improvement needed in this code. I select4-5 scripts for a day and I scalp in those whole day eq buy sbi in 3 parts as said earlier and sell or stopout once I am out then I again place order on those levels and my P& L becomes cumulative of sl & tgt hits.

    with above code I found issue that
    1) after first order completion it doesn’t let me repeat that order.
    2) if incase in that particular moment order rejected(may be due to lack of funds) or I cancel it manually as I know mkt is going reverse then it doesn’t let me fire order again after some time.
    Request you to add all order outcome in dictionary as -Buy, Sell, Cancelled,Rejected
    so incase of Cancelled, Rejected it should fire order again but incase buy then don’t as buy exist. Happy to clarify more in case of doubt. Thanks for your support.

    1. Hi Shashi Kant,

      1) Hope you missed the ‘SaltKey’ parameter. We added a additional parameter ‘SaltKey’ to place more than one order for single transaction.

      SaltKey example usage
      ‘Assume you want to place 3 BUY orders at 3 different levels. You need to choose 3 unique SaltKeys, it is nothing but just 2 character string. Say you have choosen O1, O2, O3

      ‘Order1 = PlaceOCO_BuySell(Exch,……,”O1″)
      ‘Order1 = PlaceOCO_BuySell(Exch,……,”O2″)
      ‘Order1 = PlaceOCO_BuySell(Exch,……,”O3″)

      2) This VBA code is a, very basic example to restrict another function call using in-memory variables. If you want to link this to your order status, then it needs complex coding, which is outside the scope of this article.

Leave A Comment?