How to parse fixer.io json output into excel (for dummies)

Question

I use FIXER.IO popular API in a VBA to get exchange rates into some cells of my worksheet named USD, CNY, INR etc. Fixer.io API was returning a text format giving the rates I needed. As of March 6th 2018, the legacy Fixer API (api.fixer.io) was deprecated and changed into a new version that requires an API Access Key (got it upon registration) but only returns a JSON file. If I call the url:

http://data.fixer.io/api/latest?access_key=XXXXXXXXXXXX&symbols=USD,CNY,INR,THB,SGD,AUD

I get this JSON in return:

{"success":true,"timestamp":1523343843,"base":"EUR","date":"2018-04-10","rates":{"USD":1.231986,"CNY":7.757563,"INR":79.980529,"THB":38.462602,"SGD":1.614924,"AUD":1.592345}}

How can I parse the exchange values in my Excel variables (USD, CNY ...) ? I tried to look around but my very limited programming skill did not help me to adapt any solution. Please give a "for dummy" reply :) Thanks for any help provided Regards Marco

Have a look at [this project](https://github.com/VBA-tools/VBA-JSON) Google just pointed me to. Seems decently easy to use. If you have trouble using it, please indicate where that trouble stems from. If on the other hand that project helps you accompishing your task, I can post it as an answer. — Inarion, Apr 10 '18 at 08:29
In my opinion, there is no such thing as a "for dummies" method of parsing JSON with VBA -- there are a few ways you could tackle the issue, but any of them require a moderate to high level of programming skill. That being said, your best best might be to parse the test with standard built-in text functions (InStr, Mid, Left, Right, etc.) — ashleedawg, Apr 10 '18 at 09:20

omegastripes · Answer 1 · 2020-01-02T23:15:06.213

Take a look at the below example. Import JSON.bas module into the VBA project for JSON processing.

Option Explicit

Sub Test()

    Dim sJSONString As String
    Dim vJSON
    Dim sState As String
    Dim aData()
    Dim aHeader()

    ' Retrieve data
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "http://data.fixer.io/api/latest?access_key=209f86f5304e0043a0879d8cb45c9c10&symbols=USD,CNY,INR,THB,SGD,AUD", False
        .Send
        sJSONString = .ResponseText
    End With
    ' Parse JSON response
    JSON.Parse sJSONString, vJSON, sState
    ' Refer to target dictionary containing rates
    Set vJSON = vJSON("rates")
    ' Access to each item in dictionary
    Debug.Print vJSON("USD")
    Debug.Print vJSON("CNY")
    Debug.Print vJSON("INR")
    Debug.Print vJSON("THB")
    Debug.Print vJSON("SGD")
    Debug.Print vJSON("AUD")
    ' Convert to array and output to worksheet
    JSON.ToArray vJSON, aData, aHeader
    With Sheets(1)
        .Cells.Delete
        .Cells.WrapText = False
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With

End Sub

Sub OutputArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Sub Output2DArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize( _
                UBound(aCells, 1) - LBound(aCells, 1) + 1, _
                UBound(aCells, 2) - LBound(aCells, 2) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

The output for me as follows:

BTW, the similar approach applied in other answers.

How to parse fixer.io json output into excel (for dummies)

1 Answers1