VBA Get Historical Stock Data JSON API

VBA Get Historical Stock Data JSON API

VBA to ping web API that returns JSON and how to extract and parse the individual JSON node values. Access historical stock market data via JSON API from iextrading.com

API Endpoint: https://api.iextrading.com/1.0/stock/jnj/quote

Change “jnj” to the ticker you want to pull. Will pull all historical data that is available to the present time.

VBA Get Historical Stock Data JSON API

Dim scriptcontrol, restext, jsonSrc As Variant
Set scriptcontrol = CreateObject("MSScriptControl.ScriptControl")
scriptcontrol.Language = "JScript"

With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", "https://api.iextrading.com/1.0/stock/jnj/quote", False
    .Send
    restext = Replace(.responseText, "[", "")
    restext = Replace(restext, "]", "")

    Set jsonSrc = scriptcontrol.Eval("(" + restext + ")")
        MsgBox "Symbol: " & jsonSrc.symbol
        MsgBox "52 week low: " & jsonSrc.week52Low
        MsgBox "52 week high: " & jsonSrc.week52High
End With
 JSON API Response
{
symbol: "JNJ",
companyName: "Johnson & Johnson",
primaryExchange: "New York Stock Exchange",
sector: "Healthcare",
calculationPrice: "tops",
open: 135.22,
openTime: 1555075800419,
close: 135.21,
closeTime: 1555012889207,
high: 135.55,
low: 134.947,
latestPrice: 135.78,
latestSource: "IEX real time price",
latestTime: "9:49:47 AM",
latestUpdate: 1555076987292,
latestVolume: 363591,
iexRealtimePrice: 135.78,
iexRealtimeSize: 19,
iexLastUpdated: 1555076987292,
delayedPrice: 135.06,
delayedPriceTime: 1555076082134,
extendedPrice: 135.78,
extendedChange: 0,
extendedChangePercent: 0,
extendedPriceTime: 1555076987292,
previousClose: 135.21,
change: 0.57,
changePercent: 0.00422,
iexMarketPercent: 0.03219,
iexVolume: 11704,
avgTotalVolume: 6113944,
iexBidPrice: 135.79,
iexBidSize: 100,
iexAskPrice: 138.34,
iexAskSize: 200,
marketCap: 361551198589,
peRatio: 16.6,
week52High: 148.99,
week52Low: 118.621,
ytdChange: 0.06963494921923949
}