What is an ADR?
An ADR is a stock certificate, denominated in US dollars, issued by a US based bank or broker. It represents a specific number of shares, usually one, in a foreign company’s stock i.e. a company that is not listed on a US stock exchange.
ADRs are listed and are tradeable on a US stock exchange such as the NYSE and trade in an equivalent way to any US based stock.
Why trade an ADR?
An ADR enables US investors to gain exposure to foreign stocks without the complication of trading on foreign exchanges. Also, because ADRs are priced in US dollars and their dividends are paid in US dollars, investors do not need to deal with foreign exchange conversions.
In addition, foreign companies find them beneficial as they can attract US based investors without needing to list on a US based stock exchange.
How to retrieve ADR prices in Excel
The Excel Price Feed Add-in provides access to ADR prices via the Yahoo Finance connector.
We can find the ADR ticker/symbol using the built-in search function.
For example, below we are searching for the SAP ADR. SAP is a German software company listed on the German stock exchange but not listed in the US:
Here we can see both the German listed stock (SAP.DE on the GER Exchange) as well as the ADR (SAP) listed on the NYSE (NYQ Exchange).
ADRs are treated like regular US stocks, so for example to request the current price of the SAP ADR we use the following Excel formula:
=EPF.Yahoo.Price("SAP")
Which gives the following result in Excel:
I hope this gives a good overviews of ADRs and how to easily retrieve ADR data in Excel.
The Excel Price Feed Blog 