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
12Next
Return Post new threads
View: 503|Reply: 12

Parse Relevant Data from API URL to Individual Cells

[Copy link]

2

Topics

5

Posts

20

Integral

Member

Rank: 2

Integral
20
Post on 2-7-2017 15:09:50 | All posts |Read mode
I am new to APIs and VBA. Please be patient with me.
I have an API URL that I need to get data from and insert in to Excel. The VBA code I have so far is working (more or less). However, I need to extract only the relevant data and place it in individual cells. Currently, EVERTHING that is returned from the URL is placed in cell A1.
Here is my current VBA code I am working with. It retrieves the data from the URL, places the entire result in cell A1, and updates every second. There is a stop start function for the clock as well:
1.VBA code In ThisWorkbook:
Private Sub Workbook_Open()
alertTime = Now + TimeValue(00:00:01)
Application.OnTime alertTime, startClock
End Sub2.VBA code In Module 1:
Dim clockOn As Boolean
Sub EventMacro()
If clockOn = True Then
Dim oXMLHTTP As Object
Dim sPageHTML As String, Str As String
Dim sURL As String
'URL
sURL = https://api-fxtrade.oanda.com/v1/candles?instrument=USD_CADcount=1candleFormat=midpointgranularity=S5dailyAlignment=0alignmentTimezone=America%2FVancouver
'Extract data from website to Excel using VBA
Set oXMLHTTP = CreateObject(MSXML2.ServerXMLHTTP)
oXMLHTTP.Open GET, sURL, False
oXMLHTTP.send
sPageHTML = oXMLHTTP.responseText
'Get webpage data into Excel
ThisWorkbook.Sheets(1).Cells(1, 1) = sPageHTML
alertTime = Now + TimeValue(00:00:01)
Application.OnTime alertTime, EventMacro
End If
End Sub
Sub startClock()
clockOn = True
EventMacro
End Sub
Sub stopClock()
clockOn = False
End SubThis is the result from the above code that is returned in cell A1. I need to extract only the relevant data and place it in individual cells.
{
instrument : USD_CAD,
granularity : S5,
candles : [
{
time : 2017-02-07T17:35:25.000000Z,
openMid : 1.317275,
highMid : 1.31737,
lowMid : 1.317275,
closeMid : 1.31737,
volume : 4,
complete : false
}
]
}
How do I parse the above result so that it reads something more like below? Ive put the intended target cell numbers in brackets for reference. This is an example so cell numbers as well as the actual data I want may change later. This is a good place to start for now.
HEADER:
instrument (A1), granularity (B1), time (C1), openMid (D1), highMid (E1), lowMid(F1), closeMid(G1), volume (H1)
DATA:
USD_CAD (A2), S5 (B2), 2017-02-07T17:35:25.000000Z(C2), 1.317275(D2), 1.31737(E2), 1.317275(F2), 1.31737 (G2), 4 (H2)

Thank you in advance for your assistance.












Reply

Props Report

6

Topics

732

Posts

1620

Integral

King

Rank: 6Rank: 6

Integral
1620
Post on 2-7-2017 16:03:14 | All posts



The return type is JSON, so you need a JSON parser code.
Link
https://github.com/VBA-tools/VBA-JSON
Reply Support Opposition

Props Report

6

Topics

732

Posts

1620

Integral

King

Rank: 6Rank: 6

Integral
1620
Post on 2-7-2017 22:16:11 | All posts



The return type is JSON, so you need a JSON parser code.
Link
https://github.com/VBA-tools/VBA-JSON










Reply Support Opposition

Props Report

2

Topics

5

Posts

20

Integral

Member

Rank: 2

Integral
20
 Author| Post on 2-7-2017 23:37:36 | All posts



Is this the only way to do it? Or, can it also be achieved directly, without installing the parser code?




Reply Support Opposition

Props Report

6

Topics

732

Posts

1620

Integral

King

Rank: 6Rank: 6

Integral
1620
Post on 2-7-2017 23:47:15 | All posts



Well, it might be possible to use excel's built-in functions, but it will be a bloated code (loop, cut, replace and so on). As far as I know, there is no excel functions which parse JSON (There is for XML). It might be also the reason for writing a separate code by Tim.
Reply Support Opposition

Props Report

6

Topics

732

Posts

1620

Integral

King

Rank: 6Rank: 6

Integral
1620
Post on 2-8-2017 21:41:23 | All posts



Well, it might be possible to use excel's built-in functions, but it will be a bloated code (loop, cut, replace and so on). As far as I know, there is no excel functions which parse JSON (There is for XML). It might be also the reason for writing a separate code by Tim.




Reply Support Opposition

Props Report

2

Topics

5

Posts

20

Integral

Member

Rank: 2

Integral
20
 Author| Post on 2-8-2017 22:07:48 | All posts



OK. It's working now using my TEST URL. Thank you.
HOWEVER,
I have other URLs I need to configure that begin with:
$curl -X GET https://.....
Some of them also require an API authentication key (which I have but for security I will obviously not post).
How do I correctly format the URL requests in my VBA code to provide this additional information to the API server?
thanks again




Reply Support Opposition

Props Report

6

Topics

732

Posts

1620

Integral

King

Rank: 6Rank: 6

Integral
1620
Post on 2-8-2017 23:03:48 | All posts



This link has some code lines which gives you an idea how to parse the API in to URL.
http://www.excelforum.com/showthread.php?t=1123818
By the way, how do you mange get JSON data with out the parser?
I also thought Oanda has the option of parsing the data either in CSV or XML format.
Reply Support Opposition

Props Report

6

Topics

732

Posts

1620

Integral

King

Rank: 6Rank: 6

Integral
1620
Post on 2-10-2017 22:35:34 | All posts



This link has some code lines which gives you an idea how to parse the API in to URL.
http://www.excelforum.com/showthread.php?t=1123818
By the way, how do you mange get JSON data with out the parser?
I also thought Oanda has the option of parsing the data either in CSV or XML format.




Reply Support Opposition

Props Report

2

Topics

5

Posts

20

Integral

Member

Rank: 2

Integral
20
 Author| Post on 2-10-2017 22:45:07 | All posts



I used the JSON Parser as you recommended to get it going. In addition to the URL, it looks like I also need a header in my code that gets sent to OANDA. How and where in the code would I do this?




Reply Support Opposition

Props Report

12Next
Return Post new threads

Points policy of this forum

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

GMT-5, 9-21-2017 14:36 , Processed in 0.142452 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!