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

dca
'Hoo Recruit

Re: Importing stock prices to EXCEL

did not know that the site was going to break the link i posted...

 

here it is again, hopefully in an unbreakable format -

h--p://*Link Removed - See the community guidelines for more information.*

dca
'Hoo Recruit

Re: Importing stock prices to EXCEL

are you FREAKIN' KIDDING ME ???!!!

 

here, one FINAL attempt at trying to post a **Username Protected**@mn link -

 

h--p  (three-W's).marketindex.com.au/yahoo-finance-api

New 'Hoo

Re: Importing stock prices to EXCEL

No doubt this comes from yahoo side. It was gradual: first I was not able to import portfolios, now you can not import tables through web queries. This makes yahoofinance absolutely useless.

Comma delimited files (csv query) is the last thing left available but it provides only small portion of what was accessible before through web queries like financial statements, options, more statistics & many others. 

I find explanation to this that those who download data to excel do not go to the site and do not see ads and therefore this service do not bring revenue but it has cost.

I tried to get some data from Reuters (like stats) and MarketWatch (like statements, options) and it works, I liked getting data from Yahoo more though.

Hopefully they will bring it back, but I doubt. Then I really do not see the purpose of yahoo at all.

'Hoo Recruit

Re: Importing stock prices to EXCEL

 

The CSV method worked for me without macros as well, the only problem is that you have to click ok on the login dialog prompt (and you cant tweak that in the definition)

Was able to put the CSV download link for a portfolio ok.

The only problem is that the CSV portfolio download does some default columns and not my custom or other column view...

dca
'Hoo Recruit

Re: Importing stock prices to EXCEL

the marketindex dot com macro method does NOT ask for a ya-spoo login...

also, the marketindex dot com macro method can apply your preferred column formatting...

 

i will NOT be using ya-spoo for stock data if they ask for login for the macro method i am now using...

Established 'Hoo

Re: Importing stock prices to EXCEL

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!

Tags (1)
'Hoo Recruit

Re: Importing stock prices to EXCEL

Interesting re using parameters for the columns.

I wonder if I can apply it somehow to the portfolio being pulled. As I have too many symbols in portfolio I do not think its doable via the link that pulls by quote symbol (will be too long).

 

I am pulling the whole portfolio via taking the same link as in portfolio EXPORT button (where X is the userId and Y is the Portfolio Id):

query1.finance.yahoo.com/v6/finance/portfolio/download?userId=[X]&pfId=[Y]

 

but that link always takes some default for columns (not the custom columns I have:

 

Populated columns:

Symbol

Current Price

Date

Time

Change

Open

High

Low

Volume      

         

Unpopulated columns:

Trade Date

Purchase Price

Quantity

Commission

High Limit

Low Limit

Comment

 

whereas I use:

Company name

52w range

Ex-div date

Div payment

 

 

 

Established 'Hoo

Re: Importing stock prices to EXCEL

I have too many tickers to track for a single text query myself.  So I broke things up into smaller groupings and making a text query for each of those on different worksheet tabs.  Previously I had been doing the same thing with my web query iqy method, because of the limitations in the length of the query and in the number of tickers that could be pulled in at once.

 

I've not looked much into pulling it all in from a portfolio stored at yahoo, but I would expect there may be some means of controlling what data gets returned from there as well. 

 

<edit> Just adding that the worksheets where I pull in the yahoo data provide the source for another worksheet where I link to and compile all the incoming data in a custom format that works best for me. 

'Hoo Recruit

Re: Importing stock prices to EXCEL

The canbike description of the tags being pulled is super!

I think will just go ahead and split my portfolio up and pull separately.

 

When you are pulling csv with several queries do you now need to provide credentials (click ok) for each one? Is this addressable by a macro?

 

thanks!

Established 'Hoo

Re: Importing stock prices to EXCEL

>When you are pulling csv with several queries do you now need to provide credentials (click ok) for each one?

 

Seems to only when you establish a new text query.  After you've told a particular query to remember your user login credentials, it usually only asks once per workbook session, drawing from the same credentials for your other text queries in that workbook.   I've also found Excel doesn't usually ask again for your login credentials for the next manual or auto/refresh while the file is open.

 

>Is this addressable by a macro?

Not that I know of, but maybe.  It's a feature I would expect would have had to have been built into the text query URL itself and/or the Yahoo API that watches for such an event.