cancel
Showing results for 
Search instead for 
Did you mean: 
Cancel

Help Community

Announcements
Yahoo Help Community is also available in Spanish and Chinese.

Importing stock prices to EXCEL

'Hoo Recruit

Re: Importing stock prices to EXCEL

 

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
http://finance.yahoo.com/quotes/["QUOTE","Enter stock, fund or other symbols separated by commas."]
Selection=2
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False

 

I observed when I type an address into the browser manually such as http://finance.yahoo.com/quotes/["QUOTE","IBM"]  (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 Smiley Sad .  Haven't tested this on Windows Excel but wanted to repost here in case helpful to others.

 

2. 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.

b. In the registry navigate to

HKEY_CURRENT_USER\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.

That’s it. 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!  Thanks!

 

 

Established 'Hoo

Re: Importing stock prices to EXCEL

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.

'Hoo Recruit

Re: Importing stock prices to EXCEL

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! Smiley Happy )

 

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

 

 

Pro 'Hoo

Re: Importing stock prices to EXCEL

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

http://finance.yahoo.com/q?s=     followed by stock symbol list separated by comma's

Now using:

http://finance.yahoo.com/d/quotes.csv?s=

 

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.  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.

 

Good luck!

Tags (2)
Pro 'Hoo

Re: Importing stock prices to EXCEL

One more clue for those using csv to retrieve stock quotes and doing the refresh query within an excel macro.  I found that you need to have a delay between the refresh query and the text to column expansion.  I inserted this into the macro to enable the query to finish before the text to column starts:

Application.Wait Now + TimeSerial(0, 0, 2)

 

The 2 is a 2 second delay in the macro.  Seems to work.

Tags (2)
Established 'Hoo

Re: Importing stock prices to EXCEL

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.

Pro 'Hoo

Re: Importing stock prices to EXCEL

Hi qupola,

The nice aspect of having an Excel Macro trigger the refresh of stock data, is that with a single keystroke the macro can run.  And it will trigger the query refresh, fix any formatting issues, select the summary view of interest, and you have your results.   If one is interested in checking status a couple of times a day or even a few times a week, the ease of use can be worth the set-up effort.

My two cents,

Adrian

Established 'Hoo

Re: Importing stock prices to EXCEL

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.

'Hoo Recruit

Re: Importing stock prices to EXCEL

Thank you Adrian and qupola for your helpful postings. For now the MSN Money approach seems to be working for me, but at some point (next time MS fails me?) I may try Adrian's Excel macro approach. All best,

Steve

dca
'Hoo Recruit

Re: Importing stock prices to EXCEL

been using Yahoo for YEARS to import into Excel and then Yahoo goes and pulls this stunt !!!

they "fixed" what was not even broken !!!

 

here is the "fix" i am using - *Link Removed - See the community guidelines for more information.*

i downloaded the .xls directly from that link and then just copied tabs over from my "now-broken" spreadsheet...

 

thanks a lot, Yahoo - this is actually the THIRD time (over the years) that you BROKE my spreadsheet !!!...

i pull from Google or MSN the next time, this is RIDICULOUS !!!...