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

Rasmita Nayak

New Member
Jul 15, 2019
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")

'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 = "" & Symbol & "%3AUS?timeFrame=1_DAY"

Dim sRequest As String
sRequest = sURL
httpObject.Open "GET", sRequest, False
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!

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Rasmita Nayak

New Member
Jul 15, 2019
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

Latest member

This Week's Hot Topics