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 (403 downloads)

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

aAska2Average Daily Volumea5Ask Size
bBidb2Ask (Real-time)b3Bid (Real-time)
b4Book Valueb6Bid SizecChange & Percent Change
c1Changec3Commissionc6Change (Real-time)
c8After Hours Change (Real-time)dDividend/Shared1Last Trade Date
d2Trade DateeEarnings/Sharee1Error Indication (returned for symbol changed / invalid)
e7EPS Estimate Current Yeare8EPS Estimate Next Yeare9EPS Estimate Next Quarter
f6Float SharesgDay’s LowhDay’s High
j52-week Lowk52-week Highg1Holdings Gain Percent
g3Annualized Gaing4Holdings Gaing5Holdings Gain Percent (Real-time)
g6Holdings Gain (Real-time)iMore Infoi5Order Book (Real-time)
j1Market Capitalizationj3Market Cap (Real-time)j4EBITDA
j5Change From 52-week Lowj6Percent Change From 52-week Lowk1Last Trade (Real-time) With Time
k2Change Percent (Real-time)k3Last Trade Sizek4Change From 52-week High
k5Percebt Change From 52-week HighlLast Trade (With Time)l1Last Trade (Price Only)
l2High Limitl3Low LimitmDay’s Range
m2Day’s Range (Real-time)m350-day Moving Averagem4200-day Moving Average
m5Change From 200-day Moving Averagem6Percent Change From 200-day Moving Averagem7Change From 50-day Moving Average
m8Percent Change From 50-day Moving AveragenNamen4Notes
oOpenpPrevious Closep1Price Paid
p2Change in Percentp5Price/Salesp6Price/Book
qEx-Dividend DaterP/E Ratior1Dividend Pay Date
r2P/E Ratio (Real-time)r5PEG Ratior6Price/EPS Estimate Current Year
r7Price/EPS Estimate Next YearsSymbols1Shares Owned
s7Short Ratiot1Last Trade Timet6Trade Links
t7Ticker Trendt81 yr Target PricevVolume
v1Holdings Valuev7Holdings Value (Real-time)w52-week Range
w1Day’s Value Changew4Day’s Value Change (Real-time)xStock Exchange
yDividend Yield