Comparing Bull/Bear ETF Returns: Reusable Spreadsheets for Excel & Google Sheets
Google Sheets and Microsoft Excel both offer powerful built-in functions for retrieving historical ticker prices. These tools empower you to evaluate price trends and backtest trading strategies. This post provides ready-to-use spreadsheet templates designed to automate market data downloads and compute returns. Within this prior blog post, you can find an introductory comparison of the Google Sheets and Microsoft for downloading historical ticker prices.
By analyzing pairs of Bull and Bear ETFs, you can verify data quality and confirm if performance aligns with your expectations. For example, Bull ETFs generally appreciate while Bear ETFs normally decline over time – especially longer-run timeframes. Even if market conditions cause Bull ETFs to decline, it is reasonable to expect Bear ETFs to decline more over an evaluation timeframe. Additionally, ticker prices from one data provider should match those from another provider.
By reading this post, you will learn how to apply Microsoft Excel and Google Sheets templates to any ticker symbol you choose. This post’s spreadsheet template examples demonstrate how to compare returns across different asset classes. Whether you are a seasoned self-directed trader or just beginning to use spreadsheets for analysis, these examples will sharpen your technical skills and help you make more informed market decisions.
The 24 Tickers Tracked in This Post
The following table has six key columns whose values summarize the 24 tickers tracked in this post. The 24 ETFs are organized into two sets of 12 matched tickers for Bull and Bear ETFs.
The first two columns denote the ticker and security name for the Bull ETFs.
The second two columns display the ticker and security name for the Bear ETFs.
The next-to-last last column indicates the daily ETF price percentage change relative to the underlying index for each pair of ETFs. In addition, for the first trading day, the ETF price is seeded to the first trading day’s price.
For Bull ETFs, starting on the second trading day, the ETF price is updated by multiplying the previous day’s ETF price by one plus the daily percentage return of the underlying index multiplied by the ETF’s leverage ratio.
For Bear ETFs, starting on the second trading day, the ETF price is updated by multiplying the previous day’s ETF price by one minus the daily percentage return of the underlying index multiplied by the ETF’s leverage ratio.
The last column indicates the asset class to which a pair of ETFs belongs. This post tracks six distinct types of asset classes.
Large-Cap Equity (S&P 500)
Technology Equity (Nasdaq 100)
Small-Cap Equity (Russell 2000)
Fixed Income (Long-Term Treasury)
Sector Equity (Financials)
Sector Equity (Energy)
Syntax for and Excerpts from Downloaded Bull and Bear ETF Prices
This section reveals the Microsoft Except and Google Sheets function syntax along with excerpted sample results for the Bull and Bear ETFs.
The following screenshot shows a sample spreadsheet layout along with the first ten return prices for each Bull ETF with the GOOGLEFINANCE function in a Google Sheet named GSBULL/BEAR ETF TICKER MEMBERS. As you can see, the BULL ETF tickers are spread across every other column within row 1 in the sheet showing the BULL CLOSES tab. For your convenience in following the example, the order of the ETF tickers matches the Bull ETF tickers in the preceding table.
The cursor rests in cell A2 of the sheet. The GOOGLEFINANCE function in cell A2 has four parameters.
The first parameter specifies the SPY string value in cell A1.
The second parameter (close) designates the return of close values from the function.
The third and fourth parameters designate the start and end dates to be searched for SPY close values from the Google Finance website database that the GOOGLEFINANCE function queries.
The start date in the example below is January 22, 1902. By trial and error, I found that the function returns close price values on or after that date or the first trading date for the ticker, depending on which date is most recent. The earliest possible state that worked for my trials was January 1, 1902.
The end date for the downloaded close prices is January 31, 2026, which is the date when the download was requested. If the Google Finance website has not updated daily prices as of the end date, the GOOGLEFINANCE function returns values for the preceding trading date.
The GOOGLEFINANCE FUNCTION in cell A2 can be copied to row 2 of alternating columns (from cells B2 through W2) to return close values for the remaining Bull ETFs.
As you can see, the first trading day for the SPY function is January 29, 1993. The first trading day can vary from one ticker to the next, depending on when Google Finance initially started tracking close prices for trading dates for a ticker. This is often the inception date, but the first public trading date can sometimes be after the inception date.
The next screenshot shows comparable data extracted for the Bear ETFs with the GOOGLEFINANCE function; these values are from the BEAR CLOSES tab.
The ticker values in row1 are for Bear ETFs – namely, SH through ERY.
The GOOGLEFINANCE function expressions have the same syntax and layout as for the BULL CLOSES tab.
However, the first ten trading dates are generally different for these tickers because Bull and corresponding Bear ETFs can launch of different dates.
The initial public trading date for Bull and matching Bear ETFs depends on when a sponsoring firm decides to initially offer shares for each ETF to the public.
The next screenshot shows the initial ten close dates and prices for Bull ETFs downloaded with the Microsoft Excel STOCKHISTORY function expressions. In many ways, the STOCKHISTORY function behaves similarly to the GOOGLEFINANCE function, but the STOCKHISTORY function works exclusively within Excel workbook tabs while the GOOGLEFINANCE function works exclusively from within Google Sheets. Another especially important distinction is that the STOCKHISTORY derives its historical dates and prices from the Data & Analytics unit within the London Stock Exchange Group (LSEG), formerly known as Refinitiv, which originated as part of Thomson Reuters. For more details on STOCKHISTORY function syntax review the “Getting Started Demonstrations for the STOCKHISTORY Function” section of a prior blog post titled “An Introduction to Retrieving Historical Security Prices with GOOGLEFINANCE and STOCKHISTORY”. For the example below,
The first parameter for the expression in cell A2 points at the cell (A1) with the ticker value for which it is to download date and price values.
The second and third parameters are date functions for the start and end dates, respectively, for the returned values.
The fourth parameter value of 0 is a code requesting daily dates and prices as opposed to weekly or monthly values.
As with the GOOGLEFINANCE function, you can copy the STOCKHISTORY expression in cell A2 to alternate columns across the spreadsheet. This allows you to download dates and prices for the eleven remaining Bull ETFs.
The final screenshot in this section shows the first ten return dates and prices for the Bear ETFs. The syntax and layout of the spreadsheet is the same as for the Bull ETFs. However, there are some mandatory exchange tickers that appear in cells C1 and G1. The exchange prefix in both cases is ARCX. The prefix is required because the LSEG lookup strategy requires a unique symbol before it can return values. You may have noticed that GOOGLEFINANCE did not require an exchange prefix for the SDS and PSQ tickers. This is because the GOOGLEFINANCE function takes a guess about the appropriate exchange when the ticker is not unique across exchanges tracked by the Google Finance website. If you find that returned values from GOOGLEFINANCE are unreasonable, you can use an appropriate exchange prefix to correct the issue. For the example below, the appropriate exchange prefix with the GOOGLEFINANCE function is NYSEARCA.
Correlating GOOGLEFINANCE versus STOCKHISTORY Prices
How consistent are the prices returned by GOOGLEFINANCE versus STOCKHISTORY? Evaluating this consistency involves analyzing the integration between the functions and their respective data providers. This comparison is essential for establishing a baseline for data integrity. Perfect alignment between the two sources strongly suggests accurate reporting, whereas divergence indicates potential issues with price validity for which a trader/analyst must account.
The sheer volume of data points across various functions, tickers, and dates renders a manual audit for consistency impractical. Each function introduces distinct data anomalies; for instance, GOOGLEFINANCE occasionally populates price data for non-trading days, such as weekends or holidays. Conversely, STOCKHISTORY may return null values for active trading dates. These discrepancies lead to temporal misalignment and the generation of 'not a number' (NaN) errors that resist standard spreadsheet corrections. While a programmatic solution using SQL or Python could resolve these synchronization issues, such methods fall outside the scope of this blog post. Consequently, I tasked Google Gemini with reconciling the date-aligned price sets and calculating the Pearson correlation coefficients for the corrected values. The following table shows that the typical outcome was perfect concurrence ( a correlation coefficient value of 1 or very nearly 1) for 23 of the 24 tickers tracked in this post. When the correlation is .999999 or .999998, the correlation value is equivalent to 1 (for Perfec Concurrence) for all practical purposes. A pair of missing price values for the first two dates for the TMV ticker from the STOCKHISTORY function made it impossible to compute a correlation coefficient across the full set of dates for the TMV ticker. Depending on the standard practices for your team, you can either estimate or omit the missing TMV prices. This post leaves the issue unresolved so that you can have exposure to the kinds of issues that you can encounter when computing correlation values between two different price series.
Comparing Change Rates Across Bull and Bear ETF prices
One of the main reasons for investors to download and validate historical prices is to is to compare cumulative change percentages across two or more tickers over the same timeframe. In the context of the tickers tracked in this post, it is natural to compare Bull ETFs with their corresponding Bear ETFs. This section has two subsections. The first section describes how to compute cumulative change percentages for corresponding Bull and Bear ETFs. The second subsection focuses on comparing the cumulative change percentages between Bull and Bear ETFs.
How to Compute Cumulative Change Percentages for Bull and Bear ETFs
The following screenshot shows an Excel spreadsheet from the Excel workbook into which Bull and Bear ETF prices were downloaded. The “Excerpts from the Downloaded Bull and Bear ETF Prices” section of this post presents the STOCKHISTORY functions for the Bull and Bear tickers into two Excel spreadsheets. The same section also presents the GOOGLEFINANCE functions for downloading the same tickers into Google Sheets workbook.
The Bull and Bear ticker strings appear in A5:B16. One fair way to compare matching Bull and Bear ETFs is to assess the change of prices over a common set of shared start and end dates. Columns C and D contain the start dates for the Bull and Bear ETFs, respectively. Column E contains the most recent shared start date by the Bull and Bear ETFs; this is simply the max function of the dates in columns C and D. As you may recall, the downloaded prices were collected through the final trading day in October 2026 (10/30/2026). This trading date, which is the ending date for both a Bull and Bear ticker pair appears as the date values in column F.
The values in J5:J16 show the computed cumulative change percentage in Bull ETF prices over the evaluation timeframe, which is the range of dates from column E through column F. To compute cumulative change percentage values, we need to retrieve the Bull or Bear ETF’s closing price at two specific dates: the shared starting date and the final evaluation date. Excel’s INDEX and MATCH functions make it possible to locate the correct row and column for each ticker.
The expressions for the return values in column J depend on return values from the expressions in columns H and I. The expressions for columns H, I, and J will be presented and briefly described below.
The values in N5:N16 show the computed cumulative change percentage in Bear ETF prices over the evaluation timeframe. The expressions for the return values in column N depend on return values from the expressions in columns L and M. As with the Bull ETF expressions, the Bear ETF expressions for columns L, M, and N will be presented and briefly described below.
Here are the expressions for the SPY ticker in H5:J5. You can copy the expressions in H5:J5 through to the next eleven rows for the computed cumulative change percentage and the inputs upon which the cumulative change percentage depends. Excel’s INDEX and MATCH functions locate the correct date row and the correct ticker column, ensuring that the returned price values are aligned across all tickers.
After entering and validating the expressions for row 5, copy the expressions in row 5 to the remaining eleven Bull tickers from rows 6 through 16.
Next, populate columns L through N in row 5 for the SH Bear ticker. You can do this by copying the expressions in H5:J5 TO L5:N5. Then perform two updates for the copied expressions:
Replace the BULL CLOSES FROM XL spreadsheet name with the BEAR CLOSES FROM XL spreadsheet name, and
Replace the $A5 cell reference with the $B5 cell reference.
Next, copy the updated expressions in row 5 to rows 6 through 16.
For your convenience, here is a summary of the cells, expression in cell, and topline summary of expression column values for L5:N5.
This preceding text describes how to compute cumulative change percentage for historical values to Microsoft Excel spreadsheets. The cumulative change percentages in Google Sheets can be calculated using the same logic as the Excel model described above. This involves locating the price at the Most Recent First Date and comparing it to the price at the Most Recent Overall Date. Google Sheets and supports Index and Match functions that operate in the same way as in Microsoft Excel. Other changes may be required based on different naming conventions, such as tabs for downloaded data.
How to Compare Cumulative Change Percentages between Bull and Bear ETFs
After you are sure you have valid expressions cumulative change percentage values, you can compare the values for Bull and Bear ETFs. For example, in the preceding screenshot the Bull and Bear ETF cumulative change percentage values reside in columns J and N.
A pair-wise analysis reveals a consistent trend: in every instance where data was available for both tickers, the Bull ETF's return was either more positive or less negative than its Bear counterpart. This relative outperformance across all eleven pairs with cumulative change percentage values underscores the prevailing market directionality during the evaluation period, regardless of whether the absolute return for a specific ticker was positive or negative."
While it is consistently the case that Bull ETFs return greater price growth than their Bear counterparts, there are substantial variations in price growth across ETFs. TQQQ has by far the largest price growth (24,865.33%). Its growth rate is more than three times larger than its closest competitor (SPXL), which has a price growth of 7,964.88%.
A final point of interest is that the top three Bull ETFs (TQQQ, QLD, and SPXL) — each with cumulative change percentages above 7,000% — all use 2× or 3× leverage and belong to either the Technology Equity (Nasdaq 100) or Large‑Cap Equity (S&P 500) asset classes.
Concluding Comments
The objective of this post is to provide readers a template for downloading closing prices and analyzing performance for two sets of tickers with spreadsheet functions from Excel and Google Sheets. Once you have downloaded historical closing prices, you can use them in a wide variety of ways. This tip illustrates several of these ways. I believe creative investors, traders, and analysts can find many more ways to prove the worth of historical stock prices, such as those that you can download with the STOCKHISTORY and GOOGLEFINANCE functions.
What You Learned in This Post
- How to download historical Bull and Bear ETF prices using GOOGLEFINANCE (Google Sheets) and STOCKHISTORY (Excel).
- How to identify the first trading date for each ETF.
- How to compute a shared evaluation window using the most recent of the two start dates and a common ending date.
- How to compute cumulative change percentage using the formula ((ending/starting) - 1) * 100.
- How to compare cumulative changes across Bull/Bear pairs to assess relative performance.
- How INDEX and MATCH work together to locate the correct price values. You learned that MATCH identifies the row for a specific date and the column for a specific ticker, while INDEX retrieves the corresponding price. This combination ensures that each Bull or Bear ETF’s starting and ending prices are aligned to the same evaluation window.
- Selected leveraged Bull ETFs (TQQQ, SPXL, QLD) have shown exceptionally high long‑term gains over extended evaluation periods.
Comments
Post a Comment