Building Thirty-four Ticker Historical Price Datasets with Excel’s STOCKHISTORY and Sheets’ GOOGLEFINANCE Functions
A prior post in this blog reported model results that showed three of four leveraged single-stock ETFs outperformed their underlying security counterparts. A reader of the prior post asked a very important question: does the outperformance apply to more than just three of four ticker pairs?
The reader wanted the model results replicated for a larger set of tickers than just four pairs. Providing more results for more tickers depends on the easy availability of historical ticker prices for financial instruments, such as single-stock ETFs and their underlying tickers.
To provide more empirical evidence about any trading strategy, self-directed traders and non-institutional analysts require easy ways to compile historical prices for custom sets of financial instruments. This post offers self-directed traders and non-institutional analysts step-by-step demonstrations for how to download large historical stock price datasets with either the Microsoft Excel STOCKHISTORY function or the Google Sheets GOOGLEFINANCE function – two widely available means of collecting historical stock price series without any special surcharge.
Tickers Tracked in this Post
- Issued by a firm named GraniteShares
- that depends on an underlying security with a ticker name of AAPL (Apple Corporation)
- and targets a daily return which is twice up or down relative to the underlying security’s daily change percentage.
- issued by a firm named Direxion
- that depends on an underlying security with a ticker name of AMD (Advanced Micro Devices, Inc.)
- and targets a daily return which is twice either up or down relative to the underlying security’s daily change percentage.
STOCKHISTORY Expressions and Return Values
Now that you know what tickers are tracked in this post, the next step is to show the STOCKHISTORY expressions that will download historical price values from a source website (MSN Money / MSN Finance) into an Excel worksheet in an Excel workbook.
- The first parameter is the ticker symbol. This parameter is what makes each expression unique in the table.
- The STOCKHISTORY function expression in my experience uses the second parameter to return the earliest possible date for the ticker in the first parameter from the source website (MSN Money / MSN Finance).
- The third parameter designates the last date for which data is downloaded from the source website. This parameter value must be on or before the last date for which price data is available at the source website. As an analyst, you can set this parameter according to your data collection requirements.
- The fourth parameter specifies the interval code for the returned data. A value of 0 requests daily historical prices. Other interval codes (such as weekly or monthly) exist. Daily values are used for this post because they provide the highest resolution time series for comparing leveraged single stock ETFs.
- The fifth parameter designates whether the returned data should include headers. A value of 1 indicates that the first row of the returned array contains column labels. This makes the output easier to interpret and aligns with the formatting used in the tables later in the post.
- The sixth parameter specifies the first data property to return. A value of 0 requests the Date column. In this post, Date is intentionally included first so that all tickers share a common time axis, but this is optional – STOCKHISTORY allows properties in any order.
- The seventh parameter specifies an additional data property to include. A value of 2 requests the Open column. Because the properties are listed as 0, 2, 1, the returned columns appear in the order Date → Open → Close.
- The eighth parameter specifies another additional data property to include. A value of 1 designates the Close column. Together, the three properties 0, 2, 1 produce the three column output shown in the screenshot: Date, Open, Close.
For full details on the STOCKHISTORY function — including the property codes (0–5) that control which columns appear in the returned dataset — see Microsoft’s official documentation.
The STOCKHISTORY syntax for downloading historical prices for underlying securities is identical to the syntax for downloading historical prices for single-stock ETFs. While the syntax is identical, you must, of course, change the first parameter for the STOCKHISTORY function to point at the ticker value for an underlying security (for example, replace AAPB with AAPL).
The following table shows the appropriate STOCKHISTORY function for each of the 17 underlying security tickers tracked in this post. As with the preceding table in this section there are two columns in the table — the first one for the ticker and the second for the ticker’s STOCKHISTORY expression. Table rows are ordered by ticker value.
This post section closes with several examples of STOCKHISTORY functions embedded in an Excel workbook. Each function should reside on a separate worksheet within the workbook.
GOOGLEFINANCE Expressions and Return Values
The Sheets’ GOOGLEFINANCE function allows a similarly formatted return set as the Excel’s STOCKHISTORY function reviewed in the preceding section. However, the syntax for generating return sets is slightly different with the GOOGLEFINANCE function than with the STOCKHISTORY function.
It is important to be able to generate similarly formatted return sets through different data providers. No single data provider, such as Google, Microsoft, or some other data provider can guarantee one hundred percent coverage of all financial instruments all the time. Knowing how to populate return sets with any of several different vendors allows you to track down gaps, return sets with incorrect values, and temporary outages, which can have unknown durations from minutes to weeks or longer.
The expressions in the two tables below embed GOOGLEFINANCE expressions in query function wrappers. This design feature is to return just values for Date, Open, and Close values from the underlying source website (Google Finance).
- The nested GOOGLEFINANCE expression with the “all” parameter returns six columns of data (namely, Date and OHLCV values).
- Three-column value sets are returned from the outer query function with names of Date, Open, and Close.
- Some ticker names in the first table have NASDAQ as an exchange prefix. For many tickers, the Google Finance function can automatically return the correct historical values without an exchange prefix, but a prefix is required for tickers AAPB through GGLL in the following table.
- Notice that the earliest possible start date is specified as date(1902,1,1). This is one year after the earliest possible start date for the STOCKHISTORY function. On the other hand, it serves a similar purpose — namely, in my experience, returning the first date for which Google Finance has real trading data for the ticker, without you having to look it up in advance so you can precisely specify the first trading date for each ticker in your project.
| =query(googlefinance("NASDAQ:AAPB", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("NASDAQ:AMUU", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("NASDAQ:AMZU", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("NASDAQ:AVGG", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("NASDAQ:BABX", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("NASDAQ:CONL", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("NASDAQ:CRWL", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("NASDAQ:GGLL", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("LLYX", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("METU", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("MSFU", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("MSTU", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("MUU", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("NVDL", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("PTIR", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("SNXX", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("TSLL", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("AAPL", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("AMD", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("AMZN", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("AVGO", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| * See this post's Data Issues section. |
| =query(googlefinance("COIN", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("CRWD", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("GOOGL", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("LLY", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("META", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("MSFT", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("MSTR", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("MU", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("NVDA", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("PLTR", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("SNDK", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
| =query(googlefinance("TSLA", "all", date(1902,1,1), date(2026,4,30)), "select Col1, Col2, Col5 label Col5 'Close'") |
As with the STOCKHISTORY function, it is often a best practice to include one GOOGLEFINANCE function per worksheet in a Sheets workbook for a project. It is also recommended that you name the tab for each worksheet after the ticker for the GOOGLEFINANCE expression that populates the worksheet with historical prices. This practice enforces a rule of one ticker per worksheet.
The following three screenshots show excerpted contents for the AAPB, AMUU, and TSLA tickers from a GOOGLEFINANCE function in a Sheets workbook just like the final screenshots in the preceding section show the same content for the same tickers for the STOCKHISTORY function in an Excel workbook.
- Notice that the formula bar in each of the following screenshots is filled with a GOOGLEFINANCE function. The function for each screen shot has a distinct ticker in its formula.
- The first nine rows of each screenshot has the column headers followed by the first eight rows of returned date, open, and close values.
- The actual return values for each ticker below has identical column values to the corresponding three screenshots at the end of the preceding section.
Data Issues
In compiling the data for this post, I encountered two data
issues – one for the GOOGLEFINANCE function and another for the STOCKHISTORY function. Both issues were related to the source website
for each function type, but the details for each issue were unique.
GOOGLEFINANCE Issue: Missing Data for BABA
The GOOGLEFINANCE function was unable to return values for
the BABA ticker. When attempting to
extract the historical data, the function simply threw an #N/A error stating
that the query returned no data. To
resolve this, I with the assistance of Google Gemini, attempted approximately a
dozen different syntax configurations. Every single variation failed. Because the exact same approach worked
perfectly for the other tickers, and because so many different syntax formulas
were tested, it became clear that the problem was a web server issue on
Google's end rather than a syntax error on our side.
STOCKHISTORY Issue: Incorrect Data Returned for AVL
The second issue involved the STOCKHISTORY function’s
failure to return a valid dataset for the AVL ticker — widely referenced online
as the single‑stock ETF for Broadcom (AVGO).
The following screenshot shows an excerpt of the GOOGLEFINANCE
ticker’s return set for the AVL ticker.
The next screenshot shows the STOCKHISTORY ticker’s return
set for the AVL ticker. Notice that AVL’s
first trading date from the STOCKHISTORY
function is nearly 17 years earlier than
the GOOGLEFINANCE result. When I
crosschecked these early dates and prices with Yahoo Finance, Yahoo matched
GOOGLEFINANCE — not STOCKHISTORY.
Important notice: STOCKHISTORY obtains its historical
price data from MSN Money / MSN
Finance rather than from LSEG Data & Analytics as reported in some earlier
blog posts, such as “How
to Populate a Trading Database with Refinitiv, Excel, and SQL Server”, “How
to Populate a Trading Database with Refinitiv, Excel, and SQL Server—Update 2”,
and “A
Model to Lock-in Gains When Trading Single-stock ETFs”. This distinction
matters because MSN Money maintains its own ticker history and mapping rules.
As a result, STOCKHISTORY may return outdated or legacy data for tickers that
have been recycled or previously assigned to unrelated companies. This issue did not surface in prior
applications of the STOCKHISTORY
function in this blog
These were the only two data issues that I encountered when
compiling data for this post. The reason
for showing them to you is so that you can understand how GOOGLEFINANCE and
STOCKHISTORY can complement each other when you are collecting historical price
data for a large set of tickers.
Concluding Comments
You are invited to visit the Security Trading
Analytics blog to discover follow-up posts to the current post as well as
fresh posts for demonstrating security trading analytics techniques. Also, you can discover two techniques for navigating
prior posts at “A
Blog for Traders Who Like to Analyze Their Trades and Data Analysts Who Like to
Trade” that may be useful for your security trading or security analysis
projects.
Comments
Post a Comment