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 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.
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:
Place 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.
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
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.
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