Today we released a new version of Excel Price Feed. To update your Add-in, please follow the instructions in the User Guide.
This release includes the following changes:
Yahoo Finance: EPF.Yahoo.HistoricDatePeriod formula
We’ve made several improvements to this formula.
We have fixed an issue which affected certain stock/date combinations which returned additional data points outside the requested date range.
We have also added an additional optional parameter includePrePost which if set will include pre and post market data in the returned time series (for periods less than daily).
We have also made 3 of the parameters optional so you just need to specify a ticker, period and start/end date.
For example, this formula will return hourly data for Apple from 1 to 4 October; it will return headers, date in descending date order and not include pre or post market data:
There are lies, damned lies and statistics. There are also charts that are published to mislead or to simply reinforce a point of view that’s flimsy at best or just plain wrong.
These charts are known as “chart crimes” and one appeared all over social media last month. Usually I would just make a comment and move on but this one received over 2 million views and deserves further investigation.
It was a chart comparing the growth of the Nasdaq over two periods: the dot com bubble and the current AI “bubble”. The conclusion being that we are on the same upward trajectory and all things being equal are due a major crash anytime soon.
Aside from the obvious lack of timescale on the x axis and disparity in y axis scaling, does this chart bear any resemblance to reality?
Let’s build our own using Excel Price Feed to retrieve historical market data and produce an Excel chart based on the actual data.
For the Dot com bubble period we will use 1996 to 2002 and for present day we will look at data from Q4 2022 (when ChatGPT was released) to now.
To retrieve the data we will use the Excel Price Feed HistoricDatePeriod formula. Here is an example of how to retrieve monthly data for the NASDAQ Composite Index:
=EPF.Yahoo.HistoricDatePeriod("^IXIC","MONTHLY","1 Sep 2022","1 Oct 2025","ASC",1)
We will base each time series to 1 so we are comparing like-for-like. To do this we simply divide each point by the initial starting point.
We can now plot this data in Excel; the blue line is the dot com bubble, and the orange line is the past 3 years:
Not nearly as dramatic, the trajectory is similar but we are barely halfway towards the dot-com crash equivalent peak. So if the past is an indicator of the future then we should expect the market to double again from here and crash in about a year. But as they say, the past is not always a predictor of the future…
What about individual stocks?
Let’s look at one from the dot com bubble: Cisco (light blue) and one from now: Nvidia (green).
Nvidia has climbed at a much steeper rate than Cisco. Again, similar trajectory and timescales:
If you want to build financial charts like this yourself check out Excel Price Feed today and try it free for 10 days: https://www.excelpricefeed.com/
We were recently contacted by a user who was trying to calculate the market high and low over an particular date period and more importantly the date that the high or low was reached.
Neither Excel Price Feed nor Excel have a single formula to calculate this, but we can use several Excel functions to achieve it.
For this example we will look at daily data, although the procedure and formulas used can be applied to any timeframe.
B1 is the stock ticker, B2 is the start date and B3 is the end date.
This formula outputs a table as shown below, the formula is in cell D1:
Finding the highest high over the period is easy, we simply use the MAX formula to find the maximum value of all the daily highs (column F):
=MAX(F:F)
To find the lowest low we use the MIN formula with column G:
=MIN(G:G)
The hard part is finding the date at which the highest high and lowest low was reached. We can achieve this by using a combination of Excel functions.
We will now walk through how to find the date of the highest high, which is in cell B5:
Using the MATCH function we can find the row where the highest high was achieved. The MATCH function searches a range, in this case the column (G:G) and finds the row where the highest high is, in this case the value in cell B5 (260.10).
We then take this row and use the ADDRESS function to combine it with the column where the date is located, column E (or the 5th column as this function requires a numeric column reference).
We now have an address for the location of the date, via a column and row number. To return the actual value at this address we must use the INDIRECT function, this will then provide the date value of “26 Dec 2024”
The full formula looks like this:
=INDIRECT(ADDRESS(MATCH(B5,G:G,0),5))
Below we can see the full formula in the formula bar at the top and the result is “26 Dec 2024”:
To find the date of the lowest low we just replace column G with column H and the low which is in cell B5.
We hope you find this useful and if you would like to try the spreadsheet for yourself you can download it from here.
Today we released a new version of Excel Price Feed. To update your Add-in, please follow the instructions in the User Guide.
In this release, we plugged in any gaps in formulas which were missing the Quarterly and Trailing 12 Months equivalent. The gaps were in the Balance Sheet, Income Statement and Cash Flow groups of formulas.
We also added some additional formulas for retrieving Quarterly figures, which all take as inputs a stock ticker and a quarter. Yahoo doesn’t make available equivalent TTM formulas.
Lastly, we added quite a few additional Quarterly and TTM formulas for Cash Flow. Along with the formulas for Free Cash flow (added in the previous version) below are all the Cash Flow formulas in the system.
Here is an example of the new formulas used in a sheet. This dataset is for the MSFT ticker.
Again the Quarterly formulas take a input a ticker and a reporting quarter and the Trailing 12 Month (TTM) Formulas take as input just a stock ticker. Examples:
AI is everywhere now; not a day goes by without at least one company releasing an AI related news announcement, the latest being Volkswagen who are integrating ChatGPT into their next generation of cars.
These AI announcements are usually accompanied by a spike in the company’s stock price and this, together with the recent rise in the tech and broader market, has prompted some market commentators to declare that we are in an AI fueled stock market bubble.
Are we in a bubble?
Unfortunately, we will only know if and when the bubble bursts; by its very definition we cannot know if we are in a bubble but let’s crunch some numbers to investigate further.
We’ll use Excel Price Feed to get some financial data into Excel, and we’ll start by looking at the “Magnificent Seven”.
What are the Magnificent Seven? Well, these are the largest, most significant players in the global tech industry, and are leading the charge in bringing AI to the masses:
Apple
Microsoft
Alphabet
Amazon
Nvidia
Meta
Tesla
We’ll start with market capitalization, and use the Excel Price Feed formula EPF.Yahoo.MarketCap together with a nice stacked chart to see how they stack up (excuse the pun), units are $trillion:
The total market capitalization of these 7 companies is almost $12 trillion, but how does this compare to the market in general, say the S&P500?
Here we have added the rest of the S&P500 ($27 trillion) and can see that the Magnificent Seven account for more than one quarter of the entire market:
What is even more astounding is how these 7 stack up against entire stock exchanges.
Here we are comparing them with the 3 largest non-US exchanges: Shanghai, Euronext and Japan:
Now let’s see how they have grown over the last 12 months.
We’ll use the EPF.Yahoo.Historic.Close formula to retrieve the stock price at the start of 2023 and compare it to the current stock price using EPF.Yahoo.Price
They’ve all increased by at least 50%, with Nvidia, perhaps the most AI centric stock being the star performer.
So, are we in a bubble?
Why not download Excel Price Feed today and crunch some numbers yourself. You can also download the spreadsheet created for this post from our website to get started.
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:
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:
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:
And for the 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:
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.
The Excel Price Feed Blog 