How to Populate a Trading Database with Refinitiv, Excel, and SQL Server—Update 2

Computer‑based trading analytics requires clean, reliable historical price data.  Whether you are evaluating trading models, comparing performance across securities, or projecting future price paths, the first prerequisite is a trustworthy method for downloading and preparing historical prices from a quality data vendor.

This post is the third entry in a series on transferring historical price data from Excel’s STOCKHISTORY function into SQL Server for downstream analysis.  The earlier posts—“How to Populate a Trading Database with Refinitiv, Excel, and SQL Server” and “How to Populate a Trading Database with Refinitiv, Excel, and SQL Server—Update 1”—introduced the workflow and addressed several formatting issues that arise when importing STOCKHISTORY output into SQL Server.

Excel’s STOCKHISTORY function retrieves historical prices from the London Stock Exchange Group (LSEG), formerly Refinitiv.  In most cases, LSEG returns clean, exchange‑listed trading dates.  However, some securities have corporate‑action or pre‑listing dates associated with grants, conversions, or private‑market activity.  When these dates precede the first public trading day, STOCKHISTORY returns one or more rows with date values having price field values of “#N/A”.  In this post, I refer to these early, non‑trading dates as pre‑exchange dates.

These “#N/A” rows create downstream problems because they do not map to numeric types.  If left uncorrected, they can cause errors when loading data into SQL Server, Python, or any application expecting numeric OHLC values.  The Update 1 blog post addressed several formatting issues—such as Excel returning price fields as character values with leading dollar signs—and relied on manual deletion of rows containing “#N/A”.

This Update 2 post removes the manual step.  It presents a revised T‑SQL script that programmatically omits rows with “#N/A” price values before inserting data into SQL Server.  This eliminates the need to hand‑edit worksheets and ensures that your ingestion pipeline remains reproducible, scalable, and free of data‑type errors.

This update also includes several enhancements for readability and maintainability, including simplified dynamic SQL expressions using nchar(39) to handle embedded quotation marks more cleanly.

How Data Flows from Worksheet → CSV for SQL Server Ingestion

The Update 1 post summarizes the process of migrating downloaded historical prices with the STOCKHISTORY function to SQL Server as having three steps.

• Download historical prices into Excel using STOCKHISTORY.
• Export each worksheet as a CSV file named after its ticker.
• Ingest the CSV files into SQL Server using a staging table and a clean table.

As mentioned in this post’s introduction, the historical price values are downloaded into an Excel workbook file.  The STOCKHISTORY function extracts date, open, and close values for one ticker per worksheet in the workbook file.  Each worksheet is named after the ticker whose data populates it.  The ticker column of each worksheet is populated based on an Excel expression that returns the worksheet name, which is presented in the initial post in this series of posts.

In the second step, each worksheet is saved as a csv file whose name matches its source worksheet.

The third step copies the csv files to a SQL Server table.  There are two main parts to the third step.  First, each csv is copied to a clean version of a staging table named dbo.stockhistory_stage.  Second, the staging table values are cleaned programmatically and saved in dbo.stockhistory_clean.

Here is a worksheet excerpt  for the NVDA ticker.  It has no pre-exchange dates.

Here is a similar excerpt for the PLTR ticker.  Notice that the dates from 9/14/2020 through 9/29/2020 have open and close prices of #N/A; these dates are examples of pre-exchange dates for a ticker.  The first date with a legitimate price value is 9/30/2020.

One way to remove the pre-exchange dates with invalid exchange-traded dates is to alter the STOCKHISTORY function expression to =STOCKHISTORY("PLTR","2020-09-30","2026-03-31",0,1,0,2,1).  When you do this manual step, you can create a fresh worksheet for the PLTR ticker with no invalid open and close values as in the screenshot below.

The Programmatic Solution for SQL Server Ingestion and Validation

The main purpose for this post is to illustrate how to programmatically remove rows with pre-exchange dates having invalid price values (#N/A).  This removes the need to manually prescreen the data to find the first row for a ticker with valid price values.  The following script shows one way to achieve the objective so that you no longer need to prescreen STOCKHISTORY downloads so the dates inserted into SQL Server contain only valid price values.

The script’s logic is divided into five major steps.

• Step 1 creates a fresh version of the staging table (dbo.stockhistory_stage) which serves as a landing zone for the csv files saved for worksheets in the source Excel workbook file.
• Step 2 creates a fresh version of a clean data table (dbo.stockhistory_clean) that eliminates pre-exchange rows with illegitimate price values as well as formatting conventions from Excel that do not belong in a SQL Server table.
• Step 3 configures a local variable (@folder_path varchar) and a temp table (#tickers). The local variable is for the folder path where you saved the csv files from the Excel worksheets. The rows in the #tickers table are populated with ticker names for the Excel worksheets. You will need to repopulate the local variable and the values in the temp table for each new set of data that you load into SQL Server.
• Step 4 iterates through a cursor (file_cursor) that is populated successively by the csv files saved to the folder assigned to the @folder_path local variable. The file names assigned to the cursor are the ticker column values in the #ticker temp table.
• The code at the end of the introduction to step 4 concludes by opening file_cursor, fetching the first csv file, opening a while loop, and initializing a code block with the begin keyword.
• The last three lines in step 4c are
• the end keyword to mark the end of the code block begun by the begin keyword in the step 4 introduction, 
• a close statement to close file_cursor, and 
• the deallocate file_cursor statement removes the cursor reference and destroys the cursor definition.
• Step 4a truncates the rows in the  dbo.stockhistory_stage table so that each csv file populates an empty table.
• Step 4b executes a bulk insert statement based on dynamic sql for each pass through the while loop.
• Step 4c 
• converts the Excel date values to SQL Server date values,
• reformats with try_convert and replace functions Excel currency formatting to SQL Server float value for historical price values, and 
• omits rows with a where clause historical prices, such as #N/A, that cannot be converted to float values.

/* dynamic ingestion of multiple csv files into a tall sql table ver_4 author: rick dobson purpose: load csv files and filter out pre-exchange #n/a rows. */ use securitytradinganalytics; go --------------------------------------------- -- 1. recreate staging table --------------------------------------------- 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 --------------------------------------------------------- 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 configuration --------------------------------------------------------- declare @folder_path varchar(500) = 'c:\users\user\documents\csv_from_xlsource_04_01_2026\'; 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. loop through files --------------------------------------------------------- declare @ticker varchar(20); -- needed for the fetch declare @file_path varchar(500); -- needed for the fetch declare @sql nvarchar(max); -- needed for the bulk insert declare file_cursor cursor for select ticker, @folder_path + ticker + '.csv' from #tickers; open file_cursor; fetch next from file_cursor into @ticker, @file_path; while @@fetch_status = 0 begin -- 4a. clear staging table for the current ticker truncate table dbo.stockhistory_stage; -- 4b. dynamic bulk insert -- nchar(39) is a single quote. this avoids nesting errors. set @sql = ' bulk insert dbo.stockhistory_stage from ' + nchar(39) + @file_path + nchar(39) + ' with ( firstrow = 2, fieldterminator = ' + nchar(39) + ',' + nchar(39) + ', rowterminator = ' + nchar(39) + '0x0a' + nchar(39) + ', tablock );'; exec sp_executesql @sql; -- 4c. filters out non-numeric values, such as #N/A, for historic prices 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; --------------------------------------------------------- -- 5. validation --------------------------------------------------------- select ticker, count(*) as row_count, min(date) as first_trade, max(date) as last_trade from dbo.stockhistory_clean group by ticker order by ticker;

The operational part of the preceding script ends at the conclusion of step 4.  Step 5 merely displays the count of rows along with the first and first_trade and last_trade dates per ticker.  In this post, you viewed a worksheet named PLTR_wofix that included pre-exchange dates having #N/A entries for open and close values.  The validation code in step 5 permits the verification that script can remove  pre-exchange dates from the csv file for a worksheet without any manual intervention by the application user.

Whenever you run this code for a new dataset, you will need to specify both the folder containing csv files and the ticker names for which you want data.  The preceding script names tickers for csv files that are known to be valid.  However, you can amend the ticker list in step 3 as follows to validate the row count as well as the first and last trade dates for the programmatically cleaned PLTR_wofix worksheet contents.

---------------------------------------------------------

-- 3. user configuration

---------------------------------------------------------

declare @folder_path varchar(500) = 'c:\users\user\documents\csv_from_xlsource_04_01_2026\';

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'), ('PLTR_wofix');

 

The following screenshot shows the result set from step 5.  Notice that PLTR and PLTR_wofix tickers have identical row_count, first_trade, and last_trade values.  This outcome confirms that the automatic cleaning in the script eliminates the need for manually cleaning csv files based on worksheet tabs.