Stooq is a valuable resource for downloading daily historical price and volume data for financial securities. In this article, we will explore how to use Stooq to import this data into SQL Server. We will cover the Stooq user interface, the T-SQL code for transferring Stooq data, and how to confirm the validity of the transferred data.

Why Use Stooq?

Stooq provides an alternative to manual data downloading from Yahoo Finance or using the Pandas datareader with the Yahoo Finance API. Unlike other data providers, Stooq offers historical stock price and volume data for free. While Yahoo Finance’s manual interface only allows downloading data for one symbol at a time, Stooq allows you to download data for over 11,000 security symbols in a single download.

Using the Stooq User Interface

To access Stooq, simply navigate to the Stooq website and enter the following address in your browser: “stooq.com/db/h”. On the Stooq site, select the “Historical Data” option and choose the “Daily” period. You can then download zipped CSV files containing the historical price and volume data for the symbols you need.

Transferring Stooq Data to SQL Server

Once you have downloaded the necessary CSV files from Stooq, you can use T-SQL code to import the data into SQL Server. First, create a table in your SQL Server database to store the data. You can use the following code as a template:

USE YourDatabaseName;

DROP TABLE IF EXISTS dbo.StooqData;

CREATE TABLE dbo.StooqData (
    Symbol NVARCHAR(10),
    Date DATE,
    Open FLOAT,
    High FLOAT,
    Low FLOAT,
    Close FLOAT,
    Volume BIGINT
);

BULK INSERT dbo.StooqData
FROM 'C:\Path\To\Your\Downloaded\File.csv'
WITH (
    FORMAT = 'CSV',
    FIRSTROW = 2
);

Replace “YourDatabaseName” with the name of your SQL Server database and “C:\Path\To\Your\Downloaded\File.csv” with the path to your downloaded CSV file. This code will create a table called “StooqData” with columns for the symbol, date, open price, high price, low price, close price, and volume. The BULK INSERT statement will transfer the data from the CSV file into the table.

Confirming the Validity of the Data

After importing the data into SQL Server, it’s important to verify its validity. You can run queries to check if the data has been successfully loaded and if all symbols have the same number of rows. For example, you can use the following query to display the distinct symbols in the table:

SELECT DISTINCT Symbol FROM YourDatabaseName.dbo.StooqData;

This query will return a list of all distinct symbols in the “StooqData” table. You can also count the number of rows per symbol to ensure consistency:

SELECT Symbol, COUNT(*) AS NumberOfRows
FROM YourDatabaseName.dbo.StooqData
GROUP BY Symbol;

This query will display the symbol and the number of rows for each symbol in the table. By comparing the results, you can ensure that all symbols have the same number of rows.

Conclusion

Using Stooq to import historical price and volume data into SQL Server is a convenient and cost-effective solution. By following the steps outlined in this article, you can easily download and transfer data from Stooq to SQL Server, and verify its validity. This data can then be used for various financial analysis and reporting purposes.

Remember to regularly update your data to ensure you have the most up-to-date information for your analysis. Happy data importing!

Article Last Updated: 2021-09-02