How to find a maximum value from a list of json pairs using Excel vba ?

Rasmita Nayak

New Member
Joined
Jul 15, 2019
Messages
5
Hi Experts,

I am totally new in VBA and coding.
Suppose i have a json string like this:

jsonstr = [{"id":"BGFV:US","dateTimeRanges":{},"price":[{"date":"2019-08-05","value":2.07},{"date":"2019-08-06","value":2.02},{"date":"2019-08-07","value":2.03},{"date":"2019-08-08","value":1.98},{"date":"2019-08-09","value":1.98},{"date":"2019-08-12","value":1.9100000000000001},{"date":"2019-08-13","value":1.9100000000000001},{"date":"2019-08-14","value":1.8},{"date":"2019-08-15","value":1.7},{"date":"2019-08-16","value":1.77},{"date":"2019-08-19","value":1.8399999999999999},{"date":"2019-08-20","value":1.8199999999999998},{"date":"2019-08-21","value":1.9100000000000001},{"date":"2019-08-22","value":1.97},{"date":"2019-08-23","value":1.95},{"date":"2019-08-26","value":1.96},{"date":"2019-08-27","value":2.07},{"date":"2019-08-28","value":1.87},{"date":"2019-08-29","value":1.87},{"date":"2019-08-30","value":1.79},{"date":"2019-09-03","value":1.7},{"date":"2019-09-04","value":1.71}],"timeZoneOffset":-4,"nyTradeStartTime":"09:30:00.000","nyTradeEndTime":"16:30:00.000","priceMinDecimals":2,"lastUpdateDate":"2019-09-04","lastPrice":1.71}]


I am trying to get data means " highest value" of the stock price from the above json string.
However, I only want the highest value of the “value” variable displayed in row 2 in my excelsheet.

I have written the following macro:
Please see above the part of code section.

Sub getData()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim n As Integer
Dim lastrow As Long
Dim i As Double

Set wb = ActiveWorkbook
Set ws = Sheets("Sheet1")
ws.Activate

'Last row find
lastrow = ws.Cells(Rows.count, "A").End(xlUp).Row

Set rng = ws.Range("A2:A" & lastrow)
'Clear Prior Prices
ws.Range("B2:B" & lastrow).ClearContents

n = 2
'Get Symbols list
For Each Symbol In rng

Dim httpObject As Object
Set httpObject = CreateObject("MSXML2.XMLHTTP")
Dim sURL As String
sURL = "https://www.bloomberg.com/markets/api/bulk-time-series/price/" & Symbol & "%3AUS?timeFrame=1_DAY"

Dim sRequest As String
sRequest = sURL
httpObject.Open "GET", sRequest, False
httpObject.Send
Dim sGetResult As String

sGetResult = httpObject.ResponseText
Dim oJSON As Variant
Set oJSON = JsonConverter.ParseJson(sGetResult)

On Error Resume Next
For Each item In oJSON(price)
ws.Cells(n, 2).Value = item("value")

Next item
n = n + 1

Next Symbol

MsgBox ("Data is downloaded.")

End Sub

When I run this code, it gives no error and no output.
I think I am missing some basic things but still I am unable to find it.

Any ideas would be greatly appreciated!

Thanks in advance!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Rasmita Nayak

New Member
Joined
Jul 15, 2019
Messages
5
Sorry, by mistakenly i have mention the time frame 1_DAY.
But according to Json string , the time frame was 1_MONTH
 

Watch MrExcel Video

Forum statistics

Threads
1,130,205
Messages
5,640,836
Members
417,172
Latest member
Matias_Luna

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top