Pull Current Stock Prices and Historical Data Into Excel
Now that our ability to use Yahoo! Finance's API to pull in stock information via the WEBSERVICE() function and import historical information is officially dead (thanks Verizon!), the only viable option currently is to use Google sheets to pull in stock information.
It would be awesome if Excel could import data from MSN Money so us users can continue to use spreadsheets to monitor our portfolios.
Stock data types are now available to all Windows Office 365 subscribers with an English editing language installed (our data is only available in English at this time). You can find some instructions on how to use the feature here: https://support.office.com/en-us/article/get-a-stock-quote-e5af3212-e024-4d4c-bea0-623cf07fbc54. The feature will also be appearing in Excel for Mac and Excel Online in early 2019 – as usual, Office Insiders will see the feature before the general population. See http://aka.ms/officeinsider for more info.
One pro tip to locate instruments on a different exchange is to include an ISO country code alongside the symbol, for instance MX MSFT will tell Excel to grab Microsoft from the Mexican stock exchange instead of Nasdaq. We’ll be publishing more thorough documentation in the near future.
We also acknowledge that this is only half of the requested feature in this suggestion – the current stock prices piece. The ability to access historical quote data is a work in progress, and I will update this audience once the feature begins its rollout to Office Insiders.
Feel free to leave comments and requests below, or by sending feedback directly from Excel by clicking the Smiley face in the top right corner, or going to Help > Feedback in the ribbon.
James Spotanski (Excel)
Andrew Sinclair commented
Looking through the comments and I can see many people are looking for something to use with the non Office365 versions of Excel as well as retrieve historic data etc.
I’ve built an Excel Add-In (https://www.excelpricefeed.com) which retrieves financial prices/data via simple formulas. It also includes a search function so you can easily find stock codes. It currently connects to Yahoo Finance, I am working on adding other data providers.
For example to populate a cell with the latest Apple stock price: =EPF.Yahoo.Price(“AAPL”)
You can also grab prices on dates using a formula like this: =EPF.Yahoo.Historic.Close("TSLA", "1 May 2019") Which populates the cell with the historic close price of Tesla on 1 May 2019.
The Add-in works with all versions of Excel.
Floyd Rentz commented
Good start - can you tell me the proper syntax for the ticker of preferred stock. Most brokers use (for NEE series J preferred stock) NEE-J or NEEprJ or something similar but I can't find one that works in Stock Data Types
@nyc-hdl Sorry, it's just something I wrote for personal use. I used Excel's Power Query feature to pull a few stock prices from www.nasdaq.com. I then used Excel's VBA to add bells and whistles.. Power Query ("Data/Get Data") is where the beef is.You can get your data into a spreadsheet without using VBA.. I was able to get current price, last price and % change for 5 stocks. It wasn't hard at all. If you have questions about how to do anything the Internet has all kinds of sites with the answers.
@Anonymous: Is the solution you developed available to download?
@Ed Hansberry It will be interesting to see how the large corporate customers customers feel about this strategy. Microsoft has been known to change its policies when enough big customers complain.
I'm willing to bet that a lot of individual OFF2019 customers with a need for the Stock feature rightly or wrongly feel snakebit. They bought OFF2019 just like they bought the versions before it. They won't want to hear is that they have to buy Office again or buy some third party solution. That's the bad customer experience I was referring to.
Personally, I developed my own solution so I'm OK for now. But not every Office customer has the skills or time to do that.
Have a Happy Fourth!
It is consistent @anon. Office 2016, 2013, and 2010 don't have the Stock Data Types either. Office 2019 got all Office 365 features that were released between Office 2016 and March of 2018, like the new TEXTJOIN and CONCAT functions, Pivot Table preferences, etc. But it doesn't have Stock Data types (released after) and will not get Dynamic Array functions (Insider only right now in Office 365, so not even released yet). It also won't get Fuzzy Match in Power Query.
If you want feature updates, get Office 365. If you want a *PERPETUAL* license to a particular product, then you get Office 2019, and know that you'll get no new features, only security updates and bug fixes. MS is very clear on this.
@Ed Hansberry I hear you, but IMO when a product is not consistent across platforms it can lead to confusion at the user level, difficulties in deploying applications and bad customer experiences. This is especially true for such an important feature as stock price retrieval. Plus, in the absence of a supported feature there will be a lot of time and effort spent on home grown solutions of varying quality. I'd rather see a feature update,such as is done with Windows and other products all the time.
@anonymous, Office 2019 was essentially feature frozen around build 1803/1804 (March April) of 2018, then all the bugs were worked out. The stock price feature wasn't finished until months later, so didn't make the cut. That is the problem with perpetual license products. You get what it comes with, and nothing else in the future except patches and security updates. I'm sure the feature will be in Office 2022 (or whatever the next perpetual license version is) but that will be missing whatever new stuff Office 365 has just months before it's release.
Evan Olsen commented
Can live dynamic stock quotes be linked into an excel spreadsheet using office 365?
I'm guessing that this feature isn't in OFF2019 Desktop because Microsoft would prefer that you buy OFF365 so that they can get that revenue stream. I hope I'm wrong - that's not a nice way to treat a loyal customer base.
Why is this feature not in the Office 2019 desktop release?
Al Bush commented
allow excel to download yahoo finance data into spreadsheet
I got tired of waiting for something to be done for desktop Office users, so I glued together something that at least gets me the results for the 5 stocks I own. I used the Power Query feature of Excel 2019 from a little VBA program I wrote. My Query pulls the results (Latest Price, Last Close, Change) from the Nasdaq.com web site. Other sites might work as well, but this is the first one I found that had what I wanted.
@ghanshyam, this was fixed last year in Office 365 and I think Excel 2019. I doubt MS will be making any fixes for earlier versions of office.
Ghanshyam Bhat commented
excel 2010 web query needs to support microsoft edge instead of IE11 because Yahoo Finance has stopped supporting IE11. you can no longer use IE11 to get multiple stock prices from Yahoo Finance
Ghanshyam Bhat commented
Microsoft: Please fix this problem of excel web query not working with IE11 in excel 2010
Daniel Mercure commented
Article: Excel Now Lets You Get Real-Time Stock Data From Nasdaq.
Joy Knox commented
When will historical stock prices be available? thank you
I hope also adding Chinese language.
Hi -- is there a way to use the stocks feature under data to pull a stock's price on a specific date? I'd like to be able to pull a table of historical prices by date. Thanks