Link Excel to Yahoo Finance

andreastasia95

New Member
Joined
Dec 18, 2019
Messages
18
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
Platform
  1. Windows
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!!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

andreastasia95

New Member
Joined
Dec 18, 2019
Messages
18
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
Platform
  1. Windows
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!
 

andreastasia95

New Member
Joined
Dec 18, 2019
Messages
18
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
Platform
  1. Windows
Thank you so much, you helped me already a lot!

Good evening :)
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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?
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
check attachment Italy , is that what you want ? (on sheet2)
 

andreastasia95

New Member
Joined
Dec 18, 2019
Messages
18
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
Platform
  1. Windows

ADVERTISEMENT

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.

1576775619611.png


What I would is this:
1576776013010.png


(values are random)

Thank you!!
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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

New Member
Joined
Dec 18, 2019
Messages
18
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
Platform
  1. Windows
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
so swap Attribute and Value or even remove Attribute if you want
 

Watch MrExcel Video

Forum statistics

Threads
1,129,563
Messages
5,637,076
Members
416,956
Latest member
mitzhaki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top