(Excel) ETrade List Accounts

Returns a list of E*TRADE accounts for the current user.

For more information, see https://apisb.etrade.com/docs/api/account/api-account-v1.html

Download Excel Class Modules Chilkat Excel Class Modules

' This requires the Chilkat API to have been previously unlocked.
' See Global Unlock Sample for sample code.

Dim http As Chilkat.Http
Set http = Chilkat.NewHttp

http.OAuth1 = True
http.OAuthVerifier = ""
http.OAuthConsumerKey = "ETRADE_CONSUMER_KEY"
http.OAuthConsumerSecret = "ETRADE_CONSUMER_SECRET"

' Load the access token previously obtained via the OAuth1 Authorization
Dim jsonToken As Chilkat.JsonObject
Set jsonToken = Chilkat.NewJsonObject

success = jsonToken.LoadFile("qa_data/tokens/etrade.json")
If (success <> True) Then
    Debug.Print "Failed to load OAuth1 token"
    Exit Sub
End If

http.OAuthToken = jsonToken.StringOf("oauth_token")
http.OAuthTokenSecret = jsonToken.StringOf("oauth_token_secret")


sandboxUrl = "https://apisb.etrade.com/v1/accounts/list"

liveUrl = "https://api.etrade.com/v1/accounts/list"


Set resp = http.QuickGetObj(sandboxUrl)
If (http.LastMethodSuccess <> True) Then
    Debug.Print http.LastErrorText
    Exit Sub
End If

' Make sure a successful response was received.
If (resp.StatusCode >= 300) Then
    Debug.Print resp.StatusLine
    Debug.Print resp.Header
    Debug.Print resp.BodyStr
    Exit Sub
End If

If (resp.StatusCode = 204) Then
    Debug.Print "No records available."
    Exit Sub
End If

' 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" standalone="yes"?>
' <AccountListResponse>
'     <Accounts>
'         <Account>
'             <accountId>82314598</accountId>
'             <accountIdKey>dBZOKt9xDrtRSAOl4MSiiA</accountIdKey>
'             <accountMode>IRA</accountMode>
'             <accountDesc>Brokerage</accountDesc>
'             <accountName>NickName-1</accountName>
'             <accountType>MARGIN</accountType>
'             <institutionType>BROKERAGE</institutionType>
'             <accountStatus>ACTIVE</accountStatus>
'             <closedDate>0</closedDate>
'         </Account>
'         <Account>
'             <accountId>58315636</accountId>
'             <accountIdKey>vQMsebA1H5WltUfDkJP48g</accountIdKey>
'             <accountMode>BROKERAGE</accountMode>
'             <accountDesc>Complete Savings</accountDesc>
'             <accountName>NickName-2</accountName>
'             <accountType>INDIVIDUAL</accountType>
'             <institutionType>BROKERAGE</institutionType>
'             <accountStatus>ACTIVE</accountStatus>
'             <closedDate>0</closedDate>
'         </Account>
'         <Account>
'             <accountId>70700418</accountId>
'             <accountIdKey>6_Dpy0rmuQ9cu9IbTfvF2A</accountIdKey>
'             <accountMode>CASH</accountMode>
'             <accountDesc>INDIVIDUAL</accountDesc>
'             <accountName>NickName-3</accountName>
'             <accountType>INDIVIDUAL</accountType>
'             <institutionType>BROKERAGE</institutionType>
'             <accountStatus>ACTIVE</accountStatus>
'             <closedDate>0</closedDate>
'         </Account>
'         <Account>
'             <accountId>83515143</accountId>
'             <accountIdKey>xj1Dc18FTqWPqkEEVUr5rw</accountIdKey>
'             <accountMode>CASH</accountMode>
'             <accountDesc>INDIVIDUAL</accountDesc>
'             <accountName/>
'             <accountType>CASH</accountType>
'             <institutionType>BROKERAGE</institutionType>
'             <accountStatus>CLOSED</accountStatus>
'             <closedDate>1521027780</closedDate>
' 
'         </Account>
'     </Accounts>
' </AccountListResponse>

Dim xml As Chilkat.Xml
Set xml = Chilkat.NewXml
success = xml.LoadXml(resp.BodyStr)
Debug.Print xml.GetXml()


i = 0

count_i = xml.NumChildrenHavingTag("Accounts|Account")
Do While i < count_i
    xml.I = i
    accountId = xml.GetChildIntValue("Accounts|Account[i]|accountId")
    accountIdKey = xml.GetChildContent("Accounts|Account[i]|accountIdKey")
    accountMode = xml.GetChildContent("Accounts|Account[i]|accountMode")
    accountDesc = xml.GetChildContent("Accounts|Account[i]|accountDesc")
    accountName = xml.GetChildContent("Accounts|Account[i]|accountName")
    accountType = xml.GetChildContent("Accounts|Account[i]|accountType")
    institutionType = xml.GetChildContent("Accounts|Account[i]|institutionType")
    accountStatus = xml.GetChildContent("Accounts|Account[i]|accountStatus")
    closedDate = xml.GetChildIntValue("Accounts|Account[i]|closedDate")
    i = i + 1
Loop

Debug.Print "Success."