Link Excel to Yahoo Finance

[andreastasia95]

Hello everybody,
I'm trying to download financial data from yahoo finance: specifically I have all the tickers of the shares of the Italian market (circa 450 stocks) in an Excel file and I would like to extract some tables from the "key-statistics" section. I'm not very skilled with VBA and what I did was create a query that uses a variable input (the link, easily reconstructed), based on the youtube tutorial (

). This procedures works with the downloading from reuters.com, but the problem for using that site is that I should know the RICs (reuters id of a stock), with yahoo finance I have to know only the ticker and I know it.

The problem is that while writing the exact link, Excel cannot extract the tables, but doing single a single query for every ticker, it works. I think that the problem is related to the existance of a lot of tables in that yahoo finance page (for example look at NEXI (NEXI.MI) Misure di valutazione e statistiche finanziarie).

I'm going crazy, does anyone have any ideas?

Thank you!!

 

[sandy666]

You are wellcome
Have a nice day

 

[andreastasia95]

I think that i understood a problem... your file is perfect giving a ticker, but if I want data coming from a series of ticker it doesn't work. I think that the problem is that it download data for the first ticker but take as columns "Ricavi" (means reveneus) and its value.

I have a little idea but I am bad in VBA... is it possibile to automate the process? I mean... now I have to choose the ticker from a list, but if I want to choose all the tickers of that list? I think about an iteration as a for cycle, taking all the values from the list... but I really don't know what to do... xD

Thank you guys!

 

[sandy666]

sorry, I hate vba so I can't help
have a nice day

 

[andreastasia95]

Thank you so much, you helped me already a lot!

Good evening

 

[sandy666]

You are welcome

now I have to choose the ticker from a list, but if I want to choose all the tickers of that list?

maybe do the same as for URL and use List table (Table4 in the file) instead of DV ticker table?

 

[sandy666]

check attachment Italy , is that what you want ? (on sheet2)

 

[andreastasia95]

Yeah is that, but if you see, for the second ticker there is a shift of their value. I think that the problem is behind the fact that the table catch as name of the columns the first row of the data ("Ricavi (ttm)" and "630,47k" are the revenues of ABT.MI and it is the same for "10,58M" that are the revenues of NEXI.MI).

Maybe in the picture you'll see better that. "Ricavi (ttm)" should be the same object of "Ricavi per azione (ttm)", not the name of the column. So what I would are only two columns, the first with the names of the data ("Ricavi ttm)", "Ricavi per azione (ttm)", ...) and the second with the values.

I reach my goal, because with the matrix in picture I have all the data of "Borsa Italiana", but I have to manipulate it to be more readable.

What I would is this:

(values are random)

Thank you!!

 

[sandy666]

check now,
if ticker has no values it will not show in the result table
you can change headers as the last step. it's up to you

 

[andreastasia95]

The problem is that values switch at right when the ticker change, so the table should be a matrix length(LIST) X length(LIST), not a matrix length(LIST) X 2 [hope that this notation is clear and is row*col].

 

[sandy666]

so swap Attribute and Value or even remove Attribute if you want