MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
12
Return Post new threads
Author: mamero

Parse Relevant Data from API URL to Individual Cells

[Copy link]

6

Topics

732

Posts

1620

Integral

King

Rank: 6Rank: 6

Integral
1620
Post on 2-11-2017 00:00:03 | All posts



The simple answer is I do not know.
You probably need to follow REST implementation provided by the API issuer. The link I attached deals with UK's Government site REST. Kyle- the chap who wrote the code parsed the API in to the URL.
I thought you could follow his example, but it might not be relevant.
Reply Support Opposition

Props Report

6

Topics

732

Posts

1620

Integral

King

Rank: 6Rank: 6

Integral
1620
Post on 2-12-2017 07:18:55 | All posts



The simple answer is I do not know.
You probably need to follow REST implementation provided by the API issuer. The link I attached deals with UK's Government site REST. Kyle- the chap who wrote the code parsed the API in to the URL.
I thought you could follow his example, but it might not be relevant.




Reply Support Opposition

Props Report

6

Topics

732

Posts

1620

Integral

King

Rank: 6Rank: 6

Integral
1620
Post on 2-12-2017 08:18:05 | All posts



Hi !
Like piloting a webbrowser or running a request to grab data,
it can be achieved directly in a VBA procedure and with differents ways !
As JSon structure is just about text, VBA inner text functions (specially
Split
) can do the job,
at beginner level if warming a couple of neurones !
Can be done also via a regular expression but needs a higher coding level … (many tutorials on web)
For those meeting difficulties with easy text functions,
every JSon field can be directly used in a VBA code as an object property !
Just needs to respect lower and upper cases to work,
the reason why in next demonstration the variable time is declared 'cause « Time » is a VBA statement !
Instead of a variable other way is to use
CallByName
VBA function …
Sub DemoJSon()
   Dim oJS As Object, oJSon As Object, oCandle As Object, time
   Set oJS = CreateObject(ScriptControl)
       oJS.Language = JScript
    With CreateObject(MSXML2.XMLHttp)
        .Open GET, https://api-fxtrade.oanda.com/v1/candles?instrument=USD_CADcount=1candleFormat=midpointgranularity=S5dailyAlignment=0alignmentTimezone=America%2FVancouver, False
        .setRequestHeader DNT, 1
         On Error Resume Next
        .send
         Set oJSon = oJS.Eval((  .responseText  ))
         On Error GoTo 0
    End With
          If oJSon Is Nothing Then Beep: Set oJS = Nothing: Exit Sub
            [A1:H1].Value = Split(Instrument Granularity Time OpenMid HighMid LowMid CloseMid Volume)
    For Each oCandle In oJSon.candles
                    time = time + 1
        With oCandle
            [A1:H1].Offset(time).Value = Array(oJSon.instrument, oJSon.granularity, .time, .openMid, .highMid, .lowMid, .closeMid, .volume)
        End With
    Next
   Set oJS = Nothing:  Set oJSon = Nothing
End SubDo you like it ? So thanks to click on bottom left star icon « Add Reputation » !
Reply Support Opposition

Props Report

12
Return Post new threads

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 11-20-2017 00:47 , Processed in 0.151701 second(s), 17 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!