(Excel) Rabobank GET /v3/accounts

Demonstrates a call to a Rabobank API. Specifically this makes a call to the "Accounts AIS sb" API to deliver all the consented payments accounts for a specific Rabobank customer.

(This includes source code to for signing requests for PSD2 APIs)

For more information, see https://developer-sandbox.rabobank.nl/reference/accounts-ais-sb/3-0-8

Download Excel Class Modules Chilkat Excel Class Modules

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

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

' Implements the following CURL command:

' curl --request GET \
'   --url https://api-sandbox.rabobank.nl/openapi/sandbox/payments/account-information/ais/v3/accounts \
'   --header 'accept: application/json' \
'   --header 'authorization: Bearer REPLACE_BEARER_TOKEN' \
'   --header 'date: REPLACE_THIS_VALUE' \
'   --header 'digest: REPLACE_THIS_VALUE' \
'   --header 'psu-ip-address: OPTIONAL' \
'   --header 'signature: REPLACE_THIS_VALUE' \
'   --header 'tpp-signature-certificate: MIIDkDCCAnigA ... cwE6gUl0rMA==' \
'   --header 'x-ibm-client-id: Client ID' \
'   --header 'x-request-id: REPLACE_THIS_VALUE'

' Load the previously fetched OAuth2 access token.
Dim jsonToken As Chilkat.JsonObject
Set jsonToken = Chilkat.NewJsonObject
success = jsonToken.LoadFile("qa_data/tokens/rabobank.json")
If (success = False) Then
    Debug.Print jsonToken.LastErrorText
    Exit Sub
End If

' This adds the "authorization: Bearer REPLACE_BEARER_TOKEN" header.
http.AuthToken = jsonToken.StringOf("access_token")

' This is the TTP Signature certificate: The certificate used for signing the request.
Dim sbTppCert As Chilkat.StringBuilder
Set sbTppCert = Chilkat.NewStringBuilder
Dim success As Boolean
success = sbTppCert.Append("MIIDkDCCAnigAwIBAgIEWs3AJDANBgkqhkiG9w0BAQsFADCBiTELMAkGA1UEBhMC")
success = sbTppCert.Append("TkwxEDAOBgNVBAgMB1V0cmVjaHQxEDAOBgNVBAcMB1V0cmVjaHQxETAPBgNVBAoM")
success = sbTppCert.Append("CFJhYm9iYW5rMRwwGgYDVQQLDBNPbmxpbmUgVHJhbnNhY3Rpb25zMSUwIwYDVQQD")
success = sbTppCert.Append("DBxQU0QyIEFQSSBQSSBTZXJ2aWNlcyBTYW5kYm94MB4XDTE4MDQxMTA3NTgyOFoX")
success = sbTppCert.Append("DTIzMDQxMTA3NTgyOFowgYkxCzAJBgNVBAYTAk5MMRAwDgYDVQQIDAdVdHJlY2h0")
success = sbTppCert.Append("MRAwDgYDVQQHDAdVdHJlY2h0MREwDwYDVQQKDAhSYWJvYmFuazEcMBoGA1UECwwT")
success = sbTppCert.Append("T25saW5lIFRyYW5zYWN0aW9uczElMCMGA1UEAwwcUFNEMiBBUEkgUEkgU2Vydmlj")
success = sbTppCert.Append("ZXMgU2FuZGJveDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBANoAjqGW")
success = sbTppCert.Append("UgCIm2F+0sBSEwLal+T3u+uldLikpxHCB8iL1GD7FrRjcA+MVsxhvHly7vRsHK+t")
success = sbTppCert.Append("QyMSaeK782RHpY33qxPLc8LmoQLb2EuiQxXj9POYkYBQ74qkrZnvKVlR3WoyQWeD")
success = sbTppCert.Append("OXnSY2wbNFfkP8ET4ElwyuIIEriwYhab0OIrnnrO8X82/SPZxHwEd3aQjQ6uhiw8")
success = sbTppCert.Append("paDspJbS5WjEfuwY16KVVUYlhbtAwGjvc6aK0NBm+LH9fMLpAE6gfGZNy0gzMDor")
success = sbTppCert.Append("VNbkQK1IoAGD8p9ZHdB0F3FwkILEjUiQW6nK+/fKDNJ0TBbpgZUpY8bR460qzxKd")
success = sbTppCert.Append("eZ1yPDqX2Cjh6fkCAwEAATANBgkqhkiG9w0BAQsFAAOCAQEAYL4iD6noMJAt63kD")
success = sbTppCert.Append("ED4RB2mII/lssvHhcxuDpOm3Ims9urubFWEpvV5TgIBAxy9PBinOdjhO1kGJJnYi")
success = sbTppCert.Append("7F1jv1qnZwTV1JhYbvxv3+vk0jaiu7Ew7G3ASlzruXyMhN6t6jk9MpaWGl5Uw1T+")
success = sbTppCert.Append("gNRUcWQRR44g3ahQRIS/UHkaV+vcpOa8j186/1X0ULHfbcVQk4LMmJeXqNs8sBAU")
success = sbTppCert.Append("dKU/c6ssvj8jfJ4SfrurcBhY5UBTOdQOXTPY85aU3iFloerx7Oi9EHewxInOrU5X")
success = sbTppCert.Append("zqqTz2AQPXezexVeAQxP27lzqCmYC7CFiam6QBr06VebkmnPLfs76n8CDc1cwE6g")
success = sbTppCert.Append("Ul0rMA==")
http.SetRequestHeader "tpp-signature-certificate",sbTppCert.GetAsString()

' ----------------------------------------------------------------
' We're not going to add the psu-ip-address header in this example.

' ----------------------------------------------------------------
' X-Request-ID header...
' Generate a UUID v4.
Dim crypt As Chilkat.Crypt2
Set crypt = Chilkat.NewCrypt2

xRequestId = crypt.GenerateUuid()
http.SetRequestHeader "x-request-id",xRequestId

' ----------------------------------------------------------------
' Date header...
Dim dt As Chilkat.CkDateTime
Set dt = Chilkat.NewCkDateTime
success = dt.SetFromCurrentSystemTime()

dateHdrVal = dt.GetAsRfc822(False)
' The desire date/time format is the "RFC822" format.
http.SetRequestHeader "Date",dateHdrVal

' ----------------------------------------------------------------
' Digest header...
crypt.HashAlgorithm = "SHA256"
crypt.EncodingMode = "base64"
' A GET request has no HTTP request body.  Therefore the payload is the empty string.

payload = ""

payloadDigest = crypt.HashStringENC(payload)
Dim sbDigestHdrVal As Chilkat.StringBuilder
Set sbDigestHdrVal = Chilkat.NewStringBuilder
success = sbDigestHdrVal.Append("SHA-256=")
success = sbDigestHdrVal.Append(payloadDigest)
http.SetRequestHeader "digest",sbDigestHdrVal.GetAsString()

' ----------------------------------------------------------------
' The HTTP request needs to be signed for PSD2 API's...
' Create the signing string (to use in signing the HTTP request).
' See https://developer.rabobank.nl/signing-requests-psd2-apis
' 
' For "account information" HTTP requests, we must use the following headers in the signing string.
'    date
'    digest
'    x-request-id

Dim sbStringToSign As Chilkat.StringBuilder
Set sbStringToSign = Chilkat.NewStringBuilder
success = sbStringToSign.Append("date: ")
success = sbStringToSign.AppendLine(dateHdrVal,False)
success = sbStringToSign.Append("digest: ")
success = sbStringToSign.AppendLine(sbDigestHdrVal.GetAsString(),False)
success = sbStringToSign.Append("x-request-id: ")
success = sbStringToSign.Append(xRequestId)

' ----------------------------------------------------------------
' Sign with our private key...
Dim sbPrivKey As Chilkat.StringBuilder
Set sbPrivKey = Chilkat.NewStringBuilder
success = sbPrivKey.Append("MIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQDaAI6hllIAiJth")
success = sbPrivKey.Append("ftLAUhMC2pfk97vrpXS4pKcRwgfIi9Rg+xa0Y3APjFbMYbx5cu70bByvrUMjEmni")
success = sbPrivKey.Append("u/NkR6WN96sTy3PC5qEC29hLokMV4/TzmJGAUO+KpK2Z7ylZUd1qMkFngzl50mNs")
success = sbPrivKey.Append("GzRX5D/BE+BJcMriCBK4sGIWm9DiK556zvF/Nv0j2cR8BHd2kI0OroYsPKWg7KSW")
success = sbPrivKey.Append("0uVoxH7sGNeilVVGJYW7QMBo73OmitDQZvix/XzC6QBOoHxmTctIMzA6K1TW5ECt")
success = sbPrivKey.Append("SKABg/KfWR3QdBdxcJCCxI1IkFupyvv3ygzSdEwW6YGVKWPG0eOtKs8SnXmdcjw6")
success = sbPrivKey.Append("l9go4en5AgMBAAECggEAB8nsTqalwGIhFw8mbXuhNUFlGuek/arYLD6pv28swwQH")
success = sbPrivKey.Append("7v0ZlxFUcCHF+iBl0PsDwZTZQ4ePtgGS6ehoLkWHCzb1lEv5E1YVG5qKNE2UUwRl")
success = sbPrivKey.Append("fIyPakO6AzyV/UF3uzq7C+/GuXGNTKZxKewg5yD/DCFvKoCOpxu9u36FyqP/hw0S")
success = sbPrivKey.Append("ADVlmp35/zoPDPZzu1j4FiCo0pJ9LwJcHxeJHopNAKDw9k6I4z/grskdgupsGzK2")
success = sbPrivKey.Append("BiGiQ/+wmmO68/6Xa6KWfpr1PQ6ODJHgzZsdGCVi6Ebaqlj6BbsYWxP6h3lrsGt+")
success = sbPrivKey.Append("LmHBaN2jCD6cDp+lihqFgnm8hfdv0lmbPilp71EDfwKBgQD6U8PBzZtN8yXm5WuS")
success = sbPrivKey.Append("NL+/8q5GjNmeTJBSo1gM6Y8vOT4QAE147LbVuVBDwyHPoSrNejePae6Q14PswjBy")
success = sbPrivKey.Append("T7B8DZ0OeQyGa3trrFg/ib7Vv4ZMvJqX9+WzBrzZsxTg7oCKHzmCR4vIRItKHH3z")
success = sbPrivKey.Append("Wnnhqlo8ic2AZ2O43cdJosbO1wKBgQDe8UODOLu0vnHohOKeUqF3w/ZOB2+83/js")
success = sbPrivKey.Append("YyUbSkzsGvHIwTjObuMUFTQvdMZ6IkIyJdfnDZIbvlBSD8tzL5iKFTNCK2nL1i4G")
success = sbPrivKey.Append("iFr0CYLaHAlhJ5GEbTrTMDoJeBPerZq83HPrSa/Wb0xO18QTWsoVQPFfPFbbcQyI")
success = sbPrivKey.Append("9ryJ2iIDrwKBgQC6kuAefG46ZPVk6K2KZUJdgDUgZC52a75NuW0RAqszmUiGiJM1")
success = sbPrivKey.Append("g8ip9tq6BqAWrprGV0c93shusBKlzf5p1LdHXqYmeVY6gbWVhPipMrNHgN5KJ3BZ")
success = sbPrivKey.Append("v+w1yNnMsErpcxne2HL2hPjMJTpj3GSLkm2xIlTrNhIyl9ydlr7IRUhENQKBgQCv")
success = sbPrivKey.Append("i6HxbXa/90WSJTCcIcxqla8X+dsOCf3jhJ3vQy4Wq5C+1wZ35fCAG8Ifq/+so9Uj")
success = sbPrivKey.Append("z5CVqqXlmpF8TFuSs2OVNuRJsg14J4nOMwgLKIIUZAcurQ10DN5I9Kx+UEK1EFXL")
success = sbPrivKey.Append("aHsORdNjMfgQDO2jn9WHrr9gkg6CdB2+qyoCEfS+mQKBgBW08lcy9V5RzRWb/v/j")
success = sbPrivKey.Append("xsc7ovmgAhCJhDeV7dPbx4HbFeoQJlbA8g1thdcFlcatSGyNDbvNE1GPSd4NhkpR")
success = sbPrivKey.Append("Y6Hfv53kdEzjVkEtU8lUdL7HNVJqX7bU7oZlfbYcwxWQ1Gg8C1oLIAyEt71slQtd")
success = sbPrivKey.Append("RiNYBRZTQe2F0wxbXnuUqLAw")

Dim privKey As Chilkat.PrivateKey
Set privKey = Chilkat.NewPrivateKey
success = privKey.LoadPem(sbPrivKey.GetAsString())
If (success = False) Then
    Debug.Print privKey.LastErrorText
    Exit Sub
End If

Dim rsa As Chilkat.Rsa
Set rsa = Chilkat.NewRsa
success = rsa.ImportPrivateKeyObj(privKey)
If (success = False) Then
    Debug.Print rsa.LastErrorText
    Exit Sub
End If

rsa.EncodingMode = "base64"

b64Signature = rsa.SignStringENC(sbStringToSign.GetAsString(),"SHA256")

' ----------------------------------------------------------------
' Add the "signature" header.
' 
' The signature header will look like this:
' signature: keyId="1523433508",algorithm="rsa-sha512",headers="date digest x-request-id",signature="y5o7gKxmfA6AT6...blE1A9Q=="
' 
' The keyId is the serial number of the certificate as defined in 'TPP-Signing-Certificate' header, the format should be Integer not hex. 
Dim cert As Chilkat.Cert
Set cert = Chilkat.NewCert
success = cert.SetFromEncoded(sbTppCert.GetAsString())
If (success = False) Then
    Debug.Print cert.LastErrorText
    Exit Sub
End If

Dim sbSigHdrVal As Chilkat.StringBuilder
Set sbSigHdrVal = Chilkat.NewStringBuilder
success = sbSigHdrVal.Append("keyId=""")
success = sbSigHdrVal.Append(cert.SerialDecimal)
success = sbSigHdrVal.Append(""",")
success = sbSigHdrVal.Append("algorithm=""rsa-sha256"",")
success = sbSigHdrVal.Append("headers=""date digest x-request-id"",")
success = sbSigHdrVal.Append("signature=""")
success = sbSigHdrVal.Append(b64Signature)
success = sbSigHdrVal.Append("""")

http.SetRequestHeader "signature",sbSigHdrVal.GetAsString()

' ----------------------------------------------------------------
' Add remaining headers...

http.SetRequestHeader "x-ibm-client-id","YOUR_APP_CLIENT_ID"
http.SetRequestHeader "accept","application/json"

' ----------------------------------------------------------------
' Add our certificate and key for mutual TLS 
' (provide a registered X509 client certificate during TLS handhake)

Dim tlsCert As Chilkat.Cert
Set tlsCert = Chilkat.NewCert
success = tlsCert.LoadFromFile("qa_data/certs_and_keys/ING/example_client_tls.cer")
If (success = False) Then
    Debug.Print tlsCert.LastErrorText
    Exit Sub
End If

Dim bdPrivKey As Chilkat.BinData
Set bdPrivKey = Chilkat.NewBinData
success = bdPrivKey.LoadFile("qa_data/certs_and_keys/ING/example_client_tls.key")
If (success = False) Then
    Debug.Print "Failed to load example_client_tls.key"
    Exit Sub
End If

' The OAuth 2.0 client_id for these certificates is e77d776b-90af-4684-bebc-521e5b2614dd. 
' Please note down this client_id since you will need it in the next steps to call the API.

Dim tlsPrivKey As Chilkat.PrivateKey
Set tlsPrivKey = Chilkat.NewPrivateKey
success = tlsPrivKey.LoadAnyFormat(bdPrivKey,"")
If (success = False) Then
    Debug.Print tlsPrivKey.LastErrorText
    Exit Sub
End If

' Associate the private key with the certificate.
success = tlsCert.SetPrivateKey(tlsPrivKey)
If (success = False) Then
    Debug.Print tlsCert.LastErrorText
    Exit Sub
End If

success = http.SetSslClientCert(tlsCert)
If (success = False) Then
    Debug.Print http.LastErrorText
    Exit Sub
End If

' ----------------------------------------------------------------
' Finally, send the request...

Dim sbResponseBody As Chilkat.StringBuilder
Set sbResponseBody = Chilkat.NewStringBuilder
success = http.QuickGetSb("https://api-sandbox.rabobank.nl/openapi/sandbox/payments/account-information/ais/v3/accounts",sbResponseBody)
If (success = False) Then
    Debug.Print http.LastErrorText
    Debug.Print sbResponseBody.GetAsString()
    Exit Sub
End If

Dim jResp As Chilkat.JsonObject
Set jResp = Chilkat.NewJsonObject
success = jResp.LoadSb(sbResponseBody)
jResp.EmitCompact = False

Debug.Print "Response Body:"
Debug.Print jResp.Emit()


respStatusCode = http.LastStatus
Debug.Print "Response Status Code = "; respStatusCode
If (respStatusCode >= 400) Then
    Debug.Print "Response Header:"
    Debug.Print http.LastHeader
    Debug.Print "Failed."
    Exit Sub
End If

' Sample JSON response:
' (Sample code for parsing the JSON response is shown below)

' {
'   "accounts": [
'     {
'       "_links": {
'         "account": "/v3/accounts/dW9od2VIVUhGVypIZHVpaGRhdWR3OGRoY",
'         "balances": "/v3/accounts/dW9od2VIVUhGVypIZHVpaGRhdWR3OGRoY/balances",
'         "transactions": "/v3/accounts/dW9od2VIVUhGVypIZHVpaGRhdWR3OGRoY/transactions"
'       },
'       "currency": "EUR",
'       "iban": "NL05RABO0812836782",
'       "name": "Rosie Roy",
'       "resourceId": "dW9od2VIVUhGVypIZHVpaGRhdWR3OGRoY",
'       "status": "enabled"
'     }
'   ]
' }

' Sample code for parsing the JSON response...
' Use the following online tool to generate parsing code from sample JSON:
' Generate Parsing Code from JSON


i = 0

count_i = jResp.SizeOfArray("accounts")
Do While i < count_i
    jResp.I = i
    v_linksAccount = jResp.StringOf("accounts[i]._links.account")
    v_linksBalances = jResp.StringOf("accounts[i]._links.balances")
    v_linksTransactions = jResp.StringOf("accounts[i]._links.transactions")
    currency = jResp.StringOf("accounts[i].currency")
    iban = jResp.StringOf("accounts[i].iban")
    name = jResp.StringOf("accounts[i].name")
    resourceId = jResp.StringOf("accounts[i].resourceId")
    status = jResp.StringOf("accounts[i].status")
    i = i + 1
Loop