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.
Huge progress update toward historical data! We have released our preview of the StockHistory function! https://support.microsoft.com/en-us/office/stockhistory-function-1ac8b5b3-5f62-4d94-8ab8-7504ec7239a8
However, this is only available to our Beta Channel at the moment, so when it is rolled out to General Availability, I will be sure to update the status to completed.
If you are able to use the feature in preview and have feedback, please send-a-frown for us to review.
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.
Henry Wirth commented
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.