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!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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,095,975
Messages
5,447,659
Members
405,460
Latest member
stuartbennett

This Week's Hot Topics

Top