Getting currency exchange rate from Alpha Vantage API

[Worf99]

Hi,
I need to get real time currency exchange rate EUR/USD.

I must use this<code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241);">https://www.alphavantage.co/query?function=CURRENCY_EXCHANGE_RATE&from_currency=EUR&to_currency=USD&apikey=xxxxxxxxxx</code>

in the browser it returns this

{
"Realtime Currency Exchange Rate": {
"1. From_Currency Code": "EUR",
"2. From_Currency Name": "Euro",
"3. To_Currency Code": "USD",
"4. To_Currency Name": "United States Dollar",
"5. Exchange Rate": "1.15247200",
"6. Last Refreshed": "2018-10-06 17:13:29",
"7. Time Zone": "UTC"
}
}

How can I assign the value of "5. Exchange Rate" to a variable in VBA?

Thank you very much.

 

[John_w]

See if you can adapt the code at https://www.mrexcel.com/forum/excel...url-import-parse-post5036313.html#post5036313

 

[Worf99]

Hi and thank you.
I tried creating an empty sheet and copied the above code in a new module, just to see how it works.
Then, when I write in a cell of the sheet, B3 cell for example, =Extract_JSON_Data2() I get error "syntax not correct" (it could be a little different because I use Windows 10 in italian).
So I guess I'm just doing a silly mistake. Where should I copy the code? And how to call it?

 

[lrobbo314]

The code in the link is a subroutine, not a function. If it were a function you would write it like a formula like '=Extract_JSON_Data2()'.

All you need to do is hit Alt+F8. This will bring up a window with all of your subroutines. From there you can select Extract_JSON_Data2 and run it.

 

[Worf99]

Thank you very much. Now, forgive me the silly question: how can I assign the value returned by Extract_JSON_Data2() to a variable?

 

[lrobbo314]

The following code will export the JSON data to the current worksheet. Towards the bottom of the code there is a loop 'For Each Key In KeysObj'. That is where it is going through the JSON object and writing the values to the worksheet. If instead of writing to the worksheet you want to assign the values to a variable, you would just declare a variable at the top of the code like 'Dim MyVar as String', then instead of the lines of code writing to the sheet, it would be like 'MyVar = Key'.

Public Sub Extract_JSON_Data2()


    Dim script As Object
    Dim HTTPobj As Object
    Dim URL As String
    Dim JSONobj As Object
    Dim SMAlist As Object
    Dim KeysObj As Object
    Dim Keys As Variant, Key As Variant
    Dim dateItem As Object
    Dim destCell As Range, r As Long
    Dim Tmp As Object
    
    With Worksheets("Sheet1")
        .Cells.ClearContents
        .Range("A1:B1").Value = Array("Description", "Value")
        Set destCell = .Range("A2")
    End With
    
    Set script = CreateObject("MSScriptControl.ScriptControl")
    script.Language = "JScript"
    script.AddCode "function getKeys(jsonObj) { var keys = new Array(); for (var i in jsonObj) { keys.push(i); } return keys; }"
    script.AddCode "function getItemByKey(jsonObj, key) { return jsonObj[key]; }"
    
    URL = "https://www.alphavantage.co/query?function=CURRENCY_EXCHANGE_RATE&from_currency=EUR&to_currency=USD&apikey=ES1RXJ7VF1C1L9N5"
    
    Set JSONobj = Nothing
    Set HTTPobj = CreateObject("MSXML2.XMLHTTP")
    With HTTPobj
        .Open "GET", URL, False
        .Send
        If .Status = 200 Then
            Set JSONobj = script.Eval("(" & .responseText & ")")
        End If
    End With
    
    If Not JSONobj Is Nothing Then
        Set SMAlist = VBA.CallByName(JSONobj, "Realtime Currency Exchange Rate", VbGet)
        Set KeysObj = script.Run("getKeys", SMAlist)
        r = 0
        For Each Key In KeysObj
            destCell.Offset(r, 0).Value = Key
            destCell.Offset(r, 1).Value = VBA.CallByName(SMAlist, Key, VbGet)
            r = r + 1
        Next
    End If
        
End Sub

 

[Worf99]

Thanks again for your patience and willingness to teach me.
I got "run-time error 9"
Index not included in interval

and it stops immediately

 

[lrobbo314]

That's weird. I just copied and pasted the code into a new workbook and it worked fine. What line of code does it get stuck on?

 

[Worf99]

When I go to debug it goes here

With Worksheets("Sheet1")

 

[lrobbo314]

So it's trying to access the sheet named "Sheet1" in your workbook. It's going to throw that error if you don't have a sheet in your workbook with that name. Change "Sheet1" to whatever sheet you want the results to show up in and you should be good to go.