(SQL Server) ETrade List Orders

Gets the order details for a selected brokerage account based on the search criteria provided.

For more information, see https://apisb.etrade.com/docs/api/order/api-order-v1.html#/definition/getOrders

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @http, 'OAuth1', 1
    EXEC sp_OASetProperty @http, 'OAuthVerifier', ''
    EXEC sp_OASetProperty @http, 'OAuthConsumerKey', 'ETRADE_CONSUMER_KEY'
    EXEC sp_OASetProperty @http, 'OAuthConsumerSecret', 'ETRADE_CONSUMER_SECRET'

    -- Load the access token previously obtained via the OAuth1 Authorization
    DECLARE @jsonToken int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT

    DECLARE @success int
    EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/etrade.json'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load OAuth1 token'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonToken
        RETURN
      END

    EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'oauth_token'
    EXEC sp_OASetProperty @http, 'OAuthToken', @sTmp0
    EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'oauth_token_secret'
    EXEC sp_OASetProperty @http, 'OAuthTokenSecret', @sTmp0

    DECLARE @sandboxUrl nvarchar(4000)
    SELECT @sandboxUrl = 'https://apisb.etrade.com/v1/accounts/{$accountIdKey}/orders'
    DECLARE @liveUrl nvarchar(4000)
    SELECT @liveUrl = 'https://api.etrade.com/v1/accounts/{$accountIdKey}/orders'

    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'accountIdKey', '6_Dpy0rmuQ9cu9IbTfvF2A'

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'QuickGetObj', @resp OUT, @sandboxUrl
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonToken
        RETURN
      END

    -- Make sure a successful response was received.
    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    IF @iTmp0 > 200
      BEGIN
        EXEC sp_OAGetProperty @resp, 'StatusLine', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonToken
        RETURN
      END

    -- Sample XML response:

    -- Use this online tool to generate parsing code from sample XML: 
    -- Generate Parsing Code from XML

    -- <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    -- <OrdersResponse>
    --     <marker>12345678999</marker>
    --     <next>https://api.sit.etrade.com/accounts/E5Nd4LJBsEi_UyHm4Vio9g/orders?marker=12345678999</next>
    --     <Order>
    --         <orderId>479</orderId>
    --         <details>https://api.etrade.com/accounts/E5Nd4LJBsEi_UyHm4Vio9g/orders/479</details>
    --         <orderType>OPTN</orderType>
    --         <OrderDetail>
    --             <placedTime>123453456</placedTime>
    --             <orderValue>123.0000</orderValue>
    --             <status>OPEN</status>
    --             <orderTerm>GOOD_FOR_DAY</orderTerm>
    --             <priceType>LIMIT</priceType>
    --             <limitPrice>1.5</limitPrice>
    --             <stopPrice>0</stopPrice>
    --             <marketSession>REGULAR</marketSession>
    --             <allOrNone>false</allOrNone>
    --             <Instrument>
    --                 <Product>
    --                     <symbol>RIMM</symbol>
    --                     <securityType>OPTN</securityType>
    --                     <callPut>CALL</callPut>
    --                     <expiryYear>2012</expiryYear>
    --                     <expiryMonth>3</expiryMonth>
    --                     <expiryDay>9</expiryDay>
    --                     <strikePrice>12</strikePrice>
    --                 </Product>
    --                 <symbolDescription>RESEARCH IN MOTION LTD COM</symbolDescription>
    --                 <orderAction>BUY_OPEN</orderAction>
    --                 <quantityType>QUANTITY</quantityType>
    --                 <orderedQuantity>5</orderedQuantity>
    --                 <filledQuantity>5</filledQuantity>
    --                 <averageExecutionPrice>0</averageExecutionPrice>
    --                 <estimatedCommission>9.99</estimatedCommission>
    --                 <estimatedFees>0</estimatedFees>
    --             </Instrument>
    --             <netPrice>0</netPrice>
    --             <netBid>0</netBid>
    --             <netAsk>0</netAsk>
    --             <gcd>0</gcd>
    --             <ratio/>
    --         </OrderDetail>
    --     </Order>
    --     <Order>
    --         <orderId>477</orderId>
    --         <details>https://api.etrade.com/accounts/E5Nd4LJBsEi_UyHm4Vio9g/orders/477</details>
    --         <orderType>ONE_CANCELS_ALL</orderType>
    --         <totalOrderValue>209.99</totalOrderValue>
    --         <totalCommission>10.74</totalCommission>
    --         <OrderDetail>
    --             <orderNumber>1</orderNumber>
    --             <placedTime>1331699203122</placedTime>
    --             <orderValue>123.0000</orderValue>
    --             <status>OPEN</status>
    --             <orderTerm>GOOD_FOR_DAY</orderTerm>
    --             <priceType>LIMIT</priceType>
    --             <limitPrice>2</limitPrice>
    --             <stopPrice>0</stopPrice>
    --             <marketSession>REGULAR</marketSession>
    --             <bracketedLimitPrice>2</bracketedLimitPrice>
    --             <initialStopPrice>2</initialStopPrice>
    --             <allOrNone>false</allOrNone>
    --             <Instrument>
    --                 <Product>
    --                     <symbol>ETFC</symbol>
    --                     <securityType>EQ</securityType>
    --                 </Product>
    --                 <symbolDescription>ETRADE Financials</symbolDescription>
    --                 <orderAction>BUY</orderAction>
    --                 <quantityType>QUANTITY</quantityType>
    --                 <orderedQuantity>100</orderedQuantity>
    --                 <filledQuantity>0</filledQuantity>
    --                 <averageExecutionPrice>0</averageExecutionPrice>
    --                 <estimatedCommission>9.99</estimatedCommission>
    --                 <estimatedFees>0</estimatedFees>
    --             </Instrument>
    --             <netPrice>0</netPrice>
    --             <netBid>0</netBid>
    --             <netAsk>0</netAsk>
    --             <gcd>0</gcd>
    --             <ratio/>
    --         </OrderDetail>
    --         <OrderDetail>
    --             <orderNumber>2</orderNumber>
    --             <placedTime>1331699203</placedTime>
    --             <orderValue>231.0000</orderValue>
    --             <status>OPEN</status>
    --             <orderTerm>GOOD_FOR_DAY</orderTerm>
    --             <priceType>LIMIT</priceType>
    --             <limitPrice>0.5</limitPrice>
    --             <stopPrice>0</stopPrice>
    --             <marketSession>REGULAR</marketSession>
    --             <initialStopPrice>0.5</initialStopPrice>
    --             <allOrNone>false</allOrNone>
    --             <Instrument>
    --                 <Product>
    --                     <symbol>MON</symbol>
    --                     <securityType>OPTN</securityType>
    --                     <callPut>CALL</callPut>
    --                     <expiryYear>2012</expiryYear>
    --                     <expiryMonth>4</expiryMonth>
    --                     <expiryDay>21</expiryDay>
    --                     <strikePrice>85</strikePrice>
    --                 </Product>
    --                 <symbolDescription>MON Mar 9 '12 $85 Call</symbolDescription>
    --                 <orderAction>BUY_OPEN</orderAction>
    --                 <quantityType>QUANTITY</quantityType>
    --                 <orderedQuantity>1</orderedQuantity>
    --                 <filledQuantity>0</filledQuantity>
    --                 <averageExecutionPrice>0</averageExecutionPrice>
    --                 <estimatedCommission>9.99</estimatedCommission>
    --                 <estimatedFees>0</estimatedFees>
    --             </Instrument>
    --             <netPrice>0</netPrice>
    --             <netBid>0</netBid>
    --             <netAsk>0</netAsk>
    --             <gcd>0</gcd>
    --             <ratio/>
    --         </OrderDetail>
    --     </Order>
    --     <Order>
    --         <orderId>475</orderId>
    --         <details>https://api.etrade.com/accounts/E5Nd4LJBsEi_UyHm4Vio9g/orders/475</details>
    --         <orderType>SPREADS</orderType>
    --         <OrderDetail>
    --             <placedTime>1331742953</placedTime>
    --             <executedTime>1331742955432</executedTime>
    --             <orderValue>4445.99</orderValue>
    --             <status>EXECUTED</status>
    --             <orderTerm>GOOD_FOR_DAY</orderTerm>
    --             <priceType>NET_DEBIT</priceType>
    --             <limitPrice>1.5</limitPrice>
    --             <stopPrice>0</stopPrice>
    --             <marketSession>REGULAR</marketSession>
    --             <allOrNone>false</allOrNone>
    --             <Instrument>
    --                 <Product>
    --                     <symbol>REE</symbol>
    --                     <securityType>OPTN</securityType>
    --                     <callPut>CALL</callPut>
    --                     <expiryYear>2012</expiryYear>
    --                     <expiryMonth>7</expiryMonth>
    --                     <expiryDay>21</expiryDay>
    --                     <strikePrice>7</strikePrice>
    --                 </Product>
    --                 <symbolDescription>REE Jul 21 '12 $7 Call</symbolDescription>
    --                 <orderAction>BUY_OPEN</orderAction>
    --                 <quantityType>QUANTITY</quantityType>
    --                 <orderedQuantity>2</orderedQuantity>
    --                 <filledQuantity>2</filledQuantity>
    --                 <averageExecutionPrice>1.5</averageExecutionPrice>
    --                 <estimatedCommission>7.24</estimatedCommission>
    --                 <estimatedFees>0</estimatedFees>
    --             </Instrument>
    --             <Instrument>
    --                 <Product>
    --                     <symbol>REE</symbol>
    --                     <securityType>OPTN</securityType>
    --                     <callPut>PUT</callPut>
    --                     <expiryYear>2013</expiryYear>
    --                     <expiryMonth>1</expiryMonth>
    --                     <expiryDay>19</expiryDay>
    --                     <strikePrice>12.50</strikePrice>
    --                 </Product>
    --                 <symbolDescription>REE Jan 19 '13 $12.50 Put</symbolDescription>
    --                 <orderAction>BUY_OPEN</orderAction>
    --                 <quantityType>QUANTITY</quantityType>
    --                 <orderedQuantity>2</orderedQuantity>
    --                 <filledQuantity>2</filledQuantity>
    --                 <averageExecutionPrice>1.5</averageExecutionPrice>
    --                 <estimatedCommission>7.24</estimatedCommission>
    --                 <estimatedFees>0</estimatedFees>
    --             </Instrument>
    --             <netPrice>0</netPrice>
    --             <netBid>0</netBid>
    --             <netAsk>0</netAsk>
    --             <gcd>0</gcd>
    --             <ratio/>
    --         </OrderDetail>
    --     </Order>
    -- </OrdersResponse>
    -- 

    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @sTmp0
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @orderId int

    DECLARE @details nvarchar(4000)

    DECLARE @orderType nvarchar(4000)

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @placedTime nvarchar(4000)

    DECLARE @orderValue nvarchar(4000)

    DECLARE @status nvarchar(4000)

    DECLARE @orderTerm nvarchar(4000)

    DECLARE @priceType nvarchar(4000)

    DECLARE @limitPrice nvarchar(4000)

    DECLARE @stopPrice int

    DECLARE @marketSession nvarchar(4000)

    DECLARE @allOrNone nvarchar(4000)

    DECLARE @k int

    DECLARE @count_k int

    DECLARE @symbol nvarchar(4000)

    DECLARE @securityType nvarchar(4000)

    DECLARE @callPut nvarchar(4000)

    DECLARE @expiryYear int

    DECLARE @expiryMonth int

    DECLARE @expiryDay int

    DECLARE @strikePrice nvarchar(4000)

    DECLARE @symbolDescription nvarchar(4000)

    DECLARE @orderAction nvarchar(4000)

    DECLARE @quantityType nvarchar(4000)

    DECLARE @orderedQuantity int

    DECLARE @filledQuantity int

    DECLARE @averageExecutionPrice nvarchar(4000)

    DECLARE @estimatedCommission nvarchar(4000)

    DECLARE @estimatedFees int

    DECLARE @netPrice int

    DECLARE @netBid int

    DECLARE @netAsk int

    DECLARE @gcd int

    DECLARE @orderNumber int

    DECLARE @bracketedLimitPrice int

    DECLARE @initialStopPrice nvarchar(4000)

    DECLARE @executedTime nvarchar(4000)

    DECLARE @totalOrderValue nvarchar(4000)

    DECLARE @totalCommission nvarchar(4000)

    DECLARE @marker nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @marker OUT, 'marker'
    DECLARE @next nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @next OUT, 'next'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @count_i OUT, 'Order'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @xml, 'I', @i
        EXEC sp_OAMethod @xml, 'GetChildIntValue', @orderId OUT, 'Order[i]|orderId'
        EXEC sp_OAMethod @xml, 'GetChildContent', @details OUT, 'Order[i]|details'
        EXEC sp_OAMethod @xml, 'GetChildContent', @orderType OUT, 'Order[i]|orderType'
        SELECT @j = 0
        EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @count_j OUT, 'Order[i]|OrderDetail'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @xml, 'J', @j
            EXEC sp_OAMethod @xml, 'GetChildContent', @placedTime OUT, 'Order[i]|OrderDetail[j]|placedTime'
            EXEC sp_OAMethod @xml, 'GetChildContent', @orderValue OUT, 'Order[i]|OrderDetail[j]|orderValue'
            EXEC sp_OAMethod @xml, 'GetChildContent', @status OUT, 'Order[i]|OrderDetail[j]|status'
            EXEC sp_OAMethod @xml, 'GetChildContent', @orderTerm OUT, 'Order[i]|OrderDetail[j]|orderTerm'
            EXEC sp_OAMethod @xml, 'GetChildContent', @priceType OUT, 'Order[i]|OrderDetail[j]|priceType'
            EXEC sp_OAMethod @xml, 'GetChildContent', @limitPrice OUT, 'Order[i]|OrderDetail[j]|limitPrice'
            EXEC sp_OAMethod @xml, 'GetChildIntValue', @stopPrice OUT, 'Order[i]|OrderDetail[j]|stopPrice'
            EXEC sp_OAMethod @xml, 'GetChildContent', @marketSession OUT, 'Order[i]|OrderDetail[j]|marketSession'
            EXEC sp_OAMethod @xml, 'GetChildContent', @allOrNone OUT, 'Order[i]|OrderDetail[j]|allOrNone'
            SELECT @k = 0
            EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @count_k OUT, 'Order[i]|OrderDetail[j]|Instrument'
            WHILE @k < @count_k
              BEGIN
                EXEC sp_OASetProperty @xml, 'K', @k
                EXEC sp_OAMethod @xml, 'GetChildContent', @symbol OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|Product|symbol'
                EXEC sp_OAMethod @xml, 'GetChildContent', @securityType OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|Product|securityType'
                EXEC sp_OAMethod @xml, 'GetChildContent', @callPut OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|Product|callPut'
                EXEC sp_OAMethod @xml, 'GetChildIntValue', @expiryYear OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|Product|expiryYear'
                EXEC sp_OAMethod @xml, 'GetChildIntValue', @expiryMonth OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|Product|expiryMonth'
                EXEC sp_OAMethod @xml, 'GetChildIntValue', @expiryDay OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|Product|expiryDay'
                EXEC sp_OAMethod @xml, 'GetChildContent', @strikePrice OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|Product|strikePrice'
                EXEC sp_OAMethod @xml, 'GetChildContent', @symbolDescription OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|symbolDescription'
                EXEC sp_OAMethod @xml, 'GetChildContent', @orderAction OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|orderAction'
                EXEC sp_OAMethod @xml, 'GetChildContent', @quantityType OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|quantityType'
                EXEC sp_OAMethod @xml, 'GetChildIntValue', @orderedQuantity OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|orderedQuantity'
                EXEC sp_OAMethod @xml, 'GetChildIntValue', @filledQuantity OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|filledQuantity'
                EXEC sp_OAMethod @xml, 'GetChildContent', @averageExecutionPrice OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|averageExecutionPrice'
                EXEC sp_OAMethod @xml, 'GetChildContent', @estimatedCommission OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|estimatedCommission'
                EXEC sp_OAMethod @xml, 'GetChildIntValue', @estimatedFees OUT, 'Order[i]|OrderDetail[j]|Instrument[k]|estimatedFees'
                SELECT @k = @k + 1
              END
            EXEC sp_OAMethod @xml, 'GetChildIntValue', @netPrice OUT, 'Order[i]|OrderDetail[j]|netPrice'
            EXEC sp_OAMethod @xml, 'GetChildIntValue', @netBid OUT, 'Order[i]|OrderDetail[j]|netBid'
            EXEC sp_OAMethod @xml, 'GetChildIntValue', @netAsk OUT, 'Order[i]|OrderDetail[j]|netAsk'
            EXEC sp_OAMethod @xml, 'GetChildIntValue', @gcd OUT, 'Order[i]|OrderDetail[j]|gcd'
            EXEC sp_OAMethod @xml, 'GetChildIntValue', @orderNumber OUT, 'Order[i]|OrderDetail[j]|orderNumber'
            EXEC sp_OAMethod @xml, 'GetChildIntValue', @bracketedLimitPrice OUT, 'Order[i]|OrderDetail[j]|bracketedLimitPrice'
            EXEC sp_OAMethod @xml, 'GetChildContent', @initialStopPrice OUT, 'Order[i]|OrderDetail[j]|initialStopPrice'
            EXEC sp_OAMethod @xml, 'GetChildContent', @executedTime OUT, 'Order[i]|OrderDetail[j]|executedTime'
            SELECT @j = @j + 1
          END
        EXEC sp_OAMethod @xml, 'GetChildContent', @totalOrderValue OUT, 'Order[i]|totalOrderValue'
        EXEC sp_OAMethod @xml, 'GetChildContent', @totalCommission OUT, 'Order[i]|totalCommission'
        SELECT @i = @i + 1
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @jsonToken
    EXEC @hr = sp_OADestroy @xml


END
GO