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

 

[andreastasia95]

I am so sorry, the youtube video is wrong, here is the right one:

Sorry guys!

 

[sandy666]

did you try Power Query (Get&Transform) ?

 

[andreastasia95]

did you try Power Query (Get&Transform) ?

View attachment 2293

Yes, I think... I followed all the step of the video tutorial posted.
-First I have to create a query selecting the table that i want
-Then I have to edit that query, giving a variable structure and the input of the query is the URL, so the query now is a connection only
-Then I have to create a query from table and with the powerquery editor i add a custom column that is equal to the function of the previous step giving as input a column of URLs (related to the key statistic)
- So what I should be a giant table with all the information for each ticker (with reuters was going on)

do you mean anything else with Power Query (Get&Transform)?

 

[sandy666]

could you post M-code?

 

[andreastasia95]

This is the code of the query

--
(URL) as table =>

let
Origine = Web.Page(Web.Contents(URL)),
Data9 = Origine{9}[Data],
#"Modificato tipo" = Table.TransformColumnTypes(Data9,{{"Ricavi (ttm)", type text}, {"7,17B", type text}})
in
#"Modificato tipo"
--

I have updated only the first row and what is inside Web.Contents

Then when i add the custom column in the power query editor I call the query (that is a function) givin URL as input, and URL is a column of a table in excel, as the image:

 

[sandy666]

#"Modificato tipo" = Table.TransformColumnTypes(Data9,{{"Ricavi (ttm)", type text}, {"7,17B", type text}})

so maybe check your Modificato tipo

IMHO remove whole line Modificato tipo from function like this:

(URL) as table =>
let
    Origine = Web.Page(Web.Contents(URL)),
    Data9 = Origine{9}[Data]
in
    Data9

and try again

 

[andreastasia95]

It doesn't work... I think that it try to extract data from Yahoo Finance, but i see only #NA... the problem is that doing web queries mannually for each ticker it works... the 4AIM.MI doesn't have value because is a little company, but ABT.MI is bigger and have value but the query doesn't give values...

 

[sandy666]

maybe see this: Italy
but some urls making error, I didn't check all because I am too lazy to retype it from the picture

 

[andreastasia95]

Thank you for your help, that is amazing!!!! It is a kind of Bloomberg provider but free!

Really, thank you very much for solving my problem!