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
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”)
I am currently adding historical data as well as other functionality. The Add-in works with all versions of Excel.
Niels Lauge Rasmussen commented
I miss the feature for Windows Excel 2019 What is the prospects ?
Kenny Peh commented
Hi, adding the country code does not work for MY - Malaysia and SG - Singapore
Allow Yahoo Finance to impost spread sheet to Excelll spread sheet. PLEASE
Richard Schwartz commented
I use Yahoo finance. It is very convenient to simply highlight the daily list, copy, and paste into a spreadsheet. Since the change from yesterday's prices is also given, you can subtract that to get yesterdays data, in case you skipped a day.
The big risk is that by advertising this, I may cause Yahoo to reformat their data into something that a spreadsheet cannot handle. But Microsoft excel is a very powerful package that can do most of what you will ever want to do with data. You don't have to know everything about excel in order to get good use out of it; just learn the features and capabilities you need.
One more reason to abandon Excel all together and go with one of the free or lower-priced softwares out there. New versions reducing functionality is not looking out for your user base.
When does the feature for Windows Excel 2019 come?
Please add the Stock data feature to Excel 2019 for Windows as soon as possible!
Please bring back this feature
Once a spreadsheet is setup with data for specific stocks, how do you refresh the data? Can you get it to automatically refresh every 1 minute, 5 minutes, 10 minutes, or ????
Tsonan lee commented
The data put on Excel when can use other language?
I’m an Office Insider and I have access to this feature on my Mac. Thank you. Now I don’t have to run Windows 10 in. Virtual environment to get this capability.
When will this be available on Excel365 for the MAC please?
At the most basic level, I believe an add-in function should simply be something like:
Enhancements could add an optional 'High/Low/Open/Close' specification like:
If "Date" is not a trading day, then the function would return the previous day's Close.
I used to have a similar function that worked until it was killed in 2017.
It would be great if any replacement was compatible with Excel 2010.
I realize that many of you want much more data then I use. However, I feel that I ask for the time and Microsoft tells me how to make a watch. All I want is a function. Enter the symbol, date(s), and get the closing price. Simple. Why is this so difficult. I don't need all the exess data.
It would be fantastic if we could add historic prices at a given set of dates.. so we can track our portfolio value easily over time.
Richard Schwartz commented
Until today, it was easy to get Yahoo stock prices. Just highlight and copy the displayed prices in Edge, and past into cell A1 of a blank Excel worksheet. Then copy highlight and copy the data in column B that you want, and special paste it (values and transposition) into the appropriate spot in your actual excel data worksheet.
However, Yahoo must have gotten wise to this, as there are additional blank rows inserted, seemingly at random, into the data. I am working on a way to extract the data that will skip or delete the empty cells. Then Yahoo will find another way to frustrate us.
Yes. Please reinstate this feature!
One more time. James and Team Excel, thank you for letting us have this tool.
Some feedback. All works well and data can be refreshed manually just fine. Preferred shares issues aside, I was wondering if the team could work on allowing users to define their own variables/fields.
It will impractical to add any more fields in the drop down. And there are so, so many variables that each individual user may chose from that perhaps the best course of action would be to create a standard macro that we could play with.
Example, what if I want 2017 data? or data that is weekly, monthly or quarterly? What if I want ratios such as EV/ebitda, debt/ebitda, short term debt, long term debt, acid ratios, short interest, fully diluted shares? Juts too many different ones all subject to too many variables (time periods).
Thank you again for having listened to our plight.
Yes, thank you for making this tool available.
Preferred shares are an issue: fnmas, mechel (mtl.pr), etc.