Account Positions - Fetch
Retrieve E*Trade account positions using SQL Server
- See SQLHTTP easy setup for E*Trade
- See API Call documentation for parameter values and other information
- Create the stored procedure documented below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 |
CREATE PROCEDURE usp_ETrade_Account_Positions_Fetch( @Profile nvarchar(100), @AccountID bigint, @count int = 25, @marker varchar(50) = NULL, @typeCode varchar(4) = NULL, @symbol varchar(5) = NULL, @callPut varchar(4) = NULL, @strikePrice bigint = NULL, @expYear varchar(4) =NULL, @expMonth int = NULL, @expDay int = NULL, @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS EXEC usp_ETrade_Auth_Refresh @Profile DECLARE @URL nvarchar(MAX) DECLARE @QueryString varchar(MAX) DECLARE @HTTPSessionID uniqueidentifier DECLARE @Sandbox bit SET @Sandbox = SQLHTTP.net.AuthParam(@Profile, 'Sandbox') IF @Sandbox = 1 BEGIN SET @URL = 'https://etwssandbox.etrade.com' EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'accounts', 'sandbox', 'rest', 'accountpositions', @AccountID EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'count', @count, 'marker', @marker, 'typeCode', @typeCode, 'symbol', @symbol, 'callPut', @callPut, 'strikePrice', @strikePrice, 'expYear', @expYear, 'expMonth', @expMonth, 'expDay', @expDay SET @URL = @URL + @QueryString END ELSE BEGIN SET @URL = 'https://etws.etrade.com' EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'accounts', 'rest', 'accountpositions', @AccountID EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'count', @count, 'marker', @marker, 'typeCode', @typeCode, 'symbol', @symbol, 'callPut', @callPut, 'strikePrice', @strikePrice, 'expYear', @expYear, 'expMonth', @expMonth, 'expDay', @expDay SET @URL = @URL + @QueryString END EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC usp_Etrade_Auth_Header @Profile, @HTTPSessionID, @URL, 'GET' EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'GET', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) EXEC usp_ETrade_Account_Positions_Fetch @Profile = 'My Etrade Sandbox', @AccountId = 83405188, @Response = @Response OUTPUT, @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE IF SQLHTTP.net.IsXmlValid(@Response) = 0 BEGIN RAISERROR(@Response, 16, 1) RETURN END ELSE BEGIN DECLARE @X xml SET @X = CONVERT(xml, @Response) --The XPath syntax below was easily generated by executing the following commands: --EXEC SQLHTTP.net.XQueryHelper @X --and then executing: --EXEC SQLHTTP.net.XQueryHelper @X, 'AccountPositionsResponse/AccountPositions/AccountPosition' -- SELECT T.C.value(N'costBasis[1]', N'nvarchar(MAX)') AS [costBasis] ,T.C.value(N'longOrShort[1]', N'nvarchar(MAX)') AS [longOrShort] ,T.C.value(N'productId[1]/symbol[1]', N'nvarchar(MAX)') AS [symbol] ,T.C.value(N'productId[1]/typeCode[1]', N'nvarchar(MAX)') AS [typeCode] ,T.C.value(N'productId[1]/callPut[1]', N'nvarchar(MAX)') AS [callPut] ,T.C.value(N'productId[1]/strikePrice[1]', N'nvarchar(MAX)') AS [strikePrice] ,T.C.value(N'productId[1]/expYear[1]', N'nvarchar(MAX)') AS [expYear] ,T.C.value(N'productId[1]/expMonth[1]', N'nvarchar(MAX)') AS [expMonth] ,T.C.value(N'productId[1]/expDay[1]', N'nvarchar(MAX)') AS [expDay] ,T.C.value(N'qty[1]', N'nvarchar(MAX)') AS [qty] ,T.C.value(N'currentPrice[1]', N'nvarchar(MAX)') AS [currentPrice] ,T.C.value(N'marketValue[1]', N'nvarchar(MAX)') AS [marketValue] FROM @X.nodes(N'/AccountPositionsResponse/AccountPositions/AccountPosition') T(C) END |
|
1 2 3 4 5 6 7 8 9 |
costBasis longOrShort symbol typeCode callPut strikePrice expYear expMonth expDay qty currentPrice marketValue ----------- ----------- ----------- --------- -------- ------------ -------- --------- ------- ----- ------------- ------------ 12.40 LONG SPX OPTN CALL 300 2010 3 20 2 480.00 240.00 18.30 LONG YHQ OPTN CALL 12 2010 2 20 2 5.00 10.00 990.00 LONG YUM OPTN CALL 30 2010 2 20 200 440.00 88000.00 509.95 LONG 0640P1PE7 BOND NULL NULL NULL NULL NULL 5 1.00 5.00 0.00 LONG 158843RC8 BOND NULL NULL NULL NULL NULL 4 0.00 0.00 |
IMPORTANT DISCLAIMER
CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.
USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.
USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.