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:
We recently received a feature request from one of our users to add “Retained Earnings” to our software.
What are Retained Earnings?
Retained earnings represent the cumulative net income a company has reinvested into the business instead of distributing to shareholders as dividends. In other words, it’s the company’s “savings account” of profits.
Retained Earnings = Beginning Retained Earnings + Net Income − Dividends Paid
This figure accumulates over time and reflects how much profit a company has decided to keep rather than return to shareholders. High retained earnings suggest a firm has consistently made profits and reinvested them.
Let’s compare two companies: Apple and Goldman Sachs
Retained Earnings: Apple vs. Goldman Sachs
As of their latest filings:
Apple Inc. had retained earnings of approximately $3 billion.
This comparison may seem surprising at first—after all, Apple is one of the most profitable companies in the world. So why does a financial firm like Goldman Sachs have retained earnings that dwarf Apple’s?
1. Dividend and Buyback Philosophy
Apple has paid significant dividends and engaged in massive share buybacks. Since 2012, Apple has returned hundreds of billions of dollars to shareholders, reducing its retained earnings in the process. This was a strategic choice to reward investors and reduce excess cash.
Goldman Sachs, on the other hand, has historically paid more conservative dividends and buybacks relative to its earnings. The firm tends to retain a large portion of its profits to meet regulatory capital requirements and to support future investment opportunities, such as trading capital or acquisitions.
2. Business Model and Regulatory Requirements
Goldman Sachs operates in a capital-intensive and highly regulated industry. As a major investment bank, it needs to maintain strong capital reserves to comply with Basel III regulations and withstand market shocks. Retaining earnings is one of the primary ways to build that buffer.
Apple, while capital-intensive in R&D and hardware production, doesn’t face the same regulatory capital requirements. Moreover, Apple’s enormous cash reserves (much of which were held overseas until recent tax reforms) meant it didn’t need to rely on retained earnings in the same way.
3. Use of Retained Earnings
Goldman uses retained earnings to:
Maintain risk buffers
Fund trading desks and investment banking operations
Meet regulatory ratios (like Tier 1 capital requirements)
Apple uses retained earnings to:
Invest in new products, supply chains, and technologies
Fund marketing and international expansion
Supplement shareholder returns
In Apple’s case, excess cash and borrowings have been used as alternatives to retained earnings for strategic initiatives, including acquisitions and R&D.
Retained Earnings and Excel Price Feed
So, how do we easily retrieve retained earnings in Excel? We can use the new formula:
=EPF.Yahoo.AnnualRetainedEarnings("GS",2024)
Which returns the Goldman Sachs retained earnings for 2024.
To find out more about Excel Price Feed head over to the website and try it free for 10 days: https://www.excelpricefeed.com/
We recently added 3 new formulas to the Add-in for retrieving historical market data:
EPF.Yahoo.Historic1MinuteLookback
EPF.Yahoo.Historic5MinuteLookback
EPF.Yahoo.Historic1HourLookback
These formulas use a “lookback” whereby you can specify the number of periods back from now, together with an option to specify if pre and post market data is included.
Unfortunately, the pre/post market option was not working correctly and this is what has been fixed in this release.
Here is an example of one of these formulas in action:
This dataset is for Apple stock and is hourly data points looking back the past 100 trading hours.
The dates and times shown in column A are in UTC, therefore we can see that pre and post market data is included, also known as “Outside Regular Hours”.
This data is becoming increasingly important as volumes outside normal market hours increase, and our improved historical formulas allow you to easily analyse this data in Excel.
The Excel Price Feed Blog 