A Multi-Decade Framework for Comparing Ticker Prices from Two Spreadsheet Data Providers
This post presents a framework for collecting daily historical ticker prices from two spreadsheet-based data providers over multiple decades for six tickers. The providers are the GOOGLEFINANCE function in Google Sheets and the STOCKHISTORY function in Microsoft Excel. Subsequent posts will track and analyze price trends for the six tickers as well as illustrate how to use multi-decade historical prices for evaluating a collection of buy/sell models, such as the Proper Order and Gain Lock-In (POGL) model for programmatically specifying trade entry and exit dates for tickers.
See two appendixes to this post for titles and links to prior posts covering both data providers and the POGL model.
Tickers and Spreadsheet Expressions Tracked in This Post
- CAT - Caterpillar Inc.
- World’s largest maker of construction and mining equipment
- Highly sensitive to macro cycles, commodities, and infrastructure spending
- JPM - JP Morgan Chase & Co.
- Largest U.S. bank by assets
- Core exposure to credit cycles, interest rates, and financial regulation
- LLY - Eli Lilly and Company
- Mega cap biopharma company developing biologic and small molecule therapeutics across diabetes, obesity, immunology, and oncology
- Low cyclicality, high R&D intensity
- MSFT - Microsoft Corporation
- Global enterprise software and cloud leader through Microsoft 365 and Azure, respectively
- High margin, recurring revenue
- NVDA – NVIDIA Corporation
- Dominant GPU and AI compute provider
- Hyper growth, high volatility
- SHW – The Sherwin-Williams Company
- Market leader in coatings and paints
- Tied to residential and commercial construction
You can use the GOOGLEFINANCE expressions in the following
table to import historical price data for each of the six tickers into Google
Sheets. Notice that there are two functions
inside each expression. Each expression
has a GOOGLEFINANCE function nested within a QUERY function.
The GOOGLEFINANCE function returns a multi-column dataset
for date, open, high, low, close, and volume in a tabular format. These values are returned from the Google Finance site.
- The first function argument, such as “CAT” and “SHW”, designates the ticker for which to return values.
- The second function argument in the GOOGLEFINANCE examples specifies the return of all six parameter values for each date for the ticker specified in the first function argument.
- The third and fourth function arguments designate the start and end dates, respectively.
- The start date in the examples within the following table represents the earliest possible start date (date(1902,1,1)) that the GOOGLEFINANCE function will accept, which was determined by trial and error. When a ticker has its first trading date after the start date, then GOOGLEFINANCE automatically returns values as of that subsequent date.
- The examples in this post all specify a fixed end date of date(2026,7,31). This convention forces GOOGLEFINANCE to return just through that date whenever the GOOGLEFINANCE function is recalculated. Changing the fourth parameter allows the return of values through an alternative end date.
- The three extracted columns from the GOOGLEFINANCE return table are date, open, and close.
- An alias of ‘Close’ is assigned to Col5.
| GOOGLEFINANCE Expressions for Tickers with Long-term Dates | |
| Ticker | Historical Data Expressions |
| CAT | =query(googlefinance("CAT", "all", date(1902,1,1), date(2026,7,31)), "select Col1, Col2, Col5 label Col5 'Close'") |
| JPM | =query(googlefinance("JPM", "all", date(1902,1,1), date(2026,7,31)), "select Col1, Col2, Col5 label Col5 'Close'") |
| LLY | =query(googlefinance("LLY", "all", date(1902,1,1), date(2026,7,31)), "select Col1, Col2, Col5 label Col5 'Close'") |
| MSFT | =query(googlefinance("MSFT", "all", date(1902,1,1), date(2026,7,31)), "select Col1, Col2, Col5 label Col5 'Close'") |
| NVDA | =query(googlefinance("NVDA", "all", date(1902,1,1), date(2026,7,31)), "select Col1, Col2, Col5 label Col5 'Close'") |
| SHW | =query(googlefinance("SHW", "all", date(1902,1,1), date(2026,7,31)), "select Col1, Col2, Col5 label Col5 'Close'") |
The next table shows STOCKHISTORY expressions for downloading historical prices to Microsoft Excel worksheets for each of the six tickers tracked in this post. Each expression uses the STOCKHISTORY function to return daily OHLCV values.
The STOCKHISTORY function returns a structured array
containing date‑indexed historical values from the same source as MSN Money. The function arguments
specify the ticker symbol, the start and end dates, the interval, and the
selection of columns to include in the returned dataset.
- The first argument, such as "CAT", designates the ticker for which to return historical values.
- The second and third arguments specify the start and end dates.
- The start date in these examples is DATE(1901,1,1), the earliest date STOCKHISTORY will accept, which was determined by trial and error. When a ticker’s first available trading date occurs after the specified start date, STOCKHISTORY automatically returns values beginning on the ticker’s actual first trading date.
- The end date is fixed at DATE(2026,7,31) to ensure that recalculations always return values through the same cutoff date unless the user changes this parameter.
- The fourth through the eighth arguments specify layout through field position for return values.
- The fourth argument value of 0 designates the return of daily prices (alternative argument values are available to returning weekly or monthly values).
- The fifth argument controls whether header labels are included in the returned array, with 1 indicating that headers should be displayed.
- The sixth through the eighth arguments indicate the column name and sequential positioning of the return values:
- 0 → Date
- 2 → Open
- 1 → Close
| STOCKHISTORY Expressions for Tickers with Long-term Dates | |
| Ticker | Historical Data Expressions |
| CAT | =STOCKHISTORY("CAT",DATE(1901,1,1),DATE(2026,7,31),0,1,0,2,1) |
| JPM | =STOCKHISTORY("JPM",DATE(1901,1,1),DATE(2026,7,31),0,1,0,2,1) |
| LLY | =STOCKHISTORY("LLY",DATE(1901,1,1),DATE(2026,7,31),0,1,0,2,1) |
| MSFT | =STOCKHISTORY("MSFT",DATE(1901,1,1),DATE(2026,7,31),0,1,0,2,1) |
| NVDA | =STOCKHISTORY("NVDA",DATE(1901,1,1),DATE(2026,7,31),0,1,0,2,1) |
| SHW | =STOCKHISTORY("SHW",DATE(1901,1,1),DATE(2026,7,31),0,1,0,2,1) |
Excerpted Return Values from GOOGLEFINANCE and STOCKHISTORY
This section presents excerpts for returned values from the GOOGLEFINANCE and STOCKHISTORY functions for downloading historical prices. The purpose of this section is to familiarize you with how data is returned by the functions.
The screenshot below is from a GOOGLEFINANCE workbook titled Raw GOOGLEFINANCE for Long_term 6. There is a separate tab named after each ticker tracked in this post from CAT through SHW.
The worksheet excerpt pictured below is from the CAT tab.
- The first row displays column headers, and the remaining ten rows display Date, Open, and Close column values.
- The first date value in cell A2 (6/1/1972) displays the initial date returned for CAT ticker values. This date corresponds to the IPO date or when the Google Finance site initially started tracking a ticker, whichever is most recent.
- Column D is manually specified and populated.
- Cell D1 contains a string value (Ticker).
- Cell D2 contains the string value CAT because the worksheet values in columns A through C are for the tab with a name of CAT.
- All remaining rows in column D of the CAT tab are populated by manually copying the value in cell D2 to all rows with a date in column A.
- By assigning string ticker values to column D, an analysis can keep track of which rows belong to which ticker when the contents are ultimately imported to a tall SQL Server table for subsequent verification checks and ultimately time series analyses.
The next screenshot shows the final eleven data rows returned by the GOOGLEFINANCE function for the CAT ticker. Notice that the most recent date (7/31/2026) corresponds to the end date in the GOOGLEFINANCE function in cell A1.
The next two screenshots show the first and last eleven rows returned by the GOOGLEFINANCE expression for the MSFT tab of the Raw GOOGLEFINANCE for Long_term 6 workbook. Notice that the GOOGLEFINANCE expression resides in cell A1 of the MSFT tab. The GOOGLEFINANCE expression in cell A1 populates all rows in columns A, B, and C from the first row through the ten thousandth one hundredth seventy-sixth row. As with the CAT tab, you again need to manually populate column D values. In this case, the second through the last row is populated with a value of MSFT.
The next two screenshots display the first and last eleven rows for the CAT ticker from the STOCKHISTORY function in the Raw STOCKHISTORY for long_term 6 workbook.
- Notice that the first Date column value is 7/8/2024. This date is well after the GOOGLEFINANCE first date for the CAT ticker (6/1/1972).
- As a result of the discrepancy between first date values, a Google Search was conducted for the IPO date for the CAT ticker. The search returned an IPO date of 1/2/1962 for the CAT ticker.
- Therefore, neither spreadsheet function returned data starting at CAT’s IPO date, but the first date from the STOCKHISTORY is decades after both the IPO date and the first date value returned by the GOOGLEFINANCE function for the CAT ticker.
- Furthermore, both the first and last eleven rows from the STOCKHISTORY function routinely show #N/A for Open and Close values, which is Excel’s way of indicating the data value is not available. Additionally, Open and Close values are #N/A for nearly all intervening rows.
- These outcomes conclusively demonstrate that STOCKHISTORY is not returning valid data for the CAT ticker.
A worksheet-level investigation confirmed STOCKHISTORY values for the remaining five tickers tracked in this post correspond generally to GOOGLEFINANCE values with two minor exception types.
- First, the initial STOCKHISTORY start close date can be more recent with STOCKHISTORY than with GOOGLEFINANCE. If your research is for long-term close historical trends, then this difference may be an issue. If your research does not depend on long-term close historical trends, then this difference may not matter.
- Second, STOCKHISTORY sometimes reports missing open prices for very early dates that are routinely reported by GOOGLEFINANCE. If your research requires open prices for very early dates, then this may cause you to choose GOOGLEFINANCE rather than STOCKHISTORY for your research project.
The next three screenshots present instances where STOCKHISTORY and GOOGLEFINANCE return values match exactly or just correspond generally. Each screenshot shows a pair of worksheets — the outer worksheet is from GOOGLEFINANCE, and the inner one is from STOCKHISTORY. When viewing these screenshots, you may find it useful to change your browser’s zoom factor to improve the visibility of both worksheets within a pair.
The first screenshot shows the first eleven rows from the GOOGLEFINANCE and STOCKHISTORY functions for the MSFT ticker. The inner worksheet image with the STOCKHISTORY function nests within and overlays the outer worksheet image with the GOOGLEFINANCE function. In this example pair, Date, Open, and Close values are identical across the two worksheets.
The next screenshot compares STOCKHISTORY and GOOGLEFINANCE rows with matching Date column values for the JPM ticker; the Date column values are from 3/17/1980 through 3/28/1980.
- In this excerpt pair, the STOCKHISTORY worksheet shows the rows as the first ten rows with returned values.
- All the close values from the STOCKHISTORY worksheet are within one cent of their corresponding GOOGLEFINANCE worksheet.
- While the close values are either equivalent or nearly equivalent across the two worksheets, the open values from the STOCKHISTORY worksheet are all not available as indicated by their #N/A return values. In contrast, the open values from the GOOGLEFINANCE worksheet are all specified in dollars and cents. The pattern of missing open values for some early dates was observed on the SHW tab as well.
- Also, note that the STOCKHISTORY worksheet displays its return values from the top of its worksheet whereas the corresponding dates in the GOOGLEFINANCE worksheet do not start until the 1974th row. This indicates that the GOOGLEFINANCE function returns historical data that start several years before JPM return values from the STOCKHISTORY function.
The next screenshot is again for GOOGLEFINANCE and STOCKHISTORY return values for the JPM ticker. This screenshot shows the first eleven rows with non-missing open values from the STOCKHISTORY worksheet and the rows with corresponding dates from the GOOGLEFINANCE worksheet. By comparing the open and close values from both worksheets, you can confirm for yourself that the two sets of values are identical across worksheets.
Ingesting Worksheets into Tall SQL Server Tables
As you have seen, GOOGLEFINANCE and STOCKHISTORY functions can return different values – especially for the earliest trading days in ticker historical price series. It is convenient for analysis and modeling purposes to transfer GOOGLEFINANCE and STOCKHISTORY return values to two separate tall SQL Server tables.
There is a two-step process for transferring spreadsheet return values to a tall SQL Server table.
- First, each worksheet with a ticker’s return value set is exported to a csv file. In total, there are six separate csv file sets for GOOGLEFINANCE and STOCKHISTORY functions. For ease of retrieval, the six csv files for each function type are copied to a distinct file path.
- Second, a separate SQL Server script is run to import and clean the csv files with return values for each function.
The code in this post’s Ingestion Appendix reveals the instructions for transferring the worksheet contents for all six tickers into a tall SQL Server table. Because the GOOGLEFINANCE and STOCKHISTORY return historical values have different formatting conventions, the ingestion scripts for each function must be different.
The following screenshot shows the output from the verification check included in the GOOGLEFINANCE ingestion script. As you can see, the number of rows per ticker varies by ticker with NVDA having the fewest rows (6923) and both CAT and SHW having the highest row counts (13661). The min_date column values reflect GOOGLEFINANCE start dates for each ticker, and the max_date column values reflect end dates for each ticker. The end date was arbitrarily selected to July 31, 2026 for the data examined in this post.
The next screenshot shows the output from the verification check included in the STOCKHISTORY ingestion script. As you can see, row counts are generally fewer by ticker for the STOCKHISTORY result set than matching values in the GOOGLEFINANCE result set. This outcome follows from the fact that STOCKHISTORY values do not go back in time as far as GOOGLEFINANCE values.
Please pay special attention to the row count of 50 in the STOCKHISTORY result set. As discussed in the “Excerpted Return Values from GOOGLEFINANCE and STOCKHISTORY” section, CAT ticker from STOCKHISTORY does not point at Caterpillar Inc. In addition to the CAT ticker rows being fewer in the STOCKHISTORY worksheet, many of the return rows are converted to null open and close values by the ingestion program. This dramatically reduces the CAT row count to just 50, and it is yet another bit of evidence that the STOCKHISTORY function does not return rows for Caterpillar, Inc.
Concluding Comments
This post built a working framework for pulling six tickers' full daily price histories from two spreadsheet-based providers and loading them into strongly typed SQL Server tables for downstream analysis. You can readily adapt this framework to your own requirements by changing the number and the specific tickers tracked in the framework. It is likely that you will also want to change the most recent date through which historical prices are collected.
Regarding the six tickers tracked in this post, here are
some key findings.
- For the five tickers where STOCKHISTORY returned valid data, its Close values matched GOOGLEFINANCE — often exactly and never differing by more than a penny.
- The two providers part ways mainly at the edges: GOOGLEFINANCE often reaches further back in time, and STOCKHISTORY is more prone to missing open prices for the earliest available dates.
- For CAT, that gap became outright failure — STOCKHISTORY simply did not return valid Caterpillar data. It has been my observation from prior research that both GOOGLEFINANCE and STOCKHISTORY functions can occasionally fail for different tickers, which is why cross-verifying historical data across both providers is a prudent practice for quantitative analysis.
Selected Prior Posts on GOOGLEFINANCE and/or STOCKHISTORY Functions Appendix
- GOOLEFINANCE Function in Google Sheets Can Download Historical Data via CSV Files
- Historical Price Use Cases for the GoogleFinance Function in Google Sheets
- Building Thirty-four Ticker Historical Price Datasets with Excel’s STOCKHISTORY and Sheets’ GOOGLEFINANCE Functions
- 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
Selected Prior Posts on POGL Model Appendix
T-SQL Automated Ingestion and Verification Scripts Appendix
To facilitate downstream backtesting and comparative analysis, historical daily price records exported from spreadsheet functions must be loaded into tall, strongly typed SQL Server tables. Because CSV files originating from GOOGLEFINANCE (Google Sheets) and STOCKHISTORY (Microsoft Excel) exhibit different formatting quirks—such as currency symbols, commas, and Excel formula error strings (#N/A)—separate T-SQL scripts process each provider dataset.
Both scripts follow a consistent architectural pattern:
- Staging Schema: Standardized raw string ingestion using a temporary staging table (dbo.stockhistory_stage).
- Dynamic Ingestion Loop: Sequential iteration across localized CSV files using dynamic bulk insert commands.
- Robust Data Scrubbing: Type casting via try_convert, carriage return/line-feed trimming (char(10) and char(13)), and string sanitization.
- Verification Scorecard: Summary aggregate checks detailing row counts and date boundaries per ticker.
Appendix A.1: GOOGLEFINANCE Ingestion Script (_gs)
/* dynamic ingestion of googlefinance csv files into a tall
sql table ver_2
author: rick
dobson
purpose: load
googlefinance csv exports, convert date/numeric fields directly,
populate
dbo.stockhistory_clean_gs,
and
verify row counts as well as first and last dates per ticker.
*/
use securitytradinganalytics;
go
---------------------------------------------------------
-- 1. recreate staging table
---------------------------------------------------------
if object_id('dbo.stockhistory_stage', 'U') is not null
drop table
dbo.stockhistory_stage;
create table dbo.stockhistory_stage
(
[date] varchar(50),
[open] varchar(50),
[close] varchar(50),
[ticker]
varchar(50)
);
---------------------------------------------------------
-- 2. recreate clean table for google finance (_gs)
---------------------------------------------------------
if object_id('dbo.stockhistory_clean_gs', 'U') is not null
drop table
dbo.stockhistory_clean_gs;
create table dbo.stockhistory_clean_gs
(
[date] date
not null,
[open] decimal(18,4) not null,
[close] decimal(18,4) not null,
[ticker]
varchar(20) not null,
constraint
pk_stockhistory_clean_gs primary key clustered ([ticker], [date])
);
go
---------------------------------------------------------
-- 3. configure folder path and tickers
---------------------------------------------------------
declare @folder_path varchar(255);
set @folder_path = 'C:\Users\User\Documents\Long_term 6
Ticker Set\from_GOOGLEFINANCE\';
if object_id('tempdb..#tickers') is not null
drop table
#tickers;
create table #tickers
(
ticker
varchar(20) not null
);
insert into #tickers (ticker)
values
('CAT'), ('JPM'),
('LLY'),
('MSFT'),
('NVDA'), ('SHW');
---------------------------------------------------------
-- 4. iterate through cursor and process files
---------------------------------------------------------
declare @ticker varchar(20);
declare @file_path varchar(500);
declare @sql nvarchar(max);
if cursor_status('global', 'file_cursor') >= -1
begin
close
file_cursor;
deallocate
file_cursor;
end;
declare file_cursor cursor for
select ticker from #tickers;
open file_cursor;
fetch next from file_cursor into @ticker;
while @@fetch_status = 0
begin
truncate table
dbo.stockhistory_stage;
set @file_path =
@folder_path + @ticker + '.csv';
set @sql = '
bulk insert
dbo.stockhistory_stage
from ' +
nchar(39) + @file_path + nchar(39) + '
with
(
firstrow = 2,
fieldterminator = '','',
rowterminator
= ''0x0a''
);';
exec
sp_executesql @sql;
---------------------------------------------------------
-- 4. direct
insertion of clean rows
---------------------------------------------------------
insert into
dbo.stockhistory_clean_gs ([date], [open], [close], [ticker])
select
try_convert(date, s.[date]) as [date],
try_convert(decimal(18,4), s.[open]) as [open],
try_convert(decimal(18,4), s.[close]) as [close],
upper(rtrim(ltrim(replace(replace(s.[ticker], char(13), ''), char(10),
'')))) as [ticker]
from
dbo.stockhistory_stage s
where
try_convert(date, s.[date]) is not null
and try_convert(decimal(18,4), s.[open]) is
not null
and
try_convert(decimal(18,4), s.[close]) is not null;
fetch next from
file_cursor into @ticker;
end;
close file_cursor;
deallocate file_cursor;
go
---------------------------------------------------------
-- 5. verification check
---------------------------------------------------------
select
ticker,
count(*) as
row_count,
min([date]) as
min_date,
max([date]) as
max_date
from dbo.stockhistory_clean_gs
group by ticker
order by ticker;
Appendix A.2: Excel STOCKHISTORY Ingestion Script (_xl)
The STOCKHISTORY exports often include currency symbols ($), formatting commas, whitespace, or formula error strings such as #N/A. This script incorporates a Common Table Expression (scrubbed_stage) to strip formatting artifacts. Additionally, it implements a lightweight while loop driven by #tickers to iterate through input files without creating cursor memory state.
/* dynamic ingestion of stockhistory csv files into a tall
sql table ver_13
author: rick
dobson
purpose: load csv
files from excel stockhistory export, filter out headers,
clean
string formatting elements ($ symbols, spaces, commas),
populate
stockhistory_clean_xl, and verify row counts as well as
first and
last dates for each ticker.
*/
use securitytradinganalytics;
go
---------------------------------------------------------
-- 1. recreate staging table
---------------------------------------------------------
if object_id('dbo.stockhistory_stage', 'U') is not null
drop table
dbo.stockhistory_stage;
create table dbo.stockhistory_stage
(
[date] varchar(50),
[open] varchar(50),
[close] varchar(50),
[ticker]
varchar(50)
);
---------------------------------------------------------
-- 2. recreate clean table for stockhistory (xl)
---------------------------------------------------------
if object_id('dbo.stockhistory_clean_xl', 'U') is not null
drop table
dbo.stockhistory_clean_xl;
create table dbo.stockhistory_clean_xl
(
[date] date
not null,
[open] decimal(18,4) not null,
[close] decimal(18,4) not null,
[ticker]
varchar(20) not null,
constraint
pk_stockhistory_clean_xl primary key clustered ([ticker], [date])
);
go
---------------------------------------------------------
-- 3. configure source folder path and tickers
---------------------------------------------------------
declare @folder_path varchar(255);
set @folder_path = 'C:\Users\User\Documents\Long_term 6
Ticker Set\from_STOCKHISTORY\';
if object_id('tempdb..#tickers') is not null
drop table
#tickers;
create table #tickers
(
ticker
varchar(20) not null
);
insert into #tickers (ticker)
values
('CAT'), ('JPM'),
('LLY'), ('MSFT'), ('NVDA'), ('SHW');
---------------------------------------------------------
-- 4. iterate through tickers with a WHILE loop
---------------------------------------------------------
declare @ticker varchar(20);
declare @file_path varchar(500);
declare @sql nvarchar(max);
select @ticker = min(ticker)
from #tickers;
while @ticker is not null
begin
truncate table
dbo.stockhistory_stage;
set @file_path =
@folder_path + @ticker + '.csv';
set @sql = '
bulk insert
dbo.stockhistory_stage
from ' +
nchar(39) + @file_path + nchar(39) + '
with
(
firstrow = 2,
fieldterminator = '','',
rowterminator
= ''0x0a''
);';
exec
sp_executesql @sql;
---------------------------------------------------------
-- clean,
transform, and insert into permanent table
---------------------------------------------------------
with
scrubbed_stage as (
select
try_convert(date, [date]) as [clean_date],
replace(replace(replace(nullif(ltrim(rtrim([open])), ''), '$', ''), ',',
''), ' ', '') as [raw_open],
replace(replace(replace(nullif(ltrim(rtrim([close])), ''), '$', ''),
',', ''), ' ', '') as [raw_close],
upper(rtrim(ltrim(replace(replace([ticker], char(13), ''), char(10),
'')))) as [clean_ticker]
from
dbo.stockhistory_stage
)
insert into
dbo.stockhistory_clean_xl ([date], [open], [close], [ticker])
select
[clean_date],
try_convert(decimal(18,4), [raw_open]),
try_convert(decimal(18,4), [raw_close]),
[clean_ticker]
from
scrubbed_stage
where
[clean_date]
is not null
and
try_convert(decimal(18,4), [raw_open]) is not null
and
try_convert(decimal(18,4), [raw_close]) is not null;
select @ticker =
min(ticker)
from #tickers
where ticker >
@ticker;
end;
go
---------------------------------------------------------
-- 5. revised verification and row audit scorecard
---------------------------------------------------------
print '------------------------------------------------';
print ' DATA
PIPELINE SCORECARD (XL) ';
print '------------------------------------------------';
select
[ticker],
count(*) as
total_rows,
min([date]) as
first_date,
max([date]) as
last_date
from dbo.stockhistory_clean_xl
group by [ticker]
order by [ticker];
Comments
Post a Comment