Pulling data from Stripe API into Excel in VBA

Question

I'm trying to write VBA code which will call the Stripe API and retrieve transactional data based on a set of given parameters (date, currency, etc.).

I managed to extract data for the first level data items in the JSON that is returned but can't retrieve second level or third level data.

Example of the JSON returned from the query (I anonymised the data):

{
  "object": "list",
  "data": [
{
      "id": "ch_1234567890abcdefg",
      "object": "charge",
      "amount": 5800,
      "amount_captured": 5800,
      "amount_refunded": 0,
      "application": null,
      "application_fee": null,
      "application_fee_amount": null,
      "balance_transaction": "txn_123456abcdef",
      "billing_details": {
        "address": {
          "city": "Orihuela Costa",
          "country": "ES",
          "line1": "Calle Test",
          "line2": "14",
          "postal_code": "03189",
          "state": "Alicante"
        },
        "email": "[email protected]",
        "name": "Test Person",
        "phone": null
      },
      "calculated_statement_descriptor": "Example Company Ltd.",
      "captured": true,
      "created": 1668074729,
      "currency": "eur",
      "customer": "cus_12345678",

So for example I can extract the values for any of the first level fields like id, amount, created, currency, etc.
I cannot get the data for second level or lower for example the email, name or phone fields that all come under billing_details or third level like the city or country fields that come under address which comes under billing_details.

This is the code (prior to adding input cells for date/currency etc.):

Sub StripeTransactions()

    Dim objRequest As Object
    Dim strUrl As String
    Dim strApiKey As String
    Dim strResponse As String
    Dim jsonResponse As Object
    Dim i As Integer
    Dim ws As Worksheet
    
    strApiKey = "sk_live_<redacted>"
    
    strUrl = "https://api.stripe.com/v1/charges?currency=eur&created[gte]=1667260799&created[lte]=1704067199" 
    
    Set objRequest = CreateObject("MSXML2.ServerXMLHTTP")
   
    With objRequest
        .Open "GET", strUrl, False
        .setRequestHeader "Authorization", "Bearer " & strApiKey
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" 
       
        .send
              
        strResponse = .responseText
    End With
    
    Set jsonResponse = JsonConverter.ParseJson(strResponse)
    Debug.Print strResponse
    
    Set ws = ThisWorkbook.Sheets.Add
    
    ws.Cells(1, 1).Value = "Transaction ID"
    ws.Cells(1, 2).Value = "Amount (EUR)"
    ws.Cells(1, 3).Value = "Description"
    ws.Cells(1, 4).Value = "Date & Time"
    ws.Cells(1, 5).Value = "Status"
    
    i = 2 
    For Each charge In jsonResponse("data")
        ws.Cells(i, 1).Value = charge("id")
        ws.Cells(i, 2).Value = charge("amount") / 100 
        ws.Cells(i, 3).Value = charge("description")
        ws.Cells(i, 4).Value = DateAdd("s", (charge("created")), "1/1/1970")
        ws.Cells(i, 5).Value = charge("status")
       
        i = i + 1
    Next charge
    
    Set objRequest = Nothing
    Set jsonResponse = Nothing
    Set ws = Nothing

End Sub

I've and seen other people using syntax like:

Value = charge("billing_details")("email")

That's throwing an error for me.

I also tried ("billing_details.email").

Answer 1

Using this json in a worksheet cell:

{
  "object": "list",
  "data": [
    {
      "id": "ch_1234567890abcdefg",
      "object": "charge",
      "amount": 5800,
      "amount_captured": 5800,
      "amount_refunded": 0,
      "application": null,
      "application_fee": null,
      "application_fee_amount": null,
      "balance_transaction": "txn_123456abcdef",
      "billing_details": {
        "address": {
          "city": "Orihuela Costa",
          "country": "ES",
          "line1": "Calle Test",
          "line2": "14",
          "postal_code": "03189",
          "state": "Alicante"
        },
        "email": "[email protected]",
        "name": "Test Person",
        "phone": null
      },
      "calculated_statement_descriptor": "Example Company Ltd.",
      "captured": true,
      "created": 1668074729,
      "currency": "eur",
      "customer": "cus_12345678"
    }
  ]
}

This worked fine for me:

Sub tester()
    Dim json As Object, charge As Object
    
    Set json = JsonConverter.ParseJson([A1])
    
    Debug.Print json("object")                 '>> list
    
    For Each charge In json("data")
        Debug.Print "------------------"
        Debug.Print charge("id")                                 '>> ch_1234567890abcdefg
        Debug.Print charge("billing_details")("address")("city") '>> Orihuela Costa
        Debug.Print charge("billing_details")("email")           '>> [email protected]
    Next charge

End Sub