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

New 'Hoo

Re: Importing stock prices to EXCEL - Fix not only for Mac Users

Yahoo has changed something in the address. You have to write "quote" not "quotes" to make it work. Here is the script I am using. 

 

WEB
1
http://finance.yahoo.com/quote/["QUOTE","Enter stock, fund or other symbols separated by commas."]
Selection=2
Formatting=False
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False

 

The one thing that does not work on my Mac is the comma separation. 

This script can not only be used for stock prices but for currencies as well. If you wrote as a ticker symbol EURUSD=X the current exchangerate for Euros into USD will be delivered. To pick up other abbrevations look them up in the search bar of https://finance.yahoo.com/lookup Even less known currencies can be found there (e.g. USDGEL=X tells you how many Georgian Lari you will get for one USD).

 

It is not necessary to enter the symbols again and again. When you call the script it offers a button "parameters". Click on the last option and use a cell above as reference for your ticker sympol. The stock symbol can be changed to whatever you wish. That way you can refresh the query with click on the right mouse inside the  existing data from your last query. 

 

The one thing that does not work: comma separation (but this might be caused by my German number system). So you can only use one symbol at a time. 

 

------

 

Other feature: wherever there is a list of indices you can import these with a very simple script as well. Take an empty sheet and write (e.g.) 

https://finance.yahoo.com/world-indices/

in the first line, then - important - add several empty lines. 

New 'Hoo

Re: Importing stock prices to EXCEL

Thank you. It was very helpful.

Is there a way to use Yahoo portfolio instead of a long list of company tickers?

Ofer


qupola wrote:

I'd like to point out that the columns that appear and their order are controlled by the list of parameters following the f= in the text query, between the ampersands.  Google on "canbike csv" for a good description of what keycodes you can use after the f= to pull in different columns of data.  That CANbike webpage features a list of keycodes for grabbing other kinds of financial data via the Yahoo comma delimeted file API as well. I've not found them as well documented elsewhere.

 

Also note that it's actually possible to pare things down to just retrieve the ticker symbol and last price with a

&f=sl1&e=.csv

 

after your list of stock tickers at the end of your query.  You can change the order of the columns retrieved, using 

&f=l1s&e=.csv

instead.

 

My link was just using &f=sl1d1t1c1ohgv&e=.csv for a default list of financial info, but you can add, drop, or reorder the columns retrieved as needed (as long as you have an associated keycode for that data).

 

I also found that one can use https instead of http, like so, using stock tickers and the f= parameters from my earlier post as an example:

 

https://download.finance.yahoo.com/d/quotes.csv?s=AAPL,GOOG,YHOO&f=sl1d1t1c1ohgv&e=.csv

 

Hope this helps!


 

GIB
New 'Hoo

Re: Importing stock prices to EXCEL

I have been using this for years. Woke up yesterday to:

"Sorry, Unable to process request at this time -- error 999."

 

Anyone else got this?

Anyone else uses portfolio downloads:

*Link Removed - See the community guidelines for more information.*

GIB
New 'Hoo

Re: Importing stock prices to EXCEL

This link:

query1 . finance . yahoo . com....

'Hoo Recruit

Re: Importing stock prices to EXCEL

Yes have the same issue from today!
Administratin' 'Hoo

Re: Importing stock prices to EXCEL

Update from the Finance team:  It has come to our attention that this service is being used in violation of the Yahoo Terms of Service.  As such, the service is being discontinued.  For all future markets and equities data research, please refer to finance.yahoo.com.

If you see any helpful posts, give a Kudo! If you see a response that answers your question, please mark Accept as Solution!