SQL Server
SQL Server
ETrade Get Account Balances
See more ETrade Examples
Retrieves the current account balance and related details for a specified 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 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 Authorization
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
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}/balance?instType={$instType}&realTimeNAV=true'
DECLARE @liveUrl nvarchar(4000)
SELECT @liveUrl = 'https://api.etrade.com/v1/accounts/{$accountIdKey}/balance?instType={$instType}&realTimeNAV=true'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'accountIdKey', '6_Dpy0rmuQ9cu9IbTfvF2A'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'instType', 'BROKERAGE'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', @sandboxUrl, @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @resp
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
EXEC @hr = sp_OADestroy @resp
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"?>
-- <BalanceResponse>
-- <accountId>83564979</accountId>
-- <accountType>PDT_ACCOUNT</accountType>
-- <optionLevel>LEVEL_4</optionLevel>
-- <accountDescription>KRITHH TT</accountDescription>
-- <quoteMode>6</quoteMode>
-- <dayTraderStatus>PDT_MIN_EQUITY_RES_1XK</dayTraderStatus>
-- <accountMode>PDT ACCOUNT</accountMode>
-- <Cash>
-- <fundsForOpenOrdersCash>0</fundsForOpenOrdersCash>
-- <moneyMktBalance>0</moneyMktBalance>
-- </Cash>
-- <Computed>
-- <cashAvailableForInvestment>0</cashAvailableForInvestment>
-- <netCash>93921.44</netCash>
-- <cashBalance>93921.44</cashBalance>
-- <settledCashForInvestment>0</settledCashForInvestment>
-- <unSettledCashForInvestment>0</unSettledCashForInvestment>
-- <fundsWithheldFromPurchasePower>0</fundsWithheldFromPurchasePower>
-- <fundsWithheldFromWithdrawal>0</fundsWithheldFromWithdrawal>
-- <marginBuyingPower>0</marginBuyingPower>
-- <cashBuyingPower>93921.44</cashBuyingPower>
-- <dtMarginBuyingPower>0</dtMarginBuyingPower>
-- <dtCashBuyingPower>0</dtCashBuyingPower>
-- <shortAdjustBalance>0</shortAdjustBalance>
-- <regtEquity>0</regtEquity>
-- <regtEquityPercent>0</regtEquityPercent>
-- <accountBalance>0</accountBalance>
-- </Computed>
-- <Margin>
-- <dtCashOpenOrderReserve>0</dtCashOpenOrderReserve>
-- <dtMarginOpenOrderReserve>0</dtMarginOpenOrderReserve>
-- </Margin>
-- </BalanceResponse>
DECLARE @xml int
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 @accountId int
DECLARE @accountType nvarchar(4000)
DECLARE @optionLevel nvarchar(4000)
DECLARE @accountDescription nvarchar(4000)
DECLARE @quoteMode int
DECLARE @dayTraderStatus nvarchar(4000)
DECLARE @accountMode nvarchar(4000)
DECLARE @fundsForOpenOrdersCash int
DECLARE @moneyMktBalance int
DECLARE @cashAvailableForInvestment int
DECLARE @netCash nvarchar(4000)
DECLARE @cashBalance nvarchar(4000)
DECLARE @settledCashForInvestment int
DECLARE @unSettledCashForInvestment int
DECLARE @fundsWithheldFromPurchasePower int
DECLARE @fundsWithheldFromWithdrawal int
DECLARE @marginBuyingPower int
DECLARE @cashBuyingPower nvarchar(4000)
DECLARE @dtMarginBuyingPower int
DECLARE @dtCashBuyingPower int
DECLARE @shortAdjustBalance int
DECLARE @regtEquity int
DECLARE @regtEquityPercent int
DECLARE @accountBalance int
DECLARE @dtCashOpenOrderReserve int
DECLARE @dtMarginOpenOrderReserve int
EXEC sp_OAMethod @xml, 'GetChildIntValue', @accountId OUT, 'accountId'
EXEC sp_OAMethod @xml, 'GetChildContent', @accountType OUT, 'accountType'
EXEC sp_OAMethod @xml, 'GetChildContent', @optionLevel OUT, 'optionLevel'
EXEC sp_OAMethod @xml, 'GetChildContent', @accountDescription OUT, 'accountDescription'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @quoteMode OUT, 'quoteMode'
EXEC sp_OAMethod @xml, 'GetChildContent', @dayTraderStatus OUT, 'dayTraderStatus'
EXEC sp_OAMethod @xml, 'GetChildContent', @accountMode OUT, 'accountMode'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @fundsForOpenOrdersCash OUT, 'Cash|fundsForOpenOrdersCash'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @moneyMktBalance OUT, 'Cash|moneyMktBalance'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @cashAvailableForInvestment OUT, 'Computed|cashAvailableForInvestment'
EXEC sp_OAMethod @xml, 'GetChildContent', @netCash OUT, 'Computed|netCash'
EXEC sp_OAMethod @xml, 'GetChildContent', @cashBalance OUT, 'Computed|cashBalance'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @settledCashForInvestment OUT, 'Computed|settledCashForInvestment'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @unSettledCashForInvestment OUT, 'Computed|unSettledCashForInvestment'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @fundsWithheldFromPurchasePower OUT, 'Computed|fundsWithheldFromPurchasePower'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @fundsWithheldFromWithdrawal OUT, 'Computed|fundsWithheldFromWithdrawal'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @marginBuyingPower OUT, 'Computed|marginBuyingPower'
EXEC sp_OAMethod @xml, 'GetChildContent', @cashBuyingPower OUT, 'Computed|cashBuyingPower'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @dtMarginBuyingPower OUT, 'Computed|dtMarginBuyingPower'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @dtCashBuyingPower OUT, 'Computed|dtCashBuyingPower'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @shortAdjustBalance OUT, 'Computed|shortAdjustBalance'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @regtEquity OUT, 'Computed|regtEquity'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @regtEquityPercent OUT, 'Computed|regtEquityPercent'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @accountBalance OUT, 'Computed|accountBalance'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @dtCashOpenOrderReserve OUT, 'Margin|dtCashOpenOrderReserve'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @dtMarginOpenOrderReserve OUT, 'Margin|dtMarginOpenOrderReserve'
PRINT 'Success.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @xml
END
GO