API Json to Excel Macro

I am trying to import the following api json in excel table.
http://api.manilainvestor.com/v1/stocks/hdata/dnl
Can anyone help me develop the code for the macro.

Data must be separated in these coloumns.

Date | High | Low | Open | Close | Volume


Thank you.

This will dump the data in the first sheet, starting at A1

Public Sub getData()

    Dim JSONObject As Object
    Dim scriptControl As Object
    Dim JS As Object
    Dim x As Long: x = 1

    Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
    scriptControl.Language = "JScript"
    
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "http://api.manilainvestor.com/v1/stocks/hdata/dnl", False
        .send
        Set JSONObject = scriptControl.Eval("(" + .responsetext + ")")
        .abort
        For Each JS In JSONObject
            Sheets(1).Cells(x, 1).Value = JS.Date / (CDbl(60) * CDbl(60) * CDbl(24)) + #1/1/1970#
            Sheets(1).Cells(x, 2).Value = JS.High
            Sheets(1).Cells(x, 3).Value = JS.Low
            Sheets(1).Cells(x, 4).Value = JS.Open
            Sheets(1).Cells(x, 5).Value = JS.Close
            Sheets(1).Cells(x, 6).Value = JS.Volume
            x = x + 1
        Next JS
    End With
End Sub

Thanks Kyle. You're awesome!

Hi Kyle,

I tried to use your code Excel 2010, I am receiving error message to this line:

Set scriptControl = CreateObject("MSScriptControl.ScriptControl")

What's the error?

I am getting the error Run-time error '429' ActiveX component can't create object in the line

Set scriptControl = CreateObject("MSScriptControl.ScriptControl")

using Excel 2010 x64.

You should have said that earlier really, MSScript Control is 32-bit and will work on 32-bit versions of Excel (on both 32 and 64-bit windows), but not the 64-bit version.

This will need a complete re-write, I'll have a look when I get chance.

Thanks Kyle.