VBA Excel Macro Pull Stock Quote

FYI - Yahoo has decomissioned their finance API. I'm currently looking for a work around as the current macro relies on this API which has been decomissioned.

Statement from Yahoo:
"As you are aware, on November 2 Yahoo Finance disabled the URL that allowed direct querying for quote data. The feature was originally designed for single use at a small scale, allowing users to issue quote lookup queries and receive structured data in return. However, we and our data providing partners have found that commercial entities are accessing this data at high rates and scale, often in order to resell the data or to provide revenue generating services with the data access as a core value proposition.

We have made multiple unsuccessful attempts to prevent misuse of this service in order to avoid shutting it down completely. However we were unable to stop the misuse. We have longstanding agreements and relationships with our data providers, and this commercial use of the data violated those agreements. By continuing to provide the download service we were putting our data relationships at risk and in turn threatening our ability to provide the most significant part of Yahoo Finance to our more than 75 million users each month.

We are sorry for any inconvenience the discontinuance of this service has caused. However, we continue to offer unparalleled free financial data access from both our Apps and our Sites. We provide tools to allow you to create multiple watchlists of ticker symbols and track them in great detail. We are adding to our data offerings regularly. Within the last month we incorporated a new source of data on companies' Environment, Social, and Governance performance and we have expanded global coverage to include the Johannesburg, Saudi Arabia and Qatar exchanges. There will be more goodness to come."

Pull stock quote excel tool

This Excel Tool can be used to pull any stock quote statistic.  Select which fields you want to output and click the button.  VBA macro will query the stock quote statistics and pull into the Excel tool.

VBA Excel Macro Pull Stock Quote (296 downloads)

The below Yahoo finance codes are used by the macro to designate which fields should be pulled from the database.

a Ask a2 Average Daily Volume a5 Ask Size
b Bid b2 Ask (Real-time) b3 Bid (Real-time)
b4 Book Value b6 Bid Size c Change & Percent Change
c1 Change c3 Commission c6 Change (Real-time)
c8 After Hours Change (Real-time) d Dividend/Share d1 Last Trade Date
d2 Trade Date e Earnings/Share e1 Error Indication (returned for symbol changed / invalid)
e7 EPS Estimate Current Year e8 EPS Estimate Next Year e9 EPS Estimate Next Quarter
f6 Float Shares g Day’s Low h Day’s High
j 52-week Low k 52-week High g1 Holdings Gain Percent
g3 Annualized Gain g4 Holdings Gain g5 Holdings Gain Percent (Real-time)
g6 Holdings Gain (Real-time) i More Info i5 Order Book (Real-time)
j1 Market Capitalization j3 Market Cap (Real-time) j4 EBITDA
j5 Change From 52-week Low j6 Percent Change From 52-week Low k1 Last Trade (Real-time) With Time
k2 Change Percent (Real-time) k3 Last Trade Size k4 Change From 52-week High
k5 Percebt Change From 52-week High l Last Trade (With Time) l1 Last Trade (Price Only)
l2 High Limit l3 Low Limit m Day’s Range
m2 Day’s Range (Real-time) m3 50-day Moving Average m4 200-day Moving Average
m5 Change From 200-day Moving Average m6 Percent Change From 200-day Moving Average m7 Change From 50-day Moving Average
m8 Percent Change From 50-day Moving Average n Name n4 Notes
o Open p Previous Close p1 Price Paid
p2 Change in Percent p5 Price/Sales p6 Price/Book
q Ex-Dividend Date r P/E Ratio r1 Dividend Pay Date
r2 P/E Ratio (Real-time) r5 PEG Ratio r6 Price/EPS Estimate Current Year
r7 Price/EPS Estimate Next Year s Symbol s1 Shares Owned
s7 Short Ratio t1 Last Trade Time t6 Trade Links
t7 Ticker Trend t8 1 yr Target Price v Volume
v1 Holdings Value v7 Holdings Value (Real-time) w 52-week Range
w1 Day’s Value Change w4 Day’s Value Change (Real-time) x Stock Exchange
y Dividend Yield