Excel: Retrieve Stock Quotes with a Spreadsheet Formula Some financial models need to reference stock quotes at certain dates. Instead of using those as hard-coded input data, you can create an Excel formula that will retrieve stock prices for a given date. How to Download Share Price Data in a CSV. Finance provides the simplest way to import financial data into a spreadsheet. The data (including stock prices, indices and company fundamentals) can be automatically downloaded in a CSV by simply entering a URL into your browser’s address bar.
I was using the below saved data query in Microsoft Office Excel 2011 for Mac to download stock prices into a spreadsheet. This was indeed working fine until Friday April 28 when suddently it stopped working. WEB 1 stock, fund or other symbols separated by commas.'
![Download daily stock prices free Download daily stock prices free](/uploads/1/2/5/3/125388106/568614099.png)
Selection=2 Formatting=None PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False I observed when I type an address into the browser manually such as ' (note, using the http prefix as my saved query did) my browser is being redirecting it to https (http over ssl protocol). Next I searched around and found Excel is likely doing some no-cache check for websites that are being served over https (rather than http). Here is a supposed solution that may work if you're on Windows, however I have yet to figure out how to make this work on Microsoft Excel 2011 for Mac. Haven't tested this on Windows Excel but wanted to repost here in case helpful to others.
There is a setting which allows you to bypass the no-cache check for SSL cites on the client. To do this, do the following: a.
Go to START and in the RUN line type REGEDIT. In the registry navigate to HKEYCURRENTUSER Software Microsoft Windows CurrentVersion Internet Settings c. Right click Internet Settings and left click New DWORD Value (32-bit) and name the new value “BypassSSLNoCacheCheck” without the quotes.
Double click this value and give it a value of 1. You may need to restart Excel, but you don’t need to restart the machine. You should now be able to perform the web query without a problem. If anyone knows how to succesfully get my query file working on MS Excel for Mac 2011 I would be grateful! I had been using an iqy file just like yours, StevenThorpe. It had been giving me fits on and off for the last couple of weeks, usually resolved by a reboot and/or clearing out my browser cache, but on Friday it stopped working altogether. In my own hunt for answers yesterday I ran into the same info about the no cache check and making that dword change in the Windows registry.
I also found something about unchecking a feature in Internet Explorer's advanced functions. Also mentioned was the url switch to https from http. Your mileage may vary, but I did all those things alone and in combination and still no joy. It may really be that Excel simply can't access data nested inside a given URL via an iframe, as the Microsoft support forums moderator previously stated. So it seems to me we're bumping into a limitation to Excel web queries that makes them incompatible with Yahoo's latest site revamp.
Lots of people here in other threads are begging Yahoo to bring back their old site design, bit so far the pleas seem to have fallen on deaf ears. Might be smart to look for an alternative solution anyway though, with Yahoo's future looking rather iffy these days. Hi qupolo, all, I am considering dumping Microsoft Excel in favor of LibreOffice. I searched the web for 'Downloading Stock Prices into OpenOffice/LibreOffice Calc' and found a nice article with a link to a 'Sample OpenOffice Calc file to import Yahoo! Stock quotes and historical data into spreadsheet'.
Downloaded the sample yahoo-ticker1.ods LibreOffice file, tested it out and all appeard to work fine. Also snooped around the embedded macros its using and FWIW it appears to be using http to yahoo URLs (not https). LibreOffice would free me from the shackles of MS land, however who knows what other hassles this might lead to down the road.
In the meantime I've gone back to using even more Microsoft stuff by reverting back to their 'MSN MoneyCentral Stock Quotes' plugin that appears to be working OK for me today. That said, that data query was out of commission for several months a year or so back! I hope it doesn't go down again! Just FYI for those with interest, here are the contents of that MSN MoneyCentral Stock Quotes file (replace SOMEURL with 'http colon slash slash moneycentral dot msn dot com' - hope that makes sense! ) WEB 1 SOMEURL/investor/external/excel/quotes.asp?SYMBOL='QUOTE','Enter stock, fund or other MSN MoneyCentral Investor symbols separated by commas.' Selection=EntirePage Formatting=All PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False. I'm a user of Excel 2011 on Mac.
Just replaced my excel Yahoo finance query, which stopped working Friday, with csv-based query. Learned a few lessons along the way. Maybe these will help others. So I was using followed by stock symbol list separated by comma's Now using: The new csv (comma separated) does work fine, but it then introduces some other challenges which are probably trivial for excel and excel macro guru's. But I had to hunt around for solutions.
First, the csv query will return the data and place it all in one column - a row for each symbol. So you then need to use Data menu 'Text to Column' to separate the comma delimited values out into separate cells. That works fine, but if you run this within a macro, you will discover that when the macro runs, it will bring up the 'do you want to replace existing contents' dialog box.
![How To Download Daily Stock Prices To Excel For Mac How To Download Daily Stock Prices To Excel For Mac](http://www.michael-saunders.com/stocksapp/images/sampleportfolio2.png)
Which requires a response before the macro runs to completion. To suppress this, surround the Text to Column instructions in the macro. Prior to the Text to Column instruction insert: Application.DisplayAlerts = False And then after the Text to Column insert: Application.DisplayAlerts = True For me, the macro then ran to completion without the dialog box. Last comment. My old query included ^DJI to return Dow index. It appears that Yahoo no longer has the right to return that data.
It returns NA's instead of data. Macros weren't necessary for me, but I can see how that approach might be more appropriate in certain situations I simply selected a cell in a new worksheet and hit the 'from text' connection button under the Excel data tab/ribbon menu.
Then I pasted a URL like the one I posted earlier as the requested text file and hit the open button. After satisfying the yahoo login prompt that popped up next, I followed the prompts, telling Excel the text file (in this case the URL) was delimited by commas rather than the default tabs. Finally I adjusted the data range properties as needed (being sure to turn off the filename prompt in the process). That imports each stock in a row with columns for the f= parameters as specified in the URL. Only glitch with the approach is being queried for a yahoo login (already stored) when the data refreshes. I think that may be why Kim doesn't turn on autorefreshing in the data range properties. Certainly a valid approach, no doubt here about that.
Just adding that auto refresh timing and some formatting adjustment parameters are also available via the text connection approach without adding new macros. You can control what data gets retrieved (as well as its order, I think) in the first place from the query with a properly set f= parameter in the url (see that canbike site for info about that). And of course whether or not you've activated an auto refresh schedule, one can always manually trigger an update on the fly by refreshing the data connections off the data ribbon menu.