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

Concocting trading strategies is an exciting and intellectually rewarding activity for many self‑directed traders and trading analysts.  But before you risk capital or recommend a strategy to others, it’s highly beneficial to test your ideas against reliable historical data.  A trading database or sometimes several, depending on your research goals, is the foundation for evaluating which strategies return consistent outcomes across one or several trading environments.  This post demonstrates a practical, hands‑on framework for building a trading database using Refinitiv data (now part of LSEG Data & Analytics), Excel, and SQL Server to populate a trading database.

LSEG Data & Analytics is one of the largest global data providers. Their data is used by banks, hedge funds, asset managers, academic researchers and the STOCKHISTORY function in Excel.  Therefore, self-directed traders and analysts who use Excel have access to a high quality data source through the STOCKHISTORY function.  Review a section within the “An Introduction to Retrieving Historical Security Prices with GOOGLEFINANCE and STOCKHISTORY” blog post for a selection of samples that can get you started with Excel’s STOCKHISTORY function.  Those who value quantitative analysis can perform basic statistics and inspect excerpts of their STOCKHISTORY function output via charts.

This Microsoft Support site page describes a built-in Excel feature for saving STOCKHISTORY function output on a worksheet to a csv file, and this blog post demonstrates how to take advantage of that feature.  Excel users can take advantage of the built-in Excel feature for returning STOCKHISTORY function output in a format that is useful for ingesting the historical price data into SQL Server tables.  Self-directed traders, trading analysts, and quant modelers can evaluate the performance of various models as well as perform parametric studies to discover optimal parameters for trading strategies and models.

Populating an Excel Workbook File with Historical Prices

The following screenshot presents a worksheet named SPY in an Excel workbook file (xl_source_SPY_GOOGL_MU_SNDK.xlsx).

  • The cursor rests in cell A1.  The STOCKHISTORY function for populating columns A through E appears in a textbox to the right of function selector.

  •  The STOCKHISTORY function returns historical data based on its parameter values.  For the screenshot below,

  • The first parameter names the ticker for a security, such as SPY, which designates an ETF based on the S & P 500 index.

    • The second and third parameters specify the beginning and ending trading dates returned by the function.  The parameters in the example below return historical prices from the first trading day in January 2023 through the final trading day in February 2026.

    • The fourth parameter value (0) designates the return of daily, as opposed to weekly or monthly, data.

    • The fifth parameter designates the display of column headers for returned values.The sixth through the tenth parameter values are codes indicating the order of column values (Date, Open, High, Low, Close).

  •  A thorough summary of all the STOCKHISTORY function parameters and the values that they can take is available at this Microsoft Support page.

Notice that column F shows SPY for all returned data rows.  You can achieve this with a simple string value for the ticker value.  Alternatively, this custom expression (=RIGHT(CELL("filename",A1), LEN(CELL("filename",A1)) - FIND("]", CELL("filename",A1)))) will automatically return the worksheet name for the current row.  After you have populated cell F2 with either technique you, you can copy the contents of cell F2 for all returned values with dates in column A.

 

 

The preceding screenshot is for the SPY ticker.  The remaining tabs are respectively for the GOOGL, MU, and SNDK tickers.  The GOOGL and MU tabs simply update the first STOCKHISTORY because SPY, GOOGL, and MU traded continuously from the first trading day in January 2023 through the last trading day in February 2026.  STOCKHISTORY returns a complete and uninterrupted time series for each of them.

In contrast, the SNDK ticker traded over a much shorter duration.  SanDisk was acquired by Western Digital in 2016, and the original SNDK ticker disappeared at that time.  In February 2025, Western Digital separated into two companies.  The flash‑memory business re‑emerged as SanDisk, trading again under the SNDK ticker.  SanDisk’s listing announcement confirms that the new SNDK began exchange trading on February 24, 2025.

However, both LSEG Data & Analytics (via Excel’s STOCKHISTORY function) and Finviz provide earlier prices beginning on February 13, 2025.  These values represent pre‑listing indicative pricing, which is common for spin‑offs and relistings and reflects institutional valuation activity before the official exchange debut.  STOCKHISTORY also returns a placeholder row for February 12, 2025.  This placeholder row denotes the date before the first historical data row for SanDisk.  In fact, whenever the LSEG Data & Analytics metadata says there is a start date before the actual start of historical data, STOCKHISTORY returns a placeholder row.

Saving Ticker Prices from Excel to CSV Files

After populating and configuring an Excel workbook with STOCKHISTORY function expressions, you can save the contents of each worksheet within a workbook to a csv file.  If your workbook contains four worksheets, as in the preceding section, then you can generate four csv files so that each worksheet has its own corresponding file.  This approach is advantageous because many applications – including SQL Server – can readily ingest csv files.  Archiving the worksheet data in SQL Server allows you to preserve the prices returned by the STOCKHISTORY function in an environment that is more suitable for advanced analytical and modeling applications.

The xl_source_SPY_GOOGL_MU_SNDK workbook file is saved in the location shown by the File Explorer excerpt below (C:\Users\User\Documents\csv_from_xl_spy_googl_mu_sndk).

 

 

After selecting the SPY tab in the workbook, you can save a csv file with the downloaded data on the SPY tab with a File, Save As command.  If you use a filename of SPY and a file type of CSV (MS-DOS) (*.csv), your File Explorer view of the csv_from_xl_spy_googl_mu_sndk folder will look like the following screenshot.  Notice the target folder contains the original Excel workbook tab and one Microsoft Excel Comma Separated Values File.

 

 

If you successively select the tabs for each of the three remaining tickers and save each of them as a Microsoft Excel Comma Separated Values File, your File Explorer view of the csv_from_xl_spy_googl_mu_sndk folder will contain one Microsoft Excel Worksheet file for the original workbook with data for all four tickers and four Microsoft Excel Comma Separated Values files – one for each of the four tickers.

 

 

 

You can open the SPY.csv file (or any of the other csv files) in Notepad by right clicking the csv file in File Explorer and choosing Edit in Notepad.  The row of column headers and the first ten data rows within SPY.csv  appear below.  You can display any subset of rows  to view by scrolling through the rows in the file with Notepad.

 

 

The layout of the GOOGL and MU csv files are identical to the layout for SPY.csv.  That is, the first data row has a date of 1/3/2023 and the final data row has a date of 2/27/2026.  The layout for the SNDK ticker is different.  Here is a Notepad view of the column header row followed by the first ten data rows for the SNDK.csv file.  Notice the layout of the csv file for this ticker is different than the preceding three tickers.

  • The first data row after the column header row shows #N/A values for Open, High, Low, and Close values.  This placeholder row is inserted by the STOCKHISTORY function to indicate the row before first row of data values.

  • The function inserts the placeholder row for a ticker when its start date parameter is prior to the date for the first row of values within LSEG Data & Analytics.  The start date for the STOCKHISTORY function on the SNDK tab is “2025-02-1”.  In general, any arbitrary date before the first historical value can serve as a start date in the STOCKHISTORY function.

  • The placeholder row is returned one trading day before the first set of valid return values.

 

 

Importing and Validating csv Files into SQL Server

Using Excel to prepare csv files with historical prices makes SQL Server ingestion much simpler than with other alternatives.  The reason for this is that Excel makes it easy to position the ticker directly in every row of a spreadsheet with historical prices.  Additionally, you can do this with a formula-based approach that you can copy down a column. Therefore, each row in the file contains both the ticker and its associated price history.    Embedding the ticker as a column produces cleaner, more reliable T‑SQL workflows.  Finally, the STOCKHISTORY function supplies high quality data from LSEG Data & Analytics, which delivers high value, trusted data integrated into workflow solutions, serving over 40,000 customers in 190 markets.

The following script in bold format presents a four-step process for importing the csv files returned by Excel into SQL Server and performing a basic verification check for the transferred data.  The process relies on two key tables dbo.stockhistory_stage and dbo.stockhistory_clean.  The dbo.stockhistory_stage table is populated with a bulk insert statement.  The dbo.stockhistory_clean table is populated by an insert statement with a where clause with try_convert functions to exclude rows with open, high, low, or close values that cannot be converted to a decimal(18,2) format.  The processing for the staging and clean tables repeats for each of the csv files that you need to process.

For the data handled in this post the process is repeated four times – one repetition for each of the tickers tracked in this blog post.

  • The use statement at the top of the script designates a default database (securitytradinganlytics) for the script.  Feel free to change the database name to match a database to which you have access in your computing environment.

  • Steps 1 and 2 create fresh copies of the dbo.stockhistory_stage and dbo.stockhistory_clean tables.

  • Step 3 is the core importing step for the script.  This step has four code segments within it that perform the same task for each of the csv files created in this tip.  Each of the code segments commence with a ticker comment that indicates the ticker for which data is processed in that segment and continues through the processing steps for the staging and clean tables.  Step 3 accepts the placeholder row for the SNDK ticker into the staging table, but the step excludes the placeholder row from entry into the clean table because it contains no historical data.

  • Step 4 performs various calculations to assist you with validating the data inserted into SQL Server from the csv files created from Excel worksheets based on the STOCKHISTORY function.

 

use securitytradinganalytics

go

 

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

-- 1. setup the staging table

-- we use varchar for all columns here to avoid import errors from the csv.

-- data types will be corrected during the transfer to the clean table.

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

drop table if exists dbo.stockhistory_stage;

create table dbo.stockhistory_stage (

[date] varchar(20),

[open] varchar(50),

high varchar(50),

low varchar(50),

[close] varchar(50),

ticker varchar(20)

);

 

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

-- 2. setup the clean "tall" table

-- adding a primary key on ticker and date ensures no duplicate records.

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

drop table if exists dbo.stockhistory_clean;

create table dbo.stockhistory_clean (

ticker varchar(20) not null,

[date] date not null,

[open] decimal(18,2),

high decimal(18,2),

low decimal(18,2),

[close] decimal(18,2),

constraint pk_stockhistory primary key (ticker, [date])

);

 

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

-- 3. repeating etl process for each ticker

-- we truncate the stage table between each file, but append to the clean table.

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

 

-- ticker: spy

truncate table dbo.stockhistory_stage;

bulk insert dbo.stockhistory_stage

from 'c:\users\user\documents\csv_from_xl_spy_googl_mu_sndk\spy.csv'

with ( fieldterminator = ',', firstrow = 2 );

 

insert into dbo.stockhistory_clean (ticker, [date], [open], high, low, [close])

select

ticker,

convert(date, [date]),

    try_convert(decimal(18,2), [open]),

    try_convert(decimal(18,2), high),

    try_convert(decimal(18,2), low),

    try_convert(decimal(18,2), [close])

from dbo.stockhistory_stage

where try_convert(decimal(18,2), [close]) is not null;

 

-- ticker: googl

truncate table dbo.stockhistory_stage;

bulk insert dbo.stockhistory_stage

from 'c:\users\user\documents\csv_from_xl_spy_googl_mu_sndk\googl.csv'

with ( fieldterminator = ',', firstrow = 2 );

 

insert into dbo.stockhistory_clean (ticker, [date], [open], high, low, [close])

select

ticker,

convert(date, [date]),

    try_convert(decimal(18,2), [open]),

    try_convert(decimal(18,2), high),

    try_convert(decimal(18,2), low),

    try_convert(decimal(18,2), [close])

from dbo.stockhistory_stage

where try_convert(decimal(18,2), [close]) is not null;

 

-- ticker: mu

truncate table dbo.stockhistory_stage;

bulk insert dbo.stockhistory_stage

from 'c:\users\user\documents\csv_from_xl_spy_googl_mu_sndk\mu.csv'

with ( fieldterminator = ',', firstrow = 2 );

 

insert into dbo.stockhistory_clean (ticker, [date], [open], high, low, [close])

select

ticker,

convert(date, [date]),

    try_convert(decimal(18,2), [open]),

try_convert(decimal(18,2), high),

    try_convert(decimal(18,2), low),

    try_convert(decimal(18,2), [close])

from dbo.stockhistory_stage

where try_convert(decimal(18,2), [close]) is not null;

 

-- ticker: sndk

truncate table dbo.stockhistory_stage;

bulk insert dbo.stockhistory_stage

from 'c:\users\user\documents\csv_from_xl_spy_googl_mu_sndk\sndk.csv'

with ( fieldterminator = ',', firstrow = 2 );

 

insert into dbo.stockhistory_clean (ticker, [date], [open], high, low, [close])

select

ticker,

convert(date, [date]),

    try_convert(decimal(18,2), [open]),

    try_convert(decimal(18,2), high),

    try_convert(decimal(18,2), low),

    try_convert(decimal(18,2), [close])

from dbo.stockhistory_stage

where try_convert(decimal(18,2), [close]) is not null;

 

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

-- 4. verification

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

 

-- check total record count

select count(*) as overall_count from dbo.stockhistory_clean;

 

-- check record counts by ticker to verify import success

select

ticker,

count(*) as record_count,

min([date]) as first_date,

max([date]) as last_date

from dbo.stockhistory_clean

group by ticker

order by ticker;

 

-- cross-check: first date value for each ticker

select ticker, date as first_date from(

select top 1 * from dbo.stockhistory_clean where ticker = 'GOOGL'

union

select top 1 * from dbo.stockhistory_clean where ticker = 'MU'

union

select top 1 * from dbo.stockhistory_clean where ticker = 'SNDK'

union

select top 1 * from dbo.stockhistory_clean where ticker = 'SPY'

) concatenated_rows;

 

The following three result set windows from SQL Server Management Services  provide a means of validating the values returned for the four tickers tracked in this blog post.

  • The first result set indicates the overall count of rows in the clean table is 2634.

  • The second result set reveals the count, first date, and last date for each of the tickers.

    • The first_date column values for GOOGL, MU, and SPY are 2023-01-03 per ticker.  While these three tickers have historical prices going back well before this date, 2023-01-03 indicates the beginning of a price growth cycle.  SNDK does not commence its historical price series until 2025-02-13.  Recall that this STOCKHISTORY function attempted to collect data starting on 2025-02-01, but LSEG Data & Analytics did not have historical prices for SNDK until the first_date column value in the second result set.

    •  As a result of the starting dates and a common end date of 2026-02-27, there are 791 historical prices each for GOOGL, MU, and SPY.  SNDK has just 261 historical price values.  The sum of record counts across all four tickers (791+791+791+261) matches the overall record count displayed in the first result set.

    • The third result set shows the first_date values by ticker retrieved from a concatenation of result sets from four select statements instead of the group by function in the select statement for the second result set.  Although the two result sets have different query statements for the first_date values by ticker, the dates match perfectly.

    • The query statements for each of the three result sets displayed below illustrate a typical approach to validating data.  The approach is to derive values in two different ways to determine if the results match across query statements.  In our case, the data are provided by a high quality data provider so no errors are detected.

 

 

Concluding Comments

If you wish to empirically evaluate alternative trading strategies or search for hidden relationships in historical price series, you will find a trading database very convenient to have.  This post illustrates a step-by-step approach for building a trading database for any collection of tickers in SQL Server based on high quality data from the Excel STOCKHISTORY function.  Multiple prior posts in this blog present and discuss different approaches to building a trading database based on alternative sources, such as the GOOGLEFINANCE function, Tiingo, and Yahoo Finance.


What You Learned in This Post

  • [Using the STOCKHISTORY Function]: How to populate an Excel Workbook with historical security prices.
  • [Copying Excel Worksheets to csv Files]: Creating and populating csv files from Excel Worksheet tabs.
  • [Importing a csv File into SQL Server]: A review of a T-SQL script for importing csv files into a SQL Server table.

Comments

Post a Comment

Popular posts from this blog

About the Security Trading Analytics Blog This blog aims to empower site visitors who seek examples and demonstrations of quantitative methods for tracking and projecting security prices.  Another goal of the blog is to present methods and resources that are practical and useful for individuals who want to become better traders and investors with the help of quant methods. Quantitative methods may include, but are not limited to: ·               Analysis of historical security prices ·               Technical analysis of trends and indicators ·               Models for when to buy and sell securities implemented with o    SQL Server o    Google Sheets with the GOOGLEFINANCE Function o     Tiingo o     Excel o   ...
Read more
Image
  ETF Price Puzzles: Examples Showing Tiingo’s Raw and Adjusted Series Don’t Always Differ As I was preparing an analysis of a buy‑sell model for several major ETFs, I stumbled across some unexpected puzzles in the price data.   Tiingo provides both raw and adjusted price series, but for certain ETFs the two are identical — even across known split dates. This post explains how I discovered these puzzles in selected ETF ticker prices from Tiingo.   The post also describes why the puzzles matter, and how you can adjust for them when performing ETF analyses with Tiingo data. Where I Discovered Tiingo ETF Price Puzzles I was analyzing a model based on EMA proper orders for prices when I first noticed ETF price puzzles.   Any ETF price series can have multiple EMAs depending on their period lengths.   No matter what the period length for an EMA, it is always dependent on its underlying price data.   For any trading date, EMAs with shorter period lengths...
Read more
Image
  Building and Backtesting a Buy-Sell Model with Close Prices and EMAs Buy-Sell models are among my favorite analytical techniques because they simulate what traders do on a regular basis – namely, buy and sell securities.  However, no amount of backtesting is enough to guarantee how well a buy-sell model reflects actual market performance.  This is because publicly traded financial security prices are driven by diverse factors that can change over time.  Nevertheless, buy-sell models can offer useful guidelines for buying and selling securities when current market conditions are similar to previously backtested market scenarios. EMAs are another of my favorite analytical tools for modeling the behavior of security prices.  EMA is an abbreviation for an exponential moving average.  However, an EMA is really not an averaging technique.  Instead, EMAs are a smoothing technique for time series data.  The period, or more precisely period length, of an...
Read more