American Depository Receipts (ADRs)

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:

Searching for the SAP ADR in Excel Price Feed

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:

SAP ADR stock price in Excel

I hope this gives a good overviews of ADRs and how to easily retrieve ADR data in Excel.

Market Performance 2022 YTD using Excel

Just a quick blog post today, looking at market performance for a bunch of stocks so far this year (otherwise known as year to date or YTD).

As a reference point, today (24 March 2022) the S&P500 is at -6.2% and the Nasdaq is at -11% for YTD.

I am using the Excel Price Feed Add-in to download latest price data and start of year prices for a small set of diverse US stocks. I use the live price formula (EPF.Yahoo.Price) in column C together with the historical price formula (EPF.Yahoo.Historic.Close) in column D to retrieve the stock price on the first trading day of the year which was 3rd January.

This produces the following data table (you can see the formula for cell D2 in the formula bar):

2022 YTD Stock Performance in Excel using the Excel Price Feed Add-in

Now that we have the current stock price and at the start of the year we can calculate the % change using a simple Excel formula which works out the difference (C2-D2) as a proportion of D2:

Calculate stock price YTD change in Excel

Now we can apply this formula to the rest of the table and create a simple bar chart to visualize the performance:

YTD stock performance bar chart in Excel

As we can see the tech sector is under-performing, particularly Facebook and Netflix whilst the oil giants Exxon and Chevron are out-performing everything else.

I hope this gives a good introduction to stock analysis using Excel, to find out more about Excel Price Feed head over to the website and try it free for 10 days: https://www.excelpricefeed.com/

Excel: Calculate trading days between two dates

Yesterday a customer contacted us asking if our financial markets data Add-in could calculate the number of trading days between two dates. Unfortunately we don’t have this functionality as implementing it is not as easy as you may think.

You need to take into account weekends, which for most financial markets are Saturday and Sunday. Exceptions to this include several markets in the Middle East such as Saudi Arabia where the working week is Sunday to Thursday and the “weekend” is Friday and Saturday.

You also need to account for market holidays which are usually, although not always, also national holidays.

Fortunately Excel provides the NETWORKDAYS function, which:

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays.

This function can be used, together with a holiday lookup, to provide the functionality we need.

NETWORKDAYS function in Excel

In the example above we are using a holiday list lookup in column D together with the NETWORKDAYS function in cell B4 to compute the trading days between 21 July 2020 and 1 Dec 2020 for the US market.

The NETWORKDAYS function assumes that weekends are Saturday and Sunday. If you wish to specify a different weekend then you can use the NETWORKDAYS.INTL function.