How to Populate a Trading Database with Refinitiv, Excel, and SQL Server—Update 1
The Excel STOCKHISTORY function downloads historical market data—such as stock prices and index values—directly into Excel worksheets from LSEG Data & Analytics (formerly Refinitiv). The “How to Populate a Trading Database with Refinitiv, Excel, and SQL Server” post summarizes and demonstrates a three-step process for building a data source inside SQL Server for historical stock prices and/or indexes. The three steps are as follows.
- Populate one worksheet tab per ticker with historical prices using Excel’s STOCKHISTORY function.
- Save each worksheet tab as a CSV file—one CSV per ticker.
- Bulk insert the CSV files into a tall SQL Server table indexed by ticker and date.
While the three steps worked when properly executed, it was
found that Excel formatting issues could lead to processing errors if not
manually resolved. Additionally, the
T-SQL code in the third step required substantial editing for each new set of
tickers in subsequent projects.
The new code for the third step in the current post programmatically handles Excel formatting issues and substantially reduces the requirements for processing new ticker sets in subsequent projects. For example, all the new script requires is the Windows folder for the csv files output by the second step and the ticker names for the csv files.
A T-SQL Script for Dynamically Downloading and Reformatting Prices
The following script obsoletes the T-SQL script from the
third step in the “How
to Populate a Trading Database with Refinitiv, Excel, and SQL Server” post. In addition to the new functionality described
above, this code is heavily documented with comments. These comments make it easy to re-use the
T-SQL script with different tickers in different folders from one project to
the next.
The code returns a fresh copy of the stockhistory_clean table for the csv files specified in the @folder_path local variable (C:\Users\User\Documents\csv_from_xlsource_04_01_2026\) and ticker names in the #ticker temp table. You will need to update the value for the @folder_path local variable and the rows in the #temp table whenever you work with a new ticker set or even the same ticker set for a different historical timeframe. The timeframe is the first trading day per ticker through the last trading day in March 2026.
/********************************************************************************************
dynamic
ingestion of multiple csv files into a tall sql table
author: Rick
Dobson
purpose:
recreate stockhistory_stage and stockhistory_clean tables. then dynamically
load a
set of csv files (date, open, close, ticker) into a tall sql server table.
how to use for
each new project:
1. update
@folder_path to the folder containing your csv files.
2. update the
insert into #tickers list with the tickers for this project.
3. run the
entire script. it will recreate tables and ingest all csvs automatically.
********************************************************************************************/
---------------------------------------------
use SecurityTradingAnalytics;
go
-- 1. recreate staging table (all varchar)
---------------------------------------------
if object_id('dbo.stockhistory_stage') 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 (typed, final tall table)
---------------------------------------------------------
if object_id('dbo.stockhistory_clean') is not null
drop table
dbo.stockhistory_clean;
create table dbo.stockhistory_clean
(
[date] date not null,
[open] float not null,
[close] float
not null,
[ticker] varchar(20) not null,
constraint
pk_stockhistory_clean primary key clustered (ticker, date)
);
---------------------------------------------------------
-- 3. user inputs for each new project
---------------------------------------------------------
-- 3a. folder containing csv files (include trailing
backslash)
declare @folder_path varchar(500) =
'C:\Users\User\Documents\csv_from_xlsource_04_01_2026\';
-- 3b. list of tickers for this project
if object_id('tempdb..#tickers') is not null drop table
#tickers;
create table #tickers (ticker varchar(20));
insert into #tickers (ticker)
values ('SPY'),
('SPXL'),
('QQQ'),
('TQQQ'),
('GOOGL'),
('MSFT'),
('NVDA'),
('PLTR'),
('MU'),
('MUU');
---------------------------------------------------------
-- 4. build control table with full file paths
---------------------------------------------------------
if object_id('tempdb..#ticker_files') is not null drop
table #ticker_files;
create table #ticker_files
(
ticker varchar(20),
file_path varchar(500)
);
insert into #ticker_files (ticker, file_path)
select
t.ticker,
@folder_path +
t.ticker + '.csv'
from #tickers t;
---------------------------------------------------------
-- 5. loop through files and ingest each csv
---------------------------------------------------------
declare @ticker varchar(20);
declare @file_path varchar(500);
declare @sql nvarchar(max);
declare file_cursor cursor for
select ticker,
file_path
from
#ticker_files;
open file_cursor;
fetch next from file_cursor into @ticker, @file_path;
while @@fetch_status = 0
begin
print 'loading
' + @ticker + ' from ' + @file_path;
-----------------------------------------------------
-- 5a. clear
staging table
-----------------------------------------------------
truncate table
dbo.stockhistory_stage;
-----------------------------------------------------
-- 5b. dynamic
bulk insert
-----------------------------------------------------
set @sql = N'
bulk insert
dbo.stockhistory_stage
from ''' +
@file_path + '''
with
(
firstrow = 2,
fieldterminator = '','',
rowterminator = ''0x0a'',
tablock
);
';
exec
sys.sp_executesql @sql;
-----------------------------------------------------
-- 5c. insert
into clean table with type conversion
--
-- important reminder:
-- excel often exports numbers with:
-- - currency symbols ($656.97)
-- - thousand separators (1,234.56)
-- - leading/trailing spaces
-- - text formatting
--
-- these cause try_convert(float, ...) to
return null.
--
-- the replace() functions below strip out:
-- - '$' symbols
-- - ',' thousand separators
--
-- this makes the ingestion robust against
excel formatting.
-----------------------------------------------------
insert into
dbo.stockhistory_clean (date, [open], [close], ticker)
select
try_convert(date, [date]),
try_convert(float, replace(replace([open], '$', ''), ',', '')),
try_convert(float, replace(replace([close], '$', ''), ',', '')),
@ticker
from
dbo.stockhistory_stage
where
try_convert(date, [date]) is not null
and
try_convert(float, replace(replace([open], '$', ''), ',', '')) is not null
and
try_convert(float, replace(replace([close], '$', ''), ',', '')) is not null;
fetch next from
file_cursor into @ticker, @file_path;
end
close file_cursor;
deallocate file_cursor;
---------------------------------------------------------
-- 6. validation queries
---------------------------------------------------------
print 'ingestion complete. row counts by ticker:';
select ticker, count(*) as row_count
from dbo.stockhistory_clean
group by ticker
order by ticker;
select min(date) as min_date, max(date) as max_date
from dbo.stockhistory_clean;
Here are validation query results from the preceding script
for the tickers and timeframe over which historical values were collected.
Next Steps
- Run the three-step process for the tickers described in this post to acquaint yourself with how to implement the process.
- Run the process for another set of tickers over a different timeframe that is of special interest to you.
Comments
Post a Comment