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).

Tesla: The most valuable car company in the world?

Tesla Model S car

Tesla is in the headlines, yet again, this time as it’s share price hits $500. The latest jump in price was triggered by the news that Tesla will join the S&P500 in December; it is now by far the most “valuable” car company in the world:

In 2020, Tesla has become the world’s most valuable car manufacturer and blown its sales forecasts out of the water.

Tesla soars as much as 13% after the automaker nabs a spot on the exclusive S&P 500 index | Markets Insider (businessinsider.com)

What exactly does “most valuable company” mean?

Market capitalzation is the most common measure, in the financial press, to gauge how valuable a company is. Market capitalization is simply:

Number of shares outstanding (x) the share price

That is, how much it would cost to buy all the outstanding shares of a company. On this measure, Tesla is certainly the most valuable:

Largest, by market capitalzation, car companies in the world.

Another measure is Enterprise Value:

Market Capitalization (+) Total Debt (-) Cash & Cash Equivalents

Enterprise Value is the cost of how much it would actually cost you to buy the company, as you would not only need to buy all outstanding shares but also take on all debts of the company. You could offset some (or all) of the debt by the amount of cash & cash equivalents (i.e. liquid assets) that the company owns. Enterprise Value is often used as the theoretical takeover price of a company.

In this case, Tesla is still the most valuable car company but the other car companies are much closer in value:

Largest, by enterprise value, car companies in the world.

I think we can safely say that Tesla is currently the most valuable car company in the world but maybe not by such a large margin as the financial press reports.

What about revenues, surely a company must have large revenues to justify a large valuation?

Global car companies annual revenues.

Tesla earns a fraction of the other car companies; investors are betting on Tesla growing these revenues substantially over the coming years…..

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

Recent Microsoft Excel Performance Improvements

The latest release of Excel is focused on performance improvements. The main areas that have been addressed are RealTimeData functions, faster opening of workbooks with many user defined functions and faster aggregation.

RealTimeData functions (RTD) are commonly used to update a spreadsheet from a real-time data source. For example, if you have a stock portfolio you can add RTD functions to your sheet to provide real-time prices: as the stock moves, the price in the Excel cell is updated automatically:

Excel Real Time Stock Prices

Microsoft has removed bottlenecks in the underlying memory and data structures and also made it “thread safe”. The result is a significant performance improvement on sheets with lots of RTD functions.

I have noticed with one of my testing spreadsheets, with lots of RTD functions, that Excel now consumes less CPU and is generally more responsive.

User Defined Functions (UDFs) are custom functions that can be created by users or provided by an Add-in to add additional functionality to Excel. These functions operate the same as regular functions/formulas.

Opening workbooks with many UDFs was previously very slow as Excel looked up each UDF on the sheet. Now, Excel includes a UDF cache to make this process much faster.

Aggregation functions like SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS are some of the most commonly used Excel functions. With the latest release of Excel the performance of these functions is drastically improved.

Don’t forget, to take advantage of these recent improvements you must be subscribed to the Microsoft 365 monthly or semi-annual channel.

Partnership Announcement: EODHistoricalData.com

We are pleased to announce today our partnership with data provider EODHistoricalData.com which provides comprehensive historical and fundamental data from 60+ exchanges around the world.

This partnership significantly increases the data coverage of Excel Price Feed especially fundamental data as we can now offer up to 20 years of company financial history for many stocks.

For example, here is an extract of the Financial Analysis tab for Microsoft stock from our sample spreadsheet:

Excel Price Feed is unique in the way it enables financial data to be embedded in a spreadsheet using simple Excel formulas, no more VBA or complicated web requests.

For example, to add the current market capitalization of Apple stock to a cell, the following formula is used:

=EPF.EOD.MarketCapitalization("AAPL.US")

Then, each time the spreadsheet is refreshed the latest data is retrieved.

Company fundamental data that is now easily available in Excel via the Add-in includes current market capitalization, EBITDA, PE Ratio and PEG Ratio:

Our launch integration includes historical price data (daily, weekly and monthly) as well as stock fundamental data and financial analysis data. Over the coming months we will be adding access to more datasets such as technical indicators, economic and calendar data. We will also expand our instrument coverage to include more bonds and options.

To get started visit the EODHistoricalData.com Overview & Setup page on our website.

Subscription plans for EODHistoricalData.com start from €19.99 per month.

IG Client Sentiment Analysis

IG client sentiment is an indicator that IG Index provides to show what percentage of clients have long or short positions.

For example looking at the NASDAQ index, we can currently see that 56% of clients are long this market and 44% are short:

NASDAQ IG client sentiment

A general consensus is that this is often a “contrarian” indicator, clients try to short when the market when it is going up and go long when the market is going down.

The Excel Price Feed Add-in provides historical client sentiment data which you can download into Excel. You can then combine this data with historical market data to see how client sentiment changes as the market changes.

Here is a chart of long client sentiment (the orange line) versus market daily close (the blue line) for the S&P500 Index over the past 4 months:

S&P500 IG client sentiment

There does appear to be a correlation, as the market moves up client long positions moves down – clients are moving from long to short positions as the market goes up.

Then at the peak (early September) client positions were long just before the market started dropping.

As the market moved down (i.e. for the past month) clients stayed long.

This example shows that client sentiment data can be used to provide trading signals, especially at “turning points” in sentiment. Obviously this would need to be tested against different markets but it is definitely a useful tool when looking for trading opportunities.

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.

Volatile Excel Functions

Today I was talking to a customer and his issue was a strange one which I hadn’t seen before.

Every time he changed ANYTHING on his spreadsheet he noticed that it would refresh unrelated formulas, i.e. formulas that did not reference the cells he was updating.

The main formula on his sheet was this:

=EPF.Yahoo.HistoricDatePeriod("AMD","Weekly","1 Jan 2020", TODAY(), "DESC", 1)

This is an Excel Price Feed dynamic array formula which returns weekly historical stock market data for AMD stock from 1 Jan 2020 to today.

This formula is non-volatile so should only update when any of its parameters change.

So, what was going on?

The culprit was actually one of the parameters: the Excel TODAY() function.

This built-in Excel function is a volatile function and will update ANYTIME ANYTHING on the spreadsheet changes.

What is a Volatile Function?

Microsoft defines a volatile function as follows:

…one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments (if it takes any) has changed.

https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation

There are 8 built-in volatile Excel formulas, and it is worth being aware that using these in your spreadsheet could have unintended consequences:

  • NOW
  • TODAY
  • RANDBETWEEN
  • OFFSET
  • INDIRECT
  • INFO (depending on its arguments)
  • CELL (depending on its arguments)
  • SUMIF (depending on its arguments)

The Solution

The solution was simple, replace the TODAY() function with a string value of the current date. This could be either hardcoded into the formula or better still entered into a cell and referenced from the formula.

So, if you every notice your spreadsheet starting to slow-down or start doing unnecessary calculations then look out for volatile functions.

Pre-market stock prices

Stock markets are generally only open during specific times, the “trading day”. For example, the US market is open from 9:30am to 4:00pm (EST).

However, this is not the only period when trading takes place and when prices can change, there is also the pre-market which is “open” before the regular market opens.

This is a time of very little liquidity however trading during this time can enable you to take advantage of any news or events that happen outside normal market hours.

Yahoo Finance provides prices during this period, the pre-market (or before hours) prices.

For example, below we can see the current “before hours” price for Apple stock is 364.00 and the price has moved -2.53 from yesterdays close price of 366.53:

Apple stock price: live and pre-market

We have recently added some new Excel formulas to the Add-in to provide pre-market prices in Excel:

  • EPF.Yahoo.PreMarketPrice
  • EPF.Yahoo.PreMarketChange
  • EPF.Yahoo.PreMarketChangePercent
  • EPF.Yahoo.MarketState

This last formula is used to find the current state for the market eg. whether we are in regular or pre-market trading hours: “REGULAR” or “PRE”.

The example spreadsheet below shows the formulas in action, you can see column C uses the PreMarketPrice formula which references the ticker in column A:

Excel Price Feed pre-market stock prices

We hope you find these new formulas useful and as ever keep your feedback coming, preferably on the Support Forum or leave a comment below.

Building an IG Index watchlist in Excel

Excel Price Feed Streaming Watchlist

IG Index / IG Markets provides over 16,000 different markets to trade, including FX, stocks, indices, commodities and cryptocurrencies. Keeping track of potential trading opportunities within this universe is daunting, you really need a way to filter out which markets you are interested in and monitor just those markets.

This is where watchlists can help.

They help you focus on only the markets that interest you and help quickly give you an overview of the overall financial market. For example, I like to monitor a watchlist of major stock indices, currency pairs and commodities. This is especially helpful when I first arrive at my desk in the morning and want to get a quick overview of global market sentiment.

Brokers, such as IG Index, provide basic tools for building watchlists, for example here is an extract from one of my watchlists on the IG website:

IG Web Platform Watchlist

This watchlist is fine for keeping an eye on a small selection of fields such as % day change but what if you want a more customized watchlist, with filters, sorting, visualizations, custom formulas etc?

This is where the Excel Price Feed Add-in comes in.

You can use the power and customisation of Excel using formulas and visualizations together with the live market data formulas provided by the Add-in to build your own watchlist in Excel.

For example, we can use the daily high and low price to calculate the daily range and visualize where the current price is within the daily range.

In the example below I can see that the current level of the S&P500 is near the high of the daily range (83%) and the range today is 70 points:

We can add more markets and more data, including client sentiment, to give us a live view of the market, highlighting the data we are interested in:

At a glance we can see that the major stock indices are little changed on the day and towards the high of their daily ranges. We can also see that the main mover today is the cryptocurrency Ether.

Using Excel like this we can track and compare markets exactly the way that suits our trading style. Once we have setup our spreadsheet it is updated automatically with live market data so it always gives us a live view of the market.

Download Excel Price Feed today and try it for yourself.

Welcome to our blog

This blog will be a place where we will share our thoughts, ideas and news covering finance, trading and technology. We will post product news, articles about Excel as well as thoughts on developments in trading and financial markets.

We hope you enjoy what we write and we would love to hear from you, so all comments welcome!

Andy.