SQL Server
SQL Server
ETrade v1 View Portfolio
See more HTTP Misc Examples
Get portfolio information for a selected brokerage account.Chilkat SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
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 3-Legged Authorization examples Step1 and Step2.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, '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 @json
RETURN
END
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'oauth_token'
EXEC sp_OASetProperty @http, 'OAuthToken', @sTmp0
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'oauth_token_secret'
EXEC sp_OASetProperty @http, 'OAuthTokenSecret', @sTmp0
-- See the ETrade v1 API documentation HERE.
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'accountIdKey', 'vsnhtF7d9jXxBy6HyaAC4vQ'
DECLARE @respStr nvarchar(4000)
EXEC sp_OAMethod @http, 'QuickGetStr', @respStr OUT, 'https://apisb.etrade.com/v1/accounts/{$accountIdKey}/portfolio'
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 @json
RETURN
END
-- A 200 status code indicates success.
DECLARE @statusCode int
EXEC sp_OAGetProperty @http, 'LastStatus', @statusCode OUT
PRINT 'statusCode = ' + @statusCode
-- Use the following online tool to generate parsing code from sample XML:
-- Generate Parsing Code from XML
-- A sample XML response is shown below...
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @respStr
DECLARE @tagPath nvarchar(4000)
DECLARE @accountId int
DECLARE @i int
DECLARE @count_i int
DECLARE @positionId nvarchar(4000)
DECLARE @expiryDay int
DECLARE @expiryMonth int
DECLARE @expiryYear int
DECLARE @securityType nvarchar(4000)
DECLARE @strikePrice int
DECLARE @symbol nvarchar(4000)
DECLARE @symbolDescription nvarchar(4000)
DECLARE @dateAcquired int
DECLARE @pricePaid int
DECLARE @commissions int
DECLARE @otherFees int
DECLARE @quantity int
DECLARE @positionIndicator nvarchar(4000)
DECLARE @positionType nvarchar(4000)
DECLARE @daysGain nvarchar(4000)
DECLARE @daysGainPct nvarchar(4000)
DECLARE @marketValue nvarchar(4000)
DECLARE @totalCost int
DECLARE @totalGain nvarchar(4000)
DECLARE @totalGainPct int
DECLARE @pctOfPortfolio nvarchar(4000)
DECLARE @costPerShare int
DECLARE @todayCommissions int
DECLARE @todayFees int
DECLARE @todayPricePaid int
DECLARE @todayQuantity int
DECLARE @adjPrevClose nvarchar(4000)
DECLARE @change nvarchar(4000)
DECLARE @changePct nvarchar(4000)
DECLARE @lastTrade nvarchar(4000)
DECLARE @lastTradeTime int
DECLARE @quoteStatus nvarchar(4000)
DECLARE @volume int
DECLARE @lotsDetails nvarchar(4000)
DECLARE @quoteDetails nvarchar(4000)
DECLARE @totalPages int
EXEC sp_OAMethod @xml, 'GetChildIntValue', @accountId OUT, 'AccountPortfolio|accountId'
SELECT @i = 0
EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @count_i OUT, 'AccountPortfolio|Position'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @xml, 'I', @i
EXEC sp_OAMethod @xml, 'GetChildContent', @positionId OUT, 'AccountPortfolio|Position[i]|positionId'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @expiryDay OUT, 'AccountPortfolio|Position[i]|Product|expiryDay'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @expiryMonth OUT, 'AccountPortfolio|Position[i]|Product|expiryMonth'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @expiryYear OUT, 'AccountPortfolio|Position[i]|Product|expiryYear'
EXEC sp_OAMethod @xml, 'GetChildContent', @securityType OUT, 'AccountPortfolio|Position[i]|Product|securityType'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @strikePrice OUT, 'AccountPortfolio|Position[i]|Product|strikePrice'
EXEC sp_OAMethod @xml, 'GetChildContent', @symbol OUT, 'AccountPortfolio|Position[i]|Product|symbol'
EXEC sp_OAMethod @xml, 'GetChildContent', @symbolDescription OUT, 'AccountPortfolio|Position[i]|symbolDescription'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @dateAcquired OUT, 'AccountPortfolio|Position[i]|dateAcquired'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @pricePaid OUT, 'AccountPortfolio|Position[i]|pricePaid'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @commissions OUT, 'AccountPortfolio|Position[i]|commissions'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @otherFees OUT, 'AccountPortfolio|Position[i]|otherFees'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @quantity OUT, 'AccountPortfolio|Position[i]|quantity'
EXEC sp_OAMethod @xml, 'GetChildContent', @positionIndicator OUT, 'AccountPortfolio|Position[i]|positionIndicator'
EXEC sp_OAMethod @xml, 'GetChildContent', @positionType OUT, 'AccountPortfolio|Position[i]|positionType'
EXEC sp_OAMethod @xml, 'GetChildContent', @daysGain OUT, 'AccountPortfolio|Position[i]|daysGain'
EXEC sp_OAMethod @xml, 'GetChildContent', @daysGainPct OUT, 'AccountPortfolio|Position[i]|daysGainPct'
EXEC sp_OAMethod @xml, 'GetChildContent', @marketValue OUT, 'AccountPortfolio|Position[i]|marketValue'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @totalCost OUT, 'AccountPortfolio|Position[i]|totalCost'
EXEC sp_OAMethod @xml, 'GetChildContent', @totalGain OUT, 'AccountPortfolio|Position[i]|totalGain'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @totalGainPct OUT, 'AccountPortfolio|Position[i]|totalGainPct'
EXEC sp_OAMethod @xml, 'GetChildContent', @pctOfPortfolio OUT, 'AccountPortfolio|Position[i]|pctOfPortfolio'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @costPerShare OUT, 'AccountPortfolio|Position[i]|costPerShare'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @todayCommissions OUT, 'AccountPortfolio|Position[i]|todayCommissions'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @todayFees OUT, 'AccountPortfolio|Position[i]|todayFees'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @todayPricePaid OUT, 'AccountPortfolio|Position[i]|todayPricePaid'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @todayQuantity OUT, 'AccountPortfolio|Position[i]|todayQuantity'
EXEC sp_OAMethod @xml, 'GetChildContent', @adjPrevClose OUT, 'AccountPortfolio|Position[i]|adjPrevClose'
EXEC sp_OAMethod @xml, 'GetChildContent', @change OUT, 'AccountPortfolio|Position[i]|Quick|change'
EXEC sp_OAMethod @xml, 'GetChildContent', @changePct OUT, 'AccountPortfolio|Position[i]|Quick|changePct'
EXEC sp_OAMethod @xml, 'GetChildContent', @lastTrade OUT, 'AccountPortfolio|Position[i]|Quick|lastTrade'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @lastTradeTime OUT, 'AccountPortfolio|Position[i]|Quick|lastTradeTime'
EXEC sp_OAMethod @xml, 'GetChildContent', @quoteStatus OUT, 'AccountPortfolio|Position[i]|Quick|quoteStatus'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @volume OUT, 'AccountPortfolio|Position[i]|Quick|volume'
EXEC sp_OAMethod @xml, 'GetChildContent', @lotsDetails OUT, 'AccountPortfolio|Position[i]|lotsDetails'
EXEC sp_OAMethod @xml, 'GetChildContent', @quoteDetails OUT, 'AccountPortfolio|Position[i]|quoteDetails'
SELECT @i = @i + 1
END
EXEC sp_OAMethod @xml, 'GetChildIntValue', @totalPages OUT, 'AccountPortfolio|totalPages'
-- <?xml version="1.0" encoding="UTF-8"?>
-- <PortfolioResponse>
-- <AccountPortfolio>
-- <accountId>83554788</accountId>
-- <Position>
-- <positionId>10087531</positionId>
-- <Product>
-- <expiryDay>0</expiryDay>
-- <expiryMonth>0</expiryMonth>
-- <expiryYear>0</expiryYear>
-- <securityType>EQ</securityType>
-- <strikePrice>0</strikePrice>
-- <symbol>A</symbol>
-- </Product>
-- <symbolDescription>A</symbolDescription>
-- <dateAcquired>-68400000</dateAcquired>
-- <pricePaid>0</pricePaid>
-- <commissions>0</commissions>
-- <otherFees>0</otherFees>
-- <quantity>-120</quantity>
-- <positionIndicator>TYPE2</positionIndicator>
-- <positionType>SHORT</positionType>
-- <daysGain>190.80</daysGain>
-- <daysGainPct>2.4472</daysGainPct>
-- <marketValue>-7605.60</marketValue>
-- <totalCost>0</totalCost>
-- <totalGain>-7605.60</totalGain>
-- <totalGainPct>0</totalGainPct>
-- <pctOfPortfolio>-0.0008</pctOfPortfolio>
-- <costPerShare>0</costPerShare>
-- <todayCommissions>0</todayCommissions>
-- <todayFees>0</todayFees>
-- <todayPricePaid>0</todayPricePaid>
-- <todayQuantity>0</todayQuantity>
-- <adjPrevClose>64.970000</adjPrevClose>
-- <Quick>
-- <change>-1.59</change>
-- <changePct>-2.4472</changePct>
-- <lastTrade>63.38</lastTrade>
-- <lastTradeTime>1529429280</lastTradeTime>
-- <quoteStatus>DELAYED</quoteStatus>
-- <volume>2431617</volume>
-- </Quick>
-- <lotsDetails>https://api.etrade.com/v1/accounts/JDIozUumZpHdgbIjMnAAHQ/portfolio/10087531</lotsDetails>
-- <quoteDetails>https://api.etrade.com/v1/market/quote/A</quoteDetails>
-- </Position>
-- <Position>
-- <positionId>140357348131</positionId>
-- <Product>
-- <expiryDay>0</expiryDay>
-- <expiryMonth>0</expiryMonth>
-- <expiryYear>0</expiryYear>
-- <securityType>EQ</securityType>
-- <strikePrice>0</strikePrice>
-- <symbol>TWTR</symbol>
-- </Product>
-- <symbolDescription>TWTR</symbolDescription>
-- <dateAcquired>-68400000</dateAcquired>
-- <pricePaid>0</pricePaid>
-- <commissions>0</commissions>
-- <otherFees>0</otherFees>
-- <quantity>3</quantity>
-- <positionIndicator>TYPE2</positionIndicator>
-- <positionType>LONG</positionType>
-- <daysGain>-3.915</daysGain>
-- <daysGainPct>-2.8369</daysGainPct>
-- <marketValue>134.085</marketValue>
-- <totalCost>0</totalCost>
-- <totalGain>134.085</totalGain>
-- <totalGainPct>0</totalGainPct>
-- <pctOfPortfolio>0.0235</pctOfPortfolio>
-- <costPerShare>0</costPerShare>
-- <todayCommissions>0</todayCommissions>
-- <todayFees>0</todayFees>
-- <todayPricePaid>0</todayPricePaid>
-- <todayQuantity>0</todayQuantity>
-- <adjPrevClose>46.000000</adjPrevClose>
-- <Quick>
-- <change>-1.305</change>
-- <changePct>-2.8369</changePct>
-- <lastTrade>44.695</lastTrade>
-- <lastTradeTime>1529429280</lastTradeTime>
-- <quoteStatus>DELAYED</quoteStatus>
-- <volume>26582141</volume>
-- </Quick>
-- <lotsDetails>https://api.etrade.com/v1/accounts/yIFaUoJ81qyAhgxLWRQ42g/portfolio/140357348131</lotsDetails>
-- <quoteDetails>https://api.etrade.com/v1/market/quote/TWTR</quoteDetails>
-- </Position>
-- <totalPages>1</totalPages>
-- </AccountPortfolio>
-- </PortfolioResponse>
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @xml
END
GO