sorting API key value pairs into columns

[jpowell79]

Hi guys, would really appreciate some help

I'm looking to copy some API data into Excel which I can then arrange into columns:

Here is the API data I'm extracting: API DATA

When you open up the data you'll see the complete API call.

The very first line of the data can be completely ignored: ({"status":"1","message":"OK","result"

After that, you have a whole bunch of key|value pairs for every "block" - There are many of them!

Here's the first block:

{"blockNumber":"4041874","timeStamp":"1500430428","hash":"0x8bcfce9af53f1c318aff183cd14a491aae68b0dd14172a3f40f83ee9cc452e68","nonce":"123","blockHash":"0x5467eba473d443d81e7841f8557e953079f9428cff72c4def556f9303907eaf9","from":"0x77744a927271f12dc5b9b8887064f07c37648829","contractAddress":"0xc63e7b1dece63a77ed7e4aeef5efb3b05c81438d","to":"0xde0b295669a9fd93d5f28d9ec85e40f4cb697bae","value":"696900","tokenName":"****token","tokenSymbol":"****","tokenDecimal":"4","transactionIndex":"84","gas":"51810","gasPrice":"4000000000","gasUsed":"51809","cumulativeGasUsed":"3548636","input":"deprecated","confirmations":"10726582"},

Here's the second block:

{"blockNumber":"4207393","timeStamp":"1503789024","hash":"0xa7bd87396908d54e5c6dbc1738c8a3f15ca59806cfc360a7657e0a97c7d6222c","nonce":"19","blockHash":"0x776fb5b420903d821563d946bfd303abb2d56788eab6adec83fe7ea8130b6a9a","from":"0x745708a805f36e9d4815e10b856719e152c7a000","contractAddress":"0xdab0c31bf34c897fb0fe90d12ec9401caf5c36ec","to":"0xde0b295669a9fd93d5f28d9ec85e40f4cb697bae","value":"1","tokenName":"DABcoin","tokenSymbol":"DAB","tokenDecimal":"0","transactionIndex":"99","gas":"51575","gasPrice":"1000000000","gasUsed":"51574","cumulativeGasUsed":"5127026","input":"deprecated","confirmations":"10561063"},

There can often be more than 100+ "blocks"

The "keys" in each block would remain constant. They are always the following:

blockNumber, timeStamp, hash, nonce, blockHash, from, contractAddress, to, value, tokenName, tokenSymbol, tokenDecimal, transactionIndex, gas, gasPrice, gasUsed, cumulativeGasUsed, input, confirmations

These 19 "keys" would basically act as column headers in the spreadsheet

I then want to be able to populate the rows with the "value" results for each block. So basically I would end up with 19 columns, and potentially hundreds of rows.

Once I'm at that point I can sort the data as normal and hide some of the columns that I'm not interested in.

If anyone could help I'd really appreciate it!

thanks guys

 

[Domenic]

First, if you haven't already done so, download the JSON parser for VBA from the following link, and add it to your project. Note that the file to download is called JsonConverter.bas.

GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA

JSON conversion and parsing for VBA. Contribute to VBA-tools/VBA-JSON development by creating an account on GitHub.

github.com

Then, try to adopt the following code. Note that the code will place the results in a newly created worksheet.

Option Explicit

Sub ReadEtherscanAPI()

    Dim url As String
    url = "https://api.etherscan.io/api?module=account&action=tokentx&page=1&startblock=0&endblock=999999999&sort=asc&apikey=D6CHGVM82T2ZY9RSID1DDUV78RK6WGMYPY&address=0xde0B295669a9FD93d5F28D9Ec85E40f4cb697BAe"
    
    Dim httpRequest As Object
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    
    With httpRequest
        .Open "GET", url, False
        .Send
        If .Status <> 200 Then
            MsgBox "Error " & .Status & ":" & vbCrLf & vbCrLf & .StatusText
            Exit Sub
        End If
        Dim httpResponse As Object
        Set httpResponse = JsonConverter.ParseJson(.ResponseText)
    End With
    
    Dim resultsWorksheet As Worksheet
    Set resultsWorksheet = Worksheets.Add
    
    Dim blockCollection As Collection
    Set blockCollection = httpResponse("result")
    
    Dim blockDictionary As Object
    Dim blockIndex As Long
    Dim rowIndex As Long
    Dim colIndex As Long
    
    rowIndex = 2
    With blockCollection
        For blockIndex = 1 To .Count
            Set blockDictionary = .Item(blockIndex)
            If blockIndex = 1 Then
                For colIndex = 1 To blockDictionary.Count
                    resultsWorksheet.Cells(1, colIndex).Value = blockDictionary.keys()(colIndex - 1)
                Next colIndex
            Else
                For colIndex = 1 To blockDictionary.Count
                    resultsWorksheet.Cells(rowIndex, colIndex).Value = blockDictionary.items()(colIndex - 1)
                Next colIndex
                rowIndex = rowIndex + 1
            End If
        Next blockIndex
    End With

    
End Sub

Hope this helps!

 

[jpowell79]

thank you so much for responding, I genuinely appreciate it. Unfortunately I'm not entirely sure how to get this working. ?

I installed the JsonConverter.bas and then copied the code above it to it, and then hit run (I'm assuming that's what I was meant to do?) and then I get the following:

"Compile error - User-defined type not defined."

with this line of text highlighted:

Private Function json_ParseObject(json_String As String, ByRef json_Index As Long) As Dictionary

I'm a little out of my depth when we start getting into VBA so I've no doubt it's me doing something wrong here..... any chance of an absolute idiots guide to get it working step-by-step?

thanks again!

 

[Domenic]

Sorry, I should have mentioned that you need to set the following reference (VBE >> Tools >> References) . . .

Microsoft Scripting Runtime

Check/select it, and then click on OK.

Let me know if you need further help.

Cheers!

 

[Domenic]

I installed the JsonConverter.bas and then copied the code above it to it

I would suggest that you copy/paste my code in a separate module so that it's better organized.