Skip to content 
In this tutorial, you will learn how to retrieve financial data from Yahoo Finance API using VBA in Excel. This can be useful for tracking stock prices, currency exchange rates, and other financial metrics. We will walk you through the process of setting up the API, creating a VBA macro to make the API request, and parsing the JSON response to extract the relevant data. This tutorial is suitable for beginners with some basic knowledge of VBA programming in Excel. Follow along and start harnessing the power of Yahoo Finance API to supercharge your financial analysis!
code
Dim x As Boolean
Sub YahooFinanceAPI()
On Error Resume Next
ActiveSheet.Cells(1, 4) = "shortName"
ActiveSheet.Cells(1, 5) = "regularMarketPr1ce"
ActiveSheet.Cells(1, 6) = "regularMarketChangePercent"
ActiveSheet.Cells(1, 7) = "b1d"
ActiveSheet.Cells(1, 8) = "ask"
ActiveSheet.Cells(1, 9) = "regularMarketDayLow"
ActiveSheet.Cells(1, 10) = "regularMarketPrev1ousClose"
ActiveSheet.Cells(1, 11) = "f1ftyTwoWeekLowChange"
ActiveSheet.Cells(1, 12) = "regularMarketOpen"
ActiveSheet.Cells(1, 13) = "f1ftyTwoWeekRange"
ActiveSheet.Cells(1, 14) = "f1ftyTwoWeekLowChangePercent"
ActiveSheet.Cells(1, 15) = "f1ftyTwoWeekH1ghChange"
ActiveSheet.Cells(1, 16) = "f1ftyTwoWeekH1ghChangePercent"
ActiveSheet.Cells(1, 17) = "f1ftyTwoWeekLow"
ActiveSheet.Cells(1, 18) = "f1ftyTwoWeekH1gh"
ActiveSheet.Cells(1, 19) = "tra1l1ngAnnualD1v1dendRate"
ActiveSheet.Cells(1, 20) = "tra1l1ngAnnualD1v1dendY1eld"
ActiveSheet.Cells(1, 21) = "ytdReturn"
ActiveSheet.Cells(1, 22) = "tra1l1ngThreeMonthReturns"
ActiveSheet.Cells(1, 23) = "tra1l1ngThreeMonthNavReturns"
ActiveSheet.Cells(1, 24) = "f1ftyDayAverage"
ActiveSheet.Cells(1, 25) = "f1ftyDayAverageChange"
ActiveSheet.Cells(1, 26) = "f1ftyDayAverageChangePercent"
ActiveSheet.Cells(1, 27) = "regularMarketDayH1gh"
ActiveSheet.Cells(1, 28) = "regularMarketDayRange"
ActiveSheet.Cells(1, 29) = "askS1ze"
ActiveSheet.Cells(1, 30) = "averageDa1lyVolume10Day"
ActiveSheet.Cells(1, 31) = "averageDa1lyVolume3Month"
ActiveSheet.Cells(1, 32) = "b1dS1ze"
ActiveSheet.Cells(1, 33) = "cryptoTradeable"
ActiveSheet.Cells(1, 34) = "currency"
ActiveSheet.Cells(1, 35) = "customPr1ceAlertConf1dence"
ActiveSheet.Cells(1, 36) = "esgPopulated"
ActiveSheet.Cells(1, 37) = "exchange"
ActiveSheet.Cells(1, 38) = "exchangeDataDelayedBy"
ActiveSheet.Cells(1, 39) = "exchangeT1mezoneName"
ActiveSheet.Cells(1, 40) = "exchangeT1mezoneShortName"
ActiveSheet.Cells(1, 41) = "f1rstTradeDateM1ll1seconds"
ActiveSheet.Cells(1, 42) = "fullExchangeName"
ActiveSheet.Cells(1, 43) = "gmtOffSetM1ll1seconds"
ActiveSheet.Cells(1, 44) = "language"
ActiveSheet.Cells(1, 45) = "longName"
ActiveSheet.Cells(1, 46) = "market"
ActiveSheet.Cells(1, 47) = "marketState"
ActiveSheet.Cells(1, 48) = "messageBoard1d"
Dim i As Long
Dim lastrow As Long
lastrow = ActiveSheet.Range("C1048576").End(xlUp).Row
For i = 2 To lastrow
Dim Symbol As String
Symbol = ActiveSheet.Cells(i, 3) ' Assuming the stock symbol is in cell A1
Dim url As String
url = "https://query1.finance.yahoo.com/v7/finance/quote?symbols=" & Symbol
' Create a new HTTP request
Dim http As Object
Set http = CreateObject("MSXML2.XMLHTTP")
' Send the request and retrieve the response
http.Open "GET", url, False
http.Send
Debug.Print http.responseText
' Parse the JSON response into an object
Dim json As Object
Set json = JsonConverter.ParseJson(http.responseText)
' Extract the desired data from the JSON object and split it into separate columns
ActiveSheet.Cells(i, 4) = json("quoteResponse")("result")(1)("shortName")
ActiveSheet.Cells(i, 5) = json("quoteResponse")("result")(1)("regularMarketPrice")
ActiveSheet.Cells(i, 6) = json("quoteResponse")("result")(1)("regularMarketChangePercent")
ActiveSheet.Cells(i, 7) = json("quoteResponse")("result")(1)("bid")
ActiveSheet.Cells(i, 8) = json("quoteResponse")("result")(1)("ask")
ActiveSheet.Cells(i, 9) = json("quoteResponse")("result")(1)("regularMarketDayLow")
ActiveSheet.Cells(i, 10) = json("quoteResponse")("result")(1)("regularMarketPreviousClose")
ActiveSheet.Cells(i, 11) = json("quoteResponse")("result")(1)("fiftyTwoWeekLowChange")
ActiveSheet.Cells(i, 12) = json("quoteResponse")("result")(1)("regularMarketOpen")
ActiveSheet.Cells(i, 13) = json("quoteResponse")("result")(1)("fiftyTwoWeekRange")
ActiveSheet.Cells(i, 14) = json("quoteResponse")("result")(1)("fiftyTwoWeekLowChangePercent")
ActiveSheet.Cells(i, 15) = json("quoteResponse")("result")(1)("fiftyTwoWeekHighChange")
ActiveSheet.Cells(i, 16) = json("quoteResponse")("result")(1)("fiftyTwoWeekHighChangePercent")
ActiveSheet.Cells(i, 17) = json("quoteResponse")("result")(1)("fiftyTwoWeekLow")
ActiveSheet.Cells(i, 18) = json("quoteResponse")("result")(1)("fiftyTwoWeekHigh")
ActiveSheet.Cells(i, 19) = json("quoteResponse")("result")(1)("trailingAnnualDividendRate")
ActiveSheet.Cells(i, 20) = json("quoteResponse")("result")(1)("trailingAnnualDividendYield")
ActiveSheet.Cells(i, 21) = json("quoteResponse")("result")(1)("ytdReturn")
ActiveSheet.Cells(i, 22) = json("quoteResponse")("result")(1)("trailingThreeMonthReturns")
ActiveSheet.Cells(i, 23) = json("quoteResponse")("result")(1)("trailingThreeMonthNavReturns")
ActiveSheet.Cells(i, 24) = json("quoteResponse")("result")(1)("fiftyDayAverage")
ActiveSheet.Cells(i, 25) = json("quoteResponse")("result")(1)("fiftyDayAverageChange")
ActiveSheet.Cells(i, 26) = json("quoteResponse")("result")(1)("fiftyDayAverageChangePercent")
ActiveSheet.Cells(i, 27) = json("quoteResponse")("result")(1)("regularMarketDayHigh")
ActiveSheet.Cells(i, 28) = json("quoteResponse")("result")(1)("regularMarketDayRange")
ActiveSheet.Cells(i, 29) = json("quoteResponse")("result")(1)("askSize")
ActiveSheet.Cells(i, 30) = json("quoteResponse")("result")(1)("averageDailyVolume10Day")
ActiveSheet.Cells(i, 31) = json("quoteResponse")("result")(1)("averageDailyVolume3Month")
ActiveSheet.Cells(i, 32) = json("quoteResponse")("result")(1)("bidSize")
ActiveSheet.Cells(i, 33) = json("quoteResponse")("result")(1)("cryptoTradeable")
ActiveSheet.Cells(i, 34) = json("quoteResponse")("result")(1)("currency")
ActiveSheet.Cells(i, 35) = json("quoteResponse")("result")(1)("customPriceAlertConfidence")
ActiveSheet.Cells(i, 36) = json("quoteResponse")("result")(1)("esgPopulated")
ActiveSheet.Cells(i, 37) = json("quoteResponse")("result")(1)("exchange")
ActiveSheet.Cells(i, 38) = json("quoteResponse")("result")(1)("exchangeDataDelayedBy")
ActiveSheet.Cells(i, 39) = json("quoteResponse")("result")(1)("exchangeTimezoneName")
ActiveSheet.Cells(i, 40) = json("quoteResponse")("result")(1)("exchangeTimezoneShortName")
ActiveSheet.Cells(i, 41) = json("quoteResponse")("result")(1)("firstTradeDateMilliseconds")
ActiveSheet.Cells(i, 42) = json("quoteResponse")("result")(1)("fullExchangeName")
ActiveSheet.Cells(i, 43) = json("quoteResponse")("result")(1)("gmtOffSetMilliseconds")
ActiveSheet.Cells(i, 44) = json("quoteResponse")("result")(1)("language")
ActiveSheet.Cells(i, 45) = json("quoteResponse")("result")(1)("longName")
ActiveSheet.Cells(i, 46) = json("quoteResponse")("result")(1)("market")
ActiveSheet.Cells(i, 47) = json("quoteResponse")("result")(1)("marketState")
ActiveSheet.Cells(i, 48) = json("quoteResponse")("result")(1)("messageBoardId")
Next i
End Sub
