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)
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.
Thanks for the tip to locate instruments on a different exchange i.e., include an ISO country code alongside the symbol, but it did not work very well for exchanges in the US. According to Excel Stocks, the following 22 company's tickers are all listed on foreign exchanges ACB, ALYA, APHA, BRY, CCEP, ET, GLG, KDP, MIN, NFC, NXGN, PBY, PCB, PTE, PVL, PYX, RY, SINT, TAK, USLV, VNE and WTER. However, they are also listed on either the New York Stock Exchange (NYSE), the Nasdaq or the AMEX, and those are the $US denominated quotes I'd like to get. When I add the ISO country code alongside the symbol only two(2) of the 22 company's tickers were found. These were USA MIN and USA RY. The others were not found by Excel Stocks, but they are readily available on Yahoo and Google. I'm a big fan of Excel stocks, and I know that its quotes are more reliable than either Google's or Yahoo's, but there are some annoying problems that I hope will soon be resolved.
Thank you! This works well.
Except the data selector will not retrieve information for symbols such as preferred shares. I got symbols from MSN, google finance and morningstar and data will not be found in the data selector. Is there a way
Thomas Degas commented
This is actually available for all version of Excel with the Power-user add-in
Mike Iem commented
Thanks for adding Stocks to Microsoft Excel! I have been playing with this all day with my portfolio. It would be great if you can add a few more data items like Dividend Yield, 5 year average dividend yield, payout ratio. Great job getting this going in Excel! thanks!
Jaris Hansen commented
@ed, thank you. You're right, we were on the semi-annual update channel. Had to switch to monthly update channel and in my case I also had to follow instructions in the below article, and we're up and running. Thank you.
Ed Hansberry commented
@jaris - if you are using 365 with a company, they might have you on the "Deferred" channel. Check with IT. If not, then an uninstall/reinstall should fix it. Unless specifically on deferred, you should be in 1810 at the latest, but most likely 1811.
Jaris Hansen commented
Stephen, thank you. We are also 365 subscribers but every time I check for updates it tells me I have the latest version... 1803... Hmm. Maybe I should uninstall and reinstall.
Evan Olsen commented
I am glad to see Excel now has added a stock market data source however it appears the feed is not dynamic. It does not automatically refresh as the quotes change. Does Microsoft plan on making the feed live in future updates?
Ed Hansberry commented
Anon, highly doubtful. It won't even be made available for Excel 2016 or Excel 2019. It is in Office 365, and will presumably make it in Excel 2022 or whatever the next perm license version is.