How to Import Stock Data into Your SQL Server database

As a trader, you would want to analyze the performance of stocks to invest on the right stock. In this article we will learn how to use Microsoft SQL Server database to store historical stock data which we can later analyze using TSQL scripts or Excel macros to assist investment plans.

Prepare the data:

There are several online finance portals that provide historical data to public. For example, in Google finance, you can get historical data for a ticker in a tabular format. Though there is no inbuilt option in Google finance to export the historical data as Excel file, as shown in this image, you can easily copy the data from Google finance and paste it in your Excel sheet.Prepare The Data

Prepare the database:

Update the path of mdf, ldf file and then run the script1 to create a new database on your SQL instance. The new database will be named as “STOCK”. You can change it to any name as per your requirement. If you change the database name in script1, do not forget to change it in the script2 too.

Script1:

USE [master]
GO

CREATE DATABASE [STOCK] CONTAINMENT = NONE ON PRIMARY (
    NAME = N'STOCK'
    ,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\STOCK.mdf'
    ,SIZE = 8192 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 65536 KB
    ) LOG ON (
    NAME = N'STOCK_log'
    ,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\STOCK_log.ldf'
    ,SIZE = 8192 KB
    ,MAXSIZE = 2048 GB
    ,FILEGROWTH = 65536 KB
    )
GO

Let’s import historical data:

Import Stock Data Into Your SQL Server DatabasePlace all historical data files into a folder. Update the folder path in script2 and execute it from a new query window. The script will read each file from the selected folder and historical data from each file will fall into a table “STOCKTABLE”. The script uses OPENROWSET technique to read data from Excel files. Database on a corrupt SQL Server will not allow you to read data inside it. So it is always a good practice to back up your database at regular intervals. As we are importing multiple Excel files into our SQL database, after each import, take a backup.

Script2:

DECLARE @rootfolder NVARCHAR(500) = 'C:\STOCK\' --Change the path 
DECLARE @filestable TABLE (
    cFile NVARCHAR(500)
    ,c2 INT
    ,c3 INT
    )

INSERT INTO @filestable
EXEC xp_DirTree @rootfolder
    ,1
    ,1

SELECT cFile
FROM @filestable
WHERE c3 = 1

DECLARE stockcur CURSOR
FOR
SELECT cFile
FROM @filestable
WHERE c3 = 1

DECLARE @stockfile AS VARCHAR(200)

OPEN stockcur

FETCH NEXT
FROM stockcur
INTO @stockfile

DECLARE @fcount AS INT
DECLARE @cmd NVARCHAR(4000)

SET @fcount = 0

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @fcount = @fcount + 1

    IF @fcount = 1
    BEGIN
        SET @cmd = 'SELECT * INTO [STOCK].[dbo].[STOCKTABLE]
FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database=' + @rootfolder + @stockfile + ';HDR=YES'', ''SELECT * FROM [Sheet1$]'')'

        PRINT @cmd

        EXEC (@cmd)
    END

    IF @fcount > 1
    BEGIN
        SET @cmd = 'INSERT INTO [STOCK].[dbo].[STOCKTABLE]
SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database=' + @rootfolder + @stockfile + ';HDR=YES'', ''SELECT * FROM [Sheet1$]'')'

        PRINT @cmd

        EXEC (@cmd)
    END

    FETCH NEXT
    FROM stockcur
    INTO @stockfile
END

CLOSE stockcur

DEALLOCATE stockcur

Alternate methods:

The method explained in this article is the easy way to import historical data which is spread across multiple Excel files. If you have all historical data in a single Excel file, you can easily import it using SQL Server Management Studio. You should use the option “Import Data” and you can see this when you right click on a database and point to task.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover Outlook and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.