{"id":243,"date":"2021-06-26T03:11:54","date_gmt":"2021-06-26T03:11:54","guid":{"rendered":"https:\/\/algomojo.com\/blog\/?p=243"},"modified":"2023-09-13T04:39:39","modified_gmt":"2023-09-13T04:39:39","slug":"algomojo-sending-simultaneous-orders-to-multi-clients-excelsheet-module-and-vba-code","status":"publish","type":"post","link":"https:\/\/algomojo.com\/blog\/algomojo-sending-simultaneous-orders-to-multi-clients-excelsheet-module-and-vba-code\/","title":{"rendered":"Algomojo \u2013 Sending Simultaneous Orders to Multi Clients \u2013 ExcelSheet Module and VBA Code"},"content":{"rendered":"\n

Got frequent requirements from a couple of traders to automate Excel based button trading where with a click of a button one could punch orders in multiple accounts (friends & family).<\/p>\n\n\n\n

Designed a simple interface in excel on sending simultaneous orders from the excel application to multiple trading accounts. Code uses algomojo API to transmit multiple orders using the PlaceMultiOrder function.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Supported Broker<\/strong>: Angel Angel Broking, AliceBlue, Firstock, Tradejini, Mastertrust, Zebu
Requirements:<\/strong> Algomojo Login<\/p>\n\n\n\n

Download the Excel File<\/a><\/p>\n\n\n\n

Steps to follow after downloading the Excel File<\/strong>
1)Download the File and Unzip it to the local folder
2)Press Enable Content button as the Security Warning display that Macros have been disabled
3)Sheet Place Order<\/strong> is used to Send MultiOrders
4)Sheet Clients <\/strong>is used to manage multiple clients, API Keys , API Secret Keys<\/p>\n\n\n\n

Here are the Angel Broking supported order parameters in the excel sheets<\/p>\n\n\n\n

Param<\/th>Value<\/th>Description<\/th><\/tr><\/thead>
variety<\/td>NORMAL
STOPLOSS
AMO
ROBO<\/td>		Normal Order (Regular)
Stop-loss order,
After Market Order,
ROBO (Bracket Order)<\/td><\/tr>
transactiontype<\/td>BUY
SELL<\/td>		Buy
Sell<\/td><\/tr>
ordertype<\/td>MARKET
LIMIT
STOPLOSS_LIMIT
STOPLOSS_MARKET<\/td>		Market Order(MKT)
Limit Order(L)
Stop Loss Limit Order(SL)
Stop Loss Market Order(SL-M)<\/td><\/tr>
producttype<\/td>DELIVERY
CARRYFORWARD
MARGIN
INTRADAY
BO<\/td>		Cash & Carry for equity (CNC)
Normal for futures and options (NRML)
Margin Delivery
Margin Intraday Square off (MIS)
Bracket Order (Only for ROBO)<\/td><\/tr>
Duration<\/td>DAY
IOC<\/td>		Regular Order
Immediate or Cancel<\/td><\/tr>
exchange<\/td>BSE
NSE
NFO
MCX<\/td>		BSE Equity
NSE Equity
NSE Future and Options
MCX Commodity<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Order Parameters<\/h2>\n\n\n\n

These parameters are common across different order varieties.<\/p>\n\n\n\n

Param<\/th>Description<\/th><\/tr><\/thead>
tradingsymbol<\/td>Trading Symbol of the instrument<\/td><\/tr>
symboltoken<\/td>Symbol Token is unique identifier ( can be obtained from Algomojo Watchlist section->Security Info)<\/td><\/tr>
Exchange<\/td>Name of the exchange (BSE,NSE,NFO,MCX)<\/td><\/tr>
transactiontype<\/td>BUY or SELL<\/td><\/tr>
ordertype<\/td>Order type (MARKET, LIMIT etc.)<\/td><\/tr>
quantity<\/td>Quantity to transact<\/td><\/tr>
producttype<\/td>Product type (CNC,MIS)<\/td><\/tr>
price<\/td>The min or max price to execute the order at (for LIMIT orders)<\/td><\/tr>
triggerprice<\/td>The price at which an order should be triggered (SL, SL-M)<\/td><\/tr>
squareoff<\/td>Only For ROBO (Bracket Order)<\/td><\/tr>
stoploss<\/td>Only For ROBO (Bracket Order)<\/td><\/tr>
trailingStopLoss<\/td>Only For ROBO (Bracket Order)<\/td><\/tr>
disclosedquantity<\/td>Quantity to disclose publicly (for equity trades)<\/td><\/tr>
duration<\/td>Order duration (DAY,IOC)<\/td><\/tr>
<\/td><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

VBA Source Code<\/strong> for Angel Broking<\/strong><\/p>\n\n\n\n

Sub FetchLogin()\n\nDim Broker As String\nDim Version As String\nDim apikey As String\nDim apisecret As String\nDim apidata As String\nDim numofRows As Integer\nDim i As Integer\n\nBroker = "an"\nVersion = "1.0"\n\nnumofRows = Range("A5", Range("A5").End(xlDown)).Rows.Count\napidata = "{}"\n\nFor i = 0 To numofRows - 1\n    apikey = Sheets("Clients").Cells(5 + i, "B").Value\n    apisecret = Sheets("Clients").Cells(5 + i, "C").Value\n    Limits = AMConnect(apikey, apisecret, Broker, Version, "Limits", apidata)\n    Profile = AMConnect(apikey, apisecret, Broker, Version, "Profile", apidata)\n    Set ProfileResponse = JsonConverter.ParseJson(Profile)\n    Set LimitsResponse = JsonConverter.ParseJson(Limits)\n    \n    If StrComp(ProfileResponse("message"), "SUCCESS") = 0 Then\n        Sheets("Clients").Cells(5 + i, "E") = ProfileResponse("data")("name")\n        Sheets("Clients").Cells(5 + i, "G") = LimitsResponse("data")("net")\n        Sheets("Clients").Cells(5 + i, "G") = LimitsResponse("data")("availablecash")\n    Else\n        Sheets("Clients").Cells(5 + i, "E") = "Login Error"\n        Sheets("Clients").Cells(5 + i, "F") = "Login Error"\n        Sheets("Clients").Cells(5 + i, "G") = "Login Error"\n    End If\n    \nNext\nEnd Sub\n\n\n\n\nSub AlgomojoMulitOrder()\n\n\nDim Broker As String\nDim Version As String\nDim ClientID As String\nDim user_apikey As String\nDim api_secret As String\nDim masterapikey As String\nDim masterapisecret As String\nDim variety As String\nDim tradingsymbol As String\nDim symboltoken As String\nDim transactiontype As String\nDim exchange As String\nDim ordertype As String\nDim producttype As String\nDim duration As String\nDim price As String\nDim squareoff As String\nDim stoploss As String\nDim quantity As String\nDim triggerprice As String\nDim trailingStopLoss As String\nDim disclosedquantity As String\nDim MultiplierQty As Long\n\n\nDim apidata As String\nDim numofRows As Long\nDim i As Integer\n\nBroker = Sheets("PlaceOrder").Cells(5, "A").Value\nVersion = Sheets("PlaceOrder").Cells(5, "B").Value\nmasterapikey = Sheets("PlaceOrder").Cells(5, "D").Value\nmasterapisecret = Sheets("PlaceOrder").Cells(5, "E").Value\ntradingsymbol = Sheets("PlaceOrder").Cells(5, "F").Value\nsymboltoken = Sheets("PlaceOrder").Cells(5, "G").Value\nquantity = Sheets("PlaceOrder").Cells(5, "H").Value\ntransactiontype = Sheets("PlaceOrder").Cells(5, "I").Value\nexchange = Sheets("PlaceOrder").Cells(5, "J").Value\nvariety = Sheets("PlaceOrder").Cells(5, "K").Value\nordertype = Sheets("PlaceOrder").Cells(5, "L").Value\nproducttype = Sheets("PlaceOrder").Cells(5, "M").Value\nduration = Sheets("PlaceOrder").Cells(5, "N").Value\nprice = Sheets("PlaceOrder").Cells(5, "O").Value\ntriggerprice = Sheets("PlaceOrder").Cells(5, "P").Value\nsquareoff = Sheets("PlaceOrder").Cells(5, "Q").Value\nstoploss = Sheets("PlaceOrder").Cells(5, "R").Value\ntrailingStopLoss = Sheets("PlaceOrder").Cells(5, "S").Value\ndisclosedquantity = Sheets("PlaceOrder").Cells(5, "T").Value\n\n'clear the table contents of the display output\nWorksheets("PlaceOrder").Range("C16:F200").Clear\n\nSheets("Clients").Activate\nnumofRows = Sheets("Clients").Range("A5", Range("A5").End(xlDown)).Rows.Count\napidata = "{""" & "orders" & """:" & "[ "\n\nstgy_name = "Excel"\n\n\n    \nFor i = 0 To numofRows - 1\n    Sheets("Clients").Activate\n    ClientID = Sheets("Clients").Cells(5 + i, "A").Value\n    user_apikey = Sheets("Clients").Cells(5 + i, "B").Value\n    api_secret = Sheets("Clients").Cells(5 + i, "C").Value\n    MultiplierQty = Sheets("Clients").Cells(5 + i, "D").Value * CInt(quantity)\n    Sheets("PlaceOrder").Activate\n    Sheets("PlaceOrder").Cells(16 + i, "C") = ClientID\n    apidata = apidata & "{""" _\n                & "order_refno" & """:""" & CStr(i + 1) & """,""" _\n                & "user_apikey" & """:""" & user_apikey & """,""" _\n                & "api_secret" & """:""" & api_secret & """,""" _\n                & "stgy_name" & """:""" & stgy_name & """,""" _\n                & "variety" & """:""" & variety & """,""" _\n                & "tradingsymbol" & """:""" & tradingsymbol & """,""" _\n                & "symboltoken" & """:""" & symboltoken & """,""" _\n                & "transactiontype" & """:""" & transactiontype & """,""" _\n                & "exchange" & """:""" & exchange & """,""" _\n                & "ordertype" & """:""" & ordertype & """,""" _\n                & "producttype" & """:""" & producttype & """,""" _\n                & "duration" & """:""" & duration & """,""" _\n                & "price" & """:""" & price & """,""" _\n                & "squareoff" & """:""" & squareoff & """,""" _\n                & "stoploss" & """:""" & stoploss & """,""" _\n                & "quantity" & """:""" & CStr(MultiplierQty) & """,""" _\n                & "triggerprice" & """:""" & triggerprice & """,""" _\n                & "trailingStopLoss" & """:""" & trailingStopLoss & """,""" _\n                & "disclosedquantity" & """:""" & disclosedquantity\n                \n    If i = numofRows - 1 Then\n        apidata = apidata & """}"\n    Else\n        apidata = apidata & """},"\n    End If\n       \n                \nNext\n    \napidata = apidata & "]}"\n\n\n\nPlaceOrder = AMConnect(masterapikey, masterapisecret, Broker, Version, "PlaceMultiOrder", apidata)\nSet PlaceOrderResponse = JsonConverter.ParseJson(PlaceOrder)\n\n\n\nFor i = 0 To numofRows - 1\n     If StrComp(PlaceOrderResponse(i + 1)("message"), "SUCCESS") = 0 Then\n        Sheets("PlaceOrder").Cells(16 + i, "D").Value = PlaceOrderResponse(i + 1)("message")\n        Sheets("PlaceOrder").Cells(16 + i, "E").Value = PlaceOrderResponse(i + 1)("data")("script")\n        Sheets("PlaceOrder").Cells(16 + i, "F").Value = PlaceOrderResponse(i + 1)("data")("orderid")\n        Sheets("PlaceOrder").Cells(16 + i, "F").NumberFormat = "0"\n    Else\n        Sheets("PlaceOrder").Cells(16 + i, "D") = "Login Error"\n        Sheets("PlaceOrder").Cells(16 + i, "E") = "Login Error"\n        Sheets("PlaceOrder").Cells(16 + i, "F") = "Login Error"\n    End If\n    \nNext\n\n'check multiorder response\n'Sheets("PlaceOrder").Cells(12, "D") = PlaceOrder\n\nEnd Sub\n\n\n\n\nPrivate Function AMConnect(api_key As String, api_secret As String, Broker As String, Version As String, api_name As String, InTD As String) As String\n    Dim objHTTP As Object\n    Dim result As String\n    Dim postdate As String\n    Dim BrkPrefix As String\n    Dim BaseURL As String\n    BaseURL = "https:\/\/" & LCase(Broker) & "api.algomojo.com\/" & Version & "\/"\n    postdata = "{""" & "api_key" & """:""" & api_key & """,""" _\n                    & "api_secret" & """:""" & api_secret & """,""" _\n                    & "data" & """:" & InTD & "}"\n    'MsgBox (postdata)\n    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")\n    Url = BaseURL & api_name\n    objHTTP.Open "POST", Url, False\n    objHTTP.setRequestHeader "Content-type", "application\/json"\n    objHTTP.send (postdata)\n    result = objHTTP.responseText\n    Set objHTTP = Nothing\n    AMConnect = result\n    \nEnd Function\n\n\n<\/code><\/pre>\n\n\n\n
VBA Source Code for Aliceblue, Zebu, Tradejini, Mastertrust<\/strong><\/p>\n\n\n\n
Sub FetchLogin()\n\nDim Broker As String\nDim Version As String\nDim uid As String\nDim apikey As String\nDim apisecret As String\nDim apidata As String\nDim segment As String\nDim Exchange As String\nDim product As String\nDim numofRows As Integer\nDim i As Integer\n\n\nVersion = "1.0"\nsegment = "ALL"\nExchange = ""\nproduct = ""\n\nnumofRows = Range("A5", Range("A5").End(xlDown)).Rows.Count\n\nFor i = 0 To numofRows - 1\n    Broker = Sheets("Clients").Cells(5 + i, "A").Value\n    uid = Sheets("Clients").Cells(5 + i, "B").Value\n    apikey = Sheets("Clients").Cells(5 + i, "C").Value\n    apisecret = Sheets("Clients").Cells(5 + i, "D").Value\n    \n    \n    apidata = "{""" _\n                & "uid" & """:""" & uid & """,""" _\n                & "actid" & """:""" & uid & """,""" _\n                & "segment" & """:""" & segment & """,""" _\n                & "Exchange" & """:""" & Exchange & """,""" _\n                & "product" & """:""" & product & """" _\n                & "}"\n\n    \n    \n    Limits = AMConnect(apikey, apisecret, Broker, Version, "Limits", apidata)\n    'MsgBox (Limits)\n    Set LimitsResponse = JsonConverter.ParseJson(Limits)\n    \n    If StrComp(LimitsResponse("stat"), "Ok") = 0 Then\n        Sheets("Clients").Cells(5 + i, "F") = LimitsResponse("OpeningBalance")\n        Sheets("Clients").Cells(5 + i, "G") = LimitsResponse("Netcashavailable")\n        Sheets("Clients").Cells(5 + i, "H") = LimitsResponse("turnover")\n    Else\n        Sheets("Clients").Cells(5 + i, "F") = "Login Error"\n        Sheets("Clients").Cells(5 + i, "G") = "Login Error"\n        Sheets("Clients").Cells(5 + i, "H") = "Login Error"\n    End If\n    \nNext\nEnd Sub\n\n\n\n\nSub AlgomojoMulitOrder()\n\n\nDim Broker As String\nDim Version As String\nDim ClientID As String\nDim user_apikey As String\nDim api_secret As String\nDim masterapikey As String\nDim masterapisecret As String\nDim s_prdt_ali As String\nDim Tsym As String\nDim exch As String\nDim Ttranstype As String\nDim Ret As String\nDim prctyp As String\nDim qty As String\nDim discqty As String\nDim MktPro As String\nDim Price As String\nDim TrigPrice As String\nDim Pcode As String\nDim AMO As String\nDim MultiplierQty As Long\n\n\nDim apidata As String\nDim numofRows As Long\nDim i As Integer\n\ns_prdt_ali = "BO:BO||CNC:CNC||CO:CO||MIS:MIS||NRML:NRML"\nMktPro = "NA"\ndiscqty = "0"\nAMO = "NO"\n\nBroker = Sheets("PlaceOrder").Cells(5, "A").Value\nVersion = Sheets("PlaceOrder").Cells(5, "B").Value\nmasterapikey = Sheets("PlaceOrder").Cells(5, "D").Value\nmasterapisecret = Sheets("PlaceOrder").Cells(5, "E").Value\nTsym = Sheets("PlaceOrder").Cells(5, "F").Value\nqty = Sheets("PlaceOrder").Cells(5, "G").Value\nTtranstype = Sheets("PlaceOrder").Cells(5, "H").Value\nexch = Sheets("PlaceOrder").Cells(5, "I").Value\nPcode = Sheets("PlaceOrder").Cells(5, "J").Value\nprctyp = Sheets("PlaceOrder").Cells(5, "K").Value\nRet = Sheets("PlaceOrder").Cells(5, "L").Value\nPrice = Sheets("PlaceOrder").Cells(5, "M").Value\nTrigPrice = Sheets("PlaceOrder").Cells(5, "N").Value\ndiscqty = Sheets("PlaceOrder").Cells(5, "O").Value\n\n\n'clear the table contents of the display output\nWorksheets("PlaceOrder").Range("C16:F200").Clear\n\nSheets("Clients").Activate\nnumofRows = Sheets("Clients").Range("A5", Range("A5").End(xlDown)).Rows.Count\napidata = "{""" & "orders" & """:" & "[ "\n\nstgy_name = "Excel"\n\n\n    \nFor i = 0 To numofRows - 1\n    Sheets("Clients").Activate\n    ClientID = Sheets("Clients").Cells(5 + i, "B").Value\n    user_apikey = Sheets("Clients").Cells(5 + i, "C").Value\n    api_secret = Sheets("Clients").Cells(5 + i, "D").Value\n    MultiplierQty = Sheets("Clients").Cells(5 + i, "E").Value * CInt(qty)\n    Sheets("PlaceOrder").Activate\n    Sheets("PlaceOrder").Cells(16 + i, "C") = ClientID\n    apidata = apidata & "{""" _\n                & "order_refno" & """:""" & CStr(i + 1) & """,""" _\n                & "user_apikey" & """:""" & user_apikey & """,""" _\n                & "api_secret" & """:""" & api_secret & """,""" _\n                & "stgy_name" & """:""" & stgy_name & """,""" _\n                & "s_prdt_ali" & """:""" & s_prdt_ali & """,""" _\n                & "Tsym" & """:""" & Tsym & """,""" _\n                & "exch" & """:""" & exch & """,""" _\n                & "exchange" & """:""" & Exchange & """,""" _\n                & "Ttranstype" & """:""" & Ttranstype & """,""" _\n                & "Ret" & """:""" & Ret & """,""" _\n                & "prctyp" & """:""" & prctyp & """,""" _\n                & "qty" & """:""" & CStr(MultiplierQty) & """,""" _\n                & "discqty" & """:""" & discqty & """,""" _\n                & "MktPro" & """:""" & MktPro & """,""" _\n                & "Price" & """:""" & Price & """,""" _\n                & "TrigPrice" & """:""" & TrigPrice & """,""" _\n                & "Pcode" & """:""" & Pcode & """,""" _\n                & "AMO" & """:""" & AMO\n                \n    If i = numofRows - 1 Then\n        apidata = apidata & """}"\n    Else\n        apidata = apidata & """},"\n    End If\n       \n                \nNext\n    \napidata = apidata & "]}"\n\n\n\nPlaceOrder = AMConnect(masterapikey, masterapisecret, Broker, Version, "PlaceMultiOrder", apidata)\n\nSet PlaceOrderResponse = JsonConverter.ParseJson(PlaceOrder)\n\n\n\nFor i = 0 To numofRows - 1\n     If StrComp(PlaceOrderResponse(i + 1)("stat"), "Ok") = 0 Then\n        Sheets("PlaceOrder").Cells(16 + i, "D").Value = PlaceOrderResponse(i + 1)("stat")\n        Sheets("PlaceOrder").Cells(16 + i, "E").Value = Tsym\n        Sheets("PlaceOrder").Cells(16 + i, "F").Value = PlaceOrderResponse(i + 1)("NOrdNo")\n        Sheets("PlaceOrder").Cells(16 + i, "F").NumberFormat = "0"\n    Else\n        Sheets("PlaceOrder").Cells(16 + i, "D") = "Login Error"\n        Sheets("PlaceOrder").Cells(16 + i, "E") = "Login Error"\n        Sheets("PlaceOrder").Cells(16 + i, "F") = "Login Error"\n    End If\n    \nNext\n\n'check multiorder response\n'Sheets("PlaceOrder").Cells(12, "D") = PlaceOrder\n\nEnd Sub\n\n\n\n\nPrivate Function AMConnect(api_key As String, api_secret As String, Broker As String, Version As String, api_name As String, InTD As String) As String\n    Dim objHTTP As Object\n    Dim result As String\n    Dim postdate As String\n    Dim BrkPrefix As String\n    Dim BaseURL As String\n    BaseURL = "https:\/\/" & LCase(Broker) & "api.algomojo.com\/" & Version & "\/"\n    postdata = "{""" & "api_key" & """:""" & api_key & """,""" _\n                    & "api_secret" & """:""" & api_secret & """,""" _\n                    & "data" & """:" & InTD & "}"\n    'MsgBox (postdata)\n    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")\n    Url = BaseURL & api_name\n    objHTTP.Open "POST", Url, False\n    objHTTP.setRequestHeader "Content-type", "application\/json"\n    objHTTP.send (postdata)\n    result = objHTTP.responseText\n    Set objHTTP = Nothing\n    AMConnect = result\n    \nEnd Function\n\n\n<\/code><\/pre>\n\n\n\n
VBA Code for Firstock<\/strong><\/p>\n\n\n\n
Sub FetchLogin()\n\nDim Broker As String\nDim Version As String\nDim uid As String\nDim apikey As String\nDim apisecret As String\nDim apidata As String\nDim segment As String\nDim Exchange As String\nDim product As String\nDim numofRows As Integer\nDim i As Long\nDim Total As Long\n\n\nVersion = "1.0"\n\n\nnumofRows = Range("A5", Range("A5").End(xlDown)).Rows.Count\n\nIf numofRows = 1 Then\n    Total = numofRows\nElse\n    Total = numofRows - 1\nEnd If\n\n\n    For i = 0 To Total\n        Broker = Sheets("Clients").Cells(5 + i, "A").Value\n        uid = Sheets("Clients").Cells(5 + i, "B").Value\n        apikey = Sheets("Clients").Cells(5 + i, "C").Value\n        apisecret = Sheets("Clients").Cells(5 + i, "D").Value\n    \n    \n        apidata = "{""" _\n                & "uid" & """:""" & uid & """,""" _\n                & "actid" & """:""" & uid & """" & "}"\n                \n\n    \n        'MsgBox (apidata)\n        Limits = AMConnect(apikey, apisecret, Broker, Version, "Limits", apidata)\n    \n        Set LimitsResponse = JsonConverter.ParseJson(Limits)\n    \n        If StrComp(LimitsResponse("stat"), "Ok") = 0 Then\n            Sheets("Clients").Cells(5 + i, "F") = LimitsResponse("cash")\n            Sheets("Clients").Cells(5 + i, "G") = LimitsResponse("daycash")\n            Sheets("Clients").Cells(5 + i, "H") = LimitsResponse("brkcollamt")\n        Else\n            Sheets("Clients").Cells(5 + i, "F") = "Login Error"\n            Sheets("Clients").Cells(5 + i, "G") = "Login Error"\n            Sheets("Clients").Cells(5 + i, "H") = "Login Error"\n        End If\n    \n    Next\n\nEnd Sub\n\n\n\n\nSub AlgomojoMulitOrder()\n\n\nDim Broker As String\nDim Version As String\nDim ClientID As String\nDim user_apikey As String\nDim api_secret As String\nDim masterapikey As String\nDim masterapisecret As String\nDim s_prdt_ali As String\nDim tsym As String\nDim exch As String\nDim trantype As String\nDim ret As String\nDim prctyp As String\nDim qty As String\nDim dscqty As String\nDim MktPro As String\nDim prc As String\nDim trgprc As String\nDim prd As String\nDim AMO As String\nDim ordersource As String\nDim remarks As String\nDim MultiplierQty As Long\n\n\n\n\nDim apidata As String\nDim numofRows As Long\nDim i As Integer\n\ns_prdt_ali = "BO:BO||CNC:CNC||CO:CO||MIS:MIS||NRML:NRML"\nMktPro = "NA"\ndscqty = "0"\nAMO = "NO"\nordersource = "WEB"\nremarks = "EXCEL"\n\nBroker = Sheets("PlaceOrder").Cells(5, "A").Value\nVersion = Sheets("PlaceOrder").Cells(5, "B").Value\nmasterapikey = Sheets("PlaceOrder").Cells(5, "D").Value\nmasterapisecret = Sheets("PlaceOrder").Cells(5, "E").Value\ntsym = Sheets("PlaceOrder").Cells(5, "F").Value\nqty = Sheets("PlaceOrder").Cells(5, "G").Value\ntrantype = Sheets("PlaceOrder").Cells(5, "H").Value\nexch = Sheets("PlaceOrder").Cells(5, "I").Value\nprd = Sheets("PlaceOrder").Cells(5, "J").Value\nprctyp = Sheets("PlaceOrder").Cells(5, "K").Value\nret = Sheets("PlaceOrder").Cells(5, "L").Value\nprc = Sheets("PlaceOrder").Cells(5, "M").Value\ntrgprc = Sheets("PlaceOrder").Cells(5, "N").Value\ndscqty = Sheets("PlaceOrder").Cells(5, "O").Value\n\n\n'clear the table contents of the display output\nWorksheets("PlaceOrder").Range("C16:F200").Clear\n\nSheets("Clients").Activate\nnumofRows = Sheets("Clients").Range("A5", Range("A5").End(xlDown)).Rows.Count\napidata = "{""" & "orders" & """:" & "[ "\n\nstgy_name = "Excel"\n\n\n    \nFor i = 0 To numofRows - 1\n    Sheets("Clients").Activate\n    ClientID = Sheets("Clients").Cells(5 + i, "B").Value\n    user_apikey = Sheets("Clients").Cells(5 + i, "C").Value\n    api_secret = Sheets("Clients").Cells(5 + i, "D").Value\n    MultiplierQty = Sheets("Clients").Cells(5 + i, "E").Value * CInt(qty)\n    Sheets("PlaceOrder").Activate\n    Sheets("PlaceOrder").Cells(16 + i, "C") = ClientID\n    apidata = apidata & "{""" _\n                & "order_refno" & """:""" & CStr(i + 1) & """,""" _\n                & "user_apikey" & """:""" & user_apikey & """,""" _\n                & "api_secret" & """:""" & api_secret & """,""" _\n                & "stgy_name" & """:""" & stgy_name & """,""" _\n                & "uid" & """:""" & ClientID & """,""" _\n                & "actid" & """:""" & ClientID & """,""" _\n                & "tsym" & """:""" & tsym & """,""" _\n                & "exch" & """:""" & exch & """,""" _\n                & "trantype" & """:""" & trantype & """,""" _\n                & "ret" & """:""" & ret & """,""" _\n                & "prctyp" & """:""" & prctyp & """,""" _\n                & "qty" & """:""" & CStr(MultiplierQty) & """,""" _\n                & "dscqty" & """:""" & dscqty & """,""" _\n                & "MktPro" & """:""" & MktPro & """,""" _\n                & "prc" & """:""" & prc & """,""" _\n                & "prd" & """:""" & prd & """,""" _\n                & "AMO" & """:""" & AMO & """,""" _\n                & "ordersource" & """:""" & ordersource & """,""" _\n                & "remarks" & """:""" & remarks\n                \n    If i = numofRows - 1 Then\n        apidata = apidata & """}"\n    Else\n        apidata = apidata & """},"\n    End If\n       \n                \nNext\n    \napidata = apidata & "]}"\n'MsgBox (apidata)\n\nPlaceOrder = AMConnect(masterapikey, masterapisecret, Broker, Version, "PlaceMultiOrder", apidata)\n\n\nSet PlaceOrderResponse = JsonConverter.ParseJson(PlaceOrder)\n\n\n\nFor i = 0 To numofRows - 1\n     If StrComp(PlaceOrderResponse(i + 1)("stat"), "Ok") = 0 Then\n        Sheets("PlaceOrder").Cells(16 + i, "D").Value = PlaceOrderResponse(i + 1)("stat")\n        Sheets("PlaceOrder").Cells(16 + i, "E").Value = tsym\n        Sheets("PlaceOrder").Cells(16 + i, "F").Value = PlaceOrderResponse(i + 1)("norenordno")\n        Sheets("PlaceOrder").Cells(16 + i, "F").NumberFormat = "0"\n    Else\n        Sheets("PlaceOrder").Cells(16 + i, "D") = "Login Error"\n        Sheets("PlaceOrder").Cells(16 + i, "E") = "Login Error"\n        Sheets("PlaceOrder").Cells(16 + i, "F") = "Login Error"\n    End If\n    \nNext\n\n'check multiorder response\n'Sheets("PlaceOrder").Cells(12, "D") = PlaceOrder\n\nEnd Sub\n\n\n\n\nPrivate Function AMConnect(api_key As String, api_secret As String, Broker As String, Version As String, api_name As String, InTD As String) As String\n    Dim objHTTP As Object\n    Dim result As String\n    Dim postdate As String\n    Dim BrkPrefix As String\n    Dim BaseURL As String\n    BaseURL = "https:\/\/" & LCase(Broker) & "api.algomojo.com\/" & Version & "\/"\n    postdata = "{""" & "api_key" & """:""" & api_key & """,""" _\n                    & "api_secret" & """:""" & api_secret & """,""" _\n                    & "data" & """:" & InTD & "}"\n    'MsgBox (postdata)\n    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")\n    Url = BaseURL & api_name\n    objHTTP.Open "POST", Url, False\n    objHTTP.setRequestHeader "Content-type", "application\/json"\n    objHTTP.send (postdata)\n    result = objHTTP.responseText\n    Set objHTTP = Nothing\n    AMConnect = result\n    \nEnd Function\n\n\n<\/code><\/pre>\n\n\n\n
Credits<\/strong><\/p>\n\n\n\n
VBA Code uses VBA-JSON for Json Processing<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Got frequent requirements from a couple of traders to automate Excel based button trading where with a click of a button one could punch orders in multiple accounts (friends & family). Designed a simple interface in excel on sending simultaneous orders from the excel application to multiple trading accounts. Code uses algomojo API to transmit …<\/p>\n
  Algomojo \u2013 Sending Simultaneous Orders to Multi Clients \u2013 ExcelSheet Module and VBA Code<\/span> Read More »<\/a><\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[2,38],"tags":[117,34,124,57],"_links":{"self":[{"href":"https:\/\/algomojo.com\/blog\/wp-json\/wp\/v2\/posts\/243"}],"collection":[{"href":"https:\/\/algomojo.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/algomojo.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/algomojo.com\/blog\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/algomojo.com\/blog\/wp-json\/wp\/v2\/comments?post=243"}],"version-history":[{"count":10,"href":"https:\/\/algomojo.com\/blog\/wp-json\/wp\/v2\/posts\/243\/revisions"}],"predecessor-version":[{"id":608,"href":"https:\/\/algomojo.com\/blog\/wp-json\/wp\/v2\/posts\/243\/revisions\/608"}],"wp:attachment":[{"href":"https:\/\/algomojo.com\/blog\/wp-json\/wp\/v2\/media?parent=243"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/algomojo.com\/blog\/wp-json\/wp\/v2\/categories?post=243"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/algomojo.com\/blog\/wp-json\/wp\/v2\/tags?post=243"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}