Do Returns from the GOOGLEFINANCE Function In Google Sheets Match Returns from Yahoo Finance?

A recent prior post at this blog describes the disabling of a Yahoo Finance feature for downloading historical price and volume data into csv files for free.  A Yahoo Finance web page from which you were formerly able to download historical prices without a fee has been updated by the following statement: "Downloading historical data is only available to Gold members." The disabled feature degrades Yahoo Finance as a supplier of free data for security trading analytic projects.  As a result, this blog promised to investigate and demonstrate workarounds for the disabled free feature.

The Gold member subscription plan is currently priced at $479.40 per year when paid annually.  Furthermore, a previously available payment plan for the service on a monthly basis is discontinued as of the time this post is prepared.  In evaluating a response to the price escalation from free to over $400 per year for historical data in csv files, I started to do some research on alternatives for those who still wanted the free download of historical data as csv files or at least into a traditional data analytic tool.

This post is the second investigation of alternatives for the previously free feature from Yahoo Finance.

• The first investigation introduced the GOOGLEFINANCE function in Google Sheets as a way to transfer historical prices via csv files from Google Sheets without charge.
• This second investigation drills down on returns from two prior Security Trading Analytics blog posts with historical data from the GOOGLEFINANCE function.  This second investigation includes two examples of whether you can get the same results from Google Sheets for free for which Yahoo Finance now charges you $479 per year.  These examples may be of interest to you no matter what analytic methods you invoke to complement your securities trading.

Performance for Leveraged and Unleveraged ETFs for Major Market and Other Widely Tracked Indexes

The example for this section is based on a prior post titled “Can Leveraged ETFs Safely Grow Long-Term Investments?”.  The post examines the growth of five pairs of ETFs; one member of each pair is for a leveraged ETF, and the other member of each pair is for a matching unleveraged ETF.  Three of the five pairs are for major market indexes – namely, the S&P 500 Index, the NASDAQ-100 index, and the Dow Jones Industrial Average (DJIA).  Two of the five pairs are for other widely tracked indexes – namely, the PHLX Semiconductor Sector Index and the Russell 2000 Index.  The following table lists the index names along with tickers for the unleveraged and leveraged ETFs tracking each of the five indexes.

The prior analysis of the ETFs for the five indexes used data from Yahoo Finance.  The analyses in this post extracts historical data from the Google Finance site via the GOOGLEFINANCE function in Google Sheets.  The analysis of data from Google Finance includes more recent data than was available from Yahoo Finance at the time of the earlier post.

• The current post examines the ETF growth rates of close prices from their earliest date in Google Finance through September 30, 2024.
• The prior post examines the ETF growth rates of close prices from their earliest date in Yahoo Finance through December 29, 2023.
• The initial availability of price data for each of the ETFs in Google Finance and Yahoo Finance are, respectively, within days of each other.

Two growth metrics are examined in this post – namely, the compound annual growth rate and the overall percent change.  Both metrics are applied to close prices for unleveraged and leveraged ETFs.

• The overall percent change is the percent change from the close price on the start date through the close price on the end date.
• The compound annual growth rate (cagr) is the average annual growth rate for a series of values from a start date through an end date.  The average annual growth rate is computed so that compounding the cagr over the years from the start date through the end date grows the initial time series value to the final time series value.  This metric is useful for comparing two different investments, such as leveraged ETFs versus unleveraged ETFs for the same timeframe without any withdrawals.  The computational steps for computing the cagr in a Google sheet are the same as those for computing cagr in an Excel worksheet.  The Excel computational steps are described in a prior post (How to Compare Close Prices for Three Securities Over Four Years).

The following screen shot displays the first five rows of data for the SPY ticker. The name of the Google sheet begins to display in the tab at the top of the screen shot below.  The full name for the sheet is Data to compare Google Finance with Yahoo Finance.

The GOOGLEFINANCE function expression for retrieving all historical data for the ticker (SPY) from Google Finance resides in cell A1.  When the Google Sheets cursor resides in cell A1 (as in the next screen shot), the expression appears above the letters designating columns in the Google sheet.

The GOOGLEFINANCE function references all historical data, which includes date, open, high, low, close, and volume data series.  The data for other historical data series besides date and close are hidden in columns B, C, D, and F to avoid distractions from them because they are not required for the analysis. 

The next screen excerpt shows the last five rows of data returned by the GOOGLEFINANCE function in the preceding screen shot.  The date for the final row is 9/30/2024.

Both preceding screen shots show separate tabs for DIA, UDOW, SPY, and SPXL tickers.  There are additional tabs to the right of the SPXL tab on the Google sheet with close values by date for the QQQ, TQQQ, SOXX, SOXL, IWM, and TNA tickers.  To the right of the TNA tab are two additional tabs named Summary1 and Summary2 for computing and displaying the growth rates of close values for the ten tickers.

The following Google sheet excerpt shows the computations for the growth rates for each of the ten tickers that appear in column A.  The two growth rate metrics are computed for two different timeframes.

• The first timeframe has a start date of the initial date from which Google Finance began tracking close prices for an ETF’s ticker.  Each of the ten ETF start dates is different in Google Finance.
• The second timeframe has a start date that is five years before the end date through which close prices are collected for this post, which is September 30, 2024, for both timeframes.
• The reasons for computing growth rates for two different timeframes are
• To reflect the growth from its earliest date at Google Finance.  The timeframes begin at around 14 years and extend through an upper limit of about 31 years.
• To offer a common relatively recent timeframe (the last 5 years) for comparing all tickers among each other no matter when Google Finance started tracking close prices for an ETF. 

The next screen excerpt displays just the cagr values and overall growth rates in a format for comparing leveraged versus unleveraged ETFs for three major market indexes and two additional widely tracked indexes.

For the timeframe based on all trading days, the results are as follows.

• The three leveraged ETFs for major market indexes (UDOW, SPXL, TQQQ) have consistently larger cagr and overall growth rate values than their comparable unleveraged ETFs (DIA, SPY, and QQQ).  This outcome is consistent across both time frames.
• The two leveraged ETFs for widely tracked indexes (SOXL and TNA) also have consistently larger cagr and overall growth rate values than their comparable unleveraged ETFs (SOXX and IWM).
• The growth rate results for close price values collected from Google Finance via the GOOGLEFINANCE function are approximately the same as those based on close price values from Yahoo Finance in a prior post (Can Leveraged ETFs Safely Grow Long-Term Investments?). 

For the timeframe based on the last five years, the results are as follows.

• Again, the leveraged ETFs for major market indexes have consistently larger growth rates than the growth rates for unleveraged ETFs.
• However, the growth rates for the TNA leveraged ETF is less than the unleveraged IWM ETF.  The same pattern holds true for the growth rates of the SOXL leveraged ETF versus the SOXX unleveraged ETF.  Both of these growth rate results for Google Finance close values match growth rates results for Yahoo Finance growth rates.

The patterns for winning ETF growth rates in this analysis match those in a prior analysis for Yahoo Finance close values(Can Leveraged ETFs Safely Grow Long-Term Investments?).  Furthermore, the outcome is robust over widely different look-back periods, such as 5 years and anywhere from 15 to 25 years!

Performance for Bitcoin USD Exchange Rates Versus Major Market ETFs

This section is in the style of an earlier post titled “Bitcoin USD Exchange Rates Versus Major Market ETFs”.  The earlier post reported the growth rates of bitcoins in US dollars versus two benchmarks ETFs

• the top 100 securities in the S&P 500 index and
• the top 100 securities on the NASDAQ exchange.

The earlier post uses close price data for the securities from Yahoo Finance.  This section demonstrates how to retrieve comparable data from Google Finance.

The following excerpt from a Google sheet displays names and tickers for the three securities examined in this section.  Google Finance uses the BTCUSD ticker to track bitcoin exchange rates in US dollars.  In evaluating performance between bitcoins and ETFs traded on an exchange, you need to account for the fact that bitcoins are traded 24 hours per day and 7 days per week (in other words about 365.25 days per year).  In contrast, the OEF ticker for the S&P 100 and the QQQ ticker for the NASDAQ 100 trade only on days when the market for the ETF securities are open.  According to Wikipedia, the NASDAQ and NYSE are both open for trading 252 days per calendar year.  The QQQ ticker trades on the NASDAQ exchange, and the OEF ticker trades on the NYSE exchange.

The following pair of images shows two excerpts from the same Google sheet.  The top image displays the first five rows of a tab for the BTCUSD data.  The bottom image displays the last five rows of the BTCUSD data from Google Finance.

The expression in the top image of the displays the expression for retrieving close prices for the BTCUSD ticker.  The first and second date functions in the GOOGLEFINANCE expression designate the earliest possible date and the last date for collecting data.  The first date function is set to start collecting data well before the first close value for the BTCUSD ticker in Google Finance.  Note that the earliest retrieved close value in cell B2 has a date value of 5/6/2016.

The bottom image has a final date for retrieved close values of 9/30/2024.  The time for this value is 2 minutes before the end of the calendar day.

The next pair of images displays the top and bottom rows the first and last five rows for the OEF ticker in a Google sheet.

Note that GOOGLEFINANCE expression for the OEF ticker data in the top image designates an exchange name before the ticker (NYSEARCA).  Also, note that the Google Finance data does not start until 12/1/2003.  The time for the first four data rows corresponds to 4 PM (or 16 hours from midnight on 12/1/2003).

The bottom image in the the following pair displays the final five rows of retrieved data for the OEF ticker.  As with the BTCUSD ticker, the date for the last row of data is 9/30/2024.  This date results from the second date function in the GOOGLEFINANCE expression for retrieving OEF close values.

The next pair of images present in its top and bottom rows the first and last five rows for the QQQ ticker.

The GOOGLEFINANCE expression in the top image designates the exchange name (NASDAQ) before the QQQ ticker.  The start date for the QQQ ticker precedes the start dates for the BTCUSD and OEF tickers.

The next image displays the last five dates and close values for the QQQ ticker.  As with the other tickers, the date for the last row is 9/30/2024.  This date results from the second date function in the GOOGLEFINANCE expression for the QQQ ticker.

The cagr and overall percent change values were computed from the start date through the end date for each of the three tickers.  These values were computed the same way as in the preceding section.  The following screen shot presents the results of these computations.

The table in the screen shot confirms that the BTCUSD ticker had a vastly superior growth rate for all trading days as well as the last five years of trading days.  These outcomes are generally consistent with the earlier comparison of performance of BTCUSD growth rates to OEF and QQQ growth rates computed with data from Yahoo Finance.

Concluding Comments

This post demonstrates that analyses based on historical close prices for security tickers from Google Finance generally match those from Yahoo Finance.

Instead of dwelling on differences for individual days between trading days or statistical correlations across trading days, this post dwells on comparing Google Finance and Yahoo Finance close values in two different scenarios.  The growth outcomes were similar in both scenarios for data from Google Finance and Yahoo Finance.   As a consequence, you have evidence that you can use Google Finance close values as an alternative to Yahoo Finance close values in your trading analyses.

These outcomes may be important in that they can save self-directed traders the cost of Yahoo Finance’s Gold subscription plan ($479 per year) for performing their own custom analyses of historical price data.  The outcome is also of value to self-directed traders who already perform their custom stock market analyses using Google Sheets versus a programming system, such as Python or SQL Server.  While you can download data from Google Finance via csv files to a programming language, if your analysis is simple enough, you may find that historical price data collected via the GOOGLEFINANCE function can be performed within Google Sheets.

Finally, this post confirms the stability of two prior analytical solutions.

• One for confirming the leveraged ETFs for major market indexes can dramatically increase performance compared to unleveraged ETFs.
• Next, this post confirms dramatically higher growth rates for bitcoin exchange rates in US dollars versus the top 100 securities in either the S&P 500 index or the NASDAQ index.