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:


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:

Getting Started in Ethical Investing

Interest in ethical and sustainable issues has exploded over recent years and today we can see these issues start to influence those in the financial investment community. Investors are increasingly taking into account non-financial factors in their portfolio selection, including the impact a company has on the environment and society.

This shift was compounded yesterday as US President Biden issued an executive order requiring development of a comprehensive government-wide climate risk strategy. This strategy will influence how and where the US Government directs it’s annual $680 billion budget for goods and services. It also sets in motion plans to revise the Trump-era rules that required pension fund managers to put their members financial interests ahead of climate change and other environmental issues when allocating funds for investments.

The winners in this process will be those companies and suppliers that score highly on environmental and sustainable measures.

ESG (Environment, Social and Governance) Ratings

But how do investors identify and select investments based on non-financial measures such as environmental impact?

This is where ESG (Environment, Social and Governance) Ratings come in. These ratings provide a numerical value which scores how ethical or sustainable a company is. One of the market leaders in providing ESG Risk Ratings is Sustainalytics, a Morningstar company.

They provide Risk Ratings, for many companies, which are scored on a scale from 0 to 40+, with low numbers representing a low risk and high numbers indicating a high risk/impact:

Sustainalytics Risk Ratings

Building the Portfolio

We’ll start with the S&P500 and just look at the top 30 largest companies. We want to rank these companies based on their ESG ratings and for this we can use the ESG Excel formulas provided by the Excel Price Feed Add-in.

Here is the top 5 stocks in the S&P500, we have added formulas for Market Capitalization and ESG Total Score:

ESG portfolio

This is a good start but we can improve it considerably by using some of the Excel data visualization tools. For example, we can add a “Heat Map” effect to the ESG Risk Score, this will enable us to quickly see which stocks have a high or low score. We can also add a “Data Bar” to the Market Cap column so we can easily compare the size of the company.

We can also use the Excel Data-Sort function to sort our list by ESG Risk Score, this gives us the following for the top 10 stocks:

ESG Stock Portfolio Top 10

And for the bottom 10:

ESG Stock Portfolio Bottom 10

It was not a surprise that Exxon at the bottom, but I was surprised to see so many pharmaceutical companies represented in the bottom 10.

We can refine our data further by not only comparing the stocks against each other but against their peers. For example, a company might have a relatively good score compared to the general market but not perform well against its peers, i.e. companies operating in the same industry/sector.

I’ve added a further two columns, one showing the risk score for its peers, and another showing the difference compared to its peers. I have also used a “Data Bar” to highlight the difference: a red bar (negative) indicates a score lower than its peers (a good thing) whereas a blue bar indicates a higher score.

Here is the a full ESG analysis of the top 30 stocks in the S&P500:

ESG Portfolio Analysis Top 30 stocks in the S&P500

Hopefully this gives you some ideas on how you can go about building your own ethical portfolio.

Visit the Excel Price Feed website to download the Add-in to provide all the Excel formulas used to build the spreadsheet shown above.

Coinbase IPO

Coinbase today filed its S1 document with the SEC signaling its intention to launch an IPO (Initial Public Offering).

Coinbase is a digital/crypto currency exchange, founded in 2012 and recently transitioned to become a fully remote company with no headquarters building. The company brokers the exchange of cryptocurrencies such as Bitcoin and Ethereum for fiat currencies (such as US dollars or Euros).

Actually it is not really an IPO, it’s a direct listing which means employees and early investors can sell stock but the company is not “Offering” any new stock to the “Public”. It will however be listed on the NASDAQ exchange and its stock will be publicly tradeable.

The S1 document provides an insight into the previously unseen operations and finances of the cryptocurrency exchange.

Looking through the S1 reveals that since inception it has generated $3.4b in revenue, largely from transaction fees. Their customers have during this time traded $456b of crypto-assets on the platform which suggests an average transaction fee of 0.75%.

This is a huge margin compared to other financial exchanges which generate an estimated 0.01% of volume in exchange fees. This margin, together with its vast user base (42 million verified users), scope for future growth and general positive sentiment towards crypto assets, has resulted in an estimated initial market capitalization of around $77b.

How does this financial performance stack up against other companies in the space?

Coinbase is the first crypto exchange to announce an IPO so we can only really compare it against traditional financial exchanges. Here I have used some the Excel Price Feed formulas to build a table in Excel to compare Coinbase against 5 of the biggest publicly quoted exchanges:

Comparison of stock exchange financials (including Coinbase).

Looking at this table we can see that Coinbase, based on its projected valuation, will become the most valuable publicly traded financial exchange in the world. It still has a long way to go to reach the huge revenues of ICE (which owns the NYSE), Nasdaq and CME but it has already ahead of Euronext which owns a bunch of European bourses:

Stock exchange revenues

Perhaps more important that its financial data and future prospects is this quote from the S1:

We have applied to list our Class A common stock on the Nasdaq Global Select Market under the symbol “COIN.”

This in itself will probably add a few billion in market cap

All data and the chart shown is provided by Excel Price Feed Add-in market data formulas (Yahoo Finance data).

Tesla’s Bitcoin Gamble


The cryptocurrency and stock worlds collided yesterday with the news that Tesla has bought $1.5bn worth of Bitcoin and will start accepting the cryptocurrency as payments for its vehicles.

Here is the key paragraph from the 10-K statement:

In January 2021, we updated our investment policy to provide us with more flexibility to further diversify and maximize returns on our cash that is not required to maintain adequate operating liquidity. As part of the policy, which was duly approved by the Audit Committee of our Board of Directors, we may invest a portion of such cash in certain alternative reserve assets including digital assets, gold bullion, gold exchange-traded funds and other assets as specified in the future. Thereafter, we invested an aggregate $1.50 billion in bitcoin under this policy and may acquire and hold digital assets from time to time or long-term. Moreover, we expect to begin accepting bitcoin as a form of payment for our products in the near future, subject to applicable laws and initially on a limited basis, which we may or may not liquidate upon receipt.

In the corporate finance world this is a very unusual move by Tesla.

Companies usually park any spare cash in low yielding but very safe investments such as government bonds or just keep it on hand as cash in the bank. Tesla, however, with Elon Musk at the helm, is anything but a “usual” company. As I wrote about earlier on this blog, even though Tesla is the most valuable car company in the world its revenues are nowhere near reflecting its huge valuation.

Tesla, though, can certainly afford this investment; its currently sitting on $19bn+ in cash:

But why has Tesla invested in Bitcoin?

Tesla didn’t give a specific reason, other than it provides “more flexibility to further diversify and maximize returns on our cash”. That is all well and good while Bitcoin is increasing in value (currently at $45,000 and up over 50% this year) but how will investors react if Bitcoin heads back down to $10,000 or lower, a level that it was trading at only 5 months ago?

Bitcoin is notoriously volatile; it can move +/-10% in a matter of minutes for no apparent reason. Now we have one of the largest S&P500 companies holding a sizeable Bitcoin position on its balance sheet. I do wonder what Tesla CFO Zachary Kirkhorn makes of all this? A recent Bloomberg report described him as “introducing a more conservative approach to forecasting and provided greater discipline in cost-cutting that has helped Tesla act more like the S&P 500 company it has become.” A $1.5bn Bitcoin investment is quite the opposite of acting like a S&P company!

And I do hope that the Tesla Finance Department has safely stored the details of its $1.5b investment:

Of the existing 18.5 million Bitcoin, around 20 percent — currently worth around $140 billion — appear to be in lost or otherwise stranded wallets, according to the cryptocurrency data firm Chainalysis.

Despite all this investors reacted positively to the news, the stock was up 1.3% whilst Bitcoin surged over 15%. So already the investment is paying off, although no information was provided as to when and at what price the Bitcoin transactions were executed at:

Bitcoin (BTC) price chart

How does this news square with Teslas environmental mission statement?

Well the short answer is that it doesn’t and in fact goes completely against it. Bitcoin consumes annually the electricity output of a small country. Surely if Tesla were serious about tackling environmental issues they would not be investing in Bitcoin? As the price of Bitcoin increases it becomes more economically viable to dedicate more computing resource to Bitcoin mining activities. It is likely that this news from Tesla together with the sharp spike in the Bitcoin price has enabled another warehouse full of machines to start working 24/7 to mine more bitcoin…

All data and the chart shown is provided by Excel Price Feed Add-in market data formulas (Yahoo Finance data).

GameStop (Wall St vs Main Street)

GameStop is a US video game and consumer electronics retailer which operates 5000+ stores across the world. Over the past 5 years the company has steady declined as digital downloads of games overtook the purchasing of physical video games as well as the general move to more internet retailing.

As a result, the company suffered declining revenues and profits, exacerbated more recently by the closure of stores due to the Covid-19 pandemic.

As the fortunes of the company declined, some hedge funds took notice and spotted what was to them easy money: bet on further declines in the company and therefore the stock price. Throughout late 2020 they began building short positions in GameStop stock (GME).

Enter Player 1: The Hedge Funds

How did hedge funds bet on the decline of GME? The hedge funds borrowed stock (from a bank or broker) then immediately sold this stock. At some point in the future they will be required to return the stock to the lender at which point they simply buy it back, at the now hopefully lower price, and pocket the profit.

For example:

  • They borrow the stock at $50.
  • They then sell it immediately for $50.
  • Sometime later, when they are due to return the stock, they buy it in the market for $20 and make $30 profit.

This is called “shorting” and the hedge funds were so sure that GameStop was a lost cause that it quickly became the most shorted stock in the market, in fact so much so that more stock was shorted than the entire GameStop share capital.

Enter Player 2: The Redditors

The other player in this game are The Redditors, an internet community that hangs out on Reddit, an online news and discussion forum. One corner of this site, the Wall Street Bets (WSB) forum, has become a noisy outpost for individual investors disgruntled with the way the closed world of Wall Street makes its vast profits at the expense of people like them. It is filled with members posting trading memes and screenshots of their personal trading accounts, often showing large profits or losses – both are seen as a rite of passage.

Towards the end of August 2020 WSB started to take a keen interest in GME. There was talk of a turnaround in the company and this was exacerbated with the news of ex Chewy Inc (an online pet food retailer) CEO Ryan Cohen buying up about 13% of GME. This news, coupled with the WSB dislike for Wall Street (who were now becoming more vocal in expressing how GameStop was a dying business in a dying business sector) resulted in GME becoming a “cult” stock within WSB.

As Wall Street built up huge short positions, individual investors, using mobile apps such as Robinhood, started buying GME stock. In addition, other WSB members, after seeing the huge option profits made during the March 2020 crash, started buying GME call options. Buying options enabled even those with little cash to spare to join the game and also make much larger gains than simply owning the stock.

The game had started: Wall Street vs Main Street.

Let Play Begin: The Short Squeeze and Gamma Trap

During January, the frenzy on WSB increased: members started frantically buying GME stock and GME call options whilst posting more memes and encouragement.

The market makers selling the call options needed to hedge their exposure so bought some GME stock, this is called “delta hedging”. As the stock price increased, the position needed to be re-hedged by buying yet more stock. This is called the “Gamma Trap”.

The hedge funds, with their large short positions, have to pay a fee as well as provide collateral to the lender, in order to borrow stock. As the stock price increased the fees increased and their collateral requirements increased. Some lenders may even ask for their stock back, in which case they would need to buy it from the market, thereby pushing the price up further. This is called the “Short Squeeze”.

As you may have noticed there are multiple feedback loops: As the stock price went up this forced the hedge funds and options market makers to buy more stock, which caused the price to go up even more, which caused them to buy more stock and so on.

The WSB crowd, at the same time, bought more stock and call options. They encouraged each other to hold and not sell up, no matter what happened to the price. Reports of others making huge profits, in the form of screenshots of their stock accounts showing large profits, flooded the forum. This lead to more of them wanting a piece of the action and more buying of stock and calls.

The result of this can be seen in the chart above: the stock price (and volume) erupted during January, rocketing from $17 at the start of the year to over $350 just 3 weeks later.

Game Over?

So, what happens now? As I write, GME was up 134% yesterday and is already up 6.5% in the pre-market this morning:

WSB is claiming victory over the hedge funds and Wall Street.

Melvin Capital Management is the first victim; a $12b hedge fund that is down 30% this year and was rescued this week with a $2.75b capital injection from other Wall Street players.

The SEC said it was “aware of and actively monitoring the ongoing market volatility in the options and equities markets” and the new Biden administration is “monitoring the situation”.

Will this lead to fundamental market reforms or are we entering a new age of financial power shifting from Wall Street to Main Street?

(PS: whilst writing this article GME has already rose another 20% in the pre-market…).

All data and the chart shown is provided by Excel Price Feed Add-in market data formulas (Yahoo Finance data).