Transaction Details - Fetch
Retrieve E*Trade transaction details 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 |
CREATE PROCEDURE usp_ETrade_Transaction_Details_Fetch( @Profile nvarchar(100), @AccountID bigint, @TransactionID bigint, @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS EXEC usp_ETrade_Auth_Refresh @Profile DECLARE @URL nvarchar(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', @AccountID, 'transactions', @TransactionID END ELSE BEGIN SET @URL = 'https://etws.etrade.com' EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'accounts', 'rest', @AccountID, 'transactions', @TransactionID 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 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) EXEC usp_ETrade_Transaction_Details_Fetch @Profile = 'My Etrade Sandbox', @AccountId = 83405188, @TransactionId = 345678, @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, 'transactionDetails' -- SELECT SQLHTTP.net.UnixTime_To_DateTime2( T.C.value(N'transactionDate[1]', N'nvarchar(MAX)') ) AS [transactionDate] ,T.C.value(N'transactionType[1]', N'nvarchar(MAX)') AS [transactionType] ,T.C.value(N'quantity[1]', N'nvarchar(MAX)') AS [quantity] ,T.C.value(N'amount[1]', N'nvarchar(MAX)') AS [amount] ,T.C.value(N'price[1]', N'nvarchar(MAX)') AS [price] ,T.C.value(N'commission[1]', N'nvarchar(MAX)') AS [commission] ,T.C.value(N'productId[1]/symbol[1]', N'nvarchar(MAX)') AS [symbol] ,SQLHTTP.net.UnixTime_To_DateTime2( T.C.value(N'settlementDate[1]', N'nvarchar(MAX)') ) AS [settlementDate] FROM @X.nodes(N'/transactionDetails') T(C) END |
|
1 2 3 4 5 6 7 |
transactionDate transactionType quantity amount price commission symbol settlementDate ---------------------------- ---------------- --------- -------- ------- ----------- -------- --------------------------- 2010-02-18 18:33:44.0000000 bought 10 2010.00 200.00 10.00 AAPL 2011-11-28 05:00:00.0000000 |
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.