Quotes - Fetch
Returns detailed market information for securities from E*Trade 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 |
CREATE PROCEDURE usp_ETrade_Quotes_Fetch( @Profile nvarchar(100), @Symbols nvarchar(4000), @detailFlag nvarchar(50) = 'ALL', @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS EXEC usp_ETrade_Auth_Refresh @Profile DECLARE @URL nvarchar(MAX) DECLARE @QueryString 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/market/sandbox/rest/quote/' COLLATE SQL_Latin1_General_CP1_CI_AS + @Symbols END ELSE BEGIN SET @URL = 'https://etws.etrade.com/market/rest/quote/' COLLATE SQL_Latin1_General_CP1_CI_AS + @Symbols END EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'detailFlag', @detailFlag SET @URL = @URL + @QueryString 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 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) EXEC usp_ETrade_Quotes_Fetch @Profile = 'Etrade Sandbox', @Symbols = 'GOOG,SPY,QQQ', @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, 'QuoteResponse[1]/QuoteData' -- SELECT T.C.value(N'product[1]/symbol[1]', N'nvarchar(MAX)') AS [symbol] ,T.C.value(N'all[1]/ask[1]', N'nvarchar(MAX)') AS [ask] ,T.C.value(N'all[1]/bid[1]', N'nvarchar(MAX)') AS [bid] ,T.C.value(N'all[1]/high[1]', N'nvarchar(MAX)') AS [high] ,T.C.value(N'all[1]/low[1]', N'nvarchar(MAX)') AS [low] ,T.C.value(N'all[1]/lastTrade[1]', N'nvarchar(MAX)') AS [lastTrade] ,T.C.value(N'all[1]/open[1]', N'nvarchar(MAX)') AS [open] ,T.C.value(N'all[1]/prevClose[1]', N'nvarchar(MAX)') AS [prevClose] ,T.C.value(N'dateTime[1]', N'nvarchar(MAX)') AS [dateTime] FROM @X.nodes(N'/QuoteResponse[1]/QuoteData') T(C) END |
|
1 2 3 4 5 6 7 |
symbol ask bid high low lastTrade open prevClose dateTime ------- -------- -------- -------- ----------- ----------- ------- ----------- ------------------------- GOOG 1111.5 1109.38 1123.07 1107.2779 1111.0 1122.0 1167.7 19:55:05 EST 02-04-2018 SPY 274.83 274.77 280.23 275.41 275.45 280.06 281.58 20:00:00 EST 02-04-2018 QQQ 163.83 163.76 167.7 163.72 163.76 167.34 167.96 19:59:49 EST 02-04-2018 |
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.