How to parse this URL

[cryptophan]

hi Experts..

how can i parse the data from this website into excel?

https://api.binance.com/api/v1/ticker/allPrices

into 2 columns

Symbol and Price

really appreciate the help

 

[Norie]

Goto Data>New Query and select From Other Sources>Blank Query.

When the Power Query editor opens goto View>Advanced Editor, paste this in.

let
Source = Json.Document(Web.Contents("https://api.binance.com/api/v1/ticker/allPrices")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"symbol", "price"}, {"Column1.symbol", "Column1.price"})
in
#"Expanded Column1"

and click Done.

 

[cryptophan]

Goto Data>New Query and select From Other Sources>Blank Query.

When the Power Query editor opens goto View>Advanced Editor, paste this in.

let
Source = Json.Document(Web.Contents("https://api.binance.com/api/v1/ticker/allPrices")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"symbol", "price"}, {"Column1.symbol", "Column1.price"})
in
#"Expanded Column1"

and click Done.

Thanks for the prompt answer, but sorry i'm forgot to mentions that i am still using office 2007 and win XP, so sadly Power Query does not support yet for me, any other idea ?

 

[cryptophan]

Sub testing()


Set MyRequest = CreateObject("winhttp.winhttprequest.5.1")
MyRequest.Open "GET", "https://api.binance.com/api/v3/ticker/price?symbol=ADABTC"
MyRequest.send
'MsgBox myrequest.responsetext

Dim JSON As Object
Set JSON = JsonConverter.ParseJson(MyRequest.ResponseText)

Range("A1") = JSON("symbol")
Range("B1") = JSON("price")
End Sub

using ms.office 2007
win xp
and vba-json

this code return with errors...anybody could help?

 

[Norie]

Where are you getting JsonConverter from?

Is it something you've installed?

 

[Norie]

Where are you getting JsonConverter from?

Is it something you've installed?

 

[Rick Rothstein]

I know nothing about JSON text, so I am assuming the beginning part of the code you posted in Message #4 which loads up the MyRequest object is correct... given that, I believe the following code will place the values you want into the active sheet in Columns A:B starting at Row 1...

[table="width: 500"]
[tr]
	[td]Sub ParseJSON()
  Dim Arr As Variant, MyRequest As Object
  Set MyRequest = CreateObject("winhttp.winhttprequest.5.1")
  MyRequest.Open "GET", "https://api.binance.com/api/v3/ticker/price?symbol=ADABTC"
  MyRequest.send
  Arr = Split(Replace(Replace(Replace(Replace(MyRequest.ResponseText, """", ""), "},{", " "), "[{", ""), "}]", ""))
  With Range("A1").Resize(UBound(Arr) + 1)
    .Value = Application.Transpose(Arr)
    .TextToColumns Range("A1"), xlDelimited, , , False, False, True, False, True, ":", Array(Array(1, 9), Array(2, 1), Array(3, 9), Array(4, 1))
  End With
End Sub[/td]
[/tr]
[/table]

 

[cryptophan]

Where are you getting JsonConverter from?

Is it something you've installed?

yes, it's from https://github.com/VBA-tools/VBA-JSON

 

[cryptophan]

I know nothing about JSON text, so I am assuming the beginning part of the code you posted in Message #4 which loads up the MyRequest object is correct... given that, I believe the following code will place the values you want into the active sheet in Columns A:B starting at Row 1...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ParseJSON()
  Dim Arr As Variant, MyRequest As Object
  Set MyRequest = CreateObject("winhttp.winhttprequest.5.1")
  MyRequest.Open "GET", "https://api.binance.com/api/v3/ticker/price?symbol=ADABTC"
  MyRequest.send
  Arr = Split(Replace(Replace(Replace(Replace(MyRequest.ResponseText, """", ""), "},{", " "), "[{", ""), "}]", ""))
  With Range("A1").Resize(UBound(Arr) + 1)
    .Value = Application.Transpose(Arr)
    .TextToColumns Range("A1"), xlDelimited, , , False, False, True, False, True, ":", Array(Array(1, 9), Array(2, 1), Array(3, 9), Array(4, 1))
  End With
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

hi rick
got run time error (80090326) when i try to run it

 

[Norie]

cryptophan

Did you follow the installation instructions on that page?