Option Explicit Private p&, token, dic Const nCellMax = 500 Function ParseJson(JSON$, Optional key$ = "obj") As Object p = 1 token = Tokenize(JSON) Set dic = CreateObject("Scripting.Dictionary") If token(p) = "{" Then ParseObj key Else ParseArr key Set ParseJson = dic End Function Function ParseObj(key$) Do: p = p + 1 Select Case token(p) Case "]" Case "[": ParseArr key Case "{": ParseObj key Case "{" If token(p + 1) = "}" Then p = p + 1 dic.Add key, "null" Else ParseObj key End If Case "}": key = ReducePath(key): Exit Do Case ":": key = key & "." & token(p - 1) Case ",": key = ReducePath(key) Case Else: If token(p + 1) <> ":" Then If dic.Exists(key) Then dic.Item(key) = token(p) Else dic.Add key, token(p) End If End If End Select Loop End Function Function ParseArr(key$) Dim e& Do: p = p + 1 Select Case token(p) Case "}" Case "{": ParseObj key & ArrayID(e) Case "[": ParseArr key Case "]": Exit Do Case ":": key = key & ArrayID(e) Case ",": e = e + 1 Case Else: If Not dic.Exists(key & ArrayID(e)) Then dic.Add key & ArrayID(e), token(p) End If End Select Loop End Function Function Tokenize(s$) Const Pattern = """(([^""\\]|\\.)*)""|[+\-]?(?:0|[1-9]\d*)(?:\.\d*)?(?:[eE][+\-]?\d+)?|\w+|[^\s""']+?" Tokenize = RExtract(s, Pattern, True) End Function Function RExtract(s$, Pattern, Optional bGroup1Bias As Boolean, Optional bGlobal As Boolean = True) Dim c&, m, n, v With CreateObject("vbscript.regexp") .Global = bGlobal .MultiLine = False .IgnoreCase = True .Pattern = Pattern If .Test(s) Then Set m = .Execute(s) ReDim v(1 To m.Count) For Each n In m c = c + 1 v(c) = n.Value If bGroup1Bias Then If Len(n.SubMatches(0)) Or n.Value = """""" Then v(c) = n.SubMatches(0) Next End If End With RExtract = v End Function Function ArrayID$(e) ArrayID = "(" & e & ")" End Function Function ReducePath$(key$) If InStr(key, ".") Then ReducePath = Left(key, InStrRev(key, ".") - 1) Else ReducePath = key End Function Function GetFilteredValues(dic, match) Dim c&, i&, v, w v = dic.Keys ReDim w(1 To dic.Count) For i = 0 To UBound(v) If v(i) Like match Then c = c + 1 w(c) = dic(v(i)) End If Next ReDim Preserve w(1 To c) GetFilteredValues = w End Function Function GetFilteredTable(dic, cols) Dim c&, i&, j&, v, w, z v = dic.Keys z = GetFilteredValues(dic, cols(0)) ReDim w(1 To UBound(z), 1 To UBound(cols) + 1) For j = 1 To UBound(cols) + 1 z = GetFilteredValues(dic, cols(j - 1)) For i = 1 To UBound(z) w(i, j) = z(i) Next Next GetFilteredTable = w End Function Function OpenTextFile$(f) With CreateObject("ADODB.Stream") .Charset = "utf-8" .Open .LoadFromFile f OpenTextFile = .ReadText End With End Function Function PrintDic(dictionary As Object) Dim key As Variant For Each key In dic.Keys Debug.Print key, dic(key) Next key End Function Sub Auto_Open() If MsgBox("This spreadsheet is to be used for demonstration purposes only. Use at your own risk. Wattcollc and affiliates are not liable for any consequential damages you may incur. Please use this demo at your own risk. Continue?", vbYesNo) = vbNo Then ActiveWorkbook.Close End If End Sub Sub mcrBuySell() Dim x As Integer Dim cStock As String Dim nQty As Integer Dim nLimit As Double Dim nStopLoss As Double Dim nPrice As Double Dim nSell As Double Dim cMsg As String Dim cDuration As String Dim lProceed As Boolean 'B=Price 'C=Qty Buy/Sell 'D=Limit 'E=Sell 'F=Stop Loss 'G=DURATION 'H=Order Value 'I=Notes 'Get latest prices If BlankStockRows Then Exit Sub mcrGetQuotes For x = 5 To nCellMax nLimit = 0 nStopLoss = 0 nSell = 0 cStock = ThisWorkbook.Sheets("Stocks").range("A" + Trim(Str(x))).Value If Len(cStock) = 0 Then x = nCellMax ColorCells False, x ColorQuoteCells False, x If Len(cStock) > 0 And Len(cStock) < 7 And _ IsNumeric(ThisWorkbook.Sheets("Stocks").range("C" + Trim(Str(x))).Value) Then nQty = ThisWorkbook.Sheets("Stocks").range("C" + Trim(Str(x))).Value If nQty <> 0 Then If Not IsNumeric(ThisWorkbook.Sheets("Stocks").range("D" + Trim(Str(x))).Value) Then ThisWorkbook.Sheets("Stocks").range("D" + Trim(Str(x))).Value = 0 End If nLimit = ThisWorkbook.Sheets("Stocks").range("D" + Trim(Str(x))).Value cDuration = ThisWorkbook.Sheets("Stocks").range("G" + Trim(Str(x))).Value If nLimit = 0 And ThisWorkbook.Sheets("Stocks").range("G" + Trim(Str(x))).Value <> "DAY" Then If MsgBox(cStock + " is designated as a GTC MARKET order. It will be changed to a DAY MARKET order. Proceed?", vbYesNo) = vbNo Then MsgBox "Order for " + cStock + " was not placed." ThisWorkbook.Sheets("Stocks").range("I" + Trim(Str(x))).Value = "Cancelled" Exit Sub Else ThisWorkbook.Sheets("Stocks").range("G" + Trim(Str(x))).Value = "DAY" End If End If nPrice = ThisWorkbook.Sheets("Stocks").range("B" + Trim(Str(x))).Value If ThisWorkbook.Sheets("Stocks").range("G" + Trim(Str(x))).Value <> "DAY" Then ThisWorkbook.Sheets("Stocks").range("G" + Trim(Str(x))).Value = "GTC" End If If nQty > 0 Then 'Buy Order 'Check Limit If Not IsNumeric(ThisWorkbook.Sheets("Stocks").range("D" + Trim(Str(x))).Value) Then ThisWorkbook.Sheets("Stocks").range("D" + Trim(Str(x))).Value = 0 Else nLimit = ThisWorkbook.Sheets("Stocks").range("D" + Trim(Str(x))).Value If nLimit > 0 Then If nLimit > nPrice Then MsgBox cStock + " limit price is greater than the current price! Please correct. Nothing has been purchased." Exit Sub End If End If End If 'Check Sell If Not IsNumeric(ThisWorkbook.Sheets("Stocks").range("E" + Trim(Str(x))).Value) Then ThisWorkbook.Sheets("Stocks").range("E" + Trim(Str(x))).Value = 0 nSell = 0 Else nSell = ThisWorkbook.Sheets("Stocks").range("E" + Trim(Str(x))).Value If nSell > 0 Then If nLimit > 0 Then If nSell < nLimit Then MsgBox cStock + " sell price is less than the limit price! Please correct. Nothing has been purchased." Exit Sub End If Else If nSell < nPrice Then MsgBox cStock + " sell price is less than the current price! Please correct. Nothing has been purchased." Exit Sub End If End If End If End If 'Check StopLoss If Not IsNumeric(ThisWorkbook.Sheets("Stocks").range("F" + Trim(Str(x))).Value) Then ThisWorkbook.Sheets("Stocks").range("F" + Trim(Str(x))).Value = 0 nStopLoss = 0 Else nStopLoss = ThisWorkbook.Sheets("Stocks").range("F" + Trim(Str(x))).Value If nStopLoss > 0 Then If nLimit > 0 Then If nStopLoss > nLimit Then MsgBox cStock + " stop loss price is greater than the limit price! Please correct. Nothing has been purchased." Exit Sub End If End If If nSell > 0 Then If nStopLoss > nSell Then MsgBox cStock + " stop loss price is greater than the sell price! Please correct. Nothing has been purchased." Exit Sub End If End If If nStopLoss > nPrice Then MsgBox cStock + " stop loss price is greater than the current price! Please correct. Nothing has been purchased." Exit Sub End If End If End If 'IF YOU USE THINK OR SWIM PLATFORM YOU CAN COMMENT OUT THIS CODE AND OBTAIN MORE FUNCTIONALITY 'This type of order will create an error on the TDA website. 'The order will go through, but you will get an error on the TDA Order Status page that reads: '"Review Orders information is currently unavailable. Please try again later." 'You will not be able to see any pending orders and the page will stay "locked up" like this for 24 hours before it returns to normal. 'However, if you use the Think or Swim platform, it works fine. 'COMMENT START HERE If (nSell > 0 And nStopLoss = 0) Or (nSell = 0 And nStopLoss > 0) Then 'TDA does not support this - locks up order status page If nStopLoss = 0 Then MsgBox cStock + " cannot have a sell price and no stop loss price. The TDA API will give an error if this happens. Please add a stop loss price even if it is unreasonably low. Nothing has been purchased." End If If nSell = 0 Then MsgBox cStock + " cannot have a stop loss price and no sell price. The TDA API will give an error if this happens. Please add a sell price even if it is unreasonably high. Nothing has been purchased." End If Exit Sub End If 'COMMENT END HERE Else 'Sell Order 'Check Limit If Not IsNumeric(ThisWorkbook.Sheets("Stocks").range("D" + Trim(Str(x))).Value) Then ThisWorkbook.Sheets("Stocks").range("D" + Trim(Str(x))).Value = 0 Else nLimit = ThisWorkbook.Sheets("Stocks").range("D" + Trim(Str(x))).Value If nLimit < nPrice And nLimit <> 0 Then MsgBox cStock + " limit price is less than the current price! Please correct. Nothing has been sold." Exit Sub End If End If 'No Sell ThisWorkbook.Sheets("Stocks").range("E" + Trim(Str(x))).Value = 0 'No StopLoss ThisWorkbook.Sheets("Stocks").range("F" + Trim(Str(x))).Value = 0 End If Else 'there is a stock on this line, but no buy/sell order ThisWorkbook.Sheets("Stocks").range("D" + Trim(Str(x))).Value = 0 ThisWorkbook.Sheets("Stocks").range("E" + Trim(Str(x))).Value = 0 ThisWorkbook.Sheets("Stocks").range("F" + Trim(Str(x))).Value = 0 ThisWorkbook.Sheets("Stocks").range("G" + Trim(Str(x))).Value = "" ThisWorkbook.Sheets("Stocks").range("I" + Trim(Str(x))).Value = "" End If Else 'There is no stock on the line ThisWorkbook.Sheets("Stocks").range("D" + Trim(Str(x))).Value = "" ThisWorkbook.Sheets("Stocks").range("E" + Trim(Str(x))).Value = "" ThisWorkbook.Sheets("Stocks").range("F" + Trim(Str(x))).Value = "" ThisWorkbook.Sheets("Stocks").range("G" + Trim(Str(x))).Value = "" ThisWorkbook.Sheets("Stocks").range("H" + Trim(Str(x))).Value = "" ThisWorkbook.Sheets("Stocks").range("I" + Trim(Str(x))).Value = "" End If Next x 'Loop through column A and purchase/sell any valid stock symbols with a quantity <> 0 For x = 5 To nCellMax cStock = ThisWorkbook.Sheets("Stocks").range("A" + Trim(Str(x))).Value ColorCells True, x If Len(cStock) = 0 Then ColorCells False, x x = nCellMax End If nLimit = ThisWorkbook.Sheets("Stocks").range("D" + Trim(Str(x))).Value nSell = ThisWorkbook.Sheets("Stocks").range("E" + Trim(Str(x))).Value nStopLoss = ThisWorkbook.Sheets("Stocks").range("F" + Trim(Str(x))).Value cDuration = ThisWorkbook.Sheets("Stocks").range("G" + Trim(Str(x))).Value nPrice = ThisWorkbook.Sheets("Stocks").range("B" + Trim(Str(x))).Value cMsg = "" If nLimit > 0 Then cMsg = " with a LIMIT price of " + Trim(Str(nLimit)) Else cMsg = " at MARKET price of " + Trim(Str(nPrice)) End If If nStopLoss > 0 Then cMsg = cMsg + " with a STOP LOSS of " + Trim(Str(nStopLoss)) End If If nSell > 0 Then cMsg = cMsg + " with a SELL PRICE of " + Trim(Str(nSell)) End If If cDuration = "DAY" Then cMsg = cMsg + " - order good for today (DAY)" Else cMsg = cMsg + " - order good until cancel (GTC)" End If lProceed = False If Len(cStock) > 0 And Len(cStock) < 7 And _ IsNumeric(ThisWorkbook.Sheets("Stocks").range("C" + Trim(Str(x))).Value) Then nQty = ThisWorkbook.Sheets("Stocks").range("C" + Trim(Str(x))).Value If nQty > 0 Then If MsgBox("Buy " + Trim(Str(nQty)) + " of " + cStock + cMsg, vbYesNo) = vbYes Then lProceed = True End If If nQty < 0 Then If MsgBox("Sell " + Trim(Str(nQty)) + " of " + cStock + cMsg, vbYesNo) = vbYes Then lProceed = True End If If lProceed Then PlaceOrder cStock, nQty, x, nLimit, nSell, nStopLoss, cDuration Else If nQty <> 0 Then MsgBox "Order for " + cStock + " was not placed." ThisWorkbook.Sheets("Stocks").range("I" + Trim(Str(x))).Value = "Cancelled" End If End If Else ThisWorkbook.Sheets("Stocks").range("I" + Trim(Str(x))).Value = "" End If Next x End Sub Function WriteOut(cString As String) Open "C:\Temp\SQLString.txt" For Output As #1 ' Open file for output. Print #1, cString ', 234 ' Write comma-delimited data. Close #1 ' Close file. End Function Function ColorCells(lOn As Boolean, nRow As Integer) Dim x As Integer ThisWorkbook.Sheets("Stocks").range("A" + Trim(Str(nRow))).Select If lOn Then With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 5296274 .TintAndShade = 0 .PatternTintAndShade = 0 End With Else With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With End If End Function Function ColorQuoteCells(lOn As Boolean, nRow As Integer) Dim x As Integer ThisWorkbook.Sheets("Stocks").range("B" + Trim(Str(nRow))).Select If lOn Then With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 5296274 .TintAndShade = 0 .PatternTintAndShade = 0 End With Else With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark2 .TintAndShade = 0 .PatternTintAndShade = 0 End With End If End Function Function PlaceOrder(cStock As String, nQty As Integer, nPosition As Integer, nLimit As Double, nSell As Double, nStopLoss As Double, cDuration As String) Dim cAccountNumber As String Dim cOS As String Dim httpGet As String Dim schwabClient As Client Set schwabClient = New Client schwabClient.Init cAccountNumber = schwabClient.GetAccountHash() If cDuration = "GTC" Then cDuration = "GOOD_TILL_CANCEL" End If If nLimit = 0 Then cDuration = "DAY" End If 'Buy limit 'Buy limit with a sell price 'Buy limit with a stop loss 'Buy limit with a sell price and a stop loss 'Buy market 'Buy market with a sell price 'Buy market with a stop loss 'Buy market with a sell price and a stop loss 'Sell limit 'Sell market If nQty > 0 Then 'BUY If nLimit > 0 And nStopLoss = 0 And nSell = 0 Then 'Buy limit cOS = cOS + "{" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "SINGLE" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + cDuration + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "LIMIT" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "price" + Chr(34) + ": " + Chr(34) + Trim(Str(nLimit)) + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "BUY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + "}" End If If nLimit > 0 And nStopLoss > 0 And nSell > 0 Then 'Buy limit with a sell price and a stop loss cOS = cOS + "{" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "TRIGGER" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + cDuration + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "LIMIT" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "price" + Chr(34) + ": " + Chr(34) + Trim(Str(nLimit)) + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "BUY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]," + Chr(10) cOS = cOS + " " + Chr(34) + "childOrderStrategies" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "OCO" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "childOrderStrategies" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "SINGLE" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + "GOOD_TILL_CANCEL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "LIMIT" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "price" + Chr(34) + ": " + Chr(34) + Trim(Str(nSell)) + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "SELL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + " }," + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "SINGLE" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + "GOOD_TILL_CANCEL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "STOP" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "stopPrice" + Chr(34) + ": " + Chr(34) + Trim(Str(nStopLoss)) + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "SELL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + "" cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + "}" End If If nLimit > 0 And nStopLoss = 0 And nSell > 0 Then 'Buy limit with a sell price 'This type of order will create an error on the TDA website. The order will go through, but you will get an error on the TDA Order Status page that reads "Review Orders information is currently unavailable. Please try again later." 'You will not be able to see and pending orders and the page will stay "locked up" like this for 24 hours before it returns to normal. 'However, if you use the Think or Swim platform, it works fine. cOS = cOS + "{" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "TRIGGER" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + cDuration + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "LIMIT" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "price" + Chr(34) + ": " + Chr(34) + Trim(Str(nLimit)) + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "BUY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]," + Chr(10) cOS = cOS + " " + Chr(34) + "childOrderStrategies" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "OCO" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "childOrderStrategies" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "SINGLE" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + "GOOD_TILL_CANCEL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "LIMIT" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "price" + Chr(34) + ": " + Chr(34) + Trim(Str(nSell)) + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "SELL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + " }" cOS = cOS + " ]" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + "}" End If If nLimit > 0 And nStopLoss > 0 And nSell = 0 Then 'Buy limit with a stop loss 'This type of order will create an error on the TDA website. The order will go through, but you will get an error on the TDA Order Status page that reads "Review Orders information is currently unavailable. Please try again later." 'You will not be able to see and pending orders and the page will stay "locked up" like this for 24 hours before it returns to normal. 'However, if you use the Think or Swim platform, it works fine. cOS = cOS + "{" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "TRIGGER" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + cDuration + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "LIMIT" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "price" + Chr(34) + ": " + Chr(34) + Trim(Str(nLimit)) + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "BUY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]," + Chr(10) cOS = cOS + " " + Chr(34) + "childOrderStrategies" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "SINGLE" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + "GOOD_TILL_CANCEL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "STOP" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "stopPrice" + Chr(34) + ": " + Chr(34) + Trim(Str(nStopLoss)) + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "SELL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + "" cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + "}" End If If nLimit = 0 And nStopLoss = 0 And nSell = 0 Then 'Market Buy cOS = cOS + "{" + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "MARKET" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + cDuration + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "SINGLE" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "BUY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + "}" End If If nLimit = 0 And nStopLoss = 0 And nSell > 0 Then 'Buy market with a sell price 'This type of order will create an error on the TDA website. The order will go through, but you will get an error on the TDA Order Status page that reads "Review Orders information is currently unavailable. Please try again later." 'You will not be able to see and pending orders and the page will stay "locked up" like this for 24 hours before it returns to normal. 'However, if you use the Think or Swim platform, it works fine. cOS = cOS + "{" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "TRIGGER" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + cDuration + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "MARKET" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "BUY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]," + Chr(10) cOS = cOS + " " + Chr(34) + "childOrderStrategies" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "OCO" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "childOrderStrategies" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "SINGLE" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + "GOOD_TILL_CANCEL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "LIMIT" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "price" + Chr(34) + ": " + Chr(34) + Trim(Str(nSell)) + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "SELL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + " }" cOS = cOS + " ]" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + "}" End If If nLimit = 0 And nStopLoss > 0 And nSell = 0 Then 'Buy market with a stop loss 'This type of order will create an error on the TDA website. The order will go through, but you will get an error on the TDA Order Status page that reads "Review Orders information is currently unavailable. Please try again later." 'You will not be able to see and pending orders and the page will stay "locked up" like this for 24 hours before it returns to normal. 'However, if you use the Think or Swim platform, it works fine. cOS = cOS + "{" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "TRIGGER" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + cDuration + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "MARKET" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "BUY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]," + Chr(10) cOS = cOS + " " + Chr(34) + "childOrderStrategies" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "SINGLE" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + "GOOD_TILL_CANCEL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "STOP" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "stopPrice" + Chr(34) + ": " + Chr(34) + Trim(Str(nStopLoss)) + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "SELL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + "" cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + "}" End If If nLimit = 0 And nStopLoss > 0 And nSell > 0 Then 'Buy market with a sell price and a stop loss cOS = cOS + "{" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "TRIGGER" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + cDuration + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "MARKET" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "BUY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]," + Chr(10) cOS = cOS + " " + Chr(34) + "childOrderStrategies" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "OCO" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "childOrderStrategies" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "SINGLE" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + "GOOD_TILL_CANCEL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "LIMIT" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "price" + Chr(34) + ": " + Chr(34) + Trim(Str(nSell)) + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "SELL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + " }," + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "SINGLE" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + "GOOD_TILL_CANCEL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "STOP" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "stopPrice" + Chr(34) + ": " + Chr(34) + Trim(Str(nStopLoss)) + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "SELL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(nQty)) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + "" cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + "}" End If '---------------------------------------------------------------- Else 'SELL If nLimit > 0 Then 'Limit sell cOS = cOS + "{" + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "TRIGGER" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + cDuration + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "LIMIT" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "price" + Chr(34) + ": " + Chr(34) + Trim(Str(nLimit)) + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "SELL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(Abs(nQty))) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + "}" End If If nLimit = 0 Then 'Market Sell cOS = cOS + "{" + Chr(10) cOS = cOS + " " + Chr(34) + "orderType" + Chr(34) + ": " + Chr(34) + "MARKET" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "session" + Chr(34) + ": " + Chr(34) + "NORMAL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "duration" + Chr(34) + ": " + Chr(34) + cDuration + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderStrategyType" + Chr(34) + ": " + Chr(34) + "SINGLE" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "orderLegCollection" + Chr(34) + ": [" + Chr(10) cOS = cOS + " {" + Chr(10) cOS = cOS + " " + Chr(34) + "instruction" + Chr(34) + ": " + Chr(34) + "SELL" + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "quantity" + Chr(34) + ": " + Trim(Str(Abs(nQty))) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "instrument" + Chr(34) + ": {" + Chr(10) cOS = cOS + " " + Chr(34) + "symbol" + Chr(34) + ": " + Chr(34) + cStock + Chr(34) + "," + Chr(10) cOS = cOS + " " + Chr(34) + "assetType" + Chr(34) + ": " + Chr(34) + "EQUITY" + Chr(34) + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " }" + Chr(10) cOS = cOS + " ]" + Chr(10) cOS = cOS + "}" End If End If Debug.Print cOS WriteOut cOS 'If MsgBox(cOS, vbYesNo) = vbNo Then ' Exit Function 'End If Dim orderPlaceResponse As String httpGet = schwabClient.OrderPlace(cAccountNumber, cOS) If Len(httpGet) = 0 Then ThisWorkbook.Sheets("Stocks").range("I" + Trim(Str(nPosition))).Value = "Executed" ThisWorkbook.Sheets("Stocks").range("C" + Trim(Str(nPosition))).Value = 0 ColorQuoteCells True, nPosition Else ThisWorkbook.Sheets("Stocks").range("I" + Trim(Str(nPosition))).Value = httpGet End If End Function Sub mcrGetAccountNumber() Dim schwabClient As Client Set schwabClient = New Client schwabClient.Init 5, False, True End Sub Sub mcrGetOrders() Dim cCustomerKey As String Dim cAccountNumber As String Dim cAccessToken As String Dim x As Integer Dim x2 As Integer Dim x3 As Integer Dim cStatus As String Dim cInstruction As String Dim nQty As String Dim cSymbol As String Dim nPrice As Double Dim cType As String Dim nOrderID As Double Dim cEntered As String Dim cDuration As String Dim cStatusDescr As String Dim cString As String Dim cString2 As String Dim nDaysBack As Integer Dim fromTime As String, toTime As String On Error GoTo EndIt ThisWorkbook.Sheets("Order Status").range("A5:K5000").Clear Dim schwabClient As Client Set schwabClient = New Client schwabClient.Init cAccountNumber = schwabClient.GetAccountHash() If cAccountNumber = "" Then Exit Sub End If nDaysBack = ThisWorkbook.Sheets("Order Status").range("I2").Value If nDaysBack = 0 Then nDaysBack = 30 ThisWorkbook.Sheets("Order Status").range("I2").Value = 30 End If nDaysBack = nDaysBack * -1 fromTime = Format(DateAdd("d", -30, Date), "yyyy-mm-dd\THH:MM:SS") & ".000Z" toTime = Format(DateAdd("d", 2, Date), "yyyy-mm-dd\THH:MM:SS") & ".000Z" Dim dic As Object Set dic = JsonConverter.ParseJson("[]") Set dic = schwabClient.AccountOrders(cAccountNumber, fromTime, toTime) Dim responseText As String responseText = JsonConverter.ConvertToJson(dic) Dim key As Variant ' x = 1 ' For Each key In dic.Keys ' 'Debug.Print key, dic(key) ' 'If InStr(key, "statusDescription") > 0 Then ' 'MsgBox key + " , " + dic(key) ' ThisWorkbook.Sheets("Temp").Range("A" + Trim(Str(x))).Value = key ' ThisWorkbook.Sheets("Temp").Range("B" + Trim(Str(x))).Value = dic(key) ' 'End If ' x = x + 1 ' Next key x2 = 5 ThisWorkbook.Sheets("Order Status").range("A5:K5000").Clear Dim order As Object Dim orderLeg As Object Dim childOrderStrategies As Object Dim orderCount As Integer orderCount = dic.Count For x = 1 To dic.Count Set order = dic(x) nOrderID = order("orderId") If nOrderID = 0 Then x = nCellMax Else cStatus = order("status") nQty = order("quantity") cSymbol = order("orderLegCollection")(1)("instrument")("symbol") cInstruction = order("orderLegCollection")(1)("instruction") cStatusDescr = order("statusDescription") ' Check if orderActivityCollection exists and is an array If order.Exists("orderActivityCollection") And IsArray(order("orderActivityCollection")) Then ' Check if the first element of orderActivityCollection exists and has executionLegs If UBound(order("orderActivityCollection")) >= 0 Then If Not order("orderActivityCollection")(1)("executionLegs") Is Nothing And IsArray(order("orderActivityCollection")(1)("executionLegs")) Then ' Check if the first element of executionLegs exists If UBound(order("orderActivityCollection")(1)("executionLegs")) >= 0 Then ' Get the price nPrice = order("orderActivityCollection")(1)("executionLegs")(1)("price") End If End If End If End If cType = order("orderType") cDuration = order("duration") cEntered = order("enteredTime") ThisWorkbook.Sheets("Order Status").range("A" + Trim(Str(x2))).Value = "" ThisWorkbook.Sheets("Order Status").range("B" + Trim(Str(x2))).Value = Chr(39) + Str(nOrderID) ThisWorkbook.Sheets("Order Status").range("C" + Trim(Str(x2))).Value = cStatus range("C" + Trim(Str(x2))).Select Select Case cStatus Case "REJECTED" With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 255 .TintAndShade = 0 .PatternTintAndShade = 0 End With Case "FILLED" With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 5296274 .TintAndShade = 0 .PatternTintAndShade = 0 End With Case "WORKING" range("A" + Trim(Str(x2))).Select With Selection.Font .Name = "Calibri" .FontStyle = "Bold" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 .ThemeFont = xlThemeFontMinor End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .PatternTintAndShade = 0 End With ThisWorkbook.Sheets("Order Status").range("A" + Trim(Str(x2))).Value = "CANCEL" End Select ThisWorkbook.Sheets("Order Status").range("D" + Trim(Str(x2))).Value = nQty ThisWorkbook.Sheets("Order Status").range("E" + Trim(Str(x2))).Value = cSymbol ThisWorkbook.Sheets("Order Status").range("F" + Trim(Str(x2))).Value = nPrice ThisWorkbook.Sheets("Order Status").range("G" + Trim(Str(x2))).Value = cType ThisWorkbook.Sheets("Order Status").range("H" + Trim(Str(x2))).Value = cEntered ThisWorkbook.Sheets("Order Status").range("I" + Trim(Str(x2))).Value = cDuration ThisWorkbook.Sheets("Order Status").range("J" + Trim(Str(x2))).Value = cStatusDescr x2 = x2 + 1 'Get child elements If order.Exists("childOrderStrategies") And IsArray(childOrderStrategies) Then Set childOrderStrategies = order("childOrderStrategies") For x3 = 1 To childOrderStrategies.Count nOrderID = Val(dic("obj(" + Trim(Str(x - 1)) + ").childOrderStrategies(0).childOrderStrategies(" + Trim(Str(x3 - 1)) + ").orderId")) If nOrderID = 0 Then x3 = 100 Else cStatus = dic("obj(" + Trim(Str(x - 1)) + ").childOrderStrategies(0).childOrderStrategies(" + Trim(Str(x3 - 1)) + ").status") nQty = dic("obj(" + Trim(Str(x - 1)) + ").childOrderStrategies(0).childOrderStrategies(" + Trim(Str(x3 - 1)) + ").quantity") cSymbol = dic("obj(" + Trim(Str(x - 1)) + ").childOrderStrategies(0).childOrderStrategies(" + Trim(Str(x3 - 1)) + ").orderLegCollection(0).instrument.symbol") cInstruction = dic("obj(" + Trim(Str(x - 1)) + ").childOrderStrategies(0).childOrderStrategies(" + Trim(Str(x3 - 1)) + ").orderLegCollection(0).instruction") cStatusDescr = dic("obj(" + Trim(Str(x - 1)) + ").childOrderStrategies(0).childOrderStrategies(" + Trim(Str(x3 - 1)) + ").statusDescription") nPrice = dic("obj(" + Trim(Str(x - 1)) + ").childOrderStrategies(0).childOrderStrategies(" + Trim(Str(x3 - 1)) + ").price") cType = dic("obj(" + Trim(Str(x - 1)) + ").childOrderStrategies(0).childOrderStrategies(" + Trim(Str(x3 - 1)) + ").orderType") cDuration = dic("obj(" + Trim(Str(x - 1)) + ").childOrderStrategies(0).childOrderStrategies(" + Trim(Str(x3 - 1)) + ").duration") cEntered = dic("obj(" + Trim(Str(x - 1)) + ").childOrderStrategies(0).childOrderStrategies(" + Trim(Str(x3 - 1)) + ").enteredTime") ThisWorkbook.Sheets("Order Status").range("B" + Trim(Str(x2))).Value = Chr(39) + " " + Str(nOrderID) ThisWorkbook.Sheets("Order Status").range("C" + Trim(Str(x2))).Value = cStatus range("C" + Trim(Str(x2))).Select Select Case cStatus Case "REJECTED" With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 255 .TintAndShade = 0 .PatternTintAndShade = 0 End With Case "FILLED" With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 5296274 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Select ThisWorkbook.Sheets("Order Status").range("D" + Trim(Str(x2))).Value = nQty ThisWorkbook.Sheets("Order Status").range("E" + Trim(Str(x2))).Value = cSymbol ThisWorkbook.Sheets("Order Status").range("F" + Trim(Str(x2))).Value = nPrice ThisWorkbook.Sheets("Order Status").range("G" + Trim(Str(x2))).Value = cType ThisWorkbook.Sheets("Order Status").range("H" + Trim(Str(x2))).Value = cEntered ThisWorkbook.Sheets("Order Status").range("I" + Trim(Str(x2))).Value = cDuration ThisWorkbook.Sheets("Order Status").range("J" + Trim(Str(x2))).Value = cStatusDescr x2 = x2 + 1 End If Next x3 End If End If Next x ThisWorkbook.Sheets("Order Status").range("F5:F" + Trim(Str(x2))).Select Selection.Style = "Currency" ThisWorkbook.Sheets("Order Status").range("D2").Value = "Last updated: " + Trim(Str(Date)) + " " + Trim(Str(Time)) EndIt: End Sub Function CancelOrder(nOrderID As Double, nRow As Integer) Dim cAccountNumber As String Dim oHttp As Object Dim httpGet As String Dim parsetext As String Dim schwabClient As Client Set schwabClient = New Client schwabClient.Init cAccountNumber = schwabClient.GetAccountHash() Set oHttp = schwabClient.OrderCancel(cAccountNumber, Str(nOrderID)) Set oHttp = Nothing mcrGetOrders End Function Sub mcrGetPositions() Dim cCustomerKey As String Dim cAccountNumber As String Dim cAccessToken As String Dim x As Integer Dim x2 As Integer Dim oHttp As Object Dim httpGet As String Dim parsetext As String Dim cPostData As String Dim cURL_EndPoint As String Dim cSymbol As String Dim nAveragePrice As Double Dim nLongQuantity As Double Dim nShortQuantity As Double Dim nQty As Double Dim nCost As Double Dim cLine As String Dim nValue As Double Dim nDayPL As Double On Error GoTo EndIt ThisWorkbook.Sheets("Portfolio").range("A5:E500").Clear Dim schwabClient As Client Set schwabClient = New Client schwabClient.Init cAccountNumber = schwabClient.GetAccountHash() Dim dic As Object Set dic = schwabClient.accountDetails(cAccountNumber, "positions") x2 = 5 Dim positions As Object Set positions = dic("securitiesAccount")("positions") For x = 1 To positions.Count cSymbol = dic("obj.securitiesAccount.positions(" + Trim(Str(x - 1)) + ").instrument.symbol") cSymbol = positions(x)("instrument")("symbol") If cSymbol = "" Then x = nCellMax Else nAveragePrice = positions(x)("averagePrice") nDayPL = positions(x)("currentDayProfitLoss") nLongQuantity = positions(x)("longQuantity") nShortQuantity = positions(x)("shortQuantity") nQty = nLongQuantity + nShortQuantity nValue = nAveragePrice * nQty ThisWorkbook.Sheets("Portfolio").range("A" + Trim(Str(x2))).Value = cSymbol ThisWorkbook.Sheets("Portfolio").range("B" + Trim(Str(x2))).Value = nAveragePrice ThisWorkbook.Sheets("Portfolio").range("C" + Trim(Str(x2))).Value = nQty ThisWorkbook.Sheets("Portfolio").range("D" + Trim(Str(x2))).Value = nValue ThisWorkbook.Sheets("Portfolio").range("E" + Trim(Str(x2))).Value = nDayPL x2 = x2 + 1 End If Next x ThisWorkbook.Sheets("Portfolio").range("B5:B" + Trim(Str(x2))).Select Selection.Style = "Currency" ThisWorkbook.Sheets("Portfolio").range("D5:E" + Trim(Str(x2))).Select Selection.Style = "Currency" range("A5:E" + Trim(Str(x2))).Select ActiveWorkbook.Worksheets("Portfolio").sort.SortFields.Clear ActiveWorkbook.Worksheets("Portfolio").sort.SortFields.Add key:=range( _ "A5:A" + Trim(Str(x2))), SortOn:=xlSortOnValues, order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Portfolio").sort .SetRange range("A5:E" + Trim(Str(x2))) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ThisWorkbook.Sheets("Portfolio").range("D5:D" + Trim(Str(x2))).Select ThisWorkbook.Sheets("Portfolio").range("D" + Trim(Str(x2 + 1))).Activate ActiveCell.FormulaR1C1 = "=SUM(R[-" + Trim(Str(x2 - 4)) + "]C:R[-1]C)" ThisWorkbook.Sheets("Portfolio").range("E5:E" + Trim(Str(x2))).Select ThisWorkbook.Sheets("Portfolio").range("E" + Trim(Str(x2 + 1))).Activate ActiveCell.FormulaR1C1 = "=SUM(R[-" + Trim(Str(x2 - 4)) + "]C:R[-1]C)" ThisWorkbook.Sheets("Portfolio").range("D2").Value = "Last updated: " + Trim(Str(Date)) + " " + Trim(Str(Time)) EndIt: End Sub Function BlankStockRows() As Boolean Dim lBlankRow As Boolean Dim nBlankRow As Integer Dim x As Integer Dim cStock As String BlankStockRows = False lBlankRow = False For x = 5 To nCellMax cStock = ThisWorkbook.Sheets("Stocks").range("A" + Trim(Str(x))).Value If Len(cStock) > 0 And lBlankRow = True Then MsgBox "Remove any empty stock symbol rows before running this function. Row " + Trim(Str(nBlankRow)) + " is currently blank." BlankStockRows = True Exit Function End If If Len(cStock) = 0 Then lBlankRow = True nBlankRow = x End If Next x End Function Sub mcrGetQuotes() Dim schwabClient As Client Set schwabClient = New Client schwabClient.Init Dim x As Integer Dim symbol As String Dim nQty As Integer ThisWorkbook.Sheets("Stocks").range("H5:I500").Clear ThisWorkbook.Sheets("Stocks").range("B5:B500").Clear If BlankStockRows Then Exit Sub ThisWorkbook.Sheets("Stocks").range("A5:A500").Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With 'For x = 5 To nCellMax ' ColorCells False, x 'Next x Dim quoteObj As Object 'Loop through column A and purchase any valid stock symbols with a quantity <> 0 For x = 5 To nCellMax symbol = ThisWorkbook.Sheets("Stocks").range("A" + Trim(Str(x))).Value If Len(symbol) > 0 And Len(symbol) < 6 Then Set quoteObj = schwabClient.Quote(symbol) If Not quoteObj Is Nothing Then If Not quoteObj(symbol)("quote") Is Nothing Then If IsNumeric(quoteObj(symbol)("quote")("askPrice")) Then Dim askPrice As Double askPrice = quoteObj(symbol)("quote")("askPrice") ThisWorkbook.Sheets("Stocks").range("B" + Trim(Str(x))).Value = askPrice ThisWorkbook.Sheets("Stocks").range("H" + Trim(Str(x))).Formula = "=B" + Trim(Str(x)) + "*C" + Trim(Str(x)) ThisWorkbook.Sheets("Stocks").range("I" + Trim(Str(x))).Value = "Last quote on " + Str(Date) + " : " + Str(Time) If Not IsNumeric(ThisWorkbook.Sheets("Stocks").range("C" + Trim(Str(x))).Value) _ Or IsEmpty(ThisWorkbook.Sheets("Stocks").range("C" + Trim(Str(x))).Value) Then ThisWorkbook.Sheets("Stocks").range("C" + Trim(Str(x))).Value = 0 ThisWorkbook.Sheets("Stocks").range("D" + Trim(Str(x))).Value = 0 ThisWorkbook.Sheets("Stocks").range("E" + Trim(Str(x))).Value = 0 ThisWorkbook.Sheets("Stocks").range("F" + Trim(Str(x))).Value = 0 ThisWorkbook.Sheets("Stocks").range("G" + Trim(Str(x))).Value = "" End If ColorCells True, x Else Debug.Print "Symbol: " & symbol & ", Ask Price not found" End If End If Else Debug.Print "Symbol: " & symbol & ", Quote not found" End If End If Next x ThisWorkbook.Sheets("Stocks").range("B5:B500").Select Selection.Style = "Currency" ThisWorkbook.Sheets("Stocks").range("D5:D500").Select Selection.Style = "Currency" ThisWorkbook.Sheets("Stocks").range("E5:E500").Select Selection.Style = "Currency" ThisWorkbook.Sheets("Stocks").range("F5:F500").Select Selection.Style = "Currency" ThisWorkbook.Sheets("Stocks").range("H5:H500").Select Selection.Style = "Currency" End Sub